26.1.20.1. ODBC.NET: CSHARP(C#)

26.1.20.2. ODBC.NET: VB

本节包含一些简单示例，介绍了MyODBC驱动程序与ODBC.NET一起使用的用法。

26.1.20.1. ODBC.NET: CSHARP(C#)

在下面的简单示例中创建了表my_odbc_net，并介绍了它在C#中的使用。

/**
* @sample    : mycon.cs
* @purpose   : Demo sample for ODBC.NET using MyODBC
* @author    : Venu, <venu@mysql.com>
*
* (C) Copyright MySQL AB, 1995-2004
*
**/

/* build command
*
*  csc /t:exe
*      /out:mycon.exe mycon.cs
*      /r:Microsoft.Data.Odbc.dll
*/

using Console = System.Console;
using Microsoft.Data.Odbc;

namespace myodbc3
{
class mycon
{
  static void Main(string[] args)
  {
    try
    {
      //Connection string for MyODBC 2.50
      /*string MyConString = "DRIVER={MySQL};" +
                           "SERVER=localhost;" +
                           "DATABASE=test;" +
                           "UID=venu;" +
                           "PASSWORD=venu;" +
                           "OPTION=3";
      */
      //Connection string for MyODBC 3.51
      string MyConString = "DRIVER={MySQL ODBC 3.51 Driver};" +
                           "SERVER=localhost;" +
                           "DATABASE=test;" +
                           "UID=venu;" +
                           "PASSWORD=venu;" +
                           "OPTION=3";

      //Connect to MySQL using MyODBC
      OdbcConnection MyConnection = new OdbcConnection(MyConString);
      MyConnection.Open();

      Console.WriteLine("\n !!! success, connected successfully !!!\n");

      //Display connection information
      Console.WriteLine("Connection Information:");
      Console.WriteLine("\tConnection String:" + MyConnection.ConnectionString);
      Console.WriteLine("\tConnection Timeout:" + MyConnection.ConnectionTimeout);
      Console.WriteLine("\tDatabase:" + MyConnection.Database);
      Console.WriteLine("\tDataSource:" + MyConnection.DataSource);
      Console.WriteLine("\tDriver:" + MyConnection.Driver);
      Console.WriteLine("\tServerVersion:" + MyConnection.ServerVersion);

      //Create a sample table
      OdbcCommand MyCommand = new OdbcCommand("DROP TABLE IF EXISTS my_odbc_net",MyConnection);
      MyCommand.ExecuteNonQuery();
      MyCommand.CommandText = "CREATE TABLE my_odbc_net(id int, name varchar(20), idb bigint)";
      MyCommand.ExecuteNonQuery();

      //Insert
      MyCommand.CommandText = "INSERT INTO my_odbc_net VALUES(10,'venu', 300)";
      Console.WriteLine("INSERT, Total rows affected:" + MyCommand.ExecuteNonQuery());;

      //Insert
      MyCommand.CommandText = "INSERT INTO my_odbc_net VALUES(20,'mysql',400)";
      Console.WriteLine("INSERT, Total rows affected:" + MyCommand.ExecuteNonQuery());

      //Insert
      MyCommand.CommandText = "INSERT INTO my_odbc_net VALUES(20,'mysql',500)";
      Console.WriteLine("INSERT, Total rows affected:" + MyCommand.ExecuteNonQuery());

      //Update
      MyCommand.CommandText = "UPDATE my_odbc_net SET id=999 WHERE id=20";
      Console.WriteLine("Update, Total rows affected:" + MyCommand.ExecuteNonQuery());

      //COUNT(*)
      MyCommand.CommandText = "SELECT COUNT(*) as TRows FROM my_odbc_net";
      Console.WriteLine("Total Rows:" + MyCommand.ExecuteScalar());

      //Fetch
      MyCommand.CommandText = "SELECT * FROM my_odbc_net";
      OdbcDataReader MyDataReader;
      MyDataReader =  MyCommand.ExecuteReader();
      while (MyDataReader.Read())
      {
       if(string.Compare(MyConnection.Driver,"myodbc3.dll") == 0) {
         Console.WriteLine("Data:" + MyDataReader.GetInt32(0) + " " +
                                     MyDataReader.GetString(1) + " " +
                                     MyDataReader.GetInt64(2)); //Supported only by MyODBC 3.51
       }
       else {
         Console.WriteLine("Data:" + MyDataReader.GetInt32(0) + " " +
                                     MyDataReader.GetString(1) + " " +
                                     MyDataReader.GetInt32(2)); //BIGINTs not supported by MyODBC
       }
      }

      //Close all resources
      MyDataReader.Close();
      MyConnection.Close();
    }
    catch (OdbcException MyOdbcException)//Catch any ODBC exception ..
    {
      for (int i=0; i < MyOdbcException.Errors.Count; i++)
      {
        Console.Write("ERROR #" + i + "\n" +
          "Message: " + MyOdbcException.Errors[i].Message + "\n" +
          "Native: " + MyOdbcException.Errors[i].NativeError.ToString() + "\n" +
          "Source: " + MyOdbcException.Errors[i].Source + "\n" +
          "SQL: " + MyOdbcException.Errors[i].SQLState + "\n");
      }
    }
  }
}
}

26.1.20.2. ODBC.NET: VB

在下面的简单示例中创建了表my_vb_net，并介绍了它在VB中的用法。

' @sample    : myvb.vb
' @purpose   : Demo sample for ODBC.NET using MyODBC
' @author    : Venu, <venu@mysql.com>
'
' (C) Copyright MySQL AB, 1995-2004
'
'

'
' build command
'
' vbc /target:exe
'     /out:myvb.exe
'     /r:Microsoft.Data.Odbc.dll
'     /r:System.dll
'     /r:System.Data.dll
'

Imports Microsoft.Data.Odbc
Imports System

Module myvb
  Sub Main()
      Try

          'MyODBC 3.51 connection string
          Dim MyConString As String = "DRIVER={MySQL ODBC 3.51 Driver};" & _
                         "SERVER=localhost;" & _
                         "DATABASE=test;" & _
                         "UID=venu;" & _
                         "PASSWORD=venu;" & _
                         "OPTION=3;"

          'Connection
          Dim MyConnection As New OdbcConnection(MyConString)
          MyConnection.Open()

          Console.WriteLine ("Connection State::" & MyConnection.State.ToString)

          'Drop
          Console.WriteLine ("Dropping table")
          Dim MyCommand As New OdbcCommand()
          MyCommand.Connection = MyConnection
          MyCommand.CommandText = "DROP TABLE IF EXISTS my_vb_net"
          MyCommand.ExecuteNonQuery()

          'Create
          Console.WriteLine ("Creating....")
          MyCommand.CommandText = "CREATE TABLE my_vb_net(id int, name varchar(30))"
          MyCommand.ExecuteNonQuery()

          'Insert
          MyCommand.CommandText = "INSERT INTO my_vb_net VALUES(10,'venu')"
          Console.WriteLine("INSERT, Total rows affected:" & MyCommand.ExecuteNonQuery())

          'Insert
          MyCommand.CommandText = "INSERT INTO my_vb_net VALUES(20,'mysql')"
          Console.WriteLine("INSERT, Total rows affected:" & MyCommand.ExecuteNonQuery())

          'Insert
          MyCommand.CommandText = "INSERT INTO my_vb_net VALUES(20,'mysql')"
          Console.WriteLine("INSERT, Total rows affected:" & MyCommand.ExecuteNonQuery())

          'Insert
          MyCommand.CommandText = "INSERT INTO my_vb_net(id) VALUES(30)"
          Console.WriteLine("INSERT, Total rows affected:" & MyCommand.ExecuteNonQuery())

          'Update
          MyCommand.CommandText = "UPDATE my_vb_net SET id=999 WHERE id=20"
          Console.WriteLine("Update, Total rows affected:" & MyCommand.ExecuteNonQuery())

          'COUNT(*)
          MyCommand.CommandText = "SELECT COUNT(*) as TRows FROM my_vb_net"
          Console.WriteLine("Total Rows:" & MyCommand.ExecuteScalar())

          'Select
          Console.WriteLine ("Select * FROM my_vb_net")
          MyCommand.CommandText = "SELECT * FROM my_vb_net"
          Dim MyDataReader As OdbcDataReader
          MyDataReader = MyCommand.ExecuteReader
          While MyDataReader.Read
              If MyDataReader("name") Is DBNull.Value Then
                  Console.WriteLine ("id = " & CStr(MyDataReader("id")) & "  name = " & _
                    "NULL")
              Else
                  Console.WriteLine ("id = " & CStr(MyDataReader("id")) & "  name = " & _
                                        CStr(MyDataReader("name")))
              End If
          End While

      'Catch ODBC Exception
      Catch MyOdbcException As OdbcException
          Dim i As Integer
          Console.WriteLine (MyOdbcException.ToString)

      'Catch program exception
      Catch MyException As Exception
          Console.WriteLine (MyException.ToString)
  End Try
  End Sub
End Module