DataAccess通用數據庫訪問類，簡單易用，功能強悍 - zuowj－IT工程師數位筆記本

文章出處

以下是我編寫的DataAccess通用數據庫訪問類，簡單易用，支持：內聯式創建多個參數、支持多事務提交、支持參數復用、支持更換數據庫類型，希望能幫到大家，若需支持查出來后轉換成實體，可以自行擴展datarow轉實體類，也可以搭配dapper.net實現更強大的功能。

    /// <summary>
    /// 通用數據庫訪問類，支持多種數據庫，無直接依賴某個數據庫組件
    /// 作者：左文俊
    /// 日期：2016-6-3(修訂：2017-6-1)
    /// </summary>
    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<string, string> 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<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 && paramObjs.Length > 0)
            {
                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[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<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 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<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, 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<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", "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<string, object> { 
                                    {"@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"

文章列表