C# 利用MS的 EntLib的Database类编写的DbHelper
C# 利用MS的 EntLib的Database类编写的DbHelper，由于MS的EntLib对Oracle、SQL Server和MySql已经封装，所以可以该DbHelper可以适用这三种数据库，根据Web.config中connectionString节点配置数据库连接，会自动根据providerName属性来选择是使用了Oracle数据库还是Sql Server。
需要在Web.config的配置数据库：
<connectionStrings>
<!-- 数据库连接字符串 -->
<add name="ConnectionStringWSC" connectionString="Data Source=(local);Initial Catalog=F.WSC;User ID=sa;Password=sa" providerName="System.Data.SqlClient"/>
</connectionStrings>
各位使用时需要引用微软的EntLib：
using Microsoft.Practices;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data;
1. 实现基本的数据操作： 查询、删除、新增
2. 支持事务
3. 支持存储过程使用参数
不废话，看代码：
using System; using System.Data; using System.Data.SqlClient; using System.Collections.Generic; using System.Collections; using System.IO; using System.Web; using Microsoft.Practices; using Microsoft.Practices.EnterpriseLibrary.Common.Configuration; using Microsoft.Practices.EnterpriseLibrary.Data; using System.Data.Common; using System.Diagnostics; using System.Text; namespace WSC.Common { /// <summary> /// 数据库服务基类，支持SQL Server,Oracle和MySql /// </summary> public class DbHelper : IDisposable { private Database db; private DbConnection conn; /// <summary> /// /// </summary> public string LastError = ""; #region Constructor and Destructor /// <summary> /// 数据库服务基类，支持SQL Server,Oracle和MySql /// </summary> public DbHelper() { this.LastError = string.Empty; try { db = getDb(""); conn = db.CreateConnection(); conn.Open(); } catch (Exception ex) { this.LastError = ex.Message; WriteErrLog("DbHelper", "Constructor", ex.StackTrace); throw new Exception("数据库初始化失败！", ex); } } /// <summary> /// 数据库服务基类，支持SQL Server,Oracle和MySql。Represent the system database operation class for local caller. /// <param name="connectionString">Database connection string</param> /// </summary> public DbHelper(string connectionString) { this.LastError = string.Empty; try { db = getDb(connectionString); conn = db.CreateConnection(); conn.Open(); } catch (Exception ex) { this.LastError = ex.Message; WriteErrLog("DbHelper", "Constructor", ex.StackTrace); throw new Exception("数据库初始化失败！", ex); } } private Database getDb(string connectionStringKey) { if (String.IsNullOrWhiteSpace(connectionStringKey)) connectionStringKey = Global.ConnectionStringKey; //return DatabaseFactory.CreateDatabase(connectionString); return EnterpriseLibraryContainer.Current.GetInstance<Database>(connectionStringKey); } /// <summary> /// /// </summary> ~DbHelper() { this.Dispose(); } #region IDisposable Members private bool disposed = false; /// <summary> /// GC /// </summary> public void Dispose() { Dispose(true); GC.SuppressFinalize(this); } private void Dispose(bool disposing) { if (!this.disposed && disposing) { try { if ((conn != null) && (conn.State != ConnectionState.Closed)) { conn.Close(); conn.Dispose(); } if (_transaction != null) _transaction.Dispose(); _transaction = null; conn = null; } catch { } } disposed = true; } #endregion #endregion /// <summary> /// Current database /// </summary> public Database GetDatabase { get { return db; } } #region Execute SQL /// <summary> /// 执行StoreProcedure或Select、Delete、Update等 /// </summary> /// <param name="SQL">要执行的SQL语句</param> /// <returns>SUCCESS|Error message</returns> public string Execute(string SQL) { try { if (String.IsNullOrWhiteSpace(SQL)) return "没有可执行的SQL语句"; this.LastError = string.Empty; using (DbCommand cmd = db.GetSqlStringCommand(SQL)) { cmd.Connection = conn; if (_transaction != null && _transaction.Connection.State == ConnectionState.Open) cmd.Transaction = _transaction; cmd.CommandType = CommandType.Text; cmd.CommandTimeout = 600; //10 minutes this.InjectParameters(cmd); int ret = cmd.ExecuteNonQuery(); WriteLog("Execute", SQL, "成功， 影响记录数：" + ret.ToString() + "条"); } return Global.SUCCESS; } catch (Exception ex) { this.LastError = ex.Message; WriteErrLog("Execute", SQL, ex.Message + "\\r\\n" + ex.StackTrace); return "执行失败, -2！ " + ex.Message; } } /// <summary> /// 执行多SQL语句，StoreProcedure或Select、Delete、Update等 /// </summary> /// <param name="SQL">要执行的SQL语句集合</param> /// <returns>true|false</returns> public bool Execute(List<string> SQL) { if (SQL == null || SQL.Count == 0) return false; StringBuilder sb = new StringBuilder(); SQL.ForEach(x => { sb.AppendLine(x); }); try { this.LastError = string.Empty; using (DbCommand cmd = conn.CreateCommand()) { if (_transaction != null && _transaction.Connection.State == ConnectionState.Open) cmd.Transaction = _transaction; cmd.CommandType = CommandType.Text; cmd.CommandTimeout = 600; //10 minutes this.InjectParameters(cmd); foreach (string sql in SQL) { cmd.CommandText = sql; cmd.ExecuteNonQuery(); } } WriteLog("Execute(List[SQL])", sb.ToString(), Global.SUCCESS); return true; } catch (Exception ex) { this.LastError = ex.Message; WriteErrLog("Execute", sb.ToString(), ex.Message + "\\r\\n" + ex.StackTrace); return false; } } /// <summary> /// 执行多SQL语句，StoreProcedure或Select、Delete、Update等，有事务支持。 /// </summary> /// <param name="SQL">要执行的SQL语句集合</param> /// <returns>true|false</returns> public bool ExecuteWithTransaction(List<string> SQL) { if (SQL == null || SQL.Count == 0) return false; StringBuilder sb = new StringBuilder(); SQL.ForEach(x => { sb.AppendLine(x); }); DbTransaction transaction = conn.BeginTransaction(); try { this.LastError = string.Empty; using (DbCommand cmd = conn.CreateCommand()) { cmd.Connection = conn; if (transaction != null && transaction.Connection.State == ConnectionState.Open) cmd.Transaction = transaction; cmd.CommandType = CommandType.Text; cmd.CommandTimeout = 600; //10 minutes this.InjectParameters(cmd); foreach (string sql in SQL) { cmd.CommandText = sql; cmd.ExecuteNonQuery(); } transaction.Commit(); WriteLog("ExecuteWithTransaction(List[SQL])", sb.ToString(), Global.SUCCESS); return true; } } catch (Exception ex) { this.LastError = ex.Message; WriteErrLog("Execute", sb.ToString(), ex.Message + "\\r\\n" + ex.StackTrace); // Attempt to roll back the transaction. try { transaction.Rollback(); } catch (Exception ex2) { WriteErrLog("ExecuteRollback", string.Format("Rollback Exception Type: {0}", ex2.GetType()), string.Format("Message: {0}", ex2.Message)); this.LastError += ex2.Message; } return false; } } #endregion #region Execute StoreProcedure /// <summary> /// 执行StoreProcedure /// </summary> /// <param name="spName">要执行的SQL语句</param> /// <param name="outParams">返回值</param> /// <returns>SUCCESS|Error message</returns> public string ExecuteStoreProcedure(string spName, out List<NameValuePair> outParams) { try { this.LastError = string.Empty; if (String.IsNullOrWhiteSpace(spName)) { outParams = null; this.LastError = "没有可执行的存储过程"; return this.LastError; } using (DbCommand cmd = db.GetStoredProcCommand(spName)) { cmd.Connection = conn; if (_transaction != null && _transaction.Connection.State == ConnectionState.Open) cmd.Transaction = _transaction; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandTimeout = 600; //10 minutes this.InjectParameters(cmd); int ret = cmd.ExecuteNonQuery(); outParams = new List<NameValuePair>(); foreach (DbParameter param in cmd.Parameters) { if (param.Direction == ParameterDirection.Output) { outParams.Add(new NameValuePair(param.ParameterName, param.Value.ToString())); } } WriteLog("Execute", spName, "成功， 影响记录数：" + ret.ToString() + "条, 返回参数： " + UtilityHelper.ConvertListToString(outParams)); } return Global.SUCCESS; } catch (Exception ex) { this.LastError = ex.Message; WriteErrLog("Execute", spName, ex.Message + "\\r\\n" + ex.StackTrace); outParams = null; return "执行失败, -2！ " + "\r\n" + ex.Message; } } /// <summary> /// 执行StoreProcedure /// </summary> /// <param name="spName">要执行的SQL语句</param> /// <param name="ds">执行StoreProcedure获取的数据集</param> /// <param name="outParams">返回值</param> /// <returns>SUCCESS|Error message</returns> public string ExecuteStoreProcedure(string spName, out DataSet ds, out List<NameValuePair> outParams) { try { this.LastError = string.Empty; if (String.IsNullOrWhiteSpace(spName)) { outParams = null; ds = null; this.LastError = "没有可执行的存储过程"; return this.LastError; } using (DbCommand cmd = db.GetStoredProcCommand(spName)) { cmd.Connection = conn; if (_transaction != null && _transaction.Connection.State == ConnectionState.Open) cmd.Transaction = _transaction; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandTimeout = 600; //10 minutes this.InjectParameters(cmd); ds = new DataSet(); db.LoadDataSet(cmd, ds, "OutParams"); outParams = new List<NameValuePair>(); foreach (DbParameter param in cmd.Parameters) { if (param.Direction == ParameterDirection.Output) { outParams.Add(new NameValuePair(param.ParameterName, param.Value.ToString())); } } WriteLog("Execute", spName, "成功， 返回参数：" + UtilityHelper.ConvertListToString(outParams)); return Global.SUCCESS; } } catch (Exception ex) { this.LastError = ex.Message; WriteErrLog("Execute", spName, ex.Message + "\\r\\n" + ex.StackTrace); outParams = null; ds = null; return ex.Message; } } #endregion #region Query /// <summary> /// 执行StoreProcedure或Select，返回查询结果 /// </summary> /// <param name="SQL">查询的SQL语句</param> /// <returns>返回一查询结果DataReader</returns> public DbDataReader Reader(string SQL) { try { this.LastError = string.Empty; if (String.IsNullOrWhiteSpace(SQL)) { this.LastError = "没有可执行的SQL语句"; return null; } using (DbCommand cmd = db.GetSqlStringCommand(SQL)) { cmd.Connection = conn; if (_transaction != null && _transaction.Connection.State == ConnectionState.Open) cmd.Transaction = _transaction; this.InjectParameters(cmd); DbDataReader dr = cmd.ExecuteReader(); //WriteLog("Query", SQL, GlobalDefinition.SUCCESS); return dr; } } catch (Exception ex) { this.LastError = ex.Message; WriteErrLog("Reader", SQL, ex.Message + "\\r\\n" + ex.StackTrace); //return null; throw new Exception("SQL.Reader： " + ex.Message); } } /// <summary> /// 执行StoreProcedure或Select，返回查询结果 /// </summary> /// <param name="SQL">查询的SQL语句</param> /// <returns>返回一查询结果DataSet</returns> public DataSet Query(string SQL) { this.LastError = string.Empty; if (String.IsNullOrWhiteSpace(SQL)) { this.LastError = "没有可执行的SQL语句"; return null; } try { using (DbCommand cmd = db.GetSqlStringCommand(SQL)) { if (_transaction != null && _transaction.Connection.State == ConnectionState.Open) cmd.Transaction = _transaction; this.InjectParameters(cmd); DataSet ds = new DataSet(); db.LoadDataSet(cmd, ds, "DefaultTableName"); //WriteLog("Query", SQL, GlobalDefinition.SUCCESS); return ds; } } catch (Exception ex) { this.LastError = ex.Message; WriteErrLog("Query", SQL, ex.Message + "\\r\\n" + ex.StackTrace); throw new Exception("SQL.Query： " + ex.Message, ex); } } /// <summary> /// 执行StoreProcedure或Select，返回查询结果 /// </summary> /// <param name="SQL">查询的SQL语句</param> /// <returns>返回一查询结果DataSet</returns> public DataTable QueryTable(string SQL) { this.LastError = string.Empty; if (String.IsNullOrWhiteSpace(SQL)) { this.LastError = "没有可执行的SQL语句"; return null; } try { using (DbCommand cmd = db.GetSqlStringCommand(SQL)) { if (_transaction != null && _transaction.Connection.State == ConnectionState.Open) cmd.Transaction = _transaction; this.InjectParameters(cmd); DataSet ds = new DataSet(); db.LoadDataSet(cmd, ds, "DefaultTableName"); //WriteLog("Query", SQL, GlobalDefinition.SUCCESS); if (ds != null && ds.Tables.Count > 0) return ds.Tables[0]; return null; } } catch (Exception ex) { this.LastError = ex.Message; WriteErrLog("Query", SQL, ex.Message + "\\r\\n" + ex.StackTrace); throw new Exception("SQL.QueryTable: " + ex.Message, ex); } } #endregion #region Get value /// <summary> /// 根据列序号返回SQL语句第一行,第n列,zero based. /// </summary> /// <param name="SQL">查询的SQL语句</param> /// <param name="ColumnIndex">列序号</param> /// <returns>字符串: 第一行,第n列</returns> public string GetValue(string SQL, int ColumnIndex) { try { this.LastError = string.Empty; if (String.IsNullOrWhiteSpace(SQL)) { this.LastError = "没有可执行的SQL语句"; return String.Empty; } using (DbDataReader dr = this.Reader(SQL)) { if (dr.Read()) { return dr[ColumnIndex].ToString().Trim(); } return string.Empty; } } catch (Exception ex) { this.LastError = ex.Message; WriteErrLog("GetValue-ColIndex", SQL + "\r\nColIndex=" + ColumnIndex.ToString(), ex.Message + "\\r\\n" + ex.StackTrace); throw new Exception("SQL.GetValue： " + ex.Message, ex); } } /// <summary> /// 根据列名返回SQL语句第一行,第n列 /// </summary> /// <param name="SQL">查询的SQL语句</param> /// <param name="ColumnName">列名</param> /// <returns>字符串: 第一行,第n列</returns> public string GetValue(string SQL, string ColumnName) { try { if (String.IsNullOrWhiteSpace(SQL)) { this.LastError = "没有可执行的SQL语句"; return String.Empty; } this.LastError = string.Empty; using (DbDataReader dr = this.Reader(SQL)) { if (dr.Read()) { return dr[ColumnName].ToString().Trim(); } return string.Empty; } } catch (Exception ex) { this.LastError = ex.Message; WriteErrLog("GetValue-ColName", SQL + "\r\nColName=" + ColumnName, ex.Message + "\\r\\n" + ex.StackTrace); throw new Exception("SQL.GetValue： " + ex.Message, ex); } } /// <summary> /// 是否有记录 /// </summary> /// <param name="SQL">查询的SQL语句</param> /// <returns></returns> public bool HasRows(string SQL) { try { this.LastError = string.Empty; if (String.IsNullOrWhiteSpace(SQL)) { this.LastError = "没有可执行的SQL语句"; return false; } using (DbDataReader dr = this.Reader(SQL)) { if (dr.Read()) { return true; } return false; } } catch (Exception ex) { this.LastError = ex.Message; WriteErrLog("HasRows", SQL, ex.Message + "\\r\\n" + ex.StackTrace); throw new Exception("SQL.HasRows： " + ex.Message, ex); } } #endregion #region Transaction private DbTransaction _transaction; /// <summary> /// 开始事务 /// </summary> public bool BeginTransaction() { if (db != null && conn != null && conn.State == ConnectionState.Open) { try { _transaction = conn.BeginTransaction(); } catch (Exception ex) { this.LastError = ex.Message; WriteErrLog("Transaction", "BeginTransaction", ex.Message + "\\r\\n" + ex.StackTrace); return false; } } else { this.LastError = "You must have a valid connection object before calling BeginTransaction."; WriteErrLog("Transaction", "BeginTransaction", this.LastError); return false; } return true; } /// <summary> /// 提交事务 /// </summary> public bool CommitTransaction() { if (_transaction != null && _transaction.Connection.State == ConnectionState.Open) { try { _transaction.Commit(); _transaction = null; } catch (Exception ex) { this.LastError = ex.Message; WriteErrLog("Transaction", "CommitTransaction", ex.Message + "\\r\\n" + ex.StackTrace); return false; } } else { this.LastError = "You must call BeginTransaction before calling CommitTransaction."; WriteErrLog("Transaction", "CommitTransaction", this.LastError); return false; } return true; } /// <summary> /// 回滚事务 /// </summary> public bool RollbackTransaction() { if (_transaction != null && _transaction.Connection.State == ConnectionState.Open) { try { _transaction.Rollback(); _transaction = null; } catch (Exception ex) { this.LastError = ex.Message; WriteErrLog("Transaction", "RollbackTransaction", ex.Message + "\\r\\n" + ex.StackTrace); return false; } } else { this.LastError = "You must call BeginTransaction before calling RollbackTransaction."; WriteErrLog("Transaction", "RollbackTransaction", this.LastError); return false; } return true; } #endregion #region Parameters private List<DbParameter> _parameters = new List<DbParameter>(); /// <summary> /// 参数集 /// </summary> public List<DbParameter> Parameters { get { return _parameters; } } #region Add Parameter /// <summary> /// 新增参数 /// </summary> /// <param name="parameter">SQL参数</param> protected void AddParameter(DbParameter parameter) { _parameters.Add(parameter); } /// <summary> /// 新增参数 /// </summary> /// <param name="name">参数名</param> /// <param name="type">数据类型</param> /// <param name="value">值</param> /// <returns></returns> public DbParameter AddInParameter(string name, DbType type, object value) { return AddInParameter(name, type, value, 500); } /// <summary> /// 新增参数 /// </summary> /// <param name="name">参数名</param> /// <param name="type">数据类型</param> /// <param name="value">值</param> /// <param name="size">长度</param> /// <returns></returns> public DbParameter AddInParameter(string name, DbType type, object value, int size) { if (value == null || String.IsNullOrWhiteSpace(name)) throw new Exception("The parameter name or value is empty."); DbParameter prm = conn.CreateCommand().CreateParameter(); prm.Direction = ParameterDirection.Input; prm.ParameterName = name; prm.DbType = type; prm.Size = size; prm.Value = value; _parameters.Add(prm); return prm; } /// <summary> /// 新增参数 /// </summary> /// <param name="name">参数名</param> /// <param name="type">数据类型</param> /// <returns></returns> public DbParameter AddOutParameter(string name, DbType type) { return AddOutParameter(name, type, 500); } /// <summary> /// 新增参数 /// </summary> /// <param name="name">参数名</param> /// <param name="type">数据类型</param> /// <param name="size">长度</param> /// <returns></returns> public DbParameter AddOutParameter(string name, DbType type, int size) { if (String.IsNullOrWhiteSpace(name)) throw new Exception("The parameter name is empty."); DbParameter prm = conn.CreateCommand().CreateParameter(); prm.Direction = ParameterDirection.Output; prm.ParameterName = name; prm.DbType = type; prm.Size = size; _parameters.Add(prm); return prm; } #endregion /// <summary> /// 清空参数 /// </summary> public void ClearParameters() { _parameters.Clear(); } /// <summary> /// 为DbCommand对象填充参数 /// </summary> /// <param name="command">DbCommand对象</param> private void InjectParameters(DbCommand command) { command.Parameters.Clear(); if (_parameters == null) return; foreach (DbParameter para in _parameters) { if (para != null) command.Parameters.Add(para); } this.ClearParameters(); //注入后清空参数 } #endregion #region Write log /// <summary> /// /// </summary> /// <param name="Function"></param> /// <param name="SQL"></param> /// <param name="Message"></param> private void WriteErrLog(string Function, string SQL, string Message) { } /// <summary> /// /// </summary> /// <param name="Function"></param> /// <param name="SQL"></param> /// <param name="Message"></param> private void WriteLog(string Function, string SQL, string Message) { } #endregion } }
