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>
/// 数据库连接字符串
/// </summary>
public static string ConnectionStringFormatMysql = "server={0};user id={1};password={2};database={3};port={4}";
// <summary>
// 创建数据库连接
// </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 的摘要说明
/// </summary>
public class MySqlHelper : IDbHelper
{
public void Dispose()
{
Dispose(true);
//.NET Framework 类库
// GC..::.SuppressFinalize 方法
//请求系统不要调用指定对象的终结器。
GC.SuppressFinalize(this);
}
/// <summary>
/// 虚方法，可供子类重写
/// </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>
/// 数据库连接字符串
/// </summary>
public string _connStr ="";// my.ConnectionString;
/// <summary>
/// 是否启用事务
/// </summary>
private bool _isTransaction;
/// <summary>
/// 连接使用次数
/// </summary>
private int _useCount;
/// <summary>
/// 对象连接使用次数
/// </summary>
public int UseCount
{
get
{
return _useCount;
}
}
#endregion
#region 构造函数
/// <summary>
/// 构造函数
/// </summary>
/// <param name="openTransaction">是否开启事务</param>
public MySqlHelper(bool openTransaction)
{
this._isTransaction = openTransaction;
this._useCount = 0;
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="openTransaction">是否开启事务</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("数据库连接失败") ;
}
}
/// <summary>
/// 给定连接的数据库用假设参数执行一个sql命令（不返回数据集）
/// </summary>
/// <param name="connectionString">一个有效的连接字符串</param>
/// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
/// <param name="cmdText">存储过程名称或者sql命令语句</param>
/// <param name="commandParameters">执行命令所用参数的集合</param>
/// <returns>执行命令所影响的行数</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>
/// 用现有的数据库连接执行一个sql命令（不返回数据集）
/// </summary>
/// <param name="connection">一个现有的数据库连接</param>
/// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
/// <param name="cmdText">存储过程名称或者sql命令语句</param>
/// <param name="commandParameters">执行命令所用参数的集合</param>
/// <returns>执行命令所影响的行数</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>
///使用现有的SQL事务执行一个sql命令（不返回数据集）
/// </summary>
/// <remarks>
///举例:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
/// </remarks>
/// <param name="trans">一个现有的事务</param>
/// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
/// <param name="cmdText">存储过程名称或者sql命令语句</param>
/// <param name="commandParameters">执行命令所用参数的集合</param>
/// <returns>执行命令所影响的行数</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>
/// 用执行的数据库连接执行一个返回数据集的sql命令
/// </summary>
/// <remarks>
/// 举例:
/// MySqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">一个有效的连接字符串</param>
/// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
/// <param name="cmdText">存储过程名称或者sql命令语句</param>
/// <param name="commandParameters">执行命令所用参数的集合</param>
/// <returns>包含结果的读取器</returns>
public MySqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
//创建一个MySqlCommand对象
MySqlCommand cmd = new MySqlCommand();
//创建一个MySqlConnection对象
MySqlConnection conn = new MySqlConnection(connectionString);
//在这里我们用一个try/catch结构执行sql文本命令/存储过程，因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在，
//因此commandBehaviour.CloseConnection 就不会执行
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;
}
}
/// <summary>
/// 返回DataSet
/// </summary>
/// <param name="connectionString">一个有效的连接字符串</param>
/// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
/// <param name="cmdText">存储过程名称或者sql命令语句</param>
/// <param name="commandParameters">执行命令所用参数的集合</param>
/// <returns></returns>
public DataSet GetDataSet(CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
//创建一个MySqlCommand对象
MySqlCommand cmd = new MySqlCommand();
//创建一个MySqlConnection对象
MySqlConnection conn = new MySqlConnection(this._connStr);
//在这里我们用一个try/catch结构执行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;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 返回DataDatable
/// </summary>
/// <param name="connectionString">一个有效的连接字符串</param>
/// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
/// <param name="cmdText">存储过程名称或者sql命令语句</param>
/// <param name="commandParameters">执行命令所用参数的集合</param>
/// <returns></returns>
public DataTable GetDataTable(CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
DataSet ds = GetDataSet(cmdType, cmdText, commandParameters);
return ds.Tables[0];
}
/// <summary>
/// 用指定的数据库连接字符串执行一个命令并返回一个数据集的第一列
/// </summary>
/// <remarks>
///例如:
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
/// </remarks>
///<param name="connectionString">一个有效的连接字符串</param>
/// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
/// <param name="cmdText">存储过程名称或者sql命令语句</param>
/// <param name="commandParameters">执行命令所用参数的集合</param>
/// <returns>用 Convert.To{Type}把类型转换为想要的 </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>
/// 用指定的数据库连接执行一个命令并返回一个数据集的第一列
/// </summary>
/// <remarks>
/// 例如:
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">一个存在的数据库连接</param>
/// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
/// <param name="cmdText">存储过程名称或者sql命令语句</param>
/// <param name="commandParameters">执行命令所用参数的集合</param>
/// <returns>用 Convert.To{Type}把类型转换为想要的 </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>
/// 准备执行一个命令
/// </summary>
/// <param name="cmd">sql命令</param>
/// <param name="conn">OleDb连接</param>
/// <param name="trans">OleDb事务</param>
/// <param name="cmdType">命令类型例如 存储过程或者文本</param>
/// <param name="cmdText">命令文本,例如:Select * from Products</param>
/// <param name="cmdParms">执行命令的参数</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>
/// 批量执行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>
/// 数据库辅助类接口
/// </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>
/// 批量执行SQL语句
/// </summary>
/// <param name="SQLStringList"></param>
void ExecuteSqlTran(List<string> SQLStringList);
}
