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