你的位置:首页 > ASP.net教程

[ASP.net教程]DataAccess通用数据库访问类,简单易用,功能强悍


以下是我编写的DataAccess通用数据库访问类,简单易用,支持:内联式创建多个参数、支持多事务提交、支持参数复用、支持更换数据库类型,希望能帮到大家,若需支持查出来后转换成实体,可以自行扩展datarow转实体类,也可以搭配dapper.net实现更强大的功能。

  /// <summary>  /// 通用数据库访问类,支持多种数据库,无直接依赖某个数据库组件  /// 作者:左文俊  /// 日期:2016-6-3  /// </summary>  public class DataAccess : IDisposable  {    private static DbProviderFactory dbProviderFactory = null;    private static string connectionString = null;    public static string ConnectionStringName = "default";    private DbConnection dbConnection = null;    private DbTransaction dbTransaction = null;    private bool useTransaction = false;    private bool disposed = false;    private bool committed = false;    private ParameterHelperClass paramHelper = null;    public DataAccess()      : this(ConnectionStringName)    { }    public DataAccess(string cnnStringName)    {      if (!ConnectionStringName.Equals(cnnStringName, StringComparison.OrdinalIgnoreCase) ||        dbProviderFactory == null || connectionString == null)      {        ConnectionStringName = cnnStringName;        var cnnStringSection = ConfigurationManager.ConnectionStrings[cnnStringName];        dbProviderFactory = DbProviderFactories.GetFactory(cnnStringSection.ProviderName);        connectionString = cnnStringSection.ConnectionString;      }      paramHelper = new ParameterHelperClass(this);    }    #region 私有方法    private DbConnection GetDbConnection()    {      if (dbConnection == null)      {        dbConnection = dbProviderFactory.CreateConnection();        dbConnection.ConnectionString = connectionString;      }      if (dbConnection.State == ConnectionState.Closed)      {        dbConnection.Open();      }      if (useTransaction && dbTransaction == null)      {        dbTransaction = dbConnection.BeginTransaction();        committed = false;      }      return dbConnection;    }    private DbCommand BuildDbCommand(string sqlCmdText, CommandType cmdType = CommandType.Text, DbParameter[] parameters = null)    {      var dbCmd = dbProviderFactory.CreateCommand();      var dbConn = GetDbConnection();      dbCmd.Connection = dbConn;      dbCmd.CommandText = sqlCmdText;      dbCmd.CommandType = cmdType;      if (useTransaction)      {        dbCmd.Transaction = dbTransaction;      }      if (parameters != null)      {        dbCmd.Parameters.AddRange(parameters);      }      return dbCmd;    }    private DbCommand BuildDbCommand(string sqlCmdText, CommandType cmdType = CommandType.Text, IDictionary<string, object> paramNameValues = null)    {      List<DbParameter> parameters = new List<DbParameter>();      if (paramNameValues != null)      {        foreach (var item in paramNameValues)        {          parameters.Add(BuildDbParameter(item.Key, item.Value));        }      }      return BuildDbCommand(sqlCmdText, cmdType, parameters.ToArray());    }    private DbCommand BuildDbCommand(string sqlCmdText, CommandType cmdType = CommandType.Text, params object[] paramObjs)    {      if (paramObjs != null)      {        if (paramObjs[0] is IDictionary<string, object>)        {          return BuildDbCommand(sqlCmdText, cmdType, paramObjs[0] as IDictionary<string, object>);        }        else if (paramObjs is DbParameter[])        {          return BuildDbCommand(sqlCmdText, cmdType, paramObjs as DbParameter[]);        }        else        {          List<DbParameter> parameters = new List<DbParameter>();          for (int i = 0; i < paramObjs.Length; i++)          {            parameters.Add(BuildDbParameter("@p" + i.ToString(), paramObjs[0]));          }          return BuildDbCommand(sqlCmdText, cmdType, parameters.ToArray());        }      }      else      {        return BuildDbCommand(sqlCmdText, cmdType, parameters: null);      }    }    private void ClearCommandParameters(DbCommand cmd)    {      bool canClear = true;      if (cmd.Connection != null && cmd.Connection.State != ConnectionState.Open)      {        foreach (DbParameter commandParameter in cmd.Parameters)        {          if (commandParameter.Direction != ParameterDirection.Input)          {            canClear = false;            break;          }        }      }      if (canClear)      {        cmd.Parameters.Clear();      }    }    #endregion    #region 公共方法    public void UseTransaction()    {      useTransaction = true;    }    public void Commit()    {      if (dbTransaction != null && useTransaction)      {        dbTransaction.Commit();        dbTransaction.Dispose();        dbTransaction = null;        committed = true;        useTransaction = false;      }    }    public DbParameter BuildDbParameter(string name, object value)    {      DbParameter parameter = dbProviderFactory.CreateParameter();      parameter.ParameterName = name;      parameter.Value = value;      return parameter;    }    public DbParameter BuildDbParameter(string name, object value, DbType dbType, ParameterDirection direction = ParameterDirection.Input)    {      DbParameter parameter = dbProviderFactory.CreateParameter();      parameter.ParameterName = name;      parameter.Value = value;      parameter.DbType = dbType;      parameter.Direction = direction;      return parameter;    }    public DbDataReader ExecuteReader(string sqlCmdText, CommandType cmdType = CommandType.Text, params object[] paramObjs)    {      var dbCmd = BuildDbCommand(sqlCmdText, cmdType, paramObjs);      var dr = dbCmd.ExecuteReader();      ClearCommandParameters(dbCmd);      return dr;    }    public T ExecuteScalar<T>(string sqlCmdText, CommandType cmdType = CommandType.Text, params object[] paramObjs)    {      T returnValue = default(T);      var dbCmd = BuildDbCommand(sqlCmdText, cmdType, paramObjs);      object result = dbCmd.ExecuteScalar();      try      {        returnValue = (T)Convert.ChangeType(result, typeof(T));      }      catch      { }      ClearCommandParameters(dbCmd);      return returnValue;    }    public DataSet ExecuteDataSet(string sqlCmdText, CommandType cmdType = CommandType.Text, params object[] paramObjs)    {      var dbCmd = BuildDbCommand(sqlCmdText, cmdType, paramObjs);      var dbAdapter = dbProviderFactory.CreateDataAdapter();      dbAdapter.SelectCommand = dbCmd;      DataSet returnDataSet = new DataSet();      dbAdapter.Fill(returnDataSet);      ClearCommandParameters(dbCmd);      return returnDataSet;    }    public DataTable ExecuteDataTable(string sqlCmdText, CommandType cmdType = CommandType.Text, params object[] paramObjs)    {      DataTable returnTable = new DataTable();      DataSet resultDataSet = ExecuteDataSet(sqlCmdText, cmdType, paramObjs);      if (resultDataSet != null && resultDataSet.Tables.Count > 0)      {        returnTable = resultDataSet.Tables[0];      }      return returnTable;    }    public int ExecuteCommand(string sqlCmdText, CommandType cmdType = CommandType.Text, params object[] paramObjs)    {      var dbCmd = BuildDbCommand(sqlCmdText, cmdType, paramObjs);      int execResult = dbCmd.ExecuteNonQuery();      ClearCommandParameters(dbCmd);      return execResult;    }    public void Dispose()    {      Dispose(true);      GC.SuppressFinalize(this);    }    #endregion    private void Dispose(bool disposing)    {      if (!disposed)      {        if (disposing)        {          //释放托管资源        }        if (dbTransaction != null)        {          if (!committed)          {            dbTransaction.Rollback();          }          dbTransaction.Dispose();        }        if (dbConnection != null)        {          if (dbConnection.State != ConnectionState.Closed)          {            dbConnection.Close();          }          dbConnection.Dispose();        }        disposed = true;      }    }    ~DataAccess()    {      Dispose(false);    }    public ParameterHelperClass ParameterHelper    {      get      {        return paramHelper;      }    }    public class ParameterHelperClass    {      private List<DbParameter> parameterList = null;      private DataAccess parent = null;      public ParameterHelperClass(DataAccess da)      {        parent = da;        parameterList = new List<DbParameter>();      }      public ParameterHelperClass AddParameter(string name, object value)      {        parameterList.Add(parent.BuildDbParameter(name, value));        return this;      }      public ParameterHelperClass AddParameter(string name, object value, DbType dbType, ParameterDirection direction = ParameterDirection.Input)      {        parameterList.Add(parent.BuildDbParameter(name, value, dbType, direction));        return this;      }      public ParameterHelperClass AddParametersWithValue(params object[] paramValues)      {        for (int i = 0; i < paramValues.Length; i++)        {          parameterList.Add(parent.BuildDbParameter("@p" + i.ToString(), paramValues[0]));        }        return this;      }      public DbParameter[] ToParameterArray()      {        var paramList = parameterList;        parameterList = new List<DbParameter>();        return paramList.ToArray();      }    }  }

