我的数据访问工厂类

根据.net公共类访问数据库的自定义类，不拘泥于MSSQL数据库，请同志们指点——

 

using  System;
using  System.Data;
using  System.Configuration;
using  System.Linq;
using  System.Web;
using  System.Web.Security;
using  System.Web.UI;
using  System.Web.UI.WebControls;
using  System.Web.UI.WebControls.WebParts;
using  System.Web.UI.HtmlControls;
using  System.Xml.Linq;
using  System.Data.Common;
using  System.Data.SqlClient;
using  System.Collections ;

namespace  Ztsm.Data.Common
... {
   
    public class DBHelp
    ...{
        private static readonly ConnectionStringSettingsCollection connSetColl;
        private readonly  string configConnectionString;
        private readonly  string providerName;
        private static Hashtable parmCacheTable;
        static DBHelp()
        ...{
            parmCacheTable = Hashtable.Synchronized(new Hashtable());
            connSetColl = ConfigurationManager.ConnectionStrings;
        }
        public DBHelp(string connectionStringName)
        ...{
            this.configConnectionString = connSetColl[connectionStringName].ConnectionString;
            this.providerName = connSetColl[connectionStringName].ProviderName;
        }
        /**//// <summary>
        /// 执行返回第一行第一列的值的命令
        /// </summary>
        /// <param name="connection">连接对象</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">命令文本</param>
        /// <param name="cmdParams">参数数组</param>
        /// <returns>对象类型的值</returns>
        public static object  ExecuteScalar(DbConnection connection, CommandType cmdType, string cmdText, params DbParameter[] cmdParams)
        ...{
            try
            ...{
                DbCommand cmd = PrepareCommand(connection, false, cmdType, cmdText, cmdParams);
                object o = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                connection.Close();
                return o;
            }
            catch
            ...{
                throw;
            }
           
            
        }
        /**//// <summary>
        /// 执行返回数据读取类型的命令
        /// </summary>
        /// <param name="connection">连接对象</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">命令文本</param>
        /// <param name="cmdParams">参数数组</param>
        /// <returns>DbDataReader</returns>
        public static DbDataReader ExecuteReader(DbConnection connection, CommandType cmdType, string cmdText, params DbParameter[] cmdParams)
        ...{
            try
            ...{
                DbCommand cmd = PrepareCommand(connection, false, cmdType, cmdText, cmdParams);
                DbDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return dr;
            }
            catch
            ...{
                connection.Close();
                throw;
            }
        }
        /**//// <summary>
        /// 执行不返回值的命令
        /// </summary>
        /// <param name="connection">连接对象</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">命令文本</param>
        /// <param name="cmdParams">参数数组</param>
        /// <returns>命令影响的行数</returns>
        public static  int ExecuteNonQuery(DbConnection connection, CommandType cmdType, string cmdText, params DbParameter[] cmdParams)
        ...{
            try
            ...{
                DbCommand cmd = PrepareCommand(connection, false, cmdType, cmdText, cmdParams);
                int cmdVal = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                connection.Close();
                return cmdVal;
            }
            catch
            ...{
                throw;
            }
            
        }
        /**//// <summary>
        /// 执行不返回值的命令
        /// </summary>
        /// <param name="connection">连接对象</param>
        /// <param name="isTrans">是否使用事务</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">命令文本</param>
        /// <param name="cmdParams">参数数组</param>
        /// <returns>命令影响的行数</returns>
        public static int ExecuteNonQuery(DbConnection connection,bool isTrans, CommandType cmdType, string cmdText, params DbParameter[] cmdParams)
        ...{
            int cmdVal = 0;
            DbCommand cmd = PrepareCommand(connection, isTrans, cmdType, cmdText, cmdParams);
            try
            ...{
                cmdVal = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                cmd.Transaction.Commit();
                connection.Close();
                return cmdVal;
            }
            catch
            ...{
                cmd.Transaction.Rollback();
                
                throw;
            }
            
        }
       
        /**//// <summary>
        /// 根据提供者名和连接字符串获得DbConnection
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="providerName">提供者名</param>
        /// <returns>DbConnection</returns>
        public static DbConnection GetConnection(string connectionString, string providerName)
        ...{
            DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);
            DbConnection conn = factory.CreateConnection();
            conn.ConnectionString = connectionString;
            return conn;
        }
        /**//// <summary>
        /// 通过连接字符串名称获得连接对象
        /// </summary>
        /// <param name="connectionStringName">连接字符串的名称</param>
        /// <returns></returns>
        public static DbConnection GetConnection(string connectionStringName)
        ...{
            string providename = GetProviderName(connectionStringName);
            DbProviderFactory factory = DbProviderFactories.GetFactory(providename);
            DbConnection conn = factory.CreateConnection();
            conn.ConnectionString = GetConnectionString(connectionStringName);
            return conn;
        }
        /**//// <summary>
        /// 获得连接字符串
        /// </summary>
        /// <param name="connectionStringName">连接字符串名称</param>
        /// <returns></returns>
        public static string GetConnectionString(string connectionStringName)
        ...{
            return connSetColl[connectionStringName].ConnectionString;
        }
        /**//// <summary>
        /// 获得提供者名称
        /// </summary>
        /// <param name="connectionStringName">连接字符串名称</param>
        /// <returns></returns>
        public static string GetProviderName(string connectionStringName)
        ...{
            return connSetColl[connectionStringName].ProviderName;
        }
        /**//// <summary>
        /// 这是辅助方法，为执行数据库操作准备命令
        /// </summary>
        /// <param name="conn">DbConnection 连接对象</param>
        /// <param name="isTrans">是否需要执行事务操作</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdTex">命令文本</param>
        /// <param name="cmdParams">参数数组</param>
        /// <returns>DbCommand命令对象</returns>
        private static  DbCommand  PrepareCommand(DbConnection conn, bool isTrans, CommandType cmdType, string cmdTex, DbParameter[] cmdParams)
        ...{
            if (conn.State == ConnectionState.Closed)
            ...{
                conn.Open();
            }
            DbCommand comd = conn.CreateCommand();
            comd.CommandText = cmdTex;
            if (isTrans)
            ...{
              comd.Transaction = conn.BeginTransaction();
            }
            comd.CommandType = cmdType;
            if (cmdParams != null)
            ...{
                foreach (DbParameter p in cmdParams)
                ...{
                    comd.Parameters.Add(p);
                }
            }
            return comd;
        }
        /**//// <summary>
        /// 创建输入参数
        /// </summary>
        /// <param name="providerName">数据库提供者名，可从配置信息获得</param>
        /// <param name="paramName">参数名</param>
        /// <param name="paramType">参数类型</param>
        /// <param name="paramSize">参数大小</param>
        /// <param name="value">参数值</param>
        /// <returns>DbParameter</returns>
        public static DbParameter CreateParameter(string providerName, string paramName, DbType paramType, int paramSize,object value)
        ...{
            DbProviderFactory  factory = DbProviderFactories.GetFactory(providerName);
            DbParameter param = factory.CreateParameter();
            param.ParameterName = paramName;
            param.DbType = paramType;
            param.Size = paramSize;
            param.Value = value;
            return param;

        }
        /**//// <summary>
        /// 创建具有输入输出方向的参数
        /// </summary>
        /// <param name="providerName">数据库提供者名</param>
        /// <param name="paramName">参数名</param>
        /// <param name="paramType">参数类型</param>
        /// <param name="paramSize">参数大小</param>
        /// <param name="direction">参数方向</param>
        /// <returns>DbParameter</returns>
        public static DbParameter CreateParameter(string providerName, string paramName, DbType paramType, int paramSize,ParameterDirection direction)
        ...{
            DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);
            DbParameter param = factory.CreateParameter();
            param.ParameterName = paramName;
            param.DbType = paramType;
            param.Size = paramSize;
            param.Direction = direction;
            return param;
        }
        /**//// <summary>
        /// 将参数数组存入具有同步包装的哈希表
        /// </summary>
        /// <param name="cacheKey">健</param>
        /// <param name="commandParameters">参数数组</param>
        public static void CacheParameters(string cacheKey, params DbParameter[] commandParameters)
        ...{
            parmCacheTable[cacheKey] = commandParameters;
        }
        /**//// <summary>
        /// 从具有同步包装的哈希表里去除参数数组
        /// </summary>
        /// <param name="cacheKey">健</param>
        /// <returns>DbParameter数组</returns>
        public static DbParameter[] GetCachedParameters(string cacheKey)
        ...{
            DbParameter[] cachedParms = (DbParameter[])parmCacheTable[cacheKey];
            if (cachedParms == null)
            ...{
                return null;
            }
            DbParameter[] clonedParms = new DbParameter[cachedParms.Length];
            for (int i=0;i<cachedParms.Length;i++)
            ...{
                clonedParms[i] = (DbParameter)((ICloneable)cachedParms[i]).Clone();
            }
            return clonedParms;
        }
        //---------------------------------------------------------------------------------------------
        //    以下为实例成员
        //---------------------------------------------------------------------------------------------
        /**//// <summary>
        /// 执行返回第一行第一列的值的命令
        /// </summary>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">命令文本</param>
        /// <param name="cmdParams">参数数组</param>
        /// <returns>对象类型的值</returns>
        public object ExecuteScalar(CommandType cmdType, string cmdText, params DbParameter[] cmdParams)
        ...{
            return  ExecuteScalar(this.CreateDbConnection(), cmdType, cmdText, cmdParams);
        }
        /**//// <summary>
        /// 执行返回数据读取类型的命令
        /// </summary>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">命令文本</param>
        /// <param name="cmdParams">参数数组</param>
        /// <returns>DbDataReader</returns>
        public DbDataReader ExecuteReader( CommandType cmdType, string cmdText, params DbParameter[] cmdParams)
        ...{
            return ExecuteReader(this.CreateDbConnection(), cmdType, cmdText, cmdParams);
        }
        /**//// <summary>
        /// 执行不返回值的命令
        /// </summary>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">命令文本</param>
        /// <param name="cmdParams">参数数组</param>
        /// <returns>命令影响的行数</returns>
        public int ExecuteNonQuery( CommandType cmdType, string cmdText, params DbParameter[] cmdParams)
        ...{
            return ExecuteNonQuery(this.CreateDbConnection(), cmdType, cmdText, cmdParams);
        }
        /**//// <summary>
        /// 执行不返回值的命令
        /// </summary>
        /// <param name="isTrans">是否使用事务</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">命令文本</param>
        /// <param name="cmdParams">参数数组</param>
        /// <returns>命令影响的行数</returns>
        public int ExecuteNonQuery( bool isTrans, CommandType cmdType, string cmdText, params DbParameter[] cmdParams)
        ...{
            return ExecuteNonQuery(this.CreateDbConnection(), isTrans, cmdType, cmdText, cmdParams);
        }
        /**//// <summary>
        /// 创建连接对象
        /// </summary>
        /// <returns></returns>
        public DbConnection CreateDbConnection()
        ...{
            DbProviderFactory factory = DbProviderFactories.GetFactory(this.ProviderName);
            DbConnection conn = factory.CreateConnection();
            conn.ConnectionString = this.ConfigConnectionString;
            return conn;
        }
        /**//// <summary>
        /// 创建输入参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="paramType">参数类型</param>
        /// <param name="paramSize">参数大小</param>
        /// <param name="value">参数值</param>
        /// <returns>DbParameter</returns>
        public DbParameter CreateParameter(string paramName, DbType paramType, int paramSize, object value)
        ...{
            return CreateParameter(this.ProviderName, paramName, paramType, paramSize, value);
        }
        /**//// <summary>
        /// 创建具有输入输出方向的参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="paramType">参数类型</param>
        /// <param name="paramSize">参数大小</param>
        /// <param name="direction">参数方向</param>
        /// <returns>DbParameter</returns>
        public DbParameter CreateParameter( string paramName, DbType paramType, int paramSize, ParameterDirection direction)
        ...{
            return CreateParameter(this.ProviderName, paramName, paramType, paramSize, direction);
        }

        /**//// <summary>
        /// 将参数数组存入具有同步包装的哈希表
        /// </summary>
        /// <param name="cacheKey">健</param>
        /// <param name="commandParameters">参数数组</param>
        public void CacheParams(string cacheKey, params DbParameter[] commandParameters)
        ...{
            CacheParameters(cacheKey, commandParameters);
        }
        /**//// <summary>
        /// 从具有同步包装的哈希表里去除参数数组
        /// </summary>
        /// <param name="cacheKey">健</param>
        /// <returns>DbParameter数组</returns>
        public DbParameter[] GetCachedParams(string cacheKey)
        ...{
            return GetCachedParameters(cacheKey);
        }
        /**//// <summary>
        /// 获得数据库连接字符串
        /// </summary>
        public string ConfigConnectionString
        ...{
            get
            ...{
                if (string.IsNullOrEmpty(configConnectionString))
                ...{
                    throw new NullReferenceException("未赋值");
                }
                return configConnectionString;
            }
            
        }
        /**//// <summary>
        /// 获得数据库链接的提供者名
        /// </summary>
        public string ProviderName
        ...{
            get
            ...{
                if (string.IsNullOrEmpty(providerName))
                ...{
                    throw new NullReferenceException("未赋值");
                }
                return providerName;
            }
            
        } 
    }
}