C# 利用MS的 EntLib的Database类编写的DbHelper，可以自动匹配支持Oracle、SQL Server、MySql

继续回馈ITEYE，绝对原创，好不好各位用了就知道

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. 支持存储过程使用参数

不废话，看代码：

#region Last Revised: 2009-02-02
/****************************************************************************************************
* Enterprise Library
* Author: Lin Hai
* Class description: Represent the database operation methods
* Creation date:  2008-07-08
====================================================================================================
* Update history:
*        2008-07-18:  Update the Constructor function
*        2009-02-02:  Add transaction function
*
****************************************************************************************************/
#endregion
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
   }
}