C#-連接MYSQL數據庫的3種方法及示例

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

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

3、 驅動(MySQL Connector/ODBC (MyODBC) driver) 目前為 3.51 版 安裝完成后，即可通過ODBC訪問MYSQL數據庫 方法三： 使用CoreLab 推岀的MYSQL訪問組件，面向.NET 安裝完成后，引用命名空間：CoreLab.MySql; 使用命令編譯時：csc /r:CoreLab.MySql.dll test.cs 以下為訪問MYSQL數據庫實例 編譯指令：csc /r:CoreLab.MySql.dll /r:MySql.Data.dll test.cs using System; using System.Net; using System.T

4、ext; using CoreLab.MySql; using System.Data.Odbc; using MySql.Data.MySqlClie nt; class Conn ectMySql public 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 m

5、ycm = new MySqlComma nd(select * from 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 MyConStrin

6、g = DRIVER=MySQL ODBC 3.51 Driver; + SERVER=localhost; + 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.ExecuteR

7、eader(); while(msdr.Read() if (msdr.HasRows) Co nsole.WriteLi 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 e

8、ctio nStrin g); mycn. Ope n(); MySqlComma nd mycm = new MySqlComma 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

9、 nn ectMySql(); ms.C onn ect_CoreLab(); ms.C onn ect_Odbc(); Conn ect_Net(); 1 用 MySQLDriverCS 連接 MySQL 數據庫 先下載和安裝MySQLDriverCS，地址： http:/sourceforge. net/projects/mysqldrivercs/ 在安裝文件夾下面找到MySQLDriver.dll，然后將MySQLDriver.dll添加引用到項目中 注：我下載的是版本是MySQLDriverCS-n-EasyQueryTools-4.0.1-DotNet2.0.exe using S

10、ystem; 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 mysql public partial class Form1 : Form public Form1() In i

11、tializeComp 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 mn .Exe

12、cuteN on Query(); stri ng sql = select * from excha nge ; MySQLDataAdapter mda = new MySQLDataAdapter(sql, conn); DataSet ds = new DataSet(); mda.Fill(ds, table1); this.dataGrid1.DataSource = ds.Tablestable1; conn .Close(); 2、通過 ODBC訪問mysql數據庫: 參考：http:/ na/com mun ity/Column/63.mspx 1. 安裝 Microsoft

13、 ODBC.net :我安裝的是 mysql-connector-odbc-3.51.22-win32.msi 2. 安裝MDAC 2.7或者更高版本：我安裝的是mdac_typ.exe 2.7簡體中文版 3. 安裝MySQL的ODBC驅動程序：我安裝的是odbc_net.msi 4. 管理工具- 數據源ODBC - 配置DSN 5. 解決方案管理中添加引用Microsoft.Data.Odbc.dll(1.0.3300) 6. 代碼中增加引用usi ng Microsoft.Data.Odbc; using System; using System.Collect ion s.Ge neri

14、c; using System.Comp onen tModel; using System.Draw ing; using System.Linq;vs2005好像沒有這個命名空間，在c#2008下測試自動生成的 using System.Text; using System.Wi ndows.Forms; using Microsoft.Data.Odbc; n amespace mysql public partial class Forml : Form public Form1() In itializeComp onen t(); private void Form1_Load(o

15、bject sen der, Even tArgs e) stri ng MyCo nStri ng = DRIVER=MySQL ODBC 3.51 Driver; + SERVER=localhost; + DATABASES nv; + UID=root; + 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 ect

16、ed successfully !n ”)； string query = insert into test values( hello, lucas, li); OdbcComma nd cmd = new OdbcComma nd(query, MyConnection); /處理異常：插入重復記錄有異常 try cmd.ExecuteN on Query(); catch(Excepti on ex) Con sole.WriteL in e(record duplicate.); fin ally cmd.Dispose(); /* 用 read 方法讀數據到 textbox* str

17、i 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); OdbcDataReader reader = cmd2.ExecuteReader(); while (reader.Read() tmpl = :reader0.ToStri ng(); tmp2 = reader1.ToStri ng(); tmp3 = reader2.ToStri

18、ng(); this.textBox1.Text = tmpl + + tmp2 + + tmp3; */ /* * 用 datagridview 控件顯示數據表 * string MyConString = DRIVER=MySQL ODBC 3.51 Driver; + SERVER=localhost; + DATABASES nv; + UID=root; + PASSWORD=831025; + OPTION=3; OdbcC onn ecti on MyConn ecti on = new OdbcC onn ectio n(MyCon Stri ng); OdbcDataAdap

19、ter oda = new OdbcDataAdapter(select * from customer , MyConnection); DataSet ds = new DataSet(); oda.Fill(ds, employee); this.dataGridViewl.DataSource = ds.Tablesemployee; */ MyCo nn ectio n.Close(); 文章出處： http:/www.diybl.eom/course/4_webprogram/asp.n et/asp _n etshl/2008429/112011.html 1、用 MySQLDr

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

21、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 mysql public partial class Forml : Form public Form1() In itializeComp onen t(); private void Form1_Load(object sen der, Even tArgs e) MySQLCo nn e

22、ction 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 MyS

23、QLDataAdapter(sql, conn); DataSet ds = new DataSet(); mda.Fill(ds, tablei); this.dataGridl.DataSource = ds.Tablestable1; conn .Close(); 2、通過 ODBC訪問mysql數據庫: 參考：http:/ na/com mun ity/Column/63.mspx 1. 安裝 Microsoft ODBC.net :我安裝的是 mysql-connector-odbc-3.51.22-win32.msi 2. 安裝MDAC 2.7或者更高版本：我安裝的是mdac_ty

