using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using log4net;
using System.Configuration;

namespace CH_Common
{
public class DBHelper
{
#region 成员变量
string connectionString;
SqlConnection connection;
SqlTransaction transaction;
Hashtable listParameters;
private ILog log = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
#endregion

#region 构造方法
/// <summary>
/// 默认构造方法
/// </summary>
public DBHelper()
{
this.connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
connection = new SqlConnection(connectionString);
}

/// <summary>
/// 含参的构造方法
/// </summary>
/// <param name="connectionString">连接字符串</param>
public DBHelper(string connectionString)
{
this.connectionString = connectionString;
connection = new SqlConnection(connectionString);
}
#endregion

#region 数据库连接的打开、关闭、释放
/// <summary>
/// 打开数据库连接
/// </summary>
public void Open()
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public void Close()
{
if (connection.State.ToString() == "Open")
{
connection.Close();
InitMember();
}
}
/// <summary>
/// 参数初始化
/// </summary>
void InitMember()
{
listParameters = null;
//cmd = null;
}

/// <summary>
/// 释放连接
/// </summary>
public void Dispose()
{
if (connection != null)
{
connection.Close();
connection.Dispose();
}
GC.Collect();
}
#endregion

#region 设置连接字符串
/// <summary>
/// 设置连接字符串
/// </summary>
/// <param name="strConnectionString">连接字符串</param>
public void SetConnection(string strConnectionString)
{
connectionString = strConnectionString;
connection = new SqlConnection(connectionString);
}
#endregion

#region Command的生成与设置
/// <summary>
/// 设置Command
/// </summary>
/// <param name="SQL">SQL文</param>
/// <param name="cmdType">CommandType</param>
private SqlCommand SetCmd(string SQL, CommandType cmdType)
{
Open();
SqlCommand cmd = new SqlCommand(SQL, connection);
if (null != transaction)
{
cmd.Transaction = transaction;
}
cmd.CommandType = cmdType;
cmd.CommandText = SQL;
cmd.Parameters.Clear();
string strSQLLog = "";
if (listParameters != null)
{
foreach (DictionaryEntry parameter in listParameters)
{
cmd.Parameters.Add(new SqlParameter(parameter.Key.ToString(), parameter.Value));
strSQLLog = strSQLLog + "[" + parameter.Key.ToString() + "]=[" + parameter.Value.ToString() + "]";
}
log.Info(strSQLLog);
ClearParameter();
}
return cmd;
}
/// <summary>
/// 生成Commond
/// </summary>
/// <param name="SQL"></param>
/// <returns></returns>
private SqlCommand CreateCmd(string SQL)
{
return SetCmd(SQL, CommandType.Text);
}

private SqlCommand CreateProc(string procName)
{
return SetCmd(procName, CommandType.StoredProcedure);
}

#endregion

#region SqlDataReader返回
/// <summary>
/// SqlDataReader返回
/// </summary>
/// <param name="SQL">SQL文</param>
/// <returns>SqlDataReader</returns>
private SqlDataReader ReturnDataReader(string SQL)
{
return CreateCmd(SQL).ExecuteReader();
}
#endregion

#region SqlParameter参数追加
/// <summary>
/// SqlParameter参数追加
/// </summary>
/// <param name="ParamName">参数名</param>
/// <param name="Value">参数值</param>
public void AddParameter(string ParamName, object Value)
{
try
{
if (listParameters == null)
{
listParameters = new Hashtable();
}
listParameters.Add(ParamName, Value);
}
catch (Exception ex)
{
log.Error("Add SqlParameter Error: " + ex.Message);
log.Error(ex);
throw ex;
}
}
#endregion

#region SqlParameter清空
/// <summary>
/// SqlParameter清空
/// </summary>
public void ClearParameter() { listParameters = null; }
#endregion

#region SqlDataAdapter适配器返回
/// <summary>
/// SqlDataAdapter适配器返回
/// </summary>
/// <param name="SQL">SQL语句</param>
/// <returns>SqlDataAdapter对象</returns>
private SqlDataAdapter GetDataAdapter(string SQL)
{
SqlDataAdapter Da = new SqlDataAdapter();

Da.SelectCommand = SetCmd(SQL, CommandType.Text);
SqlCommandBuilder custCB = new SqlCommandBuilder(Da);
return Da;
}
#endregion

#region SQL执行

/// <summary>
/// SQL执行
/// </summary>
/// <param name="SQL">SQL语句</param>
/// <returns>-1:失败 其他:成功</returns>
public int ExeCmd(string SQL)
{
int ret = -1;

string strSQLLog = string.Empty;
string sqlLog = "";
if (listParameters != null)
{
strSQLLog = SQL;
foreach (DictionaryEntry parameter in listParameters)
{
strSQLLog = strSQLLog.Replace("@" + parameter.Key.ToString(), "‘" + parameter.Value.ToString() + "‘");
sqlLog = sqlLog + "[" + parameter.Key.ToString() + "]=[" + parameter.Value.ToString() + "]";
}
}

try
{
log.Info("SQL Execute Start:" + SQL + " " + sqlLog);
ret = CreateCmd(SQL).ExecuteNonQuery();
log.Info("SQL Execute End");
}
catch (Exception ex)
{
log.Error("SQL Execute Error Occured:" + SQL + " " + sqlLog);
log.Error("SQL Execute ErrorMessage:" + ex.Message);
log.Error(ex);
RollbackTransaction();
throw ex;
}
finally
{
if (null == transaction)
{
Close();
}

ClearParameter();
}
return ret;
}
#endregion

#region DataSet返回

/// <summary>
/// SQL执行
/// </summary>
/// <param name="SQL">SQL文</param>
/// <returns>DataSet</returns>
public DataSet ReturnDataSet(string SQL)
{
DataSet Ds = new DataSet();
try
{
log.Info("SQL Execute Start:" + SQL);
SqlDataAdapter Da = GetDataAdapter(SQL);
Da.Fill(Ds);
log.Info("SQL Execute End");
}
catch (Exception ex)
{
log.Error("SQL Execute Error Occured:" + SQL);
log.Error("SQL Execute ErrorMessage:" + ex.Message);
log.Error(ex);
throw ex;
}
finally
{
if (null == transaction)
{
Close();
}
}
return Ds;
}
#endregion

#region DataTable返回
/// <summary>
/// DataTable返回
/// </summary>
/// <param name="SQL">SQL文</param>
/// <returns>DataTable</returns>
public DataTable ReturnDataTable(string SQL)
{
DataTable dt = new DataTable("tempDt");//跨进程序列化，需要为datatable命名
try
{
log.Info("SQL Execute Start:" + SQL);
SqlDataAdapter Da = GetDataAdapter(SQL);
Da.Fill(dt);
log.Info("SQL Execute End");
}
catch (Exception ex)
{
log.Error("SQL Execute Error Occured:" + SQL);
log.Error("SQL Execute ErrorMessage:" + ex.Message);
log.Error(ex);
throw ex;
}
finally
{
if (null == transaction)
{
Close();
}
}
return dt;
}
#endregion

#region 事务
/// <summary>
/// 开始事务
/// </summary>
public void BeginTransaction()
{
Open();
//cmd = connection.CreateCommand();
transaction = connection.BeginTransaction();

//cmd.Transaction = transaction;
}
/// <summary>
/// 提交事务
/// </summary>
public void CommitTransaction()
{
try
{
if (null != transaction)
{
transaction.Commit();
}
}
catch (System.Exception ex)
{
log.Error("transaction error:" + ex.Message);
log.Error(ex);
transaction.Rollback();
throw ex;
}
finally
{
transaction = null;
Close();
}
}
/// <summary>
/// 回滚事务
/// </summary>
public void RollbackTransaction()
{
if (null != transaction)
{
transaction.Rollback();
transaction = null;
}
}
#endregion

#region 存储过程
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="proc_name"></param>
/// <returns></returns>
public int ExecProcedure(string proc_name)
{
int ret = -1;

string strSQLLog = string.Empty;
string sqlLog = "";
if (listParameters != null)
{

foreach (DictionaryEntry parameter in listParameters)
{
strSQLLog = strSQLLog.Replace("@" + parameter.Key.ToString(), "‘" + parameter.Value.ToString() + "‘");
sqlLog = sqlLog + "[" + parameter.Key.ToString() + "]=[" + parameter.Value.ToString() + "]";
}
}

try
{
log.Info("Procedure Execute Start:" + proc_name + " " + sqlLog);
ret = CreateProc(proc_name).ExecuteNonQuery();
log.Info("Procedure Execute End");
}
catch (Exception ex)
{
log.Error("Procedure Execute Error Occured:" + proc_name + " " + sqlLog);
log.Error("Procedure Execute ErrorMessage:" + ex.Message);
log.Error(ex);
RollbackTransaction();
throw ex;
}
finally
{
if (null == transaction)
{
Close();
}

ClearParameter();
}
return ret;
}
#endregion
}
}

数据库连接通用DBHelper类