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

1、-. z.C# 连接MYSQL数据库的方法及例如连接MYSQL数据库的方法及例如 方法一：using MySql.Data using MySql.Data.MySqlClient; 其他操作跟SQL是差不多,无非就是前缀变成MySql了. 补充: 下面是连接字符串,供参考. MySqlConnection con = new MySql.Data.MySqlClient.MySqlConnection(Database=testdb;Data Source=localhost;User Id=db;Password=apple;charset=utf8); con.Open(); MySql

2、mand cmd = new MySqlmand(); cmd.Connection = con; 使用MYSQL推出的MySQL Connector/Net is an ADO.NET driver for MySQL 该组件为MYSQL为ADO.NET访问MYSQL数据库设计的.NET访问组件。 安装完成该组件后，引用命名空间MySql.Data.MySqlClient; 使用命令行编译时：csc /r:MySql.Data.dll test.cs 方法二： 通过ODBC访问MYSQL数据库 访问前要先下载两个组件：和MYSQL的ODBC驱动(MySQL Connector/ODBC (M

3、yODBC) 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.Te*t; using CoreLab.MySql; using

4、 System.Data.Odbc; using MySql.Data.MySqlClient; class ConnectMySql public void Connect_CoreLab() string constr = User Id=root;Host=localhost;Database=qing;password=qing; MySqlConnection my = new MySqlConnection(constr); my.Open(); MySqlmand mycm = new MySqlmand(select * from shop,my); MySqlDataRead

5、er msdr = mycm.E*ecuteReader(); while(msdr.Read() if (msdr.HasRows) Console.WriteLine(msdr.GetString(0); msdr.Close(); my.Close(); public void Connect_Odbc() /string MyConString =DSN=MySQL;UID=root;PWD=qing; string MyConString = DRIVER=MySQL ODBC 3.51 Driver; + SERVER=localhost; + DATABASE=test; + U

6、ID=root; + PASSWORD=qing; + OPTION=3; OdbcConnection MyConn = new OdbcConnection(MyConString); MyConn.Open(); Odbcmand mycm = new Odbcmand(select * from hello,MyConn); OdbcDataReader msdr = mycm.E*ecuteReader(); while(msdr.Read() if (msdr.HasRows) Console.WriteLine(msdr.GetString(0); msdr.Close(); M

7、yConn.Close(); public void Connect_Net() string myConnectionString = Database=test;Data Source=localhost;User Id=root;Password=qing; MySqlConnection my = new MySqlConnection(myConnectionString); my.Open(); MySqlmand mycm = new MySqlmand(select * from hello,my); MySqlDataReader msdr = mycm.E*ecuteRea

8、der(); while(msdr.Read() if (msdr.HasRows) Console.WriteLine(msdr.GetString(0); msdr.Close(); my.Close(); public static void Main() ConnectMySql ms = new ConnectMySql(); ms.Connect_CoreLab(); ms.Connect_Odbc(); Connect_Net(); 1、用MySQLDriverCS连接MySQL数据库先下载和安装MySQLDriverCS，地址： MySQLDriverCS-n-EasyQuer

9、yTools-using System;using System.Collections.Generic;using System.ponentModel;using System.Data;using System.Data.Odbc;using System.Drawing;using System.Linq;using System.Te*t;using System.Windows.Forms;using MySQLDriverCS;namespace mysql public partial class Form1 : Form public Form1() Initializepo

10、nent(); private void Form1_Load(object sender, EventArgs e) MySQLConnection conn = null; conn = new MySQLConnection(new MySQLConnectionString(localhost, inv, root, 831025).AsString); conn.Open(); MySQLmand mn = new MySQLmand(set names gb2312, conn); mn.E*ecuteNonQuery(); string sql = select * from e

11、*change ; MySQLDataAdapter mda = new MySQLDataAdapter(sql, conn); DataSet ds = new DataSet(); mda.Fill(ds, table1); this.dataGrid1.DataSource = ds.Tablestable1; conn.Close(); 2、通过ODBC访问mysql数据库：参考：.microsoft./china/munity/Column/63.msp*1. 安装Microsoft ODBC.net：我安装的是mysql-connector-odbc-win32.msi2. 安装

12、MDAC 2.7或者更高版本：我安装的是mdac_typ.e*e 2.7简体中文版3. 安装MySQL的ODBC驱动程序：我安装的是 odbc_net.msi4. 管理工具 - 数据源ODBC 配置DSN5. 解决方案管理中添加引用 Microsoft.Data.Odbc.dll(1.0.3300)6. 代码中增加引用 using Microsoft.Data.Odbc;using System;using System.Collections.Generic;using System.ponentModel;using System.Drawing;using System.Linq; /v

13、s2005好似没有这个命名空间，在c#2008下测试自动生成的using System.Te*t;using System.Windows.Forms;using Microsoft.Data.Odbc;namespace mysql public partial class Form1 : Form public Form1() Initializeponent(); private void Form1_Load(object sender, EventArgs e) string MyConString = DRIVER=MySQL ODBC 3.51 Driver; + SERVER=

14、localhost; + DATABASE=inv; + UID=root; + PASSWORD=831025; + OPTION=3; OdbcConnection MyConnection = new OdbcConnection(MyConString); MyConnection.Open(); Console.WriteLine(n success, connected successfully !n); string query = insert into test values( hello, lucas, liu); Odbcmand cmd = new Odbcmand(q

15、uery, MyConnection); /处理异常：插入重复记录有异常try cmd.E*ecuteNonQuery();catch(E*ception e*) Console.WriteLine(record duplicate.);finally cmd.Dispose();/*用read方法读数据到te*tbo* string tmp1 = null; string tmp2 = null; string tmp3 = null; query = select * from test ; Odbcmand cmd2 = new Odbcmand(query, MyConnection)

16、; OdbcDataReader reader = cmd2.E*ecuteReader(); while (reader.Read() tmp1 = reader0.ToString(); tmp2 = reader1.ToString(); tmp3 = reader2.ToString(); this.te*tBo*1.Te*t = tmp1 + + tmp2 + + tmp3; */*用datagridview控件显示数据表*string MyConString = DRIVER=MySQL ODBC 3.51 Driver; + SERVER=localhost; + DATABAS

17、E=inv; + UID=root; + PASSWORD=831025; + OPTION=3; OdbcConnection MyConnection = new OdbcConnection(MyConString);OdbcDataAdapter oda = new OdbcDataAdapter(select * from customer , MyConnection);DataSet ds = new DataSet(); oda.Fill(ds, employee); this.dataGridView1.DataSource = ds.Tablesemployee;*/ My

18、Connection.Close(); 文章出处：.diybl./course/4_webprogram/ MySQLDriverCS-n-EasyQueryTools-using System;using System.Collections.Generic;using System.ponentModel;using System.Data;using System.Data.Odbc;using System.Drawing;using System.Linq;using System.Te*t;using System.Windows.Forms;using MySQLDriverCS

19、;namespace mysql public partial class Form1 : Form public Form1() Initializeponent(); private void Form1_Load(object sender, EventArgs e) MySQLConnection conn = null; conn = new MySQLConnection(new MySQLConnectionString(localhost, inv, root, 831025).AsString); conn.Open(); MySQLmand mn = new MySQLma

20、nd(set names gb2312, conn); mn.E*ecuteNonQuery(); string sql = select * from e*change ; MySQLDataAdapter mda = new MySQLDataAdapter(sql, conn); DataSet ds = new DataSet(); mda.Fill(ds, table1); this.dataGrid1.DataSource = ds.Tablestable1; conn.Close(); 2、通过ODBC访问mysql数据库：参考：.microsoft./china/munity/

21、Column/63.msp*1. 安装Microsoft ODBC.net：我安装的是mysql-connector-odbc-win32.msi2. 安装MDAC 2.7或者更高版本：我安装的是mdac_typ.e*e 2.7简体中文版3. 安装MySQL的ODBC驱动程序：我安装的是 odbc_net.msi4. 管理工具 - 数据源ODBC 配置DSN5. 解决方案管理中添加引用 Microsoft.Data.Odbc.dll(1.0.3300)6. 代码中增加引用 using Microsoft.Data.Odbc;using System;using System.Collectio

22、ns.Generic;using System.ponentModel;using System.Drawing;using System.Linq; /vs2005好似没有这个命名空间，在c#2008下测试自动生成的using System.Te*t;using System.Windows.Forms;using Microsoft.Data.Odbc;namespace mysql public partial class Form1 : Form public Form1() Initializeponent(); private void Form1_Load(object send

23、er, EventArgs e) string MyConString = DRIVER=MySQL ODBC 3.51 Driver; + SERVER=localhost; + DATABASE=inv; + UID=root; + PASSWORD=831025; + OPTION=3; OdbcConnection MyConnection = new OdbcConnection(MyConString); MyConnection.Open(); Console.WriteLine(n success, connected successfully !n); string quer

24、y = insert into test values( hello, lucas, liu); Odbcmand cmd = new Odbcmand(query, MyConnection); /处理异常：插入重复记录有异常try cmd.E*ecuteNonQuery();catch(E*ception e*) Console.WriteLine(record duplicate.);finally cmd.Dispose();/*用read方法读数据到te*tbo* string tmp1 = null; string tmp2 = null; string tmp3 = null;

25、query = select * from test ; Odbcmand cmd2 = new Odbcmand(query, MyConnection); OdbcDataReader reader = cmd2.E*ecuteReader(); while (reader.Read() tmp1 = reader0.ToString(); tmp2 = reader1.ToString(); tmp3 = reader2.ToString(); this.te*tBo*1.Te*t = tmp1 + + tmp2 + + tmp3; */*用datagridview控件显示数据表*str

26、ing MyConString = DRIVER=MySQL ODBC 3.51 Driver; + SERVER=localhost; + DATABASE=inv; + UID=root; + PASSWORD=831025; + OPTION=3; OdbcConnection MyConnection = new OdbcConnection(MyConString);OdbcDataAdapter oda = new OdbcDataAdapter(select * from customer , MyConnection);DataSet ds = new DataSet(); o

27、da.Fill(ds, employee); this.dataGridView1.DataSource = ds.Tablesemployee;*/ MyConnection.Close(); 文章出处：.diybl./course/4_webprogram/ 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

28、ODBC 3.51 Driver; + SERVER=localhost; +DATABASE=samp_db; +UID=root; +PASSWORD=; +OPTION=3;/Connect to MySQL using Connector/ODBCOdbcConnection MyConnection = new OdbcConnection(MyConString); MyConnection.Open();Console.WriteLine(n ! success, connected successfully !n); MyConnection.Close();2.全部例程:/*

29、 sample : mycon.cs* purpose : Demo sample for ODBC.NET using Connector/ODBC* author : Venu, venumysql.* (C) Copyright MySQL AB, 1995-2003*/* build mand* * csc /t:e*e * /out:mycon.e*e mycon.cs * /r:Microsoft.Data.Odbc.dll */ using Console = System.Console;using Microsoft.Data.Odbc;namespace myodbc3cl

30、ass 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 for Connector/ODBC 3.51string MyConString = DRIVER=MySQL ODBC 3.51 Driver; + SERVER=lo

31、calhost; +DATABASE=test; +UID=venu; +PASSWORD=venu; +OPTION=3;/Connect to MySQL using Connector/ODBCOdbcConnection MyConnection = new OdbcConnection(MyConString); MyConnection.Open();Console.WriteLine(n ! success, connected successfully !n); /Display connection informationConsole.WriteLine(Connectio

32、n 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(tDrive

33、r: + MyConnection.Driver);Console.WriteLine(tServerVersion: + MyConnection.ServerVersion);/Create a sample tableOdbcmand Mymand = new Odbcmand(DROP TABLE IF E*ISTS my_odbc_net,MyConnection);Mymand.E*ecuteNonQuery();Mymand.mandTe*t = CREATE TABLE my_odbc_net(id int, name varchar(20), idb bigint);Myma

34、nd.E*ecuteNonQuery();/InsertMymand.mandTe*t = INSERT INTO my_odbc_net VALUES(10,venu, 300); Console.WriteLine(INSERT, Total rows affected: + Mymand.E*ecuteNonQuery();/InsertMymand.mandTe*t = INSERT INTO my_odbc_net VALUES(20,mysql,400); Console.WriteLine(INSERT, Total rows affected: + Mymand.E*ecute

35、NonQuery();/InsertMymand.mandTe*t = INSERT INTO my_odbc_net VALUES(20,mysql,500); Console.WriteLine(INSERT, Total rows affected: + Mymand.E*ecuteNonQuery();/UpdateMymand.mandTe*t = UPDATE my_odbc_net SET id=999 WHERE id=20; Console.WriteLine(Update, Total rows affected: + Mymand.E*ecuteNonQuery();/C

36、OUNT(*) Mymand.mandTe*t = SELECT COUNT(*) as TRows FROM my_odbc_net; Console.WriteLine(Total Rows: + Mymand.E*ecuteScalar();/FetchMymand.mandTe*t = SELECT * FROM my_odbc_net; OdbcDataReader MyDataReader;MyDataReader = Mymand.E*ecuteReader();while (MyDataReader.Read()if(string.pare(MyConnection.Drive

37、r,myodbc3.dll) = 0) Console.WriteLine(Data: + MyDataReader.GetInt32(0) + +MyDataReader.GetString(1) + +MyDataReader.GetInt64(2); /Supported only by Connector/ODBC 3.51else Console.WriteLine(Data: + MyDataReader.GetInt32(0) + +MyDataReader.GetString(1) + + MyDataReader.GetInt32(2); /BIGINTs not suppo

38、rted by Connector/ODBC/Close all resourcesMyDataReader.Close();MyConnection.Close();catch (OdbcE*ception MyOdbcE*ception)/Catch any ODBC e*ception .for (int i=0; i 0) PageCount += 1;currentPage = 1;reo = 0;LoadPage();这是前一段需要用到,精选了一些资料,希望对大家有帮助.usingSystem;usingSystem.Configuration;usingMySql.Data.My

39、SqlClient;/ / TestDatebase 的摘要说明/ publicclassTestDatebasepublicTestDatebase()/ TODO: 在此处添加构造函数逻辑/publicstaticvoidMain ( String args )MySqlConnection mysql = getMySqlCon();/查询sqlString sqlSearch =select * from student;/插入sqlString sqlInsert =insert into student values (12,*三,25,大专);/修改sqlString sqlUp

40、date =update student set name=李四 where id= 3;/删除sqlString sqlDel =delete from student where id = 12;/打印SQL语句Console.WriteLine ( sqlDel );/四种语句对象/MySqlmand mySqlmand = getSqlmand(sqlSearch, mysql);/MySqlmand mySqlmand = getSqlmand(sqlInsert, mysql);/MySqlmand mySqlmand = getSqlmand(sqlUpdate, mysql);

41、MySqlmand mySqlmand = getSqlmand ( sqlDel, mysql );mysql.Open();/getResultset(mySqlmand);/getInsert(mySqlmand);/getUpdate(mySqlmand);getDel ( mySqlmand );/记得关闭mysql.Close();String readLine = Console.ReadLine();/ / 建立mysql数据库/ / publicstaticMySqlConnection getMySqlCon()String mysqlStr =Database=test;Data S