[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() + "\r\n" + objDebugInfo + "\r\n\r\n");
                    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 Dispose()
        {
            if (this.conn != null)
            {
                if (this.conn.State != ConnectionState.Closed)
                    this.conn.Close();
                this.conn.Dispose();
            }
        }
        #endregion
    }

    #endregion


    #region DataBase Operate assistant class SQLHelper
    /// <summary>
    /// SQLHelper.
    /// </summary>
    public abstract class SQLHelper
    {
        /// <summary>
        /// DataBase ConnectionString
        /// </summary>
        public static string StrConn = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;

        //------------------------------------------------------------------------------------------------------------

        /// <summary>
        /// Create SqlParameter.
        /// </summary>
        /// <param name="parameterName">The name of the parameter to map.</param>
        /// <param name="DbType">One of the System.Data.SqlDbType values.</param>
        /// <param name="value">The length of the parameter.</param>
        /// <returns>Return new SqlParameter.</returns>
        public static SqlParameter CreateSqlParameter(string parameterName, SqlDbType DbType, object value)
        {
            SqlParameter sqlpara = new SqlParameter(parameterName, DbType);
            sqlpara.Value = value;
            return sqlpara;
        }

        /// <summary>
        /// Create SqlParameter.
        /// </summary>
        /// <param name="parameterName">The name of the parameter to map.</param>
        /// <param name="DbType">One of the System.Data.SqlDbType values.</param>        
        /// <returns>Return new SqlParameter.</returns>
        public static SqlParameter CreateSqlParameter(string parameterName, SqlDbType DbType, int size, object value)
        {            
            SqlParameter sqlpara = new SqlParameter(parameterName, DbType, size);
            sqlpara.Value = value;
            return sqlpara;
        }


        //------------------------------------------------------------------------------------------------------------

        /// <summary>
        /// Param amortize Hashtable
        /// </summary>
        private static Hashtable htParamCache = Hashtable.Synchronized(new Hashtable());

        /// <summary>
        /// Save Parameters in Cache
        /// </summary>
        /// <param name="strCacheKey"></param>
        /// <param name="sqlParameters"></param>
        public static void CacheParameters(string strCacheKey, params SqlParameter[] sqlParameters)
        {
            SQLHelper.htParamCache[strCacheKey] = sqlParameters;
        }

        /// <summary>
        /// Get Parameters from Cache
        /// </summary>
        /// <param name="strCacheKey"></param>
        /// <returns></returns>
        public static SqlParameter[] GetCachedParameters(string strCacheKey)
        {
            SqlParameter[] sqlParameters = (SqlParameter[])SQLHelper.htParamCache[strCacheKey];
            if (sqlParameters == null)
            {
                return null;
            }

            SqlParameter[] clonedParms = new SqlParameter[sqlParameters.Length];
            for (int i = 0, j = sqlParameters.Length; i < j; i++)
            {
                clonedParms[i] = (SqlParameter)((ICloneable)sqlParameters[i]).Clone();
            }

            return clonedParms;
        }

        //----------------------------------------------------------------------------------------------------------------

        /// <summary>
        /// Create new SqlComand
        /// </summary>
        /// <param name="strSql"></param>        
        public static SqlCommand CreateCommand(string strSql)
        {
            SqlCommand sqlcmd = new SqlCommand(strSql);
            return sqlcmd;
        }

        /// <summary>
        /// Create new SqlComand,Set DataBase Connection
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="sqlconn"></param>
        /// <returns></returns>

        public static SqlCommand CreateCommand(string strSql, SqlConnection sqlconn)
        {
            SqlCommand sqlcmd = new SqlCommand(strSql, sqlconn);
            return sqlcmd;
        }


        /// <summary>
        /// Create new SqlComand which has Parameters
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="sqlParameters"></param>        
        public static SqlCommand CreateCommand(string strSql, SqlParameter[] sqlParameters)
        {
            SqlCommand sqlcmd = new SqlCommand(strSql);
            foreach (SqlParameter param in sqlParameters)
            {
                sqlcmd.Parameters.Add(param);
            }
            return sqlcmd;
        }

        /// <summary>
        /// Create new SqlComand which has Parameters,Set DataBase Connection
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="conn"></param>
        /// <param name="sqlParameters"></param>        
        public static SqlCommand CreateCommand(string strSql, SqlParameter[] sqlParameters, SqlConnection sqlconn)
        {
            SqlCommand sqlcmd = new SqlCommand(strSql, sqlconn);
            foreach (SqlParameter param in sqlParameters)
            {
                sqlcmd.Parameters.Add(param);
            }
            return sqlcmd;
        }

        /// <summary>
        /// Create new SqlComand which has Parameters,Set Stored Procedure Flag
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="sqlParameters"></param>
        /// <param name="bIsStoredProcedure"></param>
        /// <returns></returns>        
        public static SqlCommand CreateCommand(string strSql, SqlParameter[] sqlParameters, bool bIsStoredProcedure)
        {
            SqlCommand sqlcmd = new SqlCommand(strSql);

            if (bIsStoredProcedure)
                sqlcmd.CommandType = CommandType.StoredProcedure;
            else
                sqlcmd.CommandType = CommandType.Text;

            foreach (SqlParameter param in sqlParameters)
            {
                sqlcmd.Parameters.Add(param);
            }
            return sqlcmd;
        }

        /// <summary>
        /// Create new SqlComand which has Parameters,Set Stored Procedure Flag and DataBase Connection
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="sqlParameters"></param>
        /// <param name="bIsStoredProcedure"></param>
        /// <returns></returns>        
        public static SqlCommand CreateCommand(string strSql, SqlParameter[] sqlParameters, bool bIsStoredProcedure, SqlConnection sqlconn)
        {
            SqlCommand sqlcmd = new SqlCommand(strSql, sqlconn);

            if (bIsStoredProcedure)
                sqlcmd.CommandType = CommandType.StoredProcedure;
            else
                sqlcmd.CommandType = CommandType.Text;

            foreach (SqlParameter param in sqlParameters)
            {
                sqlcmd.Parameters.Add(param);
            }
            return sqlcmd;
        }

        /// <summary>
        /// Create new SqlDataAdapter which has Parameters and set DataBase Connection
        /// </summary>
        /// <param name="sqlda"></param>
        /// <param name="mySqlParamter"></param>
        public static SqlDataAdapter CreateDataAdapter(string strSql, SqlParameter[] sqlParameters, SqlConnection sqlconn)
        {
            SqlDataAdapter sqlda = new SqlDataAdapter(strSql, sqlconn);
            foreach (SqlParameter param in sqlParameters)
            {
                sqlda.SelectCommand.Parameters.Add(param);
            }
            return sqlda;
        }

        /// <summary>
        /// Create new SqlDataAdapter which has Parameters,Set Stored Procedure Flag and DataBase Connection
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="mySqlParamter"></param>
        /// <param name="bIsStoredProcedure"></param>
        /// <returns></returns>
        public static SqlDataAdapter CreateDataAdapter(string strSql, SqlParameter[] sqlParameters, bool bIsStoredProcedure, SqlConnection sqlconn)
        {
            SqlDataAdapter sqlda = new SqlDataAdapter(strSql, sqlconn);

            if (bIsStoredProcedure)
                sqlda.SelectCommand.CommandType = CommandType.StoredProcedure;
            else
                sqlda.SelectCommand.CommandType = CommandType.Text;

            foreach (SqlParameter param in sqlParameters)
            {
                sqlda.SelectCommand.Parameters.Add(param);
            }
            return sqlda;
        }

        /// <summary>
        /// Create SqlParameter[]
        /// </summary>
        /// <param name="sqlParameterArr"></param>
        /// <returns></returns>
        public static SqlParameter[] CreateSqlParameters(object[,] sqlParameterArr)
        {
            SqlParameter[] sqlParameters = new SqlParameter[sqlParameterArr.GetLength(0)];
            int i = 0;
            foreach (SqlParameter param in sqlParameters)
            {
                sqlParameters[i] = new SqlParameter(Convert.ToString(sqlParameterArr[i, 0]), sqlParameterArr[i, 1]);
                i++;
            }
            return sqlParameters;
        }

        /// <summary>
        /// add Parameters for Command
        /// </summary>
        /// <param name="sqlcmd"></param>
        /// <param name="mySqlParamter"></param>
        public static void AddCommandParams(ref SqlCommand sqlcmd, SqlParameter[] sqlParameters)
        {
            foreach (SqlParameter param in sqlParameters)
            {
                sqlcmd.Parameters.Add(param);
            }
        }

        /// <summary>
        /// add Parameters for DataAdapter
        /// </summary>
        /// <param name="sqlda"></param>
        /// <param name="mySqlParamter"></param>
        public static void AddDataAdapterParam(ref SqlDataAdapter sqlda, SqlParameter[] sqlParameters)
        {
            foreach (SqlParameter param in sqlParameters)
            {
                sqlda.SelectCommand.Parameters.Add(param);
            }
        }

        /// <summary>
        /// Get SQLScript
        /// </summary>
        /// <param name="strFilepath"></param>
        /// <param name="strNodePath"></param>
        public static string GetSQLScript(string strFilepath,string strNodePath)
        {
            string strSql;
            XmlDocument xmldoc = new XmlDocument();
            xmldoc.Load(strFilepath);
            XmlNode node = xmldoc.SelectSingleNode(strNodePath);
            strSql = node.ChildNodes[0].InnerText;
            return strSql;
        }               
    }
    #endregion
}