C#-连接MYSQL数据库的3种方法及示例

1、C#连接MYSQL 数据库的方法及示例 连接MYSQL数据库的方法及示例 方法一：usi ng MySql.Datausi ng MySql.Data.MySqlClie nt;其他操作跟SQL是差不多，无非就是前缀变成MySql 了 .补充：下面是连接字符串，供参考.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 访问MYSQL数据库设计的.NET访问组件。安装完成该组件后，引用命名空间MySql.Data.MySqlClie nt;使用命令行编译时：csc /r:MySql.Data.dll test.cs方法二：通过ODBC访问MYSQL数据库访问前要先下载两个组件： 和MYSQL的ODBC 驱动(MySQL Connect

3、or/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.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 数据库先下载和安装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

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、通过 ODBC访问mysql数据库:参考： 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简体中文版3. 安装MySQL

13、的ODBC驱动程序：我安装的是odbc_net.msi4. 管理工具- 数据源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好像没有这个命名空间，在c#2008下测试自动生成的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、/处理异常：插入重复记录有异常trycmd.ExecuteN on Query();catch(Excepti on ex)Con sole.WriteL in e(record duplicate.);fin allycmd.Dispose();/*用 read 方法读数据到 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 控件显示数据表 *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 数据库先下载和安装MySQLDriverCS，地址：http:/sourceforge. net/projects/mysqldrivercs/在安装文件夹下面找到MySQLDriver.dll，然后将MySQLDriver.dll添加引用到项目中注：我下载的是版本是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、通过 ODBC访问mysql数据库:参考： 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简体中文版3. 安装MySQL的ODBC驱动程序：我安装的是odbc_net.msi4. 管理工具- 数据源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好像没有这个命名空间，在c#2008下测试自动生成的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);/处理异常：插入重复记录有异常trycmd.ExecuteN on Query();catch(Excepti on ex)Con sole.WriteL in e(record duplicate.);fin allycmd.Dispose();嗫*用read方法读数据到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 控件显示数据表 *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数据库1. 连接：1. 安装 Microsoft ODBC.net。2. 安装MySQL的ODBC驱动程序。2. 解决方案管理中添加引用Microsoft.Data.Odbc.dll(1.0.3300)3

30、. 代码中增加引用using Microsoft.Data.Odbc;4. 编写代码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();这是前一段需要用到，精选了一些资料，希望对

41、大家有帮助.usingusingusingSystem;System.C on figurati on;MySql.Data.MySqlClie nt;/ / TestDatebase的摘要说明/ publicclass TestDatebasepublic TestDatebase()/ TODO:在此处添加构造函数逻辑/public staticvoid Main ( Stri ng args )MySqlCo nn ection mysql = getMySqlCo n();/查询sqlStri ng sqlSearch = /插入sqlString sqll nsert = 张三,25

42、,大专)；/修改sqlString sqlUpdate = where id= 3;/删除sqlString sqlDel =/打印SQL语句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 );/四种语句对象/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 );/记得关闭mysql

44、.Close();Stri ng readL ine = Con sole.ReadL in e();/ /建立mysql数据库链接/ / 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