C#-連接MYSQL數(shù)據(jù)庫(kù)的3種方法及示例

1、C#連接MYSQL 數(shù)據(jù)庫(kù)的方法及示例 連接MYSQL數(shù)據(jù)庫(kù)的方法及示例 方法一：usi ng MySql.Datausi ng MySql.Data.MySqlClie nt;其他操作跟SQL是差不多，無(wú)非就是前綴變成MySql 了 .補(bǔ)充：下面是連接字符串，供參考.MySqlC onnection con = newMySql.Data.MySqlClie nt.MySqlCo nn ectio n( Database=testdb;DataSource=localhost;User ld=db;Password=apple;charset=utf8); con. Ope n();MySq

2、lComma nd cmd = new MySqlComma nd();cmd.C onnection = con;使用 MYSQL 推岀的 MySQL Connector/Net is an ADO.NET driver for MySQL該組件為 MYSQL為ADO.NET 訪(fǎng)問(wèn)MYSQL數(shù)據(jù)庫(kù)設(shè)計(jì)的.NET訪(fǎng)問(wèn)組件。安裝完成該組件后，引用命名空間MySql.Data.MySqlClie nt;使用命令行編譯時(shí)：csc /r:MySql.Data.dll test.cs方法二：通過(guò)ODBC訪(fǎng)問(wèn)MYSQL數(shù)據(jù)庫(kù)訪(fǎng)問(wèn)前要先下載兩個(gè)組件： 和MYSQL的ODBC 驅(qū)動(dòng)(MySQL Connect

3、or/ODBC(MyODBC) driver) 目前為 3.51 版安裝完成后，即可通過(guò)ODBC訪(fǎng)問(wèn)MYSQL數(shù)據(jù)庫(kù)方法三：使用CoreLab 推岀的MYSQL訪(fǎng)問(wèn)組件，面向.NET安裝完成后，引用命名空間：CoreLab.MySql;使用命令編譯時(shí)：csc /r:CoreLab.MySql.dll test.cs以下為訪(fǎng)問(wèn)MYSQL數(shù)據(jù)庫(kù)實(shí)例編譯指令：csc /r:CoreLab.MySql.dll /r:MySql.Data.dll test.csusing System;using System.Net;using System.Text;using CoreLab.MySql;usin

