C#-連接MYSQL數(shù)據(jù)庫的3種方法及示例

1、C# 連接MYSQL數(shù)據(jù)庫的方法及示例連接MYSQL數(shù)據(jù)庫的方法及示例 方法一：using MySql.Data using MySql.Data.MySqlClient; 其他操作跟SQL是差不多,無非就是前綴變成MySql了. 補(bǔ)充: 下面是連接字符串,供參考. MySqlConnection con = new MySql.Data.MySqlClient.MySqlConnection("Database='testdb'Data Source='localhost'User Id='db'Password='apple

2、'charset='utf8'"); con.Open(); MySqlCommand cmd = new MySqlCommand(); cmd.Connection = con; 使用MYSQL推出的MySQL Connector/Net is an ADO.NET driver for MySQL 該組件為MYSQL為ADO.NET訪問MYSQL數(shù)據(jù)庫設(shè)計(jì)的.NET訪問組件。 安裝完成該組件后，引用命名空間MySql.Data.MySqlClient; 使用命令行編譯時(shí)：csc /r:MySql.Data.dll test.cs 方法二： 通過ODBC訪

3、問MYSQL數(shù)據(jù)庫 訪問前要先下載兩個(gè)組件：和MYSQL的ODBC驅(qū)動(dòng)(MySQL Connector/ODBC (MyODBC) driver)目前為3.51版 安裝完成后，即可通過ODBC訪問MYSQL數(shù)據(jù)庫 方法三： 使用CoreLab推出的MYSQL訪問組件，面向.NET 安裝完成后，引用命名空間：CoreLab.MySql; 使用命令編譯時(shí)：csc /r:CoreLab.MySql.dll test.cs 以下為訪問MYSQL數(shù)據(jù)庫實(shí)例 編譯指令：csc /r:CoreLab.MySql.dll /r:MySql.Data.dll test.cs using System; usin

4、g System.Net; using 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.Op

5、en(); MySqlCommand mycm = 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;

6、PWD=qing" string MyConString = "DRIVER=MySQL ODBC 3.51 Driver;" + "SERVER=localhost;" + "DATABASE=test;" + "UID=root;" + "PASSWORD=qing;" + "OPTION=3" OdbcConnection MyConn = new OdbcConnection(MyConString); MyConn.Open(); OdbcCommand

