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

1、C#连接MYSQL数据库的3种方法及示例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); co

2、n.Open(); MySqlCommand cmd = new MySqlCommand(); 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驱动(My

3、SQL 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.Text; using

4、 CoreLab.MySql; using 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 mycn = new MySqlConnection(constr); mycn.Open(); MySqlCommand mycm = new MySqlCommand(select

5、 * from shop,mycn); MySqlDataReader msdr = mycm.ExecuteReader(); while(msdr.Read() if (msdr.HasRows) Console.WriteLine(msdr.GetString(0); msdr.Close(); mycn.Close(); public void Connect_Odbc() /string MyConString =DSN=MySQL;UID=root;PWD=qing; string MyConString = DRIVER=MySQL ODBC 3.51 Driver; + SER

6、VER=localhost; + DATABASE=test; + UID=root; + PASSWORD=qing; + OPTION=3; OdbcConnection MyConn = new OdbcConnection(MyConString); MyConn.Open(); OdbcCommand mycm = new OdbcCommand(select * from hello,MyConn); OdbcDataReader msdr = mycm.ExecuteReader(); while(msdr.Read() if (msdr.HasRows) Console.Wri

7、teLine(msdr.GetString(0); msdr.Close(); MyConn.Close(); public void Connect_Net() string myConnectionString = Database=test;Data Source=localhost;User Id=root;Password=qing; MySqlConnection mycn = new MySqlConnection(myConnectionString); mycn.Open(); MySqlCommand mycm = new MySqlCommand(select * fro

8、m hello,mycn); MySqlDataReader msdr = mycm.ExecuteReader(); while(msdr.Read() if (msdr.HasRows) Console.WriteLine(msdr.GetString(0); msdr.Close(); mycn.Close(); public static void Main() ConnectMySql ms = new ConnectMySql(); ms.Connect_CoreLab(); ms.Connect_Odbc(); Connect_Net(); 1、用MySQLDriverCS连接M

9、ySQL数据库先下载和安装MySQLDriverCS，地址：在安装文件夹下面找到MySQLDriver.dll，然后将MySQLDriver.dll添加引用到项目中注：我下载的是版本是 MySQLDriverCS-n-EasyQueryTools-4.0.1-DotNet2.0.exe using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Data.Odbc;using System.Drawing;using System.Linq;us

10、ing System.Text;using System.Windows.Forms;using MySQLDriverCS; namespace mysql public partial class Form1 : Form public Form1() InitializeComponent(); private void Form1_Load(object sender, EventArgs e) MySQLConnection conn = null; conn = new MySQLConnection(new MySQLConnectionString(localhost, inv

11、, 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 =

12、ds.Tablestable1; conn.Close(); 2、通过ODBC访问mysql数据库： 参考： 1. 安装Microsoft ODBC.net：我安装的是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. 代码中增加引用 using Micros

13、oft.Data.Odbc; using System;using System.Collections.Generic;using System.ComponentModel;using System.Drawing;using System.Linq; /vs2005好像没有这个命名空间，在c#2008下测试自动生成的using System.Text;using System.Windows.Forms;using Microsoft.Data.Odbc; namespace mysql public partial class Form1 : Form public Form1() I

14、nitializeComponent(); private void Form1_Load(object sender, 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.Wri

15、teLine(n success, connected successfully !n); string query = insert into test values( hello, lucas, liu); OdbcCommand cmd = new OdbcCommand(query, MyConnection); /处理异常：插入重复记录有异常try cmd.ExecuteNonQuery();catch(Exception ex) Console.WriteLine(record duplicate.);finally cmd.Dispose(); /*用read方法读数据到text

16、box* string tmp1 = null; string tmp2 = null; string tmp3 = null; query = select * from test ; OdbcCommand cmd2 = new OdbcCommand(query, MyConnection); OdbcDataReader reader = cmd2.ExecuteReader(); while (reader.Read() tmp1 = reader0.ToString(); tmp2 = reader1.ToString(); tmp3 = reader2.ToString(); t

17、his.textBox1.Text = tmp1 + + tmp2 + + tmp3; */ /*用datagridview控件显示数据表*string 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 OdbcDataAda

18、pter(select * from customer , MyConnection);DataSet ds = new DataSet(); oda.Fill(ds, employee); this.dataGridView1.DataSource = ds.Tablesemployee;*/ MyConnection.Close(); 文章出处：1、用MySQLDriverCS连接MySQL数据库先下载和安装MySQLDriverCS，地址：在安装文件夹下面找到MySQLDriver.dll，然后将MySQLDriver.dll添加引用到项目中注：我下载的是版本是 MySQLDriverC

19、S-n-EasyQueryTools-4.0.1-DotNet2.0.exe using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Data.Odbc;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using MySQLDriverCS; namespace mysql public partial class Form

20、1 : Form public Form1() InitializeComponent(); private void Form1_Load(object sender, EventArgs e) MySQLConnection conn = null; conn = new MySQLConnection(new MySQLConnectionString(localhost, inv, root, 831025).AsString); conn.Open(); MySQLCommand commn = new MySQLCommand(set names gb2312, conn); co

21、mmn.ExecuteNonQuery(); string sql = select * from exchange ; MySQLDataAdapter mda = new MySQLDataAdapter(sql, conn); DataSet ds = new DataSet(); mda.Fill(ds, table1); this.dataGrid1.DataSource = ds.Tablestable1; conn.Close(); 2、通过ODBC访问mysql数据库： 参考： 1. 安装Microsoft ODBC.net：我安装的是mysql-connector-odbc-

22、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. 代码中增加引用 using Microsoft.Data.Odbc; using System;using System.Collections.Generic;using System.ComponentModel;using System.Dra

23、wing;using System.Linq; /vs2005好像没有这个命名空间，在c#2008下测试自动生成的using System.Text;using System.Windows.Forms;using Microsoft.Data.Odbc; namespace mysql public partial class Form1 : Form public Form1() InitializeComponent(); private void Form1_Load(object sender, EventArgs e) string MyConString = DRIVER=MyS

24、QL 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 query = insert into test values( hello, lucas, liu);

25、 OdbcCommand cmd = new OdbcCommand(query, MyConnection); /处理异常：插入重复记录有异常try cmd.ExecuteNonQuery();catch(Exception ex) Console.WriteLine(record duplicate.);finally cmd.Dispose(); /*用read方法读数据到textbox* string tmp1 = null; string tmp2 = null; string tmp3 = null; query = select * from test ; OdbcCommand

26、 cmd2 = new OdbcCommand(query, MyConnection); OdbcDataReader reader = cmd2.ExecuteReader(); while (reader.Read() tmp1 = reader0.ToString(); tmp2 = reader1.ToString(); tmp3 = reader2.ToString(); this.textBox1.Text = tmp1 + + tmp2 + + tmp3; */ /*用datagridview控件显示数据表*string MyConString = DRIVER=MySQL O

27、DBC 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(); oda.Fill(ds, employee); this.data

28、GridView1.DataSource = ds.Tablesemployee;*/ MyConnection.Close(); 文章出处：C#连接mysql数据库 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/ODBCOdbcConnection MyConnection = new OdbcConnection(MyConString); MyConnection.Open();Console.WriteL