c#串連MySQL的教程及執行個體方法

先，我們到官方下載mysql-connector-net-5.5
Mysql的connector/net5.0下載地址:
http://dev.mysql.com/get/Downloads/Connector-Net/mysql-connector-net-5.0.6.zip/from/pick
安裝好以後,
點屬性,然後點尋找目標,點向上一層目錄,找到Binaries\.NET 2.0,然後將這個檔案複製到你的工程目錄下,一般這樣的DLL檔案會儲存到bin目錄下.

在 字碼頁裡輸入using Mysql.Data.MysqlClient;然後再在Page_Load函數裡寫MysqlConnection，在單詞寫到一半時提示就出來了，下 面的就不用寫了吧?都已經出現”代碼智能完成了”,隨便寫一段代碼試試就可以了,和Sqlserver完全相似.
下面提供兩段代碼供參考,一個字碼頁

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using MySql.Data.MySqlClient;
public partial class Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string query = "select * from guestbook";
MySqlConnection myConnection = new MySqlConnection("server=localhost;user id=root;password=;database=guestbook");
MySqlCommand myCommand=new MySqlCommand(query,myConnection);
myConnection.Open();
myCommand.ExecuteNonQuery();
MySqlDataReader myDataReader = myCommand.ExecuteReader();
string bookres="";
while (myDataReader.Read()==true)
{
bookres+=myDataReader["id"];
bookres+=myDataReader["user"];
bookres += myDataReader["pass"];
}
myDataReader.Close();
myConnection.Close();
lb1.Text = bookres;
}
}


1、下載MySQL資料庫，地址：http://www.mysql.com/，大小隻有幾十個兆而已，安裝很方便，接提示即可。

2、 MySQL安裝後預設是沒有用戶端工具的(像SQLServer的企業管理器，查詢分析器等)，只是一個伺服器儲存資料，為了方便你要再下載一個用戶端工 具，有很多，推薦使用SQL Manager for MySQL，簡潔小巧，功能也強大。下載：http://www.sqlmanager.net/en/products/mysql/manager /download

3、ASP.NET串連MySQL需要一個組件(.net本身不提供訪問MySQL的驅 動)MySQL.Data.Dll，此為官方提供(純C#開發，開源噢)，有多個版本選擇，採用的資料訪問模式為ADO.NET，跟asp.net訪問 sqlserver很像，非常簡單。下載(裡邊有個demo)：http://dev.mysql.com/downloads/connector /net/5.1.html



c#串連MySql資料庫的兩種方法

測試環境：Windows XP + MySql 5.0.24 + Visual C# 2008 Exdivss Edition

By lucas 2008.12.29

一、用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.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 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);

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

        }

    }

}


二、通過ODBC訪問mysql資料庫：

參考：http://www.microsoft.com/china/community/Column/63.mspx

1.      安裝Microsoft ODBC.net：我安裝的是mysql-connector-odbc-3.51.22-win32.msi

2.      安裝MDAC 2.7或者更高版本：我安裝的是mdac_typ.exe 2.7簡體中文版

3.      安裝MySQL的ODBC驅動程式：我安裝的是 odbc_net.msi

4.      管理工具 -> 資料來源ODBC ?>配置DSN…

5.      解決方案管理中添加引用 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;

namespace mysql

{

    public partial class Form1 : Form

    {

        public 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 = 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);

            //處理異常：插入重複記錄有異常

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 cmd2 = new OdbcCommand(query, MyConnection);

            OdbcDataReader reader = cmd2.ExecuteReader();

            while (reader.Read())

            {

                tmp1 = reader[0].ToString();

                tmp2 = reader[1].ToString();

                tmp3 = reader[2].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 = 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();

        }

    }

}