多种灵活用法,使用示例代码如下:

用法一:采用内联式创建参数数组对象,然后执行SQL命令

      using (DataAccess da = new DataAccess())      {        var programInfo = new ProgramInfo() { Name="test", Version="1.0", InstalledLocation=AppDomain.CurrentDomain.BaseDirectory };         var parameters = da.ParameterHelper.AddParameter("@Mbno", "18823167089")             .AddParameter("@Msg", string.Format("程序名:{0},版本:{1},安装路径:{2},已停止运行了,请尽快处理!",                    programInfo.Name, programInfo.Version, programInfo.InstalledLocation))             .AddParameter("@SendTime", DateTime.Now)             .AddParameter("@KndType", "监控异常通知")             .ToParameterArray();        da.ExecuteCommand("insert into OutBox(Mbno,Msg,SendTime,KndType) values(@Mbno,@Msg,@SendTime,@KndType)", paramObjs: parameters);      }

用法二:在用法一基础上使用事务来进行提交

      using (DataAccess da = new DataAccess())      {        var programInfo = new ProgramInfo() { Name = "test", Version = "1.0", InstalledLocation = AppDomain.CurrentDomain.BaseDirectory };         var parameters = da.ParameterHelper.AddParameter("@Mbno", "18823167089")             .AddParameter("@Msg", string.Format("程序名:{0},版本:{1},安装路径:{2},已停止运行了,请尽快处理!",                    programInfo.Name, programInfo.Version, programInfo.InstalledLocation))             .AddParameter("@SendTime", DateTime.Now)             .AddParameter("@KndType", "监控异常通知")             .ToParameterArray();        da.UseTransaction();        da.ExecuteCommand("insert into OutBox(Mbno,Msg,SendTime,KndType) values(@Mbno,@Msg,@SendTime,@KndType)", paramObjs: parameters);        da.Commit();      }

