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

1、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();

2、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 的

3、ODBC 驱动(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; us

4、ing System.Text; using 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 =

5、 new MySqlCommand(“select * 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=

6、MySQL ODBC 3.51 Driver;“ + “SERVER=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

7、(msdr.Read() if (msdr.HasRows) Console.WriteLine(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(); MySql

8、Command mycm = new MySqlCommand(“select * from 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.Conn

9、ect_Odbc(); Connect_Net(); 1、用 MySQLDriverCS 连接 MySQL 数据库先下载和安装 MySQLDriverCS，地址：http:/ MySQLDriver.dll，然后将 MySQLDriver.dll 添加引用到项目中注：我下载的是版本是 MySQLDriverCS-n-EasyQueryTools-4.0.1-DotNet2.0.exeusing System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Da

10、ta.Odbc;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using MySQLDriverCS;namespace mysqlpublic partial class Form1 : Formpublic Form1()InitializeComponent();private void Form1_Load(object sender, EventArgs e)MySQLConnection conn = null;conn = new MySQLConnectio

11、n(new MySQLConnectionString(“localhost“, “inv“, “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

12、.Fill(ds, “table1“);this.dataGrid1.DataSource = ds.Tables“table1“;conn.Close();2、通过 ODBC 访问 mysql 数据库：参考：http:/ 安装 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. 解决

13、方案管理中添加引用 Microsoft.Data.Odbc.dll(1.0.3300)6. 代码中增加引用 using Microsoft.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

14、;namespace mysqlpublic partial class Form1 : Formpublic Form1()InitializeComponent();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

15、 = new OdbcConnection(MyConString);MyConnection.Open();Console.WriteLine(“n success, connected successfully !n“);string query = “insert into test values( hello, lucas, liu)“;OdbcCommand cmd = new OdbcCommand(query, MyConnection);/处理异常：插入重复记录有异常trycmd.ExecuteNonQuery();catch(Exception ex)Console.Writ

16、eLine(“record duplicate.“);finallycmd.Dispose();/*用 read 方法读数据到 textbox*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.ToStr

17、ing();tmp2 = reader1.ToString();tmp3 = reader2.ToString();this.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

18、 = new OdbcConnection(MyConString);OdbcDataAdapter oda = new OdbcDataAdapter(“select * from customer “, MyConnection);DataSet ds = new DataSet();oda.Fill(ds, “employee“);this.dataGridView1.DataSource = ds.Tables“employee“;*/MyConnection.Close();文章出处：http:/ MySQLDriverCS 连接 MySQL 数据库先下载和安装 MySQLDrive

19、rCS，地址：http:/ MySQLDriver.dll，然后将 MySQLDriver.dll 添加引用到项目中注：我下载的是版本是 MySQLDriverCS-n-EasyQueryTools-4.0.1-DotNet2.0.exeusing 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 Sys

20、tem.Windows.Forms;using MySQLDriverCS;namespace mysqlpublic partial class Form1 : Formpublic Form1()InitializeComponent();private void Form1_Load(object sender, EventArgs e)MySQLConnection conn = null;conn = new MySQLConnection(new MySQLConnectionString(“localhost“, “inv“, “root“, “831025“).AsString

21、);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 = ds.Tables“table1“;conn.Clo

22、se();2、通过 ODBC 访问 mysql 数据库：参考：http:/ 安装 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 Microsoft.Data

23、.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 mysqlpublic partial class Form1 : Formpublic Form1()InitializeC

24、omponent();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.WriteLine(“

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

26、ring 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();this.textBox1.Te

27、xt = 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 OdbcDataAdapt

28、er(“select * from customer “, MyConnection);DataSet ds = new DataSet();oda.Fill(ds, “employee“);this.dataGridView1.DataSource = ds.Tables“employee“;*/MyConnection.Close();文章出处：http:/ mysql 数据库 1.连接:1.安装 Microsoft ODBC.net。2.安装 MySQL 的 ODBC 驱动程序。2.解决方案管理中添加引用 Microsoft.Data.Odbc.dll(1.0.3300)3.代码中增加引

29、用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.Wr

30、iteLine(“n ! success, connected successfully !n“); MyConnection.Close();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

31、sing Console = System.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

32、 for Connector/ODBC 3.51string MyConString = “DRIVER=MySQL ODBC 3.51 Driver;“ + “SERVER=localhost;“ +“DATABASE=test;“ +“UID=venu;“ +“PASSWORD=venu;“ +“OPTION=3“;/Connect to MySQL using Connector/ODBCOdbcConnection MyConnection = new OdbcConnection(MyConString); MyConnection.Open();Console.WriteLine(

33、“n ! success, connected successfully !n“); /Display connection informationConsole.WriteLine(“Connection Information:“); Console.WriteLine(“tConnection String:“ + MyConnection.ConnectionString); Console.WriteLine(“tConnection Timeout:“ + MyConnection.ConnectionTimeout); Console.WriteLine(“tDatabase:“

34、 + MyConnection.Database); Console.WriteLine(“tDataSource:“ + MyConnection.DataSource);Console.WriteLine(“tDriver:“ + MyConnection.Driver);Console.WriteLine(“tServerVersion:“ + MyConnection.ServerVersion);/Create a sample tableOdbcCommand MyCommand = new OdbcCommand(“DROP TABLE IF EXISTS my_odbc_net

35、“,MyConnection);MyCommand.ExecuteNonQuery();MyCommand.CommandText = “CREATE TABLE my_odbc_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

36、.ExecuteNonQuery();/InsertMyCommand.CommandText = “INSERT INTO my_odbc_net VALUES(20,mysql,400)“; Console.WriteLine(“INSERT, Total rows affected:“ + MyCommand.ExecuteNonQuery();/InsertMyCommand.CommandText = “INSERT INTO my_odbc_net VALUES(20,mysql,500)“; Console.WriteLine(“INSERT, Total rows affect

37、ed:“ + MyCommand.ExecuteNonQuery();/UpdateMyCommand.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:“

38、 + MyCommand.ExecuteScalar();/FetchMyCommand.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.GetInt32(0) + “ “

39、+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 supported by Connector/ODBC/Close all resourcesMyDataReader.Clos

40、e();MyConnection.Close();catch (OdbcException MyOdbcException)/Catch any ODBC exception for (int i=0; i 0) PageCount += 1;currentPage = 1;recNo = 0;LoadPage();这是前一段需要用到,精选了一些资料,希望对大家有帮助.using System;using System.Configuration;using MySql.Data.MySqlClient;/ / TestDatebase 的摘要说明/ public class TestDate

41、basepublic TestDatebase()/ TODO: 在此处添加构造函数逻辑/public static void Main ( String args )MySqlConnection mysql = getMySqlCon();/查询 sqlString sqlSearch = “select * from student“;/插入 sqlString sqlInsert = “insert into student values (12,张三,25,大专)“;/修改 sqlString sqlUpdate = “update student set name=李四 where

42、 id= 3“;/删除 sqlString sqlDel = “delete from student where id = 12“;/打印 SQL 语句Console.WriteLine ( sqlDel );/四种语句对象/MySqlCommand mySqlCommand = getSqlCommand(sqlSearch, mysql);/MySqlCommand mySqlCommand = getSqlCommand(sqlInsert, mysql);/MySqlCommand mySqlCommand = getSqlCommand(sqlUpdate, mysql);MySq

43、lCommand mySqlCommand = getSqlCommand ( sqlDel, mysql );mysql.Open();/getResultset(mySqlCommand);/getInsert(mySqlCommand);/getUpdate(mySqlCommand);getDel ( mySqlCommand );/记得关闭mysql.Close();String readLine = Console.ReadLine();/ / 建立 mysql 数据库链接/ / public static MySqlConnection getMySqlCon()String m

44、ysqlStr = “Database=test;Data Source=127.0.0.1;User Id=root;Password=root;pooling=false;CharSet=utf8;port=3306“;/ String mySqlCon = ConfigurationManager.ConnectionStrings“MySqlCon“.ConnectionString;MySqlConnection mysql = new MySqlConnection ( mysqlStr );return mysql;/ / 建立执行命令语句对象/ / / / public sta

45、tic MySqlCommand getSqlCommand ( String sql,MySqlConnection mysql )MySqlCommand mySqlCommand = new MySqlCommand ( sql, mysql );/ MySqlCommand mySqlCommand = new MySqlCommand(sql);/ mySqlCommand.Connection = mysql;return mySqlCommand;/ / 查询并获得结果集并遍历/ / public static void getResultset ( MySqlCommand m

46、ySqlCommand )MySqlDataReader reader = mySqlCommand.ExecuteReader();trywhile ( reader.Read() )if ( reader.HasRows )Console.WriteLine ( “编号:“ + reader.GetInt32 ( 0 ) + “|姓名:“ + reader.GetString ( 1 ) + “|年龄:“ + reader.GetInt32 ( 2 ) + “|学历:“ + reader.GetString ( 3 ) );catch ( Exception )Console.WriteLine ( “查询失败了！“ );finallyreader.Close();/ / 添加数据/ / public static void getInsert ( MySqlCommand mySqlCommand )trymySqlCommand.ExecuteNonQuery();catch ( Exception ex )String message = ex.Message;Console.WriteLine ( “插入数据失败了！“ + message );/ / 修改数据/