24、p.exe 2.7簡體中文版 3. 安裝MySQL的ODBC驅動程序：我安裝的是odbc_net.msi 4. 管理工具- 數據源ODBC - 配置DSN 5. 解決方案管理中添加引用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好像沒有這個命

25、名空間，在c#2008下測試自動生成的 using System.Text; using System.Wi ndows.Forms; using Microsoft.Data.Odbc; n amespace mysql public partial class Form1 : Form public 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; + SE

26、RVER=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 query = insert into test values( hello, lucas, li)； OdbcCo

27、mma nd cmd = new OdbcComma nd(query, MyConnection); /處理異常：插入重復記錄有異常 try cmd.ExecuteN on Query(); catch(Excepti on ex) Con sole.WriteL in e(record duplicate.); fin ally cmd.Dispose(); 囁*用read方法讀數據到textbox* string tmpl = null; string tmp2 = null; stri ng tmp3 = n ull; query = select * from test ; Odbc

28、Comma 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; */ /* * 用 datagridview 控件顯示數據表 * string MyConStri

29、ng = 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 ds = new DataSet(); od

30、a.Fill(ds, employee); this.dataGridView1.DataSource = ds.Tablesemployee; */ MyCo nn ectio n.Close(); 文章出處： http:/www.diybl.eom/course/4webprogram/ netshl/2008429/112011.html C#連接mysql數據庫 1. 連接： 1. 安裝 Microsoft ODBC.net。 2. 安裝MySQL的ODBC驅動程序。 2. 解決方案管理中添加引用Microsoft.Data.Odbc.dll(1.0.3300) 3. 代碼中增加引用

31、using Microsoft.Data.Odbc; 4. 編寫代碼 string MyConString = DRIVER=MySQL ODBC 3.51 Driver; + SERVER=localhost; + DATABASE=samp_db; + UID=root; + PASSWORD+ 0PTI0N=3; /Connect to MySQL using Connector/ODBC OdbcConnectionMyConnection = new OdbcConnection(MyConString); MyConnection.Open(); Console.WriteLine

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

33、sing Console = System.Console; using Microsoft.Data.Odbc; namespace myodbc3 class mycon static 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 stri

34、ng MyConString SERVER=localhost; for Connector/ODBC =DRIVER=MySQL DATABASE=test; 3.51 ODBC 3.51 Driver; + UID=venu; + PASSWORD=venu; 0PTI0N=3; /Connect to MySQL using Connector/ODBC OdbcConnection MyConnection = new OdbcConnection(MyConString); MyConnection.Open(); Console.WriteLine(n ! success, con

35、nected successfully !n); /Display connection information Console.WriteLine(Connection Console.WriteLine(tConnection Console.WriteLine(tConnection Console.WriteLine(tDatabase: Console.WriteLine(tDataSource: Information:); String: + MyConnection.ConnectionString); Timeout: + MyConnection.ConnectionTim

36、eout); + MyConnection.Database); + 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.E

37、xecuteNonQuery(); MyCommand.CommandText= CREATE TABLE my_odbc_net(id int, name varchar(20), i db bigint); MyCommand.ExecuteNonQuery(); /Insert MyCommand.CommandText =INSERT INTO my_odbc_net VALUES(10,venu, 300); Console.WriteLine(INSERT, Total rows affected: + MyCommand.ExecuteNonQuery(); /Insert My

38、Command.CommandText Console.WriteLine(INSERT, =INSERT INTO my_odbc_net VALUES(20,mysql,400); 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();

39、/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 MyComm

40、and.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.Getlnt32(0)+ + MyDataReader.GetString(1)+ + MyDataReader.Getlnt64(

41、2); else Console.WriteLine(Data: MyDataReader.GetString(1) MyDataReader.GetInt32(2); /Supported only by Connector/ODBC 3.51 + MyDataReader.GetInt32(0) /BIGINTs not supported by Connector/ODBC /Close all resources MyDataReader.Close(); MyConnection.Close(); catch (OdbcException MyOdbcException)/Catch

42、 any ODBC exception . for (int i=0; i 0) PageCount +=1; currentPage = 1; recNo = 0; LoadPage(); 這是前一段需要用到，精選了一些資料，希望對大家有幫助. using using using System; System.C on figurati on; MySql.Data.MySqlClie nt; / / TestDatebase的摘要說明 / public class TestDatebase public TestDatebase() / / TODO:在此處添加構造函數邏輯 / publi

43、c staticvoid Main ( Stri ng args ) MySqlCo nn ection mysql = getMySqlCo n(); /查詢sql Stri ng sqlSearch = /插入sql String sqll nsert = 張三,25,大專)； /修改sql String sqlUpdate = where id= 3; /刪除sql String sqlDel = /打印SQL語句 select * from stude nt; insert into student values (12, update stude nt set n ame=李四 de

44、lete from stude nt where id = 12; Console.WriteLine ( sqlDel ); /四種語句對象 /MySqlComma ndmySqlComma n= getSqlComma nd(sqlSearch, mysql); /MySqlComma ndmySqlComma n= getSqlComma nd(sqll nsert, mysql); /MySqlComma ndmySqlComma n= getSqlComma nd(sqlUpdate, mysql); MySqlComma nd mySqlComma nd = getSqlComma

45、 nd ( sqlDel, mysql ); mysql.Ope n(); getResultset(mySqlComma nd); /getl nsert(mySqlComma nd); getUpdate(mySqlComma nd); getDel ( mySqlComma nd ); /記得關閉 mysql.Close(); Stri ng readL ine = Con sole.ReadL in e(); / /建立mysql數據庫鏈接 / / Stri ng mysqIStr =Database=test;Data Source=;User ld=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 (mysql