用法三:在用法二基础上使用事务一次性执行多个SQL命令

      using (DataAccess da = new DataAccess())      {        var programInfo = new ProgramInfo() { Name = "test", Version = "1.0", InstalledLocation = AppDomain.CurrentDomain.BaseDirectory };         var parameters = da.ParameterHelper.AddParameter("@Mbno", "18823167089")             .AddParameter("@Msg", string.Format("程序名:{0},版本:{1},安装路径:{2},已停止运行了,请尽快处理!",                    programInfo.Name, programInfo.Version, programInfo.InstalledLocation))             .AddParameter("@SendTime", DateTime.Now)             .AddParameter("@KndType", "监控异常通知")             .ToParameterArray();        da.UseTransaction();        da.ExecuteCommand("insert into OutBox(Mbno,Msg,SendTime,KndType) values(@Mbno,@Msg,@SendTime,@KndType)", paramObjs: parameters);        da.ExecuteCommand("insert into OutBox(Mbno,Msg,SendTime,KndType) values(@Mbno,@Msg,@SendTime,@KndType)", paramObjs: parameters);        da.Commit();      }

用法四:在用法一基础上使用多个事务来执行多个SQL并进行多次提交

      using (DataAccess da = new DataAccess())      {        var programInfo = new ProgramInfo() { Name = "test", Version = "1.0", InstalledLocation = AppDomain.CurrentDomain.BaseDirectory };        var parameters = da.ParameterHelper.AddParameter("@Mbno", "18823167089")             .AddParameter("@Msg", string.Format("程序名:{0},版本:{1},安装路径:{2},已停止运行了,请尽快处理!",                    programInfo.Name, programInfo.Version, programInfo.InstalledLocation))             .AddParameter("@SendTime", DateTime.Now)             .AddParameter("@KndType", "监控异常通知")             .ToParameterArray();        da.UseTransaction();        da.ExecuteCommand("insert into OutBox(Mbno,Msg,SendTime,KndType) values(@Mbno,@Msg,@SendTime,@KndType)", paramObjs: parameters);        da.Commit();        da.UseTransaction();        da.ExecuteCommand("insert into OutBox(Mbno,Msg,SendTime,KndType) values(@Mbno,@Msg,@SendTime,@KndType)", paramObjs: parameters);        da.Commit();      }

