這里介紹下比較簡單的方式,引用MySql.Data.dll然后添加一個MySqlHelper類來對MySql數據庫進行訪問和操作。
?
1.將MySql.Data.dll引用到你的項目中
下載地址:MySql.Data.rar
添加引用后在你的數據庫操作類(如MySqlHelper.cs)中添加引用聲明
using?MySql.Data;
using?MySql.Data.MySqlClient;
?
2.添加一個MySqlHelper類來對MySql數據庫進行操作。?MySqlHelper.CS網上有很多列子,把代碼拷下來就可以直接使用。如果里面的ExcecuteNonquery、GetDataSet等基本訪問數據庫方法還是無法滿足你開發(fā)需求的話,你還可以自己重新改寫代碼來擴充其功能。
MySqlHelper:
using?System;
using?System.Collections;
using?System.Configuration;
using?MySql.Data;
using?MySql.Data.MySqlClient;
using?System.Data;
///?
public?abstract?class?MySqlHelper
{
????//數據庫連接字符串
????public?static?string?Conn?=?"Database='wp';Data?Source='localhost';User?Id='root';Password='root';charset='utf8';pooling=true";
????//?用于緩存參數的HASH表
????private?static?Hashtable?parmCache?=?Hashtable.Synchronized(new?Hashtable());
????///?
????///?一個有效的連接字符串
????///?命令類型(存儲過程,?文本,?等等)
????///?存儲過程名稱或者sql命令語句
????///?執(zhí)行命令所用參數的集合
????///?
????public?static?int?ExecuteNonQuery(string?connectionString,?CommandType?cmdType,?string?cmdText,?params?MySqlParameter[]?commandParameters)
????{
????????MySqlCommand?cmd?=?new?MySqlCommand();
????????using?(MySqlConnection?conn?=?new?MySqlConnection(connectionString))
????????{
????????????PrepareCommand(cmd,?conn,?null,?cmdType,?cmdText,?commandParameters);
????????????int?val?=?cmd.ExecuteNonQuery();
????????????cmd.Parameters.Clear();
????????????return?val;
????????}
????}
????///?
????///?一個現有的數據庫連接
????///?命令類型(存儲過程,?文本,?等等)
????///?存儲過程名稱或者sql命令語句
????///?執(zhí)行命令所用參數的集合
????///?
????public?static?int?ExecuteNonQuery(MySqlConnection?connection,?CommandType?cmdType,?string?cmdText,?params?MySqlParameter[]?commandParameters)
????{
????????MySqlCommand?cmd?=?new?MySqlCommand();
????????PrepareCommand(cmd,?connection,?null,?cmdType,?cmdText,?commandParameters);
????????int?val?=?cmd.ExecuteNonQuery();
????????cmd.Parameters.Clear();
????????return?val;
????}
????///?
????///?
????///?一個現有的事務
????///?命令類型(存儲過程,?文本,?等等)
????///?存儲過程名稱或者sql命令語句
????///?執(zhí)行命令所用參數的集合
????///?
????public?static?int?ExecuteNonQuery(MySqlTransaction?trans,?CommandType?cmdType,?string?cmdText,?params?MySqlParameter[]?commandParameters)
????{
????????MySqlCommand?cmd?=?new?MySqlCommand();
????????PrepareCommand(cmd,?trans.Connection,?trans,?cmdType,?cmdText,?commandParameters);
????????int?val?=?cmd.ExecuteNonQuery();
????????cmd.Parameters.Clear();
????????return?val;
????}
????///?
????///?
????///?一個有效的連接字符串
????///?命令類型(存儲過程,?文本,?等等)
????///?存儲過程名稱或者sql命令語句
????///?執(zhí)行命令所用參數的集合
????///?
????public?static?MySqlDataReader?ExecuteReader(string?connectionString,?CommandType?cmdType,?string?cmdText,?params?MySqlParameter[]?commandParameters)
????{
????????//創(chuàng)建一個MySqlCommand對象
????????MySqlCommand?cmd?=?new?MySqlCommand();
????????//創(chuàng)建一個MySqlConnection對象
????????MySqlConnection?conn?=?new?MySqlConnection(connectionString);
????????//在這里我們用一個try/catch結構執(zhí)行sql文本命令/存儲過程,因為如果這個方法產生一個異常我們要關閉連接,因為沒有讀取器存在,
????????//因此commandBehaviour.CloseConnection?就不會執(zhí)行
????????try
????????{
????????????//調用?PrepareCommand?方法,對?MySqlCommand?對象設置參數
????????????PrepareCommand(cmd,?conn,?null,?cmdType,?cmdText,?commandParameters);
????????????//調用?MySqlCommand??的?ExecuteReader?方法
????????????MySqlDataReader?reader?=?cmd.ExecuteReader(CommandBehavior.CloseConnection);
????????????//清除參數
????????????cmd.Parameters.Clear();
????????????return?reader;
????????}
????????catch
????????{
????????????//關閉連接,拋出異常
????????????conn.Close();
????????????throw;
????????}
????}
????///?
????///?一個有效的連接字符串
????///?命令類型(存儲過程,?文本,?等等)
????///?存儲過程名稱或者sql命令語句
????///?執(zhí)行命令所用參數的集合
????///?
????public?static?DataSet?GetDataSet(string?connectionString,?CommandType?cmdType,?string?cmdText,?params?MySqlParameter[]?commandParameters)
????{
????????//創(chuàng)建一個MySqlCommand對象
????????MySqlCommand?cmd?=?new?MySqlCommand();
????????//創(chuàng)建一個MySqlConnection對象
????????MySqlConnection?conn?=?new?MySqlConnection(connectionString);
????????//在這里我們用一個try/catch結構執(zhí)行sql文本命令/存儲過程,因為如果這個方法產生一個異常我們要關閉連接,因為沒有讀取器存在,
????????try
????????{
????????????//調用?PrepareCommand?方法,對?MySqlCommand?對象設置參數
????????????PrepareCommand(cmd,?conn,?null,?cmdType,?cmdText,?commandParameters);
????????????//調用?MySqlCommand??的?ExecuteReader?方法
????????????MySqlDataAdapter?adapter?=?new?MySqlDataAdapter();
????????????adapter.SelectCommand?=?cmd;
????????????DataSet?ds?=?new?DataSet();
????????????adapter.Fill(ds);
????????????//清除參數
????????????cmd.Parameters.Clear();
????????????conn.Close();
????????????return?ds;
????????}
????????catch?(Exception?e)
????????{
????????????throw?e;
????????}
????}
????///?
????///?
????///一個有效的連接字符串
????///?命令類型(存儲過程,?文本,?等等)
????///?存儲過程名稱或者sql命令語句
????///?執(zhí)行命令所用參數的集合
????///?
????public?static?object?ExecuteScalar(string?connectionString,?CommandType?cmdType,?string?cmdText,?params?MySqlParameter[]?commandParameters)
????{
????????MySqlCommand?cmd?=?new?MySqlCommand();
????????using?(MySqlConnection?connection?=?new?MySqlConnection(connectionString))
????????{
????????????PrepareCommand(cmd,?connection,?null,?cmdType,?cmdText,?commandParameters);
????????????object?val?=?cmd.ExecuteScalar();
????????????cmd.Parameters.Clear();
????????????return?val;
????????}
????}
????///?
????///?
????///?一個存在的數據庫連接
????///?命令類型(存儲過程,?文本,?等等)
????///?存儲過程名稱或者sql命令語句
????///?執(zhí)行命令所用參數的集合
????///?
????public?static?object?ExecuteScalar(MySqlConnection?connection,?CommandType?cmdType,?string?cmdText,?params?MySqlParameter[]?commandParameters)
????{
????????MySqlCommand?cmd?=?new?MySqlCommand();
????????PrepareCommand(cmd,?connection,?null,?cmdType,?cmdText,?commandParameters);
????????object?val?=?cmd.ExecuteScalar();
????????cmd.Parameters.Clear();
????????return?val;
????}
????///?
????///?添加到緩存的變量
????///?一個將要添加到緩存的sql參數集合
????public?static?void?CacheParameters(string?cacheKey,?params?MySqlParameter[]?commandParameters)
????{
????????parmCache[cacheKey]?=?commandParameters;
????}
????///?
????///?用于找回參數的關鍵字
????///?
????public?static?MySqlParameter[]?GetCachedParameters(string?cacheKey)
????{
????????MySqlParameter[]?cachedParms?=?(MySqlParameter[])parmCache[cacheKey];
????????if?(cachedParms?==?null)
????????????return?null;
????????MySqlParameter[]?clonedParms?=?new?MySqlParameter[cachedParms.Length];
????????for?(int?i?=?0,?j?=?cachedParms.Length;?i?<?j;?i++)
????????????clonedParms[i]?=?(MySqlParameter)((ICloneable)cachedParms[i]).Clone();
????????return?clonedParms;
????}
????///?
????///?sql命令
????///?OleDb連接
????///?OleDb事務
????///?命令類型例如?存儲過程或者文本
????///?命令文本,例如:Select?*?from?Products
????///?執(zhí)行命令的參數
????private?static?void?PrepareCommand(MySqlCommand?cmd,?MySqlConnection?conn,?MySqlTransaction?trans,?CommandType?cmdType,?string?cmdText,?MySqlParameter[]?cmdParms)
????{
????????if?(conn.State?!=?ConnectionState.Open)
????????????conn.Open();
????????cmd.Connection?=?conn;
????????cmd.CommandText?=?cmdText;
????????if?(trans?!=?null)
????????????cmd.Transaction?=?trans;
????????cmd.CommandType?=?cmdType;
????????if?(cmdParms?!=?null)
????????{
????????????foreach?(MySqlParameter?parm?in?cmdParms)
????????????????cmd.Parameters.Add(parm);
????????}
????}
}
?
MySqlHelper里面的方法和SqlHelper差不多,就不一一說明了。
除此之外引用DBUTILITY類庫也是一個很好的選擇,DBUTILITY.DLL是一個集成SQLHELPER、MYSQLHELPER以及ORACLEHELPER等眾多數據庫操作類的類庫,添加引用后就可以直接調用其中方法對數據庫進行查詢、編輯等操作,十分的方便。
?
3.配置你的MYSQL數據庫鏈接字符串如下:
//數據庫連接字符串
public?static?string?Conn?=?"Database='數據庫名';Data?Source='數據庫服務器地址';User?Id='數據庫用戶名';Password='密碼';charset='utf8';pooling=true";
由于PHPMYADMIN相當于一個小型網站,因此在你的數據庫連接中還要設置下PHPMYADMIN編碼charset='utf8'
?
4.以上準備工作完成后,就可以通過調用MySQLHelper類里面的方法來對MYSQL數據庫進行操作了,這些方法的用法也與SqlHelper類似。
dgv.DataSource=MySqlHelper.GetDataSet(MySqlHelper.Conn,?CommandType.Text,?"select?*?from?wp_posts",?null).Tables[0].DefaultView;
執(zhí)行MySql查詢后得到結果:
對應的MySql數據庫中的數據: