首页 / 软件开发 / C# / 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}}