[C#]分享一个以前的项目使用的DataBaseAccess类

最近在整理以前的资料时，看到了以前我们在项目中经常用的一个数据库访问类，虽然现在已经可以用代码生成工具生成比较完整的数据库访问类，但是这个类在我们以前的项目中久经考验，所以我觉得还是比较好用，废话不多说了，上代码：//======================================================================
//
//        filename : DataBaseAccess.cs
//
//        description: 1. data base access operation class DataBaseAccess.
//                      2. data base access operation help class SQLHelper.
//
//
//
//======================================================================using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;
using System.Collections;
using System.Collections.Generic;
using System.Reflection;
using System.Xml;namespace DAL
{
    #region Database Access
    /// <summary>
    /// DataBase Operate Class DataBaseAccess
    /// </summary>
    public class DataBaseAccess
    {
        /// <summary>
        /// DataBase Connection
        /// </summary>
        private SqlConnection conn = new SqlConnection（SQLHelper.StrConn）;
        /// <summary>
        /// DataBase Connection
        /// </summary>
        public SqlConnection Conn
        {
            get
            {
                return conn;
            }
        }
        /// <summary>
        /// Construct
        /// </summary>
        public DataBaseAccess（）
        {        }        /// <summary>
        /// Destruct
        /// </summary>
        ~DataBaseAccess（）
        {
            CloseDB（）;
        }        #region "***** Debug Configuration *****"
        /// <summary>
        ///Judge whether the state is Debug
        /// </summary>
        /// <returns>if the state is Debug return true,else false</returns>
        private bool JudgeDebug（）
        {
            bool bIsDebug = false;            #if DEBUG            string strIsDebug = ConfigurationManager.AppSettings["IsDebug"];
            bIsDebug = （（bIsDebug || （strIsDebug ！= null && strIsDebug.Equals（"true"）））？ true : false）;            #endif            return bIsDebug;
        }                /// <summary>
        /// Output the Debug Information
        /// </summary>
        /// <param name="objDebugInfo">Debug Information</param>
        private void debug（object objDebugInfo）
        {
            #if DEBUG
            //if open debug,output the debug information into the file（the Directory in which Current programe run and the file name is DebugInfo[日期].ini）
            if （JudgeDebug（））
            {
                string strPath = System.Environment.CurrentDirectory + "\DebugInfo\";
                if （！Directory.Exists（strPath））
                {
                    Directory.CreateDirectory（strPath）;
                }                try
                {
                    StreamWriter swDebugOutput = new StreamWriter（strPath + DateTime.Now.ToLongDateString（） + ".ini", true, System.Text.Encoding.Unicode）;
                    swDebugOutput.Write（"time：" + DateTime.Now.ToString（） + " " + objDebugInfo + " "）;
                    swDebugOutput.Close（）;
                    swDebugOutput.Dispose（）;
                }
                catch （Exception ex）
                {
                    throw ex;
                }
            }            #endif
        }        #endregion        #region "***** Database Basic Operation *****"        #region ExecuteSql        /// <summary>
        /// Execute SQL（insert,delete,update）command,return the number of the rows which are affected
        /// </summary>
        /// <param name="sqlcmd">SQL Command which will be Executed</param>
        /// <returns>return the number of the rows which are affected</returns>
        public int ExecuteSql（SqlCommand sqlcmd）
        {
            debug（"Now Execute DataBaseAccess"s Method:ExecuteSql（SqlCommand），Return Type：int "）;
            this.conn.Open（）;
            sqlcmd.Connection = this.conn;
            SqlTransaction trans = this.conn.BeginTransaction（）;
            sqlcmd.Transaction = trans;
            try
            {
                debug（"Execute SQL Command：" + sqlcmd.CommandText）;
                int iReturnValue = sqlcmd.ExecuteNonQuery（）;
                trans.Commit（）;
                return iReturnValue;
            }
            catch （SqlException ex）
            {
                debug（"Exception Information：" + ex.ToString（））;
                trans.Rollback（）;
                throw ex;
            }
            finally
            {
                sqlcmd.Dispose（）;
                this.conn.Close（）;
            }
        }        /// <summary>
        /// Execute SQL（insert,delete,update）command,return the number of the rows which are affected
        /// </summary>
        /// <param name="sqlcmd">SQL Command which will be Executed</param>
        /// <returns>return the number of the rows which are affected</returns>
        public int ExecuteSql（string strSql）
        {
            debug（"Now Execute DataBaseAccess"s Method:ExecuteSql（string），Return Type：int "）;
            return ExecuteSql（new SqlCommand（strSql,this.conn））;
        }        /// <summary>
        /// Execute SQL（insert,delete,update）command,return the number of the rows which are affected.
        /// </summary>
        /// <param name="strSql">SQL Command which will be Executed</param>
        /// <param name="sqlParameters">SQL Parameter</param>
        /// <returns>return the number of the rows which are affected</returns>
        public int ExecuteSql（string strSql, SqlParameter[] sqlParameters）
        {
            debug（"Now Execute DataBaseAccess"s Method:ExecuteSql（string, SqlParameter[]），Return Type：int "）;
            return ExecuteSql（SQLHelper.CreateCommand（strSql, sqlParameters, this.conn））;
        }        #endregion        #region ExecuteSqlDic        /// <summary>
        /// Execute mutil-SQL（insert,delete,update）command,keep an affair.
        /// </summary>
        /// <param name="sqlcmd">SQL Command collection which will be Executed</param>
        /// <param name="bNotAffectRowRollback">if true,once one SQL Execute,the result of the execution is invalid,if false,ignore the result and rollback.</param>
        /// <returns>return the list number of the rows which are affected</returns>
        public List<int> ExecuteSqlDic（Dictionary<string, SqlParameter[]> dic, bool bNotAffectRowRollback）
        {
            debug（"Now Execute DataBaseAccess"s Method:ExecuteSqlDic（Dictionary<string, SqlParameter[]>, bool），Return Type：List<int> "）;
            List<int> iReturnValueList = new List<int>（）;
            this.conn.Open（）;
            SqlTransaction trans = this.conn.BeginTransaction（）;
            try
            {
                foreach （KeyValuePair<string, SqlParameter[]> kvp in dic）
                {
                    SqlCommand sqlcmd = SQLHelper.CreateCommand（kvp.Key, kvp.Value, this.conn）;
                    sqlcmd.Transaction = trans;
                    debug（"Execute SQL Command：" + sqlcmd.CommandText）;
                    int iAffectRow=sqlcmd.ExecuteNonQuery（）;
                    iReturnValueList.Add（iAffectRow）;
                    if （bNotAffectRowRollback && iAffectRow == 0）
                    {
                        trans.Rollback（）;
                        iReturnValueList.Clear（）;
                        return iReturnValueList;
                    }
                }
                trans.Commit（）;
            }
            catch （SqlException ex）
            {
                debug（"Exception Information：" + ex.ToString（））;
                trans.Rollback（）;
                throw ex;
            }
            finally
            {
                this.conn.Close（）;
            }            return iReturnValueList;
        }        /// <summary>
        /// Execute mutil-SQL（insert,delete,update）command,keep an affair.
        /// </summary>
        /// <param name="sqlcmd">SQL Command collection which will be Executed</param>
        /// <returns>return the list number of the rows which are affected</returns>
        public List<int> ExecuteSqlDic（Dictionary<string, SqlParameter[]> dic）
        {
            debug（"Now Execute DataBaseAccess"s Method:ExecuteSqlDic（Dictionary<string, SqlParameter[]>），Return Type：List<int> "）;
            return ExecuteSqlDic（dic, false）;
        }        #endregion        #region        /// <summary>
        /// Execute SQL Command,Return single Result.
        /// </summary>
        /// <param name="sqlcmd">SQL Command collection which will be Executed</param>
        /// <returns>return single Result</returns>
        public object ExecScalar（SqlCommand sqlcmd）
        {
            debug（"Now Execute DataBaseAccess"s Method:ExecScalar（SqlCommand），Return Type：object "）;
            sqlcmd.Connection = this.conn;
            try
            {
                debug（"Execute SQL Command：" + sqlcmd.CommandText）;
                this.conn.Open（）;
                object r = sqlcmd.ExecuteScalar（）;
                //if （Object.Equals（r, null））
                //{
                //    throw new Exception（"object is null！"）;
                //}
                //else
                //{
                //    return r;
                //}
                return r;
            }
            catch （SqlException ex）
            {
                debug（"Exception Information：" + ex.ToString（））;
                throw ex;
            }
            finally
            {
                sqlcmd.Dispose（）;
                this.conn.Close（）;
            }
        }

        /// <summary>
        /// Execute SQL Command,Return single Result.
        /// </summary>
        /// <param name="sqlcmd">SQL Command collection which will be Executed</param>
        /// <returns>return single Result</returns>
        public object ExecScalar（string strSql）
        {
            debug（"Now Execute DataBaseAccess"s Method:ExecScalar（string），Return Type：object "）;
            return ExecScalar（new SqlCommand（strSql,this.conn））;
        }        /// <summary>
        /// Execute SQL Command,Return single Result.
        /// </summary>
        /// <param name="sqlcmd">SQL Command collection which will be Executed</param>
        /// <param name="sqlParameters">SQL Parameters Collection</param>
        /// <returns>return single Result</returns>
        public object ExecScalar（string strSql, SqlParameter[] sqlParameters）
        {
            debug（"Now Execute DataBaseAccess"s Method:ExecScalar（string,SqlParameter[]），Return Type：object "）;
            return ExecScalar（SQLHelper.CreateCommand（strSql, sqlParameters, this.conn））;
        }        #endregion        #region ExecScalarEx        /// <summary>
        /// Execute SQL command,if result set has note return 1,if result set is null return 0
        /// </summary>
        /// <param name="sqlcmd">SQL Command which will be Executed</param>
        /// <returns>Execute SQL command,if result set has note return 1,if result set is null return 0</returns>
        public int ExecScalarEx（SqlCommand sqlcmd）
        {
            debug（"Now Execute DataBaseAccess"s Method:ExecScalarEx（SqlCommand），Return Type：int "）;
            sqlcmd.Connection = this.conn;
            try
            {
                debug（"Execute SQL Command：" + sqlcmd.CommandText）;
                this.conn.Open（）;
                SqlDataReader myDr = sqlcmd.ExecuteReader（CommandBehavior.CloseConnection）;                if （myDr.Read（））
                {
                    return 1;
                }
                else
                {
                    return 0;
                }
            }
            catch （SqlException ex）
            {
                debug（"Exception Information：" + ex.ToString（））;
                throw ex;
            }
            finally
            {
                sqlcmd.Dispose（）;
                this.conn.Close（）;
            }
        }        /// <summary>
        /// Execute SQL command,if result set has note return 1,if result set is null return 0
        /// </summary>
        /// <param name="strSql">SQL Command which will be Executed</param>
        /// <returns>Execute SQL command,if result set has note return 1,if result set is null return 0</returns>
        public int ExecScalarEx（string strSql）
        {
            debug（"Now Execute DataBaseAccess"s Method:ExecScalarEx（strSql），Return Type：int "）;
            return ExecScalarEx（new SqlCommand（strSql, this.conn））;
        }        /// <summary>
        /// Execute SQL command,if result set has note return 1,if result set is null return 0
        /// </summary>
        /// <param name="strSql">SQL Command which will be Executed</param>
        /// <param name="sqlParameters">SQL Command Collection</param>
        /// <returns>Execute SQL command,if result set has note return 1,if result set is null return 0</returns>
        public int ExecScalarEx（string strSql, SqlParameter[] sqlParameters）
        {
            debug（"Now Execute DataBaseAccess"s Method:ExecScalarEx（string,SqlParameter[]），Return Type：int "）;
            return ExecScalarEx（SQLHelper.CreateCommand（strSql, sqlParameters, this.conn））;
        }        #endregion        #region ExecuteSqlDs        /// <summary>
        /// Execute SQL Command,return DataSet.
        /// </summary>
        /// <param name="sqlcmd">SQL Command which will be Executed</param>
        /// <param name="strTableName">table name</param>
        /// <returns>return DataSet.</returns>
        public DataSet ExecuteSqlDs（SqlCommand sqlcmd, string strTableName）
        {
            debug（"Now Execute DataBaseAccess"s Method:ExecuteSqlDs（SqlCommand,string），Return Type：DataSet "）;
            sqlcmd.Connection = this.conn;
            SqlDataAdapter sqlda = new SqlDataAdapter（sqlcmd）;
            DataSet dsReturn = new DataSet（）;
            try
            {
                debug（"Execute SQL Command：" + sqlcmd.CommandText）;
                this.conn.Open（）;
                sqlda.Fill（dsReturn, strTableName）;
                return dsReturn;
            }
            catch （SqlException ex）
            {
                debug（"Exception information：" + ex.ToString（））;
                throw ex;
            }
            finally
            {
                sqlcmd.Dispose（）;
                sqlda.Dispose（）;
                this.conn.Close（）;
            }
        }

        /// <summary>
        /// Execute SQL Command,return DataSet.
        /// </summary>
        /// <param name="strSql">SQL Command which will be Executed</param>
        /// <param name="strTableName">table name</param>
        /// <returns>return dataset.</returns>
        public DataSet ExecuteSqlDs（string strSql, string strTableName）
        {
            debug（"Now Execute DataBaseAccess"s Method:ExecuteSqlDs（string,string），Return Type：DataSet "）;
            return ExecuteSqlDs（new SqlCommand（strSql, this.conn）, strTableName）;
        }                /// <summary>
        /// Execute SQL Command,return DataSet.
        /// </summary>
        /// <param name="strSql">SQL Command which will be Executed</param>
        /// <param name="sqlParameters">SQL Parameter Collection</param>
        /// <param name="strTableName">table name</param>
        /// <returns>return DataSet.</returns>
        public DataSet ExecuteSqlDs（string strSql, SqlParameter[] sqlParameters, string strTableName）
        {
            debug（"Now Execute DataBaseAccess"s Method:ExecuteSqlDs（string,SqlParameter[],string），Return Type：DataSet "）;
            return ExecuteSqlDs（SQLHelper.CreateCommand（strSql, sqlParameters, this.conn）, strTableName）;
        }        #endregion        #region ExecuteSqlFillDs        /// <summary>
        /// Execute SQL Command,add new resultset into current ref DataSet.
        /// </summary>
        /// <param name="sqlcmd">SQL Command which will be Executed</param>
        /// <param name="strTableName">table name</param>
        /// <param name="dsRef">current Dataset</param>
        public void ExecuteSqlFillDs（SqlCommand sqlcmd, string strTableName, ref DataSet dsRef）
        {
            debug（"Now Execute DataBaseAccess"s Method:ExecuteSqlFillDs（SqlCommand,string,ref DataSet）"）;
            sqlcmd.Connection = this.conn;
            SqlDataAdapter sqlda = new SqlDataAdapter（sqlcmd）;
            try
            {
                debug（"Execute SQL Command：" + sqlcmd.CommandText）;
                this.conn.Open（）;
                sqlda.Fill（dsRef, strTableName）;
            }
            catch （SqlException ex）
            {
                debug（"Exception information：" + ex.ToString（））;
                throw ex;
            }
            finally
            {
                sqlcmd.Dispose（）;
                sqlda.Dispose（）;
                this.conn.Close（）;
            }
        }        /// <summary>
        /// Execute SQL Command,add new resultset into current ref DataSet.
        /// </summary>
        /// <param name="strSql">SQL Command which will be Executed</param>
        /// <param name="strTableName">table name</param>
        /// <param name="dsRef">current Dataset</param>
        public void ExecuteSqlFillDs（string strSql, string strTableName, ref DataSet dsRef）
        {
            debug（"Now Execute DataBaseAccess"s Method:ExecuteSqlFillDs（string,string,ref DataSet）"）;
            ExecuteSqlFillDs（new SqlCommand（strSql）,strTableName, ref dsRef）;
        }        /// <summary>
        /// Execute SQL Command,add new resultset into current ref DataSet.
        /// </summary>
        /// <param name="strSql">SQL Command which will be Executed</param>
        /// <param name="sqlParameters">SQL Parameters Collection</param>
        /// <param name="strTableName">table name</param>
        /// <param name="dsRef">Current Dataset</param>
        public void ExecuteSqlFillDs（string strSql, SqlParameter[] sqlParameters, string strTableName, ref DataSet dsRef）
        {
            debug（"Now Execute DataBaseAccess"s Method:ExecuteSqlFillDs（string strSql, SqlParameter[], string, ref DataSet）"）;
            ExecuteSqlFillDs（SQLHelper.CreateCommand（strSql, sqlParameters, this.conn）, strTableName, ref dsRef）;
        }        #endregion        #region ExecuteSqlDsEx        /// <summary>
        /// Define pagination（Execute SQL Command,return DataSet）.
        /// </summary>
        /// <param name="sqlcmd">SQL Command which will be Executed</param>
        /// <param name="iStartRecord">index of StartRecord</param>
        /// <param name="iMaxRecord">number of Records</param>
        /// <param name="strTableName">table name</param>
        /// <returns>return DataSet</returns>
        public DataSet ExecuteSqlDsEx（SqlCommand sqlcmd, int iStartRecord, int iMaxRecord, string strTableName）
        {
            debug（"Now Execute DataBaseAccess"s Method:ExecuteSqlDsEx（SqlCommand,int,int,string）,Return Type：DataSet "）;
            sqlcmd.Connection = this.conn;
            SqlDataAdapter sqlda = new SqlDataAdapter（sqlcmd）;
            DataSet dsReapter = new DataSet（）;            try
            {
                debug（"Execute SQL Command：" + sqlcmd.CommandText）;
                this.conn.Open（）;
                if （iStartRecord < 0） iStartRecord = 0;
                sqlda.Fill（dsReapter, iStartRecord, iMaxRecord, strTableName）;
                return dsReapter;
            }
            catch （SqlException ex）
            {
                debug（"Exception information：" + ex.ToString（））;
                throw ex;
            }
            finally
            {
                sqlcmd.Dispose（）;
                sqlda.Dispose（）;
                this.conn.Close（）;
            }
        }        /// <summary>
        /// Define pagination（Execute SQL Command,return DataSet）.
        /// </summary>
        /// <param name="strSql">SQL Command which will be Executed</param>
        /// <param name="iStartRecord">index of StartRecord</param>
        /// <param name="iMaxRecord">number of Records</param>
        /// <param name="strTableName">table name</param>
        /// <returns>return DataSet.</returns>
        public DataSet ExecuteSqlDsEx（string strSql, int iStartRecord, int iMaxRecord, string strTableName）
        {
            debug（"Now Execute DataBaseAccess"s Method:ExecuteSqlDsEx（string,int,int,string）,Return Type：DataSet "）;
            return ExecuteSqlDsEx（new SqlCommand（strSql）, iStartRecord, iMaxRecord, strTableName）;
        }        /// <summary>
        /// Define pagination（Execute SQL Command,return DataSet）.
        /// </summary>
        /// <param name="strSql">SQL Command which will be Executed</param>
        /// <param name="sqlParameters">SQL Parameters Collection</param>
        /// <param name="iStartRecord">index of StartRecord</param>
        /// <param name="iMaxRecord">number of Records</param>
        /// <param name="strTableName">table name</param>
        /// <returns>return DataSet.</returns>
        public DataSet ExecuteSqlDsEx（string strSql, SqlParameter[] sqlParameters, int iStartRecord, int iMaxRecord, string strTableName）
        {
            debug（"Now Execute DataBaseAccess"s Method:ExecuteSqlDsEx（string, SqlParameter[], int, int, string）,Return Type：DataSet "）;
            return ExecuteSqlDsEx（SQLHelper.CreateCommand（strSql, sqlParameters, this.conn）, iStartRecord, iMaxRecord, strTableName）;
        }        #endregion        #region ExecuteSqlDr        /// <summary>
        /// Execute SQL Command,return SqlDataReader.
        /// </summary>
        /// <param name="sqlcmd">SQL Command which will be Executed</param>
        /// <returns>Return SqlDataReader</returns>
        public SqlDataReader ExecuteSqlDr（SqlCommand sqlcmd）
        {
            debug（"Now Execute DataBaseAccess"s Method:ExecuteSqlDr（SqlCommand）,Return Type：SqlDataReader "）;
            sqlcmd.Connection = this.conn;
            SqlDataReader sqldr;
            try
            {
                debug（"Execute SQL Command：" + sqlcmd.CommandText.ToString（））;
                this.conn.Open（）;
                sqldr = sqlcmd.ExecuteReader（CommandBehavior.CloseConnection）;
                return sqldr;
            }
            catch （SqlException ex）
            {
                debug（"Exception information：" + ex.ToString（））;
                throw ex;
            }
            finally
            {
                sqlcmd.Dispose（）;
            }
        }        /// <summary>
        /// Execute SQL Command,return SqlDataReader.
        /// </summary>
        /// <param name="strSql">SQL Command which will be Executed</param>
        /// <returns>Return SqlDataReader</returns>
        public SqlDataReader ExecuteSqlDr（string strSql）
        {
            debug（"Now Execute DataBaseAccess"s Method:ExecuteSqlDr（string）,Return Type：SqlDataReader "）;
            return ExecuteSqlDr（new SqlCommand（strSql））;
        }        /// <summary>
        /// Execute SQL Command,return SqlDataReader.
        /// </summary>
        /// <param name="strSql">SQL Command which will be Executed</param>
        /// <param name="sqlParameters">SQL Parameters Collection</param>
        /// <returns>Return SqlDataReader</returns>
        public SqlDataReader ExecuteSqlDr（string strSql, SqlParameter[] sqlParameters）
        {
            debug（"Now Execute DataBaseAccess"s Method:ExecuteSqlDr（string, SqlParameter[]）,Return Type：SqlDataReader "）;
            return ExecuteSqlDr（SQLHelper.CreateCommand（strSql, sqlParameters, this.conn））;
        }        #endregion         #region ExecuteSqlTarn        /// <summary>
        /// Execute SQL Command,Keep affair.
        /// </summary>
        /// <param name="strSql">SQL Command which will be Executed</param>
        /// <param name="sqlParameters">SQL Parameters Collection</param>
        public void ExecuteSqlTran（string strSql, SqlParameter[] sqlParameters）
        {
                conn.Open（）;
                SqlCommand sqlcmd = SQLHelper.CreateCommand（strSql, sqlParameters, this.conn）;
                using（SqlTransaction sqltrans = this.conn.BeginTransaction（））
                {
                    sqlcmd.Transaction = sqltrans;
                    try
                    {
                        sqlcmd.ExecuteNonQuery（）;
                        sqltrans.Commit（）;
                    }
                    catch （System.Data.SqlClient.SqlException E）
                    {
                        sqltrans.Rollback（）;
                        throw E;
                    }
                    finally
                    {
                        sqlcmd.Dispose（）;
                        this.conn.Close（）;
                    }
                }
        }        /// <summary>
        /// execute SQL script,Keep SqlTransaction 。
        /// </summary>
        /// <param name="objSqlList">save sql command and sql parameter</param>
        public void ExecuteSqlTran（Dictionary<string ,SqlParameter []> objSqlList）
        {
            conn.Open（）;
            using （SqlTransaction trans = conn.BeginTransaction（））
            {
                try
                {
                    SqlCommand sqlcmd = new SqlCommand（）;
                    //circulation
                    foreach （KeyValuePair<string, SqlParameter[]> kvp in objSqlList）
                    {
                        //the key value is by|Division ，Serial number|sql script
                        string[] tmp = kvp.Key.ToString（）.Split（new char[] { "|" }）;
                        string cmdText = tmp[1];
                        //get SqlParameter value
                        SqlParameter[] sqlParms = kvp.Value;
                        if （sqlParms！=null）
                            sqlcmd = SQLHelper.CreateCommand（cmdText, sqlParms, this.conn）;
                        sqlcmd.Transaction = trans;
                        int val = sqlcmd.ExecuteNonQuery（）;
                        //clear SqlParameter
                        sqlcmd.Parameters.Clear（）;
                    }
                    trans.Commit（）;
                }
                catch
                {
                    trans.Rollback（）;
                    throw;
                }
                finally
                {
                    this.conn.Close（）;
                }
            }        }        #endregion        #endregion        #region other        /// <summary>
        ///Close DataBase Connection.
        /// </summary>
        public void CloseDB（）
        {
            if （this.conn ！= null）
            {
                if （this.conn.State ！= ConnectionState.Closed）
                    this.conn.Close（）;
            }
        }

        /// <summary>
        /// Dispose Resource
        /// </summary>
        public void