[转]C#访问数据库类

using System;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.Common;using System.Configuration;using System.Collections;namespace DBUtility{    public class DataAccess    {        private string _confirString = "ConnectionString";        public DataAccess()        {        }        /// <param name="configString">app.config 关键字</param>        public DataAccess(string configString)        {            ConfigString = configString;        }        /// <summary>        /// 属性,设置数据库连接字符串        /// </summary>        public string ConfigString        {            get            {                return _confirString;            }            set            {                _confirString = value;            }        }        //===========================================GetProviderName=============================        #region 获得数据库的类型public string GetProviderName(string ConfigString)        /// <summary>        /// 返回数据提供者        /// </summary>        /// <returns>返回数据提供者</returns>        public string GetProviderName(string ConfigString)        {            ConnectionStringSettingsCollection ConfigStringCollention = ConfigurationManager.ConnectionStrings;            if (ConfigStringCollention == null || ConfigStringCollention.Count <= 0)            {                throw new Exception("app.config 中无连接字符串!");            }            ConnectionStringSettings StringSettings = null;            if (ConfigString == string.Empty)            {                StringSettings = ConfigurationManager.ConnectionStrings["ConnectionString"];            }            else            {                StringSettings = ConfigurationManager.ConnectionStrings[ConfigString];            }            return StringSettings.ProviderName;        }        /// <summary>        /// 返回数据提供者        /// </summary>        /// <returns></returns>        public string GetProviderName()        {            return GetProviderName(ConfigString);        }        #endregion        //===========================================获得连接字符串==============================        #region 获得连接字符串        /// <summary>        /// 获得连接字符串        /// </summary>        /// <returns></returns>        private string GetConnectionString(string ConfigString)        {            ConnectionStringSettingsCollection ConfigStringCollention = ConfigurationManager.ConnectionStrings;            if (ConfigStringCollention == null || ConfigStringCollention.Count <= 0)            {                throw new Exception("app.config 中无连接字符串!");            }            ConnectionStringSettings StringSettings = null;            if (ConfigString == string.Empty)            {                StringSettings = ConfigurationManager.ConnectionStrings["ConnectionString"];            }            else            {                StringSettings = ConfigurationManager.ConnectionStrings[ConfigString];            }            return StringSettings.ConnectionString;        }        private string GetConnectionString()        {            return GetConnectionString(ConfigString);        }        #endregion        //===========================================GetDbproviderFactory========================        #region 返回数据工厂  public DbProviderFactory GetDbProviderFactory()        /// <summary>        /// 返回数据工厂        /// </summary>        /// <returns></returns>        private DbProviderFactory GetDbProviderFactory()        {            DbProviderFactory f = null;            string ProviderName = GetProviderName();            switch (ProviderName)            {                case "System.Data.SqlClient":                    f = GetDbProviderFactory("System.Data.SqlClient");                    break;                case "System.Data.OracleClient":                    f = GetDbProviderFactory("System.Data.OracleClient");                    break;                case "System.Data.OleDb":                    f = GetDbProviderFactory("System.Data.OleDb");                    break;                default:                    f = GetDbProviderFactory("System.Data.SqlClient");                    break;            }            return f;        }        /// <summary>        /// 返回数据工厂        /// </summary>        /// <param name="providername"></param>        /// <returns></returns>        private DbProviderFactory GetDbProviderFactory(string providername)        {            return DbProviderFactories.GetFactory(providername);        }        #endregion        //===========================================CreateConnection============================        #region 创建数据库连接 public DbConnection CreateConnection()        /// <summary>        /// 创建数据库连接        /// </summary>        /// <returns></returns>        private DbConnection CreateConnection()        {            DbConnection con = GetDbProviderFactory().CreateConnection();            con.ConnectionString = GetConnectionString();            return con;        }        /// <summary>        /// 创建数据库连接        /// </summary>        /// <param name="provdername"></param>        /// <returns></returns>        private DbConnection CreateConnection(string provdername)        {            DbConnection con = GetDbProviderFactory(provdername).CreateConnection();            con.ConnectionString = GetConnectionString();            return con;        }        #endregion        //===========================================CreateCommand===============================        #region 创建执行命令对象 public override DbCommand CreateCommand(string sql, CommandType cmdType, DbParameter[] parameters)        /// <summary>        /// 创建执行命令对象        /// </summary>        /// <param name="sql"></param>        /// <param name="cmdType"></param>        /// <param name="parameters"></param>        /// <returns></returns>        private DbCommand CreateCommand(string sql, CommandType cmdType, DbParameter[] parameters)        {            DbCommand _command = GetDbProviderFactory().CreateCommand();            _command.Connection = CreateConnection();            _command.CommandText = sql;            _command.CommandType = cmdType;            if (parameters != null && parameters.Length > 0)            {                foreach (DbParameter param in parameters)                {                    _command.Parameters.Add(param);                }            }            return _command;        }        /// <summary>        /// 创建执行命令对象        /// </summary>        /// <param name="sql">SQL语句</param>        /// <returns>执行命令对象实例</returns>        private DbCommand CreateCommand(string sql)        {            DbParameter[] parameters = new DbParameter[0];            return CreateCommand(sql, CommandType.Text, parameters);        }        /// <summary>        /// 创建执行命令对象        /// </summary>        /// <param name="sql">SQL语句</param>        /// <returns>执行命令对象实例</returns>        private DbCommand CreateCommand(string sql, CommandType cmdtype)        {            DbParameter[] parameters = new DbParameter[0];            return CreateCommand(sql, cmdtype, parameters);        }        /// <summary>        /// 创建执行命令对象        /// </summary>        /// <param name="sql">ＳＱＬ语句</param>        /// <param name="parameters">参数</param>        /// <returns>执行命令对象实例</returns>        private DbCommand CreateCommand(string sql, DbParameter[] parameters)        {            return CreateCommand(sql, CommandType.Text, parameters);        }        #endregion        //===========================================CreateAdapter()=============================        #region 创建数据适配器 CreateAdapter(string sql)        /// <summary>        /// 创建数据适配器        /// </summary>        /// <param name="sql">SQL,语句</param>        /// <returns>数据适配器实例</returns>        private DbDataAdapter CreateAdapter(string sql)        {            DbParameter[] parameters = new DbParameter[0];            return CreateAdapter(sql, CommandType.Text, parameters);        }        /// <summary>        /// 创建数据适配器        /// </summary>        /// <param name="sql">SQL语句</param>        /// <param name="cmdtype">命令类型</param>        /// <returns>数据适配器实例</returns>        private DbDataAdapter CreateAdapter(string sql, CommandType cmdtype)        {            DbParameter[] parameters = new DbParameter[0];            return CreateAdapter(sql, cmdtype, parameters);        }        /// <summary>        /// 创建数据适配器        /// </summary>        /// <param name="connectionString">数据库连接字符串</param>        /// <param name="sql">SQL语句</param>        /// <param name="cmdtype">命令类型</param>        /// <param name="parameters">参数</param>        /// <returns>数据适配器实例</returns>        private DbDataAdapter CreateAdapter(string sql, CommandType cmdtype, DbParameter[] parameters)        {            DbConnection _connection = CreateConnection();            DbCommand _command = GetDbProviderFactory().CreateCommand();            _command.Connection = _connection;            _command.CommandText = sql;            _command.CommandType = cmdtype;            if (parameters != null && parameters.Length > 0)            {                foreach (DbParameter _param in parameters)                {                    _command.Parameters.Add(_param);                }            }            DbDataAdapter da = GetDbProviderFactory().CreateDataAdapter();            da.SelectCommand = _command;            return da;        }        #endregion        //===========================================CreateParameter=============================        #region 生成参数 public override SqlParameter CreateParameter(string field, string dbtype, string value)        /// <summary>        /// 创建参数        /// </summary>        /// <param name="field">参数字段</param>        /// <param name="dbtype">参数类型</param>        /// <param name="value">参数值</param>        /// <returns></returns>        private DbParameter CreateParameter(string field, string dbtype, string value)        {            DbParameter p = GetDbProviderFactory().CreateParameter();            p.ParameterName = field;            p.Value = value;            return p;        }        #endregion        //===========================================ExecuteCommand()============================        #region 执行非查询语句,并返回受影响的记录行数 ExecuteCommand(string sql)        /// <summary>        /// 执行非查询语句,并返回受影响的记录行数        /// </summary>        /// <param name="sql">SQL语句</param>        /// <returns>受影响记录行数</returns>        public int ExecuteCommand(string sql)        {            DbParameter[] parameters = new DbParameter[0];            return ExecuteCommand(sql, CommandType.Text, parameters);        }        /// <summary>        /// 执行非查询语句,并返回受影响的记录行数        /// </summary>        /// <param name="sql">SQL语句</param>        /// <param name="cmdtype">命令类型</param>        /// <returns>受影响记录行数</returns>        public int ExecuteCommand(string sql, CommandType cmdtype)        {            DbParameter[] parameters = new DbParameter[0];            return ExecuteCommand(sql, CommandType.Text, parameters);        }        /// <summary>        /// 执行非查询语句,并返回受影响的记录行数        /// </summary>        /// <param name="sql">SQL语句</param>        /// <param name="parameters">参数</param>        /// <returns>受影响记录行数</returns>        public int ExecuteCommand(string sql, DbParameter[] parameters)        {            return ExecuteCommand(sql, CommandType.Text, parameters);        }        /// <summary>        ///批量执行SQL语句         /// </summary>        /// <param name="SqlList">SQL列表</param>        /// <returns></returns>        public bool ExecuteCommand(ArrayList SqlList)        {            DbConnection con = CreateConnection();            con.Open();            bool iserror = false;            string strerror = "";            DbTransaction SqlTran = con.BeginTransaction();            try            {                for (int i = 0; i < SqlList.Count; i++)                {                    DbCommand _command = GetDbProviderFactory().CreateCommand();                    _command.Connection = con;                    _command.CommandText = SqlList[i].ToString();                    _command.Transaction = SqlTran;                    _command.ExecuteNonQuery();                }            }            catch (Exception ex)            {                iserror = true;                strerror = ex.Message;            }            finally            {                if (iserror)                {                    SqlTran.Rollback();                    throw new Exception(strerror);                }                else                {                    SqlTran.Commit();                }                con.Close();            }            if (iserror)            {                return false;            }            else            {                return true;            }        }        /// <summary>        /// 执行非查询语句,并返回受影响的记录行数        /// </summary>        /// <param name="sql">SQL语句</param>        /// <param name="cmdtype">命令类型</param>        /// <param name="parameters">参数</param>        /// <returns>受影响记录行数</returns>        public int ExecuteCommand(string sql, CommandType cmdtype, DbParameter[] parameters)        {            int _result = 0;            DbCommand _command = CreateCommand(sql, cmdtype, parameters);            try            {                _command.Connection.Open();                _result = _command.ExecuteNonQuery();            }            catch (Exception ex)            {                throw new Exception(ex.Message);            }            finally            {                _command.Connection.Close();            }            return _result;        }        #endregion        //===========================================ExecuteScalar()=============================        #region 执行非查询语句,并返回首行首列的值 ExecuteScalar(string sql)        /// <summary>        /// 执行非查询语句,并返回首行首列的值        /// </summary>        /// <param name="sql">SQL语句</param>        /// <returns>Object</returns>        public object ExecuteScalar(string sql)        {            DbParameter[] parameters = new DbParameter[0];            return ExecuteScalar(sql, CommandType.Text, parameters);        }        /// <summary>        /// 执行非查询语句,并返回首行首列的值        /// </summary>        /// <param name="sql">SQL语句</param>        /// <param name="cmdtype">命令类型</param>        /// <returns>Object</returns>        public object ExecuteScalar(string sql, CommandType cmdtype)        {            DbParameter[] parameters = new DbParameter[0];            return ExecuteScalar(sql, CommandType.Text, parameters);        }        /// <summary>        /// 执行非查询语句,并返回首行首列的值        /// </summary>        /// <param name="sql">SQL语句</param>        /// <param name="parameters">参数</param>        /// <returns>Object</returns>        public object ExecuteScalar(string sql, DbParameter[] parameters)        {            return ExecuteScalar(sql, CommandType.Text, parameters);        }        /// <summary>        /// 执行非查询语句,并返回首行首列的值        /// </summary>        /// <param name="sql">SQL语句</param>        /// <param name="cmdtype">命令类型</param>        /// <param name="parameters">参数</param>        /// <returns>Object</returns>        public object ExecuteScalar(string sql, CommandType cmdtype, DbParameter[] parameters)        {            object _result = null;            DbCommand _command = CreateCommand(sql, cmdtype, parameters);            try            {                _command.Connection.Open();                _result = _command.ExecuteScalar();            }            catch            {                throw;            }            finally            {                _command.Connection.Close();            }            return _result;        }        #endregion        //===========================================ExecuteReader()=============================        #region 执行查询，并以DataReader返回结果集  ExecuteReader(string sql)        /// <summary>        /// 执行查询，并以DataReader返回结果集        /// </summary>        /// <param name="sql">SQL语句</param>        /// <returns>IDataReader</returns>        public DbDataReader ExecuteReader(string sql)        {            DbParameter[] parameters = new DbParameter[0];            return ExecuteReader(sql, CommandType.Text, parameters);        }        /// <summary>        /// 执行查询，并以DataReader返回结果集        /// </summary>        /// <param name="sql">SQL语句</param>        /// <param name="cmdtype">命令类型</param>        /// <returns>IDataReader</returns>        public DbDataReader ExecuteReader(string sql, CommandType cmdtype)        {            DbParameter[] parameters = new DbParameter[0];            return ExecuteReader(sql, CommandType.Text, parameters);        }        /// <summary>        /// 执行查询，并以DataReader返回结果集        /// </summary>        /// <param name="sql">SQL语句</param>        /// <param name="parameters">参数</param>        /// <returns>IDataReader</returns>        public DbDataReader ExecuteReader(string sql, DbParameter[] parameters)        {            return ExecuteReader(sql, CommandType.Text, parameters);        }        /// <summary>        /// 执行查询，并以DataReader返回结果集        /// </summary>        /// <param name="sql">SQL语句</param>        /// <param name="cmdtype">命令类型</param>        /// <param name="parameters">参数</param>        /// <returns>IDataReader</returns>        public DbDataReader ExecuteReader(string sql, CommandType cmdtype, DbParameter[] parameters)        {            DbDataReader _result;            DbCommand _command = CreateCommand(sql, cmdtype, parameters);            try            {                _command.Connection.Open();                _result = _command.ExecuteReader(CommandBehavior.CloseConnection);            }            catch            {                throw;            }            finally            {            }            return _result;        }        #endregion        //===========================================GetDataSet()================================        #region 执行查询，并以DataSet返回结果集 GetDataSet(string sql)        /// <summary>        /// 执行查询，并以DataSet返回结果集        /// </summary>        /// <param name="sql">SQL语句</param>        /// <returns>DataSet</returns>        public DataSet GetDataSet(string sql)        {            DbParameter[] parameters = new DbParameter[0];            return GetDataSet(sql, CommandType.Text, parameters);        }        /// <summary>        /// 执行查询，并以DataSet返回结果集        /// </summary>        /// <param name="sql">SQL语句</param>        /// <param name="cmdtype">命令类型</param>        /// <returns>DataSet</returns>        public virtual DataSet GetDataSet(string sql, CommandType cmdtype)        {            DbParameter[] parameters = new DbParameter[0];            return GetDataSet(sql, CommandType.Text, parameters);        }        /// <summary>        /// 执行查询，并以DataSet返回结果集        /// </summary>        /// <param name="sql">SQL语句</param>        /// <param name="parameters">参数</param>        /// <returns>DataSet</returns>        public virtual DataSet GetDataSet(string sql, DbParameter[] parameters)        {            return GetDataSet(sql, CommandType.Text, parameters);        }        /// <summary>        /// 执行查询，并以DataSet返回结果集        /// </summary>        /// <param name="sql">SQL语句</param>        /// <param name="cmdtype">命令类型</param>        /// <param name="parameters">参数</param>        /// <returns>DataSet</returns>        public virtual DataSet GetDataSet(string sql, CommandType cmdtype, DbParameter[] parameters)        {            DataSet _result = new DataSet();            IDataAdapter _dataAdapter = CreateAdapter(sql, cmdtype, parameters);            try            {                _dataAdapter.Fill(_result);            }            catch            {                throw;            }            finally            {            }            return _result;        }        /// <summary>        /// 执行查询,并以DataSet返回指定记录的结果集        /// </summary>        /// <param name="sql">SQL语句</param>        /// <param name="StartIndex">开始索引</param>        /// <param name="RecordCount">显示记录</param>        /// <returns>DataSet</returns>        public virtual DataSet GetDataSet(string sql, int StartIndex, int RecordCount)        {            return GetDataSet(sql, StartIndex, RecordCount);        }        #endregion        //===========================================GetDataView()===============================        #region 执行查询，并以DataView返回结果集   GetDataView(string sql)        /// <summary>        /// 执行查询，并以DataView返回结果集        /// </summary>        /// <param name="sql">SQL语句</param>        /// <param name="cmdtype">命令类型</param>        /// <param name="parameters">参数</param>        /// <returns>DataView</returns>        public DataView GetDataView(string sql)        {            DbParameter[] parameters = new DbParameter[0];            DataView dv = GetDataSet(sql, CommandType.Text, parameters).Tables[0].DefaultView;            return dv;        }        /// <summary>        /// 执行查询，并以DataView返回结果集        /// </summary>        /// <param name="sql">SQL语句</param>        /// <param name="cmdtype">命令类型</param>        /// <param name="parameters">参数</param>        /// <returns>DataView</returns>        public DataView GetDataView(string sql, CommandType cmdtype)        {            DbParameter[] parameters = new DbParameter[0];            DataView dv = GetDataSet(sql, cmdtype, parameters).Tables[0].DefaultView;            return dv;        }        /// <summary>        /// 执行查询，并以DataView返回结果集        /// </summary>        /// <param name="sql">SQL语句</param>        /// <param name="cmdtype">命令类型</param>        /// <param name="parameters">参数</param>        /// <returns>DataView</returns>        public DataView GetDataView(string sql, DbParameter[] parameters)        {            DataView dv = GetDataSet(sql, CommandType.Text, parameters).Tables[0].DefaultView;            return dv;        }        /// <summary>        /// 执行查询，并以DataView返回结果集        /// </summary>        /// <param name="sql">SQL语句</param>        /// <param name="cmdtype">命令类型</param>        /// <param name="parameters">参数</param>        /// <returns>DataView</returns>        public DataView GetDataView(string sql, CommandType cmdtype, DbParameter[] parameters)        {            DataView dv = GetDataSet(sql, cmdtype, parameters).Tables[0].DefaultView;            return dv;        }        /// <summary>        /// 执行查询,并以DataView返回指定记录的结果集        /// </summary>        /// <param name="sql">SQL语句</param>        /// <param name="StartIndex">开始索引</param>        /// <param name="RecordCount">显示记录</param>        /// <returns>DataView</returns>        public DataView GetDataView(string sql, int StartIndex, int RecordCount)        {            return GetDataSet(sql, StartIndex, RecordCount).Tables[0].DefaultView;        }        #endregion        //===========================================GetDataTable()==============================        #region 执行查询，并以DataTable返回结果集   GetDataTable(string sql)        /// <summary>        /// 执行查询，并以DataTable返回结果集        /// </summary>        /// <param name="sql">SQL语句</param>        /// <param name="cmdtype">命令类型</param>        /// <param name="parameters">参数</param>        /// <returns>DataTable</returns>        public DataTable GetDataTable(string sql)        {            DbParameter[] parameters = new DbParameter[0];            DataTable dt = GetDataSet(sql, CommandType.Text, parameters).Tables[0];            return dt;        }        /// <summary>        /// 执行查询，并以DataTable返回结果集        /// </summary>        /// <param name="sql">SQL语句</param>        /// <param name="cmdtype">命令类型</param>        /// <param name="parameters">参数</param>        /// <returns>DataTable</returns>        public DataTable GetDataTable(string sql, CommandType cmdtype)        {            DbParameter[] parameters = new DbParameter[0];            DataTable dt = GetDataSet(sql, cmdtype, parameters).Tables[0];            return dt;        }        /// <summary>        /// 执行查询，并以DataTable返回结果集        /// </summary>        /// <param name="sql">SQL语句</param>        /// <param name="cmdtype">命令类型</param>        /// <param name="parameters">参数</param>        /// <returns>DataTable</returns>        public DataTable GetDataTable(string sql, DbParameter[] parameters)        {            DataTable dt = GetDataSet(sql, CommandType.Text, parameters).Tables[0];            return dt;        }        /// <summary>        /// 执行查询，并以DataTable返回结果集        /// </summary>        /// <param name="sql">SQL语句</param>        /// <param name="cmdtype">命令类型</param>        /// <param name="parameters">参数</param>        /// <returns>DataTable</returns>        public DataTable GetDataTable(string sql, CommandType cmdtype, DbParameter[] parameters)        {            DataTable dt = GetDataSet(sql, cmdtype, parameters).Tables[0];            return dt;        }        /// <summary>        /// 执行查询,并以DataTable返回指定记录的结果集        /// </summary>        /// <param name="sql">SQL语句</param>        /// <param name="StartIndex">开始索引</param>        /// <param name="RecordCount">显示记录</param>        /// <returns>DataTable</returns>        public DataTable GetDataTable(string sql, int StartIndex, int RecordCount)        {            return GetDataSet(sql, StartIndex, RecordCount).Tables[0];        }        /// <summary>        /// 执行查询,返回以空行填充的指定条数记录集        /// </summary>        /// <param name="sql">SQL语句</param>        /// <param name="SizeCount">显示记录条数</param>        /// <returns>DataTable</returns>        public DataTable GetDataTable(string sql, int SizeCount)        {            DataTable dt = GetDataSet(sql).Tables[0];            int b = SizeCount - dt.Rows.Count;            if (dt.Rows.Count < SizeCount)            {                for (int i = 0; i < b; i++)                {                    DataRow dr = dt.NewRow();                    dt.Rows.Add(dr);                }            }            return dt;        }        #endregion    }}