7、mycm = new OdbcCommand("select * from hello",MyConn); OdbcDataReader msdr = mycm.ExecuteReader(); while(msdr.Read() if (msdr.HasRows) Console.WriteLine(msdr.GetString(0); msdr.Close(); MyConn.Close(); public void Connect_Net() string myConnectionString = "Database=test;Data Source=loc

8、alhost;User Id=root;Password=qing" MySqlConnection mycn = new MySqlConnection(myConnectionString); mycn.Open(); MySqlCommand mycm = new MySqlCommand("select * from hello",mycn); MySqlDataReader msdr = mycm.ExecuteReader(); while(msdr.Read() if (msdr.HasRows) Console.WriteLine(msdr.Get

9、String(0); msdr.Close(); mycn.Close(); public static void Main() ConnectMySql ms = new ConnectMySql(); ms.Connect_CoreLab(); ms.Connect_Odbc(); Connect_Net(); 1、用MySQLDriverCS連接MySQL數(shù)據(jù)庫先下載和安裝MySQLDriverCS，地址：在安裝文件夾下面找到MySQLDriver.dll，然后將MySQLDriver.dll添加引用到項(xiàng)目中注：我下載的是版本是 MySQLDriverCS-n-EasyQueryTool

10、s-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 Form1 : Form public F

11、orm1() 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 MySQLComm

12、and("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.Close(

13、); 2、通過ODBC訪問mysql數(shù)據(jù)庫： 參考： 1. 安裝Microsoft ODBC.net：我安裝的是mysql-connector-odbc-3.51.22-win32.msi2. 安裝MDAC 2.7或者更高版本：我安裝的是mdac_typ.exe 2.7簡(jiǎn)體中文版3. 安裝MySQL的ODBC驅(qū)動(dòng)程序：我安裝的是 odbc_net.msi4. 管理工具 -> 數(shù)據(jù)源ODBC >配置DSN5. 解決方案管理中添加引用 Microsoft.Data.Odbc.dll(1.0.3300)6. 代碼中增加引用 using Microsoft.Data.Odbc; using

14、 System;using System.Collections.Generic;using System.ComponentModel;using System.Drawing;using System.Linq; /vs2005好像沒有這個(gè)命名空間，在c#2008下測(cè)試自動(dòng)生成的using System.Text;using System.Windows.Forms;using Microsoft.Data.Odbc; namespace mysql public partial class Form1 : Form public Form1() InitializeComponent()

15、; 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 OdbcConnec

16、tion(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); /處理異

17、常：插入重復(fù)記錄有異常try cmd.ExecuteNonQuery();catch(Exception ex) Console.WriteLine("record duplicate.");finally cmd.Dispose(); /*用read方法讀數(shù)據(jù)到textbox* string tmp1 = null; string tmp2 = null; string tmp3 = null; query = "select * from test " OdbcCommand cmd2 = new OdbcCommand(query, MyConne

18、ction); OdbcDataReader reader = cmd2.ExecuteReader(); while (reader.Read() tmp1 = reader0.ToString(); tmp2 = reader1.ToString(); tmp3 = reader2.ToString(); this.textBox1.Text = tmp1 + " " + tmp2 + " " + tmp3; */ /*用datagridview控件顯示數(shù)據(jù)表*string MyConString = "DRIVER=MySQL ODBC

19、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 ", My

20、Connection);DataSet ds = new DataSet(); oda.Fill(ds, "employee"); this.dataGridView1.DataSource = ds.Tables"employee"*/ MyConnection.Close(); 文章出處：1、用MySQLDriverCS連接MySQL數(shù)據(jù)庫先下載和安裝MySQLDriverCS，地址：在安裝文件夾下面找到MySQLDriver.dll，然后將MySQLDriver.dll添加引用到項(xiàng)目中注：我下載的是版本是 MySQLDriverCS-n-EasyQ

21、ueryTools-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 Form1 : Form

22、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 M

23、ySQLCommand("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" con

24、n.Close(); 2、通過ODBC訪問mysql數(shù)據(jù)庫： 參考： 1. 安裝Microsoft ODBC.net：我安裝的是mysql-connector-odbc-3.51.22-win32.msi2. 安裝MDAC 2.7或者更高版本：我安裝的是mdac_typ.exe 2.7簡(jiǎn)體中文版3. 安裝MySQL的ODBC驅(qū)動(dòng)程序：我安裝的是 odbc_net.msi4. 管理工具 -> 數(shù)據(jù)源ODBC >配置DSN5. 解決方案管理中添加引用 Microsoft.Data.Odbc.dll(1.0.3300)6. 代碼中增加引用 using Microsoft.Data.Odb

25、c; using System;using System.Collections.Generic;using System.ComponentModel;using System.Drawing;using System.Linq; /vs2005好像沒有這個(gè)命名空間，在c#2008下測(cè)試自動(dòng)生成的using System.Text;using System.Windows.Forms;using Microsoft.Data.Odbc; namespace mysql public partial class Form1 : Form public Form1() InitializeCom

26、ponent(); 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 Od

27、bcConnection(MyConString); MyConnection.Open(); Console.WriteLine("n success, connected successfully !n"); string query = "insert into test values( ''hello'', ''lucas'', ''liu'')" OdbcCommand cmd = new OdbcCommand(query, MyConnectio

28、n); /處理異常：插入重復(fù)記錄有異常try cmd.ExecuteNonQuery();catch(Exception ex) Console.WriteLine("record duplicate.");finally cmd.Dispose(); /*用read方法讀數(shù)據(jù)到textbox* string tmp1 = null; string tmp2 = null; string tmp3 = null; query = "select * from test " OdbcCommand cmd2 = new OdbcCommand(query,

29、 MyConnection); OdbcDataReader reader = cmd2.ExecuteReader(); while (reader.Read() tmp1 = reader0.ToString(); tmp2 = reader1.ToString(); tmp3 = reader2.ToString(); this.textBox1.Text = tmp1 + " " + tmp2 + " " + tmp3; */ /*用datagridview控件顯示數(shù)據(jù)表*string MyConString = "DRIVER=MyS

30、QL 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 &q

31、uot;, MyConnection);DataSet ds = new DataSet(); oda.Fill(ds, "employee"); this.dataGridView1.DataSource = ds.Tables"employee"*/ MyConnection.Close(); 文章出處：C#連接mysql數(shù)據(jù)庫   1.連接:1.安裝Microsoft ODBC.net。2.安裝MySQL的ODBC驅(qū)動(dòng)程序。2.解決方案管理中添加引用Microsoft.Data.Odbc.dll(1.0.3300)3.代碼中增加引用usi

32、ng 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 Odbc

33、Connection(MyConString); MyConnection.Open();Console.WriteLine("n ! success, connected successfully !n"); MyConnection.Close();2.全部例程:/* sample : mycon.cs* purpose : Demo sample for ODBC.NET using Connector/ODBC* author : Venu, venu* (C) Copyright MySQL AB, 1995-2003*/* build command* * cs

34、c /t:exe * /out:mycon.exe mycon.cs * /r:Microsoft.Data.Odbc.dll */ using 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

35、;" +"DATABASE=test;" +"UID=venu;" +"PASSWORD=venu;" +"OPTION=3"*/Connection string for Connector/ODBC 3.51string MyConString = "DRIVER=MySQL ODBC 3.51 Driver;" + "SERVER=localhost;" +"DATABASE=test;" +"UID=venu;" +&

36、quot;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("Connec

37、tion Information:"); Console.WriteLine("tConnection String:" + MyConnection.ConnectionString); Console.WriteLine("tConnection Timeout:" + MyConnection.ConnectionTimeout); Console.WriteLine("tDatabase:" + MyConnection.Database); Console.WriteLine("tDataSource:&

38、quot; + 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",MyConnection);MyComman

39、d.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:&quo

40、t; + MyCommand.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,'mysq

41、l',500)" Console.WriteLine("INSERT, Total rows affected:" + MyCommand.ExecuteNonQuery();/UpdateMyCommand.CommandText = "UPDATE my_odbc_net SET id=999 WHERE id=20" Console.WriteLine("Update, Total rows affected:" + MyCommand.ExecuteNonQuery();/COUNT(*) MyCommand

42、.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 = MyCommand.ExecuteReader();while (MyDataReader.Read

43、()if(string.Compare(MyConnection.Driver,"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:" +

44、MyDataReader.GetInt32(0) + " " +MyDataReader.GetString(1) + " " + MyDataReader.GetInt32(2); /BIGINTs not supported by Connector/ODBC/Close all resourcesMyDataReader.Close();MyConnection.Close();catch (OdbcException MyOdbcException)/Catch any ODBC exception .for (int i=0; i < M

45、yOdbcException.Errors.Count; i+)Console.Write("ERROR #" + i + "n" +"Message: " + MyOdbcException.Errorsi.Message + "n" +"Native: " + MyOdbcException.Errorsi.NativeError.ToString() + "n" +"Source: " + MyOdbcException.Errorsi.Source

46、 + "n" +"SQL: " + MyOdbcException.Errorsi.SQLState + "n");3.使用dataset填充dataGrid:OdbcConnection con = new OdbcConnection("DRIVER=MySQL ODBC 3.51 Driver;" + "SERVER=glf;" +"DATABASE=qxk_db;" +"UID=root;" +"PASSWORD=;" +&qu

47、ot;OPTION=3");da=new OdbcDataAdapter("select * from achi_eval",con);ds=new DataSet();da.Fill(ds,"customers");dtSource = ds.Tables"customers"pageSize=20;maxRec = dtSource.Rows.Count;PageCount = maxRec / pageSize;if (maxRec % pageSize) > 0) PageCount += 1;currentP

48、age = 1;recNo = 0;LoadPage();這是前一段需要用到,精選了一些資料,希望對(duì)大家有幫助.using System;using System.Configuration;using MySql.Data.MySqlClient;/ <summary>/ TestDatebase 的摘要說明/ </summary>public class TestDatebase    public TestDatebase()   &#

49、160;        /        / TODO: 在此處添加構(gòu)造函數(shù)邏輯        /        public static void Main ( String args )     

50、;       MySqlConnection mysql = getMySqlCon();        /查詢sql        String sqlSearch = "select * from student"        /插入sql 

51、       String sqlInsert = "insert into student values (12,'張三',25,'大專')"        /修改sql        String sqlUpdate = "update student set name='李

52、四' where id= 3"        /刪除sql        String sqlDel = "delete from student where id = 12"        /打印SQL語句        Cons

53、ole.WriteLine ( sqlDel );        /四種語句對(duì)象        /MySqlCommand mySqlCommand = getSqlCommand(sqlSearch, mysql);        /MySqlCommand mySqlCommand = getSqlCommand(sqlInsert, mysql);&#

54、160;       /MySqlCommand mySqlCommand = getSqlCommand(sqlUpdate, mysql);        MySqlCommand mySqlCommand = getSqlCommand ( sqlDel, mysql );        mysql.Open();    

55、    /getResultset(mySqlCommand);        /getInsert(mySqlCommand);        /getUpdate(mySqlCommand);        getDel ( mySqlCommand );     

56、   /記得關(guān)閉        mysql.Close();        String readLine = Console.ReadLine();        / <summary>    / 建立mysql數(shù)據(jù)庫鏈接    / &

57、lt;/summary>    / <returns></returns>    public static MySqlConnection getMySqlCon()            String mysqlStr = "Database=test;Data Source=;User Id=root;P

58、assword=root;pooling=false;CharSet=utf8;port=3306"        / String mySqlCon = ConfigurationManager.ConnectionStrings"MySqlCon".ConnectionString;        MySqlConnection mysql = new MySqlConnection ( mysqlStr );        return mysql;        / <summary>    / 建立執(zhí)行命令語句對(duì)象    / </summary>