4、g System.Data.Odbc;using MySql.Data.MySqlClie nt;class Conn ectMySqlpublic void Conn ect_CoreLab()stri ng con str = User ld=root;Host=localhost;Database=qi ng;password=qi ng; MySqlC onn ectio n myc n = new MySqlC onn ectio n(c on str);mycn. Ope n();MySqlComma nd mycm = new MySqlComma nd(select * fro

5、m shop,myc n);MySqlDataReader msdr = mycm.ExecuteReader();while(msdr.Read()if (msdr.HasRows)Co nsole.WriteLi ne(msdr.GetStri ng(0);msdr.Close();mycn .Close();public void Conn ect_Odbc()/stri ng MyCo nStr ing =DSN=MySQL;UID=root;PWD=qi ng;string MyConString = DRIVER=MySQL ODBC 3.51 Driver; +SERVER=lo

6、calhost; +DATABASE=test; +UID=root; +P ASSWORD=qi ng; +OPTION=3;OdbcCo nn ection MyC onn = new OdbcCo nn ectio n(MyCo nStri ng);MyCo nn .Ope n();OdbcComma nd mycm = new OdbcComma nd(select * from hello,MyC onn);OdbcDataReader msdr = mycm.ExecuteReader();while(msdr.Read()if (msdr.HasRows)Co nsole.Wri

7、teLi ne(msdr.GetStri ng(0);msdr.Close();MyCo nn .Close();public void Conn ect_Net()stri ng myConn ecti on Stri ng = Database=test;Data Source=localhost;User ld=root;Password=qi ng;MySqlC onn ectio n myc n = new MySqlC onn ectio n(myConn ectio nStrin g); mycn. Ope n();MySqlComma nd mycm = new MySqlCo

8、mma nd(select * from hello,myc n); MySqlDataReader msdr = mycm.ExecuteReader();while(msdr.Read()if (msdr.HasRows)Co nsole.WriteLi ne(msdr.GetStri ng(0);msdr.Close();mycn .Close();public static void Mai n()Conn ectMySql ms = new Co nn ectMySql();ms.C onn ect_CoreLab();ms.C onn ect_Odbc();Conn ect_Net

9、();1 用 MySQLDriverCS 連接 MySQL 數(shù)據(jù)庫(kù)先下載和安裝MySQLDriverCS，地址：http:/sourceforge. net/projects/mysqldrivercs/在安裝文件夾下面找到MySQLDriver.dll，然后將MySQLDriver.dll添加引用到項(xiàng)目中注：我下載的是版本是MySQLDriverCS-n-EasyQueryTools-4.0.1-DotNet2.0.exe using System;using System.Collect ion s.Ge neric;using System.Comp onen tModel;using

10、System.Data;using System.Data.Odbc;using System.Draw ing;using System.Linq;using System.Text;using System.Wi ndows.Forms;usi ng MySQLDriverCS;n amespace mysqlpublic partial class Form1 : Formpublic Form1()In itializeComp onen t(); private void Form1_Load(object sen der, Even tArgs e)MySQLCo nn ectio

11、n conn = null;conn = new MySQLCo nn ectio n(new MySQLCo nn ectio nStri ng(localhost, inv, root, 831025).AsStri ng);conn. Ope n();MySQLComma nd commn = new MySQLComma nd(set names gb2312, conn);com mn .ExecuteN on Query();stri ng sql = select * from excha nge ;MySQLDataAdapter mda = new MySQLDataAdap

12、ter(sql, conn);DataSet ds = new DataSet();mda.Fill(ds, table1);this.dataGrid1.DataSource = ds.Tablestable1;conn .Close();2、通過(guò) ODBC訪(fǎng)問(wèn)mysql數(shù)據(jù)庫(kù):參考： na/com mun ity/Column/63.mspx1. 安裝 Microsoft ODBC.net :我安裝的是 mysql-connector-odbc-3.51.22-win32.msi2. 安裝MDAC 2.7或者更高版本：我安裝的是mdac_typ.exe 2.7簡(jiǎn)體中文版3. 安裝MySQL

13、的ODBC驅(qū)動(dòng)程序：我安裝的是odbc_net.msi4. 管理工具- 數(shù)據(jù)源ODBC - 配置DSN5. 解決方案管理中添加引用Microsoft.Data.Odbc.dll(1.0.3300)6. 代碼中增加引用usi ng Microsoft.Data.Odbc;using System;using System.Collect ion s.Ge neric;using System.Comp onen tModel;using System.Draw ing;using System.Linq;vs2005好像沒(méi)有這個(gè)命名空間，在c#2008下測(cè)試自動(dòng)生成的using System.T

14、ext;using System.Wi ndows.Forms;using Microsoft.Data.Odbc;n amespace mysqlpublic partial class Forml : Formpublic Form1()In itializeComp onen t();private void Form1_Load(object sen der, Even tArgs e)stri ng MyCo nStri ng = DRIVER=MySQL ODBC 3.51 Driver; +SERVER=localhost; +DATABASES nv; +UID=root; +

15、PASSWORD=831025; +OPTION=3;OdbcC onn ecti on MyConnection = new OdbcC onn ectio n(MyCon Stri ng);MyCo nn ectio n. Ope n();Con sole.WriteL in e(n success, conn ected successfully !n ”)； string query = insert into test values( hello, lucas, li);OdbcComma nd cmd = new OdbcComma nd(query, MyConnection);

16、/處理異常：插入重復(fù)記錄有異常trycmd.ExecuteN on Query();catch(Excepti on ex)Con sole.WriteL in e(record duplicate.);fin allycmd.Dispose();/*用 read 方法讀數(shù)據(jù)到 textbox*stri ng tmpl :=n ull;stri ng tmp2 :=n ull;stri ng tmp3 :=n ull;query = select * from test ;OdbcComma nd cmd2 = new OdbcComma nd(query, MyConnection);Odb

17、cDataReader reader = cmd2.ExecuteReader();while (reader.Read()tmpl =:reader0.ToStri ng();tmp2 = reader1.ToStri ng();tmp3 = reader2.ToStri ng();this.textBox1.Text = tmpl + + tmp2 + + tmp3;*/*用 datagridview 控件顯示數(shù)據(jù)表 *string MyConString = DRIVER=MySQL ODBC 3.51 Driver; +SERVER=localhost; + DATABASES nv;

18、 +UID=root; +PASSWORD=831025; +OPTION=3;OdbcC onn ecti on MyConn ecti on = new OdbcC onn ectio n(MyCon Stri ng);OdbcDataAdapter oda = new OdbcDataAdapter(select * from customer , MyConnection);DataSet ds = new DataSet();oda.Fill(ds, employee);this.dataGridViewl.DataSource = ds.Tablesemployee;*/MyCo

19、nn ectio n.Close();文章出處： http:/www.diybl.eom/course/4_webprogram/asp.n et/asp _n etshl/2008429/112011.html 1、用 MySQLDriverCS 連接 MySQL 數(shù)據(jù)庫(kù)先下載和安裝MySQLDriverCS，地址：http:/sourceforge. net/projects/mysqldrivercs/在安裝文件夾下面找到MySQLDriver.dll，然后將MySQLDriver.dll添加引用到項(xiàng)目中注：我下載的是版本是MySQLDriverCS-n-EasyQueryTools-4

20、.0.1-DotNet2.0.exeusing System;using System.Collect ion s.Ge neric;using System.Comp onen tModel;using System.Data;using System.Data.Odbc;using System.Draw ing;using System.Linq;using System.Text;using System.Wi ndows.Forms;usi ng MySQLDriverCS;n amespace mysqlpublic partial class Forml : Formpublic

21、 Form1()In itializeComp onen t();private void Form1_Load(object sen der, Even tArgs e)MySQLCo nn ection conn = null;conn = new MySQLCo nn ectio n(new MySQLCo nn ectio nStri ng(localhost, inv, root, 831025).AsStri ng);conn. Ope n();MySQLComma nd commn = new MySQLComma nd(set names gb2312, conn); com

22、mn .ExecuteN on Query();stri ng sql = select * from excha nge ;MySQLDataAdapter mda = new MySQLDataAdapter(sql, conn);DataSet ds = new DataSet();mda.Fill(ds, tablei);this.dataGridl.DataSource = ds.Tablestable1;conn .Close();2、通過(guò) ODBC訪(fǎng)問(wèn)mysql數(shù)據(jù)庫(kù):參考： na/com mun ity/Column/63.mspx1. 安裝 Microsoft ODBC.ne

23、t :我安裝的是 mysql-connector-odbc-3.51.22-win32.msi2. 安裝MDAC 2.7或者更高版本：我安裝的是mdac_typ.exe 2.7簡(jiǎn)體中文版3. 安裝MySQL的ODBC驅(qū)動(dòng)程序：我安裝的是odbc_net.msi4. 管理工具- 數(shù)據(jù)源ODBC - 配置DSN5. 解決方案管理中添加引用Microsoft.Data.Odbc.dll(1.0.3300)6. 代碼中增加引用usi ng Microsoft.Data.Odbc;using System;using System.Collect ion s.Ge neric;using System.

24、Comp onen tModel;using System.Draw ing;using System.Linq;vs2005好像沒(méi)有這個(gè)命名空間，在c#2008下測(cè)試自動(dòng)生成的using System.Text;using System.Wi ndows.Forms;using Microsoft.Data.Odbc;n amespace mysqlpublic partial class Form1 : Formpublic Form1()In itializeComp onen t(); private void Form1_Load(object sen der, Even tArgs

25、 e)stri ng MyCo nStri ng = DRIVER=MySQL ODBC 3.51 Driver; +SERVER=localhost; +DATABASE=inv; + UID=root; +PASSWORD=831025; +0PTI0N=3;OdbcC onn ecti on MyConnection = new OdbcC onn ectio n(MyCon Stri ng);MyCo nn ectio n. Ope n();Con sole.WriteL in e(n success, conn ected successfully !n ”)；string quer

26、y = insert into test values( hello, lucas, li)；OdbcComma nd cmd = new OdbcComma nd(query, MyConnection);/處理異常：插入重復(fù)記錄有異常trycmd.ExecuteN on Query();catch(Excepti on ex)Con sole.WriteL in e(record duplicate.);fin allycmd.Dispose();囁*用read方法讀數(shù)據(jù)到textbox*string tmpl = null;string tmp2 = null;stri ng tmp3

27、= n ull;query = select * from test ;OdbcComma nd cmd2 = new OdbcComma nd(query, MyConnection);OdbcDataReader reader = cmd2.ExecuteReader();while (reader.Read()tmp1 = reader0.ToStri ng();tmp2 = reader1.ToStri ng();tmp3 = reader2.ToStri ng();this.textBox1.Text = tmp1 + + tmp2 + + tmp3;*/*用 datagridvie

28、w 控件顯示數(shù)據(jù)表 *string MyConString = DRIVER=MySQL ODBC 3.51 Driver; +SERVER=localhost; + DATABASE=inv; + UID=root; +PASSWORD=831025; + 0PTI0N=3;OdbcC onn ecti on MyConn ecti on = new OdbcC onn ectio n(MyCon Stri ng);OdbcDataAdapter oda = new OdbcDataAdapter(select * from customer , MyConnection);DataSet

29、ds = new DataSet();oda.Fill(ds, employee);this.dataGridView1.DataSource = ds.Tablesemployee;*/MyCo nn ectio n.Close();文章出處：http:/www.diybl.eom/course/4webprogram/ netshl/2008429/112011.htmlC#連接mysql數(shù)據(jù)庫(kù)1. 連接：1. 安裝 Microsoft ODBC.net。2. 安裝MySQL的ODBC驅(qū)動(dòng)程序。2. 解決方案管理中添加引用Microsoft.Data.Odbc.dll(1.0.3300)3

30、. 代碼中增加引用using Microsoft.Data.Odbc;4. 編寫(xiě)代碼string MyConString = DRIVER=MySQL ODBC 3.51 Driver; +SERVER=localhost; +DATABASE=samp_db; +UID=root; +PASSWORD+0PTI0N=3;OdbcConnection/Connect to MySQL using Connector/ODBCMyConnection = new OdbcConnection(MyConString);MyConnection.Open();Console.WriteLine(n

31、MyConnection.Close();! success, connected successfully !n);2.全部例程：/* sample : mycon.cs* purpose : Demo sample for ODBC.NET using Connector/ODBC* author : Venu, venu* (C) Copyright MySQL AB, 1995-2003*/* build command* csc /t:exe* /out:mycon.exe mycon.cs* /r:Microsoft.Data.Odbc.dll*/using Console = S

32、ystem.Console;using Microsoft.Data.Odbc;namespace myodbc3class myconstatic void Main(string args)try/Connection string for Connector/ODBC 2.50/*string MyConString = DRIVER=MySQL; +SERVER=localhost; DATABASE=test; UID=venu; + PASSWORD=venu; OPTION=3;*/Connection string string MyConString SERVER=local

33、host;for Connector/ODBC=DRIVER=MySQLDATABASE=test;3.51ODBC 3.51 Driver; +UID=venu; +PASSWORD=venu;0PTI0N=3;/Connect to MySQL using Connector/ODBCOdbcConnection MyConnection = new OdbcConnection(MyConString);MyConnection.Open();Console.WriteLine(n! success, connectedsuccessfully!n);/Display connectio

34、n informationConsole.WriteLine(ConnectionConsole.WriteLine(tConnectionConsole.WriteLine(tConnectionConsole.WriteLine(tDatabase:Console.WriteLine(tDataSource:Information:);String: + MyConnection.ConnectionString);Timeout: + MyConnection.ConnectionTimeout);+ MyConnection.Database);+ MyConnection.DataS

35、ource);Console.WriteLine(tDriver:+ MyConnection.Driver);Console.WriteLine(tServerVersion:+ MyConnection.ServerVersion);/Create a sample tableOdbcCommand MyCommand = new OdbcCommand(DROP TABLE IF EXISTS my_odbc_ net,MyConnection);MyCommand.ExecuteNonQuery();MyCommand.CommandText= CREATE TABLE my_odbc

36、_net(id int, name varchar(20), idb bigint);MyCommand.ExecuteNonQuery();/InsertMyCommand.CommandText=INSERT INTO my_odbc_net VALUES(10,venu, 300);Console.WriteLine(INSERT,Total rows affected: + MyCommand.ExecuteNonQuery();/InsertMyCommand.CommandTextConsole.WriteLine(INSERT,=INSERT INTO my_odbc_net V

37、ALUES(20,mysql,400);Total rows affected:+ MyCommand.ExecuteNonQuery();/InsertMyCommand.CommandText=INSERT INTO my_odbc_net VALUES(20,mysql,500);Console.WriteLine(INSERT,Total rows affected:+ MyCommand.ExecuteNonQuery();/UpdateMyCommand.CommandText=UPDATE my_odbc_netSET id=999 WHERE id=20;Console.Wri

38、teLine(Update,Total rows affected:+ MyCommand.ExecuteNonQuery();/COUNT(*)MyCommand.CommandText= SELECT COUNT(*) as TRows FROM my_odbc_net;Console.WriteLine(Total Rows: + MyCommand.ExecuteScalar();/FetchMyCommand.CommandText= SELECT * FROM my_odbc_net;OdbcDataReader MyDataReader;MyDataReader = MyComm

39、and.ExecuteReader();while (MyDataReader.Read()if(string.Compare(MyConnection.Driver,myodbc3.dll)=0) Console.WriteLine(Data:+ MyDataReader.Getlnt32(0)+ +MyDataReader.GetString(1)+ +MyDataReader.Getlnt64(2);else Console.WriteLine(Data:MyDataReader.GetString(1)MyDataReader.GetInt32(2); /Supported only

40、by Connector/ODBC 3.51+ MyDataReader.GetInt32(0)/BIGINTs not supportedby Connector/ODBC/Close all resourcesMyDataReader.Close();MyConnection.Close();catch (OdbcException MyOdbcException)/CatchanyODBCexception .for (int i=0; i 0)PageCount +=1;currentPage = 1;recNo = 0;LoadPage();這是前一段需要用到，精選了一些資料，希望對(duì)

41、大家有幫助.usingusingusingSystem;System.C on figurati on;MySql.Data.MySqlClie nt;/ / TestDatebase的摘要說(shuō)明/ publicclass TestDatebasepublic TestDatebase()/ TODO:在此處添加構(gòu)造函數(shù)邏輯/public staticvoid Main ( Stri ng args )MySqlCo nn ection mysql = getMySqlCo n();/查詢(xún)sqlStri ng sqlSearch = /插入sqlString sqll nsert = 張三,25

42、,大專(zhuān))；/修改sqlString sqlUpdate = where id= 3;/刪除sqlString sqlDel =/打印SQL語(yǔ)句select * from stude nt;insert into student values (12,update stude nt set n ame=李四delete from stude nt where id = 12;Console.WriteLine ( sqlDel );/四種語(yǔ)句對(duì)象/MySqlComma ndmySqlComma n= getSqlComma nd(sqlSearch, mysql);/MySqlComma ndm

43、ySqlComma n= getSqlComma nd(sqll nsert, mysql);/MySqlComma ndmySqlComma n= getSqlComma nd(sqlUpdate, mysql);MySqlComma nd mySqlComma nd = getSqlComma nd ( sqlDel, mysql );mysql.Ope n();getResultset(mySqlComma nd);/getl nsert(mySqlComma nd);getUpdate(mySqlComma nd);getDel ( mySqlComma nd );/記得關(guān)閉mysql

44、.Close();Stri ng readL ine = Con sole.ReadL in e();/ /建立mysql數(shù)據(jù)庫(kù)鏈接/ / public staticMySqlCo nn ection getMySqlCo n()Stri ng mysqIStr =Database=test;DataSource=;Userld=root;Password=root;pooli ng=false;CharSet=utf8;port=3306;/ Stri ng mySqlCo n =Con figuratio nMan ager.C onn ectio nStri ngsMySqlCo n.C onn ectio nStri ng;MySqlConnection mysql =new MySqlConnection