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

/// 
    /// 通用数据库访问类，支持多种数据库，无直接依赖某个数据库组件    /// 作者：左文俊    /// 日期：2016-6-3(修订：2017-6-1)    /// 
    public class DataAccess : IDisposable    {        [ThreadStatic]        private static DbProviderFactory _dbProviderFactory = null;        [ThreadStatic]        private static string _connectionString = string.Empty;        [ThreadStatic]        public static string ConnectionStringName = string.Empty;        private DbProviderFactory dbProviderFactory = null;        private string connectionString = string.Empty;        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("default")        { }        public DataAccess(string cnnStringName, Func
     
       DecryptCnnStringFunc = null)        {            if (!string.Equals(ConnectionStringName, cnnStringName, StringComparison.OrdinalIgnoreCase) ||                _dbProviderFactory == null || _connectionString == null)            {                ConnectionStringName = cnnStringName;                var cnnStringSection = ConfigurationManager.ConnectionStrings[cnnStringName];                _dbProviderFactory = DbProviderFactories.GetFactory(cnnStringSection.ProviderName);                _connectionString = cnnStringSection.ConnectionString;                if (DecryptCnnStringFunc != null)                {                    _connectionString = DecryptCnnStringFunc(_connectionString);                }            }            dbProviderFactory = _dbProviderFactory;            connectionString = _connectionString;            paramHelper = new ParameterHelperClass(this);        }        public DataAccess(string cnnString, string providerName)        {            if (!string.Equals(_connectionString, cnnString, StringComparison.OrdinalIgnoreCase) || _dbProviderFactory == null)            {                ConnectionStringName = string.Empty;                _connectionString = cnnString;                _dbProviderFactory = DbProviderFactories.GetFactory(providerName);            }            dbProviderFactory = _dbProviderFactory;            connectionString = _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;            dbCmd.CommandTimeout = 30 * 60;            if (useTransaction)            {                dbCmd.Transaction = dbTransaction;            }            if (parameters != null)            {                dbCmd.Parameters.AddRange(parameters);            }            return dbCmd;        }        private DbCommand BuildDbCommand(string sqlCmdText, CommandType cmdType = CommandType.Text, IDictionary
      
        paramNameValues = null)        {            List
       
         parameters = new List
        
         ();            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 && paramObjs.Length > 0)            {                if (paramObjs[0] is IDictionary
         
          )                {                    return BuildDbCommand(sqlCmdText, cmdType, paramObjs[0] as IDictionary
          
           ); } else if (paramObjs is DbParameter[]) { return BuildDbCommand(sqlCmdText, cmdType, paramObjs as DbParameter[]); } else { List
           
             parameters = new List
            
             (); for (int i = 0; i < paramObjs.Length; i++) { parameters.Add(BuildDbParameter("@p" + i.ToString(), paramObjs[i])); } 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 != null ? value : DBNull.Value; return parameter; } public DbParameter BuildDbParameter(string name, object value, DbType dbType, int size = -1, ParameterDirection direction = ParameterDirection.Input) { DbParameter parameter = dbProviderFactory.CreateParameter(); parameter.ParameterName = name; parameter.Value = value != null ? value : DBNull.Value; parameter.DbType = dbType; parameter.Direction = direction; if (size != -1) { parameter.Size = size; } return parameter; } public DbParameter BuildDbParameter(string name, DbType dbType, string srcColumn, int size = -1, bool srcColumnNullMapping = true, ParameterDirection direction = ParameterDirection.Input) { DbParameter parameter = dbProviderFactory.CreateParameter(); parameter.ParameterName = name; parameter.SourceColumn = srcColumn; parameter.SourceColumnNullMapping = srcColumnNullMapping; parameter.DbType = dbType; parameter.Direction = direction; if (size != -1) { parameter.Size = size; } 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
             
              (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 int BatchExecuteCommand(string sqlCmdText, DbParameter[] sqlParams, DataTable srcTable, int batchSize = 0, bool needAllReChange = true) { var dbCmd = BuildDbCommand(sqlCmdText, CommandType.Text, sqlParams); dbCmd.UpdatedRowSource = System.Data.UpdateRowSource.None; var dbAdapter = dbProviderFactory.CreateDataAdapter(); dbAdapter.AcceptChangesDuringUpdate = false; sqlCmdText = sqlCmdText.Trim(); string bathExecType = System.Text.RegularExpressions.Regex.Match(sqlCmdText, @"^\w+\b").Value.ToUpper(); if (needAllReChange) { srcTable.AcceptChanges(); } switch (bathExecType) { case "INSERT": { dbAdapter.InsertCommand = dbCmd; if (needAllReChange) { foreach (DataRow row in srcTable.Rows) row.SetAdded(); } break; } case "UPDATE": { dbAdapter.UpdateCommand = dbCmd; if (needAllReChange) { foreach (DataRow row in srcTable.Rows) row.SetModified(); } break; } case "DELETE": { dbAdapter.DeleteCommand = dbCmd; if (needAllReChange) { for (int r = srcTable.Rows.Count - 1; r >= 0; r--) srcTable.Rows[r].Delete(); } break; } default: { throw new ArgumentException("无效的SQL命令！", "sqlCmdText"); } } dbAdapter.UpdateBatchSize = batchSize; int execResult = dbAdapter.Update(srcTable); ; ClearCommandParameters(dbCmd); return execResult; } public void Dispose() { Dispose(true); GC.SuppressFinalize(this); } #endregion private void Dispose(bool disposing) { if (!disposed) { try { if (disposing) { //释放托管资源 } if (dbTransaction != null) { if (!committed) { dbTransaction.Rollback(); } dbTransaction.Dispose(); } if (dbConnection != null) { if (dbConnection.State != ConnectionState.Closed) { dbConnection.Close(); } dbConnection.Dispose(); } } catch { } disposed = true; } } ~DataAccess() { Dispose(false); } public ParameterHelperClass ParameterHelper { get { return paramHelper; } } public class ParameterHelperClass { private List
              
                parameterList = null; private DataAccess parent = null; public ParameterHelperClass(DataAccess da) { parent = da; parameterList = new List
               
                (); } public ParameterHelperClass AddParameter(string name, object value) { parameterList.Add(parent.BuildDbParameter(name, value)); return this; } public ParameterHelperClass AddParameter(string name, object value, DbType dbType, int size = -1, ParameterDirection direction = ParameterDirection.Input) { parameterList.Add(parent.BuildDbParameter(name, value, dbType, size, direction)); return this; } public ParameterHelperClass AddParameter(string name, DbType dbType, string srcColumn, int size = -1, bool srcColumnNullMapping = true, ParameterDirection direction = ParameterDirection.Input) { parameterList.Add(parent.BuildDbParameter(name, dbType, srcColumn, size, srcColumnNullMapping, 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[i])); } return this; } public DbParameter[] ToParameterArray() { var paramList = parameterList; parameterList = new List
                
                 (); 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", "188231670**")                          .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", "188231670**")                          .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", "188231670**")                          .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", "188231670**")                          .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", "188231670**")                          .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", "188231670**").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
     
       {                                     {"@Mbno", "188231670**"},                                    {"@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,                                                "188231670**"//若采用直接是输入值数组，那么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"