using System;
using System.Collections;
using System.Configuration;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Data;
using DBHelper;
using System.Collections.Generic;
public static class DbHelper
{
public static string ConnectionString = "";// my.ConnectionString;
/// <summary>
/// 數(shù)據(jù)庫(kù)連接字符串
/// </summary>
public static string ConnectionStringFormatMysql = "server={0};user id={1};password={2};database={3};port={4}";
// <summary>
// 創(chuàng)建數(shù)據(jù)庫(kù)連接
// </summary>
// <returns></returns>
public static IDbHelper NewHelper(bool openTransaction = false)
{
return new MySqlHelper(openTransaction, ConnectionString);
}
public static void Init()
{
ConnectionString = string.Format(ConnectionStringFormatMysql , "localhost" , "root" , "root" , "mysql" , 3306);
}
}
/// <summary>
///MYSQLHelper 的摘要說(shuō)明
/// </summary>
public class MySqlHelper : IDbHelper
{
public void Dispose()
{
Dispose(true);
//.NET Framework 類(lèi)庫(kù)
// GC..::.SuppressFinalize 方法
//請(qǐng)求系統(tǒng)不要調(diào)用指定對(duì)象的終結(jié)器。
GC.SuppressFinalize(this);
}
/// <summary>
/// 虛方法,可供子類(lèi)重寫(xiě)
/// </summary>
/// <param name="disposing"></param>
protected virtual void Dispose(bool disposing)
{
if (!m_disposed)
{
if (disposing)
{
// Release managed resources
}
// Release unmanaged resources
m_disposed = true;
}
}
~MySqlHelper()
{
Dispose(false);
}
public bool m_disposed;
#region 成員變量
/// <summary>
/// 數(shù)據(jù)庫(kù)連接字符串
/// </summary>
public string _connStr ="";// my.ConnectionString;
/// <summary>
/// 是否啟用事務(wù)
/// </summary>
private bool _isTransaction;
/// <summary>
/// 連接使用次數(shù)
/// </summary>
private int _useCount;
/// <summary>
/// 對(duì)象連接使用次數(shù)
/// </summary>
public int UseCount
{
get
{
return _useCount;
}
}
#endregion
#region 構(gòu)造函數(shù)
/// <summary>
/// 構(gòu)造函數(shù)
/// </summary>
/// <param name="openTransaction">是否開(kāi)啟事務(wù)</param>
public MySqlHelper(bool openTransaction)
{
this._isTransaction = openTransaction;
this._useCount = 0;
}
/// <summary>
/// 構(gòu)造函數(shù)
/// </summary>
/// <param name="openTransaction">是否開(kāi)啟事務(wù)</param>
public MySqlHelper(bool openTransaction, string connStr)
{
this._isTransaction = openTransaction;
_connStr = connStr;
this._useCount = 0;
}
#endregion
public bool IsTestConnection(string connstring)
{
try
{
MySqlCommand cmd = new MySqlCommand();
MySqlConnection conn = new MySqlConnection(connstring);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
return true;
}
catch (Exception)
{
throw new Exception("數(shù)據(jù)庫(kù)連接失敗") ;
}
}
/// <summary>
/// 給定連接的數(shù)據(jù)庫(kù)用假設(shè)參數(shù)執(zhí)行一個(gè)sql命令(不返回?cái)?shù)據(jù)集)
/// </summary>
/// <param name="connectionString">一個(gè)有效的連接字符串</param>
/// <param name="cmdType">命令類(lèi)型(存儲(chǔ)過(guò)程, 文本, 等等)</param>
/// <param name="cmdText">存儲(chǔ)過(guò)程名稱(chēng)或者sql命令語(yǔ)句</param>
/// <param name="commandParameters">執(zhí)行命令所用參數(shù)的集合</param>
/// <returns>執(zhí)行命令所影響的行數(shù)</returns>
public int ExecuteNonQuery(CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
MySqlCommand cmd = new MySqlCommand();
using (MySqlConnection conn = new MySqlConnection(this._connStr))
{
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
catch (MySqlException ex)
{
throw new Exception(ex.Message);
}
finally
{
conn.Close();
}
}
}
/// <summary>
/// 用現(xiàn)有的數(shù)據(jù)庫(kù)連接執(zhí)行一個(gè)sql命令(不返回?cái)?shù)據(jù)集)
/// </summary>
/// <param name="connection">一個(gè)現(xiàn)有的數(shù)據(jù)庫(kù)連接</param>
/// <param name="cmdType">命令類(lèi)型(存儲(chǔ)過(guò)程, 文本, 等等)</param>
/// <param name="cmdText">存儲(chǔ)過(guò)程名稱(chēng)或者sql命令語(yǔ)句</param>
/// <param name="commandParameters">執(zhí)行命令所用參數(shù)的集合</param>
/// <returns>執(zhí)行命令所影響的行數(shù)</returns>
public 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;
}
/// <summary>
///使用現(xiàn)有的SQL事務(wù)執(zhí)行一個(gè)sql命令(不返回?cái)?shù)據(jù)集)
/// </summary>
/// <remarks>
///舉例:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
/// </remarks>
/// <param name="trans">一個(gè)現(xiàn)有的事務(wù)</param>
/// <param name="cmdType">命令類(lèi)型(存儲(chǔ)過(guò)程, 文本, 等等)</param>
/// <param name="cmdText">存儲(chǔ)過(guò)程名稱(chēng)或者sql命令語(yǔ)句</param>
/// <param name="commandParameters">執(zhí)行命令所用參數(shù)的集合</param>
/// <returns>執(zhí)行命令所影響的行數(shù)</returns>
public 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;
}
/// <summary>
/// 用執(zhí)行的數(shù)據(jù)庫(kù)連接執(zhí)行一個(gè)返回?cái)?shù)據(jù)集的sql命令
/// </summary>
/// <remarks>
/// 舉例:
/// MySqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">一個(gè)有效的連接字符串</param>
/// <param name="cmdType">命令類(lèi)型(存儲(chǔ)過(guò)程, 文本, 等等)</param>
/// <param name="cmdText">存儲(chǔ)過(guò)程名稱(chēng)或者sql命令語(yǔ)句</param>
/// <param name="commandParameters">執(zhí)行命令所用參數(shù)的集合</param>
/// <returns>包含結(jié)果的讀取器</returns>
public 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ǔ)過(guò)程,因?yàn)槿绻@個(gè)方法產(chǎn)生一個(gè)異常我們要關(guān)閉連接委乌,因?yàn)闆](méi)有讀取器存在枣宫,
//因此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;
}
}
/// <summary>
/// 返回DataSet
/// </summary>
/// <param name="connectionString">一個(gè)有效的連接字符串</param>
/// <param name="cmdType">命令類(lèi)型(存儲(chǔ)過(guò)程, 文本, 等等)</param>
/// <param name="cmdText">存儲(chǔ)過(guò)程名稱(chēng)或者sql命令語(yǔ)句</param>
/// <param name="commandParameters">執(zhí)行命令所用參數(shù)的集合</param>
/// <returns></returns>
public DataSet GetDataSet(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(this._connStr);
//在這里我們用一個(gè)try/catch結(jié)構(gòu)執(zhí)行sql文本命令/存儲(chǔ)過(guò)程撼玄,因?yàn)槿绻@個(gè)方法產(chǎn)生一個(gè)異常我們要關(guān)閉連接炬搭,因?yàn)闆](méi)有讀取器存在蜈漓,
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;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 返回DataDatable
/// </summary>
/// <param name="connectionString">一個(gè)有效的連接字符串</param>
/// <param name="cmdType">命令類(lèi)型(存儲(chǔ)過(guò)程, 文本, 等等)</param>
/// <param name="cmdText">存儲(chǔ)過(guò)程名稱(chēng)或者sql命令語(yǔ)句</param>
/// <param name="commandParameters">執(zhí)行命令所用參數(shù)的集合</param>
/// <returns></returns>
public DataTable GetDataTable(CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
DataSet ds = GetDataSet(cmdType, cmdText, commandParameters);
return ds.Tables[0];
}
/// <summary>
/// 用指定的數(shù)據(jù)庫(kù)連接字符串執(zhí)行一個(gè)命令并返回一個(gè)數(shù)據(jù)集的第一列
/// </summary>
/// <remarks>
///例如:
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
/// </remarks>
///<param name="connectionString">一個(gè)有效的連接字符串</param>
/// <param name="cmdType">命令類(lèi)型(存儲(chǔ)過(guò)程, 文本, 等等)</param>
/// <param name="cmdText">存儲(chǔ)過(guò)程名稱(chēng)或者sql命令語(yǔ)句</param>
/// <param name="commandParameters">執(zhí)行命令所用參數(shù)的集合</param>
/// <returns>用 Convert.To{Type}把類(lèi)型轉(zhuǎn)換為想要的 </returns>
public 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;
}
}
/// <summary>
/// 用指定的數(shù)據(jù)庫(kù)連接執(zhí)行一個(gè)命令并返回一個(gè)數(shù)據(jù)集的第一列
/// </summary>
/// <remarks>
/// 例如:
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">一個(gè)存在的數(shù)據(jù)庫(kù)連接</param>
/// <param name="cmdType">命令類(lèi)型(存儲(chǔ)過(guò)程, 文本, 等等)</param>
/// <param name="cmdText">存儲(chǔ)過(guò)程名稱(chēng)或者sql命令語(yǔ)句</param>
/// <param name="commandParameters">執(zhí)行命令所用參數(shù)的集合</param>
/// <returns>用 Convert.To{Type}把類(lèi)型轉(zhuǎn)換為想要的 </returns>
public 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;
}
/// <summary>
/// 準(zhǔn)備執(zhí)行一個(gè)命令
/// </summary>
/// <param name="cmd">sql命令</param>
/// <param name="conn">OleDb連接</param>
/// <param name="trans">OleDb事務(wù)</param>
/// <param name="cmdType">命令類(lèi)型例如 存儲(chǔ)過(guò)程或者文本</param>
/// <param name="cmdText">命令文本,例如:Select * from Products</param>
/// <param name="cmdParms">執(zhí)行命令的參數(shù)</param>
private 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);
}
}
/// <summary>
/// 批量執(zhí)行SQL
/// </summary>
/// <param name="SQLStringList"></param>
public void ExecuteSqlTran(List<string> SQLStringList)
{
using (MySqlConnection conn = new MySqlConnection(this._connStr))
{
MySqlCommand cmd = new MySqlCommand();
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
MySqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (Exception E)
{
tx.Rollback();
throw new Exception(E.Message);
}
finally
{
conn.Close();
}
}
}
}
/// <summary>
/// 數(shù)據(jù)庫(kù)輔助類(lèi)接口
/// </summary>
public interface IDbHelper : IDisposable
{
bool IsTestConnection(string connstring);
int ExecuteNonQuery(CommandType cmdType , string cmdText , params MySqlParameter [] commandParameters);
int ExecuteNonQuery(MySqlConnection connection , CommandType cmdType , string cmdText , params MySqlParameter [] commandParameters);
int ExecuteNonQuery(MySqlTransaction trans , CommandType cmdType , string cmdText , params MySqlParameter [] commandParameters);
MySqlDataReader ExecuteReader(string connectionString , CommandType cmdType , string cmdText , params MySqlParameter [] commandParameters);
DataSet GetDataSet(CommandType cmdType , string cmdText , params MySqlParameter [] commandParameters);
DataTable GetDataTable(CommandType cmdType , string cmdText , params MySqlParameter [] commandParameters);
object ExecuteScalar(string connectionString , CommandType cmdType , string cmdText , params MySqlParameter [] commandParameters);
object ExecuteScalar(MySqlConnection connection , CommandType cmdType , string cmdText , params MySqlParameter [] commandParameters);
/// <summary>
/// 批量執(zhí)行SQL語(yǔ)句
/// </summary>
/// <param name="SQLStringList"></param>
void ExecuteSqlTran(List<string> SQLStringList);
}