mysql 与C#【转】

1.连接:
1.安装Microsoft ODBC.net。
2.安装MySQL的ODBC驱动程序。
2.解决方案管理中添加引用Microsoft.Data.Odbc.dll(1.0.3300)
3.代码中增加引用
using Microsoft.Data.Odbc;
4.编写代码
string MyConString = "DRIVER={MySQL ODBC 3.51 Driver};" +
"SERVER=localhost;" +
"DATABASE=samp_db;" +
"UID=root;" +
"PASSWORD=;" +
"OPTION=3";
//Connect to MySQL using Connector/ODBC
OdbcConnection MyConnection = new OdbcConnection(MyConString);
MyConnection.Open();
Console.WriteLine("/n !!! success, connected successfully !!!/n");
MyConnection.Close();

5详细例程

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=root;" +
                "PASSWORD=1;" +
                "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)
                    {
                        Console.WriteLine("Data:" + MyDataReader.GetInt32(0) + " " +
                        MyDataReader.GetString(1) + " " +
                        MyDataReader.GetInt64(2)); //Supported only by Connector/ODBC 3.51
                    }
                    else
                    {
                        Console.WriteLine("Data:" + MyDataReader.GetInt32(0) + " " +
                        MyDataReader.GetString(1) + " " +
                        MyDataReader.GetInt32(2)); //BIGINTs not supported by Connector/ODBC
                    }
                }

               //Close reader
                MyDataReader.Close();
                //
                OdbcDataAdapter adp = new OdbcDataAdapter(MyCommand);
                DataSet ds = new DataSet();
                adp.Fill(ds);
                Console.WriteLine("OdbcDataAdapter count :" + ds.Tables.Count + " rows :" + ds.Tables[0].Rows.Count);
               //close connection
                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");
                }
            }
            Console.Read();
        }
    }
}

 

5 Adapter

           MySQLConnection conn = null;
            conn = new MySQLConnection(new MySQLConnectionString("localhost", "inv", "root", "831025").AsString); 
            conn.Open();
            MySQLCommand commn = new MySQLCommand("set names gb2312", conn); 
            commn.ExecuteNonQuery();
            string sql = "select * from exchange "; 
            MySQLDataAdapter mda = new MySQLDataAdapter(sql, conn);

            DataSet ds = new DataSet();
            mda.Fill(ds, "table1");
            this.dataGrid1.DataSource = ds.Tables["table1"];
            conn.Close(); 54ne.com

 

6.Mysql 4.1版本已经支持transaction

 http://remindme.blogbus.com/logs/34155274.html

首先必须将系统自动提交函数置零 :

SET @@AUTOCOMMIT = 0;

查看是否置零:

SELECT @@AUTOCOMMIT;

事务处理例子:

START TRANSACTION;

张三的账户金额＝张三的账户金额－1000；

李四的账户金额＝李四的账户金额＋1000；

COMMIT;