用法五:事务提交+SQL命令查询

      using (DataAccess da = new DataAccess())      {        var programInfo = new ProgramInfo() { Name = "test", Version = "1.0", InstalledLocation = AppDomain.CurrentDomain.BaseDirectory };         var parameters = da.ParameterHelper.AddParameter("@Mbno", "18823167089")             .AddParameter("@Msg", string.Format("程序名:{0},版本:{1},安装路径:{2},已停止运行了,请尽快处理!",                    programInfo.Name, programInfo.Version, programInfo.InstalledLocation))             .AddParameter("@SendTime", DateTime.Now)             .AddParameter("@KndType", "监控异常通知")             .ToParameterArray();        da.UseTransaction();        da.ExecuteCommand("insert into OutBox(Mbno,Msg,SendTime,KndType) values(@Mbno,@Msg,@SendTime,@KndType)", paramObjs: parameters);        da.Commit();        parameters = da.ParameterHelper.AddParameter("@Mbno", "18823167089").ToParameterArray();        var table = da.ExecuteDataTable("select Mbno,Msg,SendTime,KndType from OutBox where Mbno=@Mbno", paramObjs: parameters);        System.Windows.Forms.MessageBox.Show(table.Rows.Count.ToString());      }

用法六:不采用内联方式创建参数,而是执行SQL命令时直接传入各类型的参数

      using (DataAccess da = new DataAccess())      {        var programInfo = new ProgramInfo() { Name = "test", Version = "1.0", InstalledLocation = AppDomain.CurrentDomain.BaseDirectory };        da.ExecuteCommand("insert into OutBox(Mbno,Msg,SendTime,KndType) values(@Mbno,@Msg,@SendTime,@KndType)",                  System.Data.CommandType.Text,                 new Dictionary<string, object> {                   {"@Mbno", "18823167089"},                  {"@Msg", string.Format("程序名:{0},版本:{1},安装路径:{2},已停止运行了,请尽快处理!",                    programInfo.Name, programInfo.Version, programInfo.InstalledLocation)},                  {"@SendTime", DateTime.Now},                  {"@KndType", "监控异常通知"}                 });        var table = da.ExecuteDataTable("select Mbno,Msg,SendTime,KndType from OutBox where Mbno=@p0",                        System.Data.CommandType.Text,                        "18823167089"//若采用直接是输入值数组,那么SQL命令中的参数占位符必需定义成:@p0,@p1...                      );        System.Windows.Forms.MessageBox.Show(table.Rows.Count.ToString());      }

用法七:除了上面使用DataAccess.ParameterHelper属性的AddParameter(string name, object value)方法来创建参数,还可以使用AddParameter(string name, object value, DbType dbType, ParameterDirection direction = ParameterDirection.Input)来创建指定输入输出及类型的参数,还有AddParametersWithValue(params object[] paramValues)来根据值数组创建参数

若需要更换数据库类型,只需要在配置文件的connectionStrings节点加入相关的连接子节点,注意providerName特性,providerName常用的如下:

Aceess数据库:providerName="System.Data.OleDb"

Oracle 数据库:providerName="System.Data.OracleClient"或者providerName="Oracle.DataAccess.Client"

SQLite数据库:providerName="System.Data.SQLite"

SQL SERVER数据库:providerName="System.Data.SqlClient"

MYSQL数据库:providerName="MySql.Data.MySqlClient"

ODBC连接数据库:providerName="System.Data.Odbc"