闭门造车，发布通用数据库访问类（兼容多种数据库）

利用c# 2.0数据库访问工厂重写了一个通用数据库访问类，利用web.config的 providerName可轻松切换数据库

web.config文件配置

   <appSettings/>
  <connectionStrings>
    <add name="Provider" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;data source=" providerName="System.Data.OleDb"/>
    <!--<add name="Provier" connectionString="server=local;database=aa;user=sa;pwd=" providerName="System.Data.SqlClient"/>-->
    <add name="DataPath" connectionString="~\\App_Data\\xx.mdb"/>
  </connectionStrings>

 

dbhelper.cs

利用c# 2.0数据库访问工厂重写了一个通用数据库访问类，利用web.config的 providerName可轻松切换数据库

 

  1  public class DbHelper
  2     {
  3         private DbConnection Connection;
  4         private DbProviderFactory provider;
  5         private CommandType _CommandType;
  6         public CommandType CommandType
  7         {
  8             get { return _CommandType; }
  9             set { _CommandType = value; }
 10         }
 11         /// <summary>
 12         /// 初始化Dbconnection对象
 13         /// </summary>
 14         /// <returns></returns>
 15         private DbConnection buildInitConnection()
 16         {
 17             string connectionString = ConfigurationManager.ConnectionStrings["Provider"].ConnectionString;
 18             if (ConfigurationManager.ConnectionStrings["Provider"].ProviderName == "System.Data.OleDb")
 19             {
 20                 connectionString += HttpContext.Current.Server.MapPath(ConfigurationManager.ConnectionStrings["DataPath"].ConnectionString);
 21             }
 22             provider = DbProviderFactories.GetFactory(ConfigurationManager.ConnectionStrings["Provider"].ProviderName);
 23             Connection = provider.CreateConnection();
 24             Connection.ConnectionString = connectionString;
 25             return Connection;
 26 
 27         }
 28         /// <summary>
 29         /// 默认构造函数(初始化DbConnection)
 30         /// </summary>
 31         public DbHelper()
 32         {
 33             buildInitConnection();
 34             this._CommandType = CommandType.Text;
 35         }
 36         /// <summary>
 37         /// 初始化DbCommand对象
 38         /// </summary>
 39         /// <returns></returns>
 40         private DbCommand BuildInitCommand()
 41         {
 42             DbCommand Command = provider.CreateCommand();
 43             Command.Connection = Connection;
 44             Command.CommandType = this._CommandType;
 45             return Command;
 46         }
 47         /// <summary>
 48         /// 初始化DbCommand对象
 49         /// </summary>
 50         /// <param name="commandText">T-Sql语句</param>
 51         /// <returns>DbCommand对象</returns>
 52         private DbCommand BuildInitCommand(string commandText)
 53         {
 54             DbCommand Command = provider.CreateCommand();
 55             Command.Connection = Connection;
 56             Command.CommandType = this._CommandType;
 57             Command.CommandText = commandText;
 58             return Command;
 59         }
 60         /// <summary>
 61         /// 初始化DbCommand对象
 62         /// </summary>
 63         /// <param name="commandText">T-Sql语句</param>
 64         /// <param name="op">DbParameter数组</param>
 65         /// <returns>DbCommand对象</returns>
 66         private DbCommand BuildInitCommand(string commandText, DbParameter[] op)
 67         {
 68             DbCommand Command = provider.CreateCommand();
 69             Command.Connection = Connection;
 70             Command.CommandType = this._CommandType;
 71             Command.CommandText = commandText;
 72             foreach (DbParameter p in op)
 73             {
 74                 Command.Parameters.Add(p);
 75             }
 76             return Command;
 77         }
 78         /// <summary>
 79         /// 执行T-Sql语句,返回受影响的行数
 80         /// </summary>
 81         /// <param name="commandText">T-Sql语句</param>
 82         /// <returns></returns>
 83         public int ExecuteNonQuery(string commandText)
 84         {
 85             int flagValue;
 86             DbCommand Command = BuildInitCommand(commandText);
 87             Connection.Open();
 88             flagValue = Command.ExecuteNonQuery();
 89             Connection.Close();
 90             return flagValue;
 91         }
 92         /// <summary>
 93         /// 执行带数的T-Sql语句,返回受影响的行数
 94         /// </summary>
 95         /// <param name="commandText">T-Sql语句</param>
 96         /// <param name="op"></param>
 97         /// <returns></returns>
 98         public int ExecuteNonQuery(string commandText, DbParameter[] op)
 99         {
             int flagValue;
             DbCommand Command = BuildInitCommand(commandText, op);
             Connection.Open();
             flagValue = Command.ExecuteNonQuery();
             Connection.Close();
             return flagValue;
         }
         /// <summary>
         /// 执行T-Sql语句,返回第一行第一列
         /// </summary>
         /// <param name="commandText">T-Sql语句</param>
         /// <returns></returns>
         public Object ExecuteScalar(string commandText)
         {
             DbCommand Command = BuildInitCommand(commandText);
             Connection.Open();
             object flagValue = Command.ExecuteScalar();
             Connection.Close();
             return flagValue;
         }
         /// <summary>
         /// 执行带参数的T-Sql语句,返回第一行第一列
         /// </summary>
         /// <param name="commandText">T-Sql语句</param>
         /// <param name="op"></param>
         /// <returns></returns>
         public object ExecuteScalar(string commandText, DbParameter[] op)
         {
             DbCommand Command = BuildInitCommand(commandText, op);
             Connection.Open();
             object flagValue = Command.ExecuteScalar();
             Connection.Close();
             return flagValue;
         }
         /// <summary>
         /// 执行T-Sql语句，返回DbDataReader
         /// </summary>
         /// <param name="commandText">T-Sql语句</param>
         /// <returns></returns>
         public DbDataReader ExecuteReader(string commandText)
         {
             DbCommand Command = BuildInitCommand(commandText);
             Connection.Open();
             DbDataReader Reader = Command.ExecuteReader(CommandBehavior.CloseConnection);
             return Reader;
         }
         /// <summary>
         /// 执行带参数的T-Sql语句，返回DbDataReader
         /// </summary>
         /// <param name="commandText">T-Sql语句</param>
         /// <param name="op"></param>
         /// <returns></returns>
         public DbDataReader ExecuteReader(string commandText, DbParameter[] op)
         {
             DbCommand Command = BuildInitCommand(commandText, op);
             Connection.Open();
             DbDataReader Reader = Command.ExecuteReader(CommandBehavior.CloseConnection);
             return Reader;
         }
         /// <summary>
         /// 执行T-Sql语句,返回DataSet;
         /// </summary>
         /// <param name="commandText"></param>
         /// <param name="tableName"></param>
         /// <returns></returns>
         public DataSet ExecuteDataSet(string commandText, string tableName)
         {
             DbDataAdapter Adapter = provider.CreateDataAdapter();
             Adapter.SelectCommand = BuildInitCommand(commandText);
             DataSet Ds = new DataSet();
             Connection.Open();
             Adapter.Fill(Ds, tableName);
             Connection.Close();
             return Ds;
         }
         /// <summary>
         /// 执行带参数的T-Sql语句,返回DataSet;
         /// </summary>
         /// <param name="commandText"></param>
         /// <param name="op"></param>
         /// <param name="tableName"></param>
         /// <returns></returns>
         public DataSet ExecuteDataSet(string commandText, DbParameter[] op, string tableName)
         {
             DbDataAdapter Adapter = provider.CreateDataAdapter();
             Adapter.SelectCommand = BuildInitCommand(commandText, op);
             DataSet Ds = new DataSet();
             Connection.Open();
             Adapter.Fill(Ds, tableName);
             Connection.Close();
             return Ds;
         }
         /// <summary>
         /// 执行数据库事务
         /// </summary>
         /// <param name="sqlStringList"></param>
         public void ExecuteTransaction(System.Collections.ArrayList sqlStringList)
         {
             Connection.Open();
             DbTransaction tran = Connection.BeginTransaction();
             DbCommand Command = BuildInitCommand();
             Command.Transaction = tran;
             try
             {
                 for (int i = 0; i < sqlStringList.Count; i++)
                 {
                     Command.CommandText = sqlStringList[i].ToString();
                     Command.ExecuteNonQuery();
                 }
                 tran.Commit();
                 Connection.Close();
             }
             catch (System.Data.Common.DbException E)
             {
                 tran.Rollback();
                 throw new Exception(E.Message);
             }
         }
     }

 

一个小问题：事务处理函数只能执行纯SQL语句，如果想同时执行纯sql语句和带参的SQL语句，则不行。该如何改。请高手们指教