6.7.2.1 将连接器/ODBC 与 ODBC.NET 和 C# 一起使用（C sharp）

骑猪看日落 2023-09-29 11:07 4阅读 0赞

[MySQL :: MySQL Connector/ODBC Developer Guide :: 6.7.2.1 Using Connector/ODBC with ODBC.NET and C\# (C sharp)][MySQL _ MySQL Connector_ODBC Developer Guide _ 6.7.2.1 Using Connector_ODBC with ODBC.NET and C_ _C sharp]

以下示例创建一个表 `my_odbc_net`并演示它在 C\# 中的用法。

/**
     * @sample    : mycon.cs
     * @purpose   : Demo sample for ODBC.NET using Connector/ODBC
     *
     **/
    
    /* build command
     *
     *  csc /t:exe
     *      /out:mycon.exe mycon.cs
     *      /r:Microsoft.Data.Odbc.dll
     */
    
    using Console = System.Console;
    
    using System.Data.Odbc;
    
    namespace myodbc3
    {
        class mycon
        {
            static void Main(string[] args)
            {
                try
                {
                    //Connection string for Connector/ODBC 3.51
                    string MyConString = "DRIVER={MySQL ODBC 3.51 Driver};" +
                      "SERVER=localhost;" +
                      "DATABASE=db;" +
                      "UID=root;" +
                      "PASSWORD=root;" +
                      "OPTION=3";
    
                    //Connect to MySQL using Connector/ODBC
                    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)
                        {
                            //Supported only by Connector/ODBC 3.51
                            Console.WriteLine("Data:" + MyDataReader.GetInt32(0) + " " +
                                              MyDataReader.GetString(1) + " " +
                                              MyDataReader.GetInt64(2));
                        }
                        else
                        {
                            //BIGINTs not supported by Connector/ODBC
                            Console.WriteLine("Data:" + MyDataReader.GetInt32(0) + " " +
                                              MyDataReader.GetString(1) + " " +
                                              MyDataReader.GetInt32(2));
                        }
                    }
    
                    //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");
                    }
                }
            }
        }
    }

/**
     * @sample    : mycon.cs
     * @purpose   : Demo sample for ODBC.NET using Connector/ODBC
     *
     **/
    
    /* 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 Connector/ODBC 3.51
              string MyConString = "DRIVER={MySQL ODBC 3.51 Driver};" +
                "SERVER=localhost;" +
                "DATABASE=test;" +
                "UID=venu;" +
                "PASSWORD=venu;" +
                "OPTION=3";
    
              //Connect to MySQL using Connector/ODBC
              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) {
                    //Supported only by Connector/ODBC 3.51
                    Console.WriteLine("Data:" + MyDataReader.GetInt32(0) + " " +
                                      MyDataReader.GetString(1) + " " +
                                      MyDataReader.GetInt64(2));
                  }
                  else {
                    //BIGINTs not supported by Connector/ODBC
                    Console.WriteLine("Data:" + MyDataReader.GetInt32(0) + " " +
                                      MyDataReader.GetString(1) + " " +
                                      MyDataReader.GetInt32(2));
                  }
                }
    
              //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");
                }
            }
        }
      }
    }

[MySQL _ MySQL Connector_ODBC Developer Guide _ 6.7.2.1 Using Connector_ODBC with ODBC.NET and C_ _C sharp]: https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-examples-programming-net-csharp.html