通用数据库访问类

/*********************************************************************************************************
 名称空间:DataBase
 模块名称:DAO.cs
 类库介绍:实现独立于数库的的DAO类,支持SQL数据提供者|OLEDB数据提供者|ODBC数据提供者
          数据库配置信息,详见web.config中<appSettings>节ConnType数据库类型,ConnectionString连接字符串.
    说明:
    未选择使用基于连接的事务处理,可选择基于数据源的事务处理.或者COM+.
 模块作者:意识流  E-Mail:[e-mail]
 编写日期:2005-10-15
 修改日期:2006-04-02
  ********************************************************************************************************/
using System;
using System.ComponentModel;
using System.Collections;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;
using System.Configuration;
namespace DataBase {
 /// <summary>
 /// 通用的数据库处理类，通过ado.net与数据库连接
 /// </summary>
 public class DAO : IDisposable {
  private string conntype;//连接类型
  private IDbConnection conn;//数据库连接
  
  #region 构造函数******************************************************************************************
  ///<summary>
  ///在构造函数
  ///</summary>
  ///<param name="ConnType">数据连接类型,在web.config中设置OLE/SQL/ODBC</param>
  ///<param name="ConnectionString">连接字符串,在web.config中设置</param>
  //********************************************************************************************************
  
  public DAO(){
   switch(ConfigurationSettings.AppSettings["ConnType"]){
    case "OLE":
                    conntype="OLE";
     conn=new OleDbConnection();
                    break;
    case "SQL":
     conntype="SQL";
     conn=new SqlConnection();
     break;
    case "ODBC":
     conntype="ODBC";
     conn=new OdbcConnection();
     break;
    default:
     conntype="SQL";
     conn=new SqlConnection();
     break;
   }
   conn.ConnectionString=ConfigurationSettings.AppSettings["ConnectionString"];
   
  }
  #endregion
   
  #region 执行存储过程,不返回记录***************************************************************************
  /// <summary>
  /// 执行存储过程,不返回记录
  /// </summary>
  /// <param name="RunProcNon">方法</param>
  /// <param name="procName">存储过程名称</param>
  /// <param name="paras">存储过程所需参数,可选无,IDbDataParamter,IDbDataParamter[]</param>
  /// <returns>返回影响行数</returns>
  //******************************************************************************************************** 
  public int RunProcNon(string procName) {
   IDbCommand cmd = CreateCommand(procName);
   this.Open();
   int returnValue=cmd.ExecuteNonQuery();
   this.Close();
   return returnValue;
  }

  public int RunProcNon(string procName,IDbDataParameter para){
   IDbCommand cmd=CreateCommand(procName,para);
   this.Open();
   int returnValue=cmd.ExecuteNonQuery();
   this.Close();
   return returnValue;
  }

  public int RunProcNon(string procName, IDbDataParameter[] paras) {
   IDbCommand cmd = CreateCommand(procName,paras);
   this.Open();
   int returnValue=cmd.ExecuteNonQuery();
   this.Close();
   return returnValue;
  }

  #endregion
  
  #region 执行存储过程,返回首行首列*************************************************************************
  /// <summary>
  /// 执行存储过程,返回一行一列
  /// </summary>
  /// <param name="RunProcScalar">方法名</param>
  /// <param name="procName">存储过程名称</param>
  /// <param name="paras">存储过程所需参数,可选无,IDbDataParamter,IDbDataParamter[]</param>
  /// <returns>返回object对象</returns>
  //********************************************************************************************************
  public object RunProcScalar(string procName){
   IDbCommand cmd=CreateCommand(procName);
   this.Open();
   object returnValue=cmd.ExecuteScalar();
   this.Close();
   return returnValue;
  }

  public object RunProcScalar(string procName,IDbDataParameter para){
   IDbCommand cmd=CreateCommand(procName,para);
   this.Open();
   object returnValue=cmd.ExecuteScalar();
   this.Close();
   return returnValue;
  }
  
  public object RunProcScalar(string procName,IDbDataParameter[] paras){
   IDbCommand cmd=CreateCommand(procName,paras);
   this.Open();
   object returnValue=cmd.ExecuteScalar();
   this.Close();
   return returnValue;
  }
  
  #endregion

  #region 执行存储过程,返回IDataReader**********************************************************************
  /// <summary>
  /// 执行存储过程,返回IDataReader
  /// </summary>
  /// <param name="RunProcNon">方法名</param>
  /// <param name="procName">存储过程名称</param>
  /// <param name="paras">存储过程所需参数,可选无,IDbDataParamter,IDbDataParamter[]</param>
  /// <returns>返回一个IDataReader对象</returns>
  //********************************************************************************************************

  public IDataReader RunProcReader(string procName) {
   IDbCommand cmd = CreateCommand(procName);
   this.Open();
   IDataReader dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
   return dataReader;
  }
  
  public IDataReader RunProcReader(string procName,IDbDataParameter para){
   IDbCommand cmd=CreateCommand(procName,para);
   this.Open();
   IDataReader dataReader=cmd.ExecuteReader(CommandBehavior.CloseConnection);
   return dataReader;
  }
  
  public IDataReader RunProcReader(string procName, IDbDataParameter[] paras) {
   IDbCommand cmd = CreateCommand(procName,paras);
   this.Open();
   IDataReader dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
   return dataReader;
  }
  
  #endregion

  #region 执行存储过程,返回DataSet**************************************************************************
  /// <summary>
  /// 执行存储过程,返回DataSet
  /// </summary>
  /// <param name="RunProcNon">方法名</param>
  /// <param name="procName">存储过程名称</param>
  /// <param name="paras">存储过程所需参数,可选无,IDbDataParamter,IDbDataParamter[]</param>
  /// <returns>返回DataSet</returns>
  //********************************************************************************************************
  public DataSet RunProcDataSet(string procName){
   IDbCommand cmd=CreateCommand(procName);
   IDbDataAdapter da=CreateAdapter();
   da.SelectCommand=cmd;
   DataSet ds=new DataSet();
   this.Open();
   da.Fill(ds);
   this.Close();
   return ds;
  }
  public DataSet RunProcDataSet(string procName,IDbDataParameter para){
   IDbCommand cmd=CreateCommand(procName,para);
   IDbDataAdapter da=CreateAdapter();
   da.SelectCommand=cmd;
   DataSet ds=new DataSet();
   this.Open();
   da.Fill(ds);
   this.Close();
   return ds;
  }
  public DataSet RunProcDataSet(string procName,IDbDataParameter[] paras){
   IDbCommand cmd=CreateCommand(procName,paras);
   IDbDataAdapter da=CreateAdapter();
   da.SelectCommand=cmd;
   DataSet ds=new DataSet();
   this.Open();
   da.Fill(ds);
   this.Close();
   return ds;
  }
  #endregion

  #region 执行存储过程,返回DataTable************************************************************************
  /// <summary>
  /// 执行存储过程,返回DataTable
  /// </summary>
  /// <param name="RunProcNon">方法名</param>
  /// <param name="procName">存储过程名称</param>
  /// <param name="paras">存储过程所需参数,可选无,IDbDataParamter,IDbDataParamter[]</param>
  /// <returns>返回DataTable</returns>
  //********************************************************************************************************
  public DataTable RunProcDataTable(string procName) {
   IDbCommand cmd=CreateCommand(procName);
   IDbDataAdapter da=CreateAdapter();
   da.SelectCommand=cmd;
   DataSet ds=new DataSet();
   this.Open();
   da.Fill(ds);
   this.Close();
   DataTable dt=ds.Tables[0];
   ds.Dispose();
   return dt;   
  }
  public DataTable RunProcDataTable(string procName,IDbDataParameter para){
   IDbCommand cmd=CreateCommand(procName,para);
   IDbDataAdapter da=CreateAdapter();
   da.SelectCommand=cmd;
   DataSet ds=new DataSet();
   this.Open();
   da.Fill(ds);
   this.Close();
   DataTable dt=ds.Tables[0];
   ds.Dispose();
   return dt; 
  }
  public DataTable RunProcDataTable(string procName,IDbDataParameter[] paras){
   IDbCommand cmd=CreateCommand(procName,paras);
   IDbDataAdapter da=CreateAdapter();
   da.SelectCommand=cmd;
   DataSet ds=new DataSet();
   this.Open();
   da.Fill(ds);
   this.Close();
   DataTable dt=ds.Tables[0];
   ds.Dispose();
   return dt; 
  }
  #endregion

  #region 创建一个IDbCommand实例****************************************************************************
  /// <summary>
  /// 创建一个IDbCommand对象
  /// </summary>
  /// <param name="CreateCommand">方法名</param>
  /// <param name="porcName">存储过程名称,可选,不指定生成一个一般的IDbCommand实例</param>
  ///<param name="paras">存储过程参数,可选无,IDbDataParameter,IDbDataParamter[]</param>
  //********************************************************************************************************
  public IDbCommand CreateCommand() {
   IDbCommand cmd=conn.CreateCommand();
   return cmd;
  }
  private IDbCommand CreateCommand(string procName){
   IDbCommand cmd=conn.CreateCommand();
   cmd.CommandType=CommandType.StoredProcedure;
   cmd.CommandText=procName;
   return cmd;
  }
  private IDbCommand CreateCommand(string procName,IDbDataParameter para){
   IDbCommand cmd=conn.CreateCommand();
   cmd.CommandText=procName;
   cmd.CommandType=CommandType.StoredProcedure;
   cmd.Parameters.Add(para);
   return cmd;
  }
  private IDbCommand CreateCommand(string procName,IDbDataParameter[] paras){
   IDbCommand cmd=conn.CreateCommand();
   cmd.CommandType=CommandType.StoredProcedure;
   cmd.CommandText=procName;
   if(paras.Length!=0){
    foreach (IDbDataParameter para in paras){
     cmd.Parameters.Add(para);
    }
   }
   return cmd;
  }
  #endregion

  #region 创建一个IDbDataAdapter实例************************************************************************
  /// <summary>
  /// 创建一个IDbDataAdapter
  /// </summary>
  /// <param name="CreateAdapter">方法名</param>
  /// <returns>返回IDbDataAdapter</returns>
  //********************************************************************************************************  
  public IDbDataAdapter CreateAdapter(){
   switch(conntype){
    case "SQL":
     return (IDbDataAdapter)new SqlDataAdapter();
     
    case "OLE":
     return (IDbDataAdapter)new OleDbDataAdapter();
    case "ODBC":
     return (IDbDataAdapter)new OdbcDataAdapter();
    default:
     return (IDbDataAdapter)new SqlDataAdapter();

   }
  }
  #endregion

  #region 生成存储过程参数**********************************************************************************
  /// <summary>
  /// 生成存储过程传入/传出参数
  /// </summary>
  ///<param name="MakeInParam">方法名,生成一个传入参数IDbDataParameter实例</param>
  ///<param name="MakeOutParam">方法名,生成一个传出参数IDbDataParameter实例</param>
  ///<param name="MakeReturnParam">方法名,生成一个返回参数IDbDataParameter实例</param>
  /// <param name="ParamName">存储过程名称</param>
  /// <param name="DbType">参数类型</param></param>
  /// <param name="Value">参数值</param>
  /// <returns>返回IDbDataParameter</returns>
  //********************************************************************************************************  
  public IDbDataParameter MakeInParam(string ParamName,DbType Type, object Value) {
   return MakeParam(ParamName,Type,ParameterDirection.Input,Value);
  }
  
  public IDbDataParameter MakeOutParam(string ParamName,DbType Type,object Value) {
   return MakeParam(ParamName,Type,ParameterDirection.Output,Value);
  }

  public IDbDataParameter MakeReturnParam(string ParamName,DbType Type,object Value){
   return MakeParam(ParamName,Type,ParameterDirection.ReturnValue,Value);
  }

  private IDbDataParameter MakeParam(string ParamName,System.Data.DbType DbType,ParameterDirection Direction, object Value) {
   
   IDbDataParameter para=conn.CreateCommand().CreateParameter();
   para.ParameterName=ParamName;
   para.DbType=DbType;
   para.Direction=Direction;   
   if (Direction != ParameterDirection.Output && Direction!=ParameterDirection.ReturnValue){
    para.Value=Value;
   }
   
   return para;
 
  }
  #endregion

  #region 数据库基本操作************************************************************************************
  /// <summary>
  /// 数据库基本操作
  /// </summary>
  /// <param name="ExecuteNon">方法,执行命令不返回记录</param>
  /// <param name="ExecuteScalar">方法,执命令返回首行首列</param>
  /// <param name="ExecuteDataSet">方法,执行命令返回DataSet</param>
  /// <param name="ExecuteDataTable">方法,执行命令返回DataTable</param>
  /// <param name="ExecuteReader">方法,执行命令返回IDataReader</param>
  /// <param name="CmdText">参数,命令字符串</param>
    //*********************************************************************************************************
  public int ExecuteNon(string CmdText){
   IDbCommand cmd=CreateCommand();
   cmd.CommandType=CommandType.Text;
   cmd.CommandText=CmdText;
   this.Open();
   int returnValue=cmd.ExecuteNonQuery();
   this.Close();
   return returnValue;
  }
  public object ExecuteScalar(string CmdText){
   IDbCommand cmd=CreateCommand();
   cmd.CommandType=CommandType.Text;
   cmd.CommandText=CmdText;
   this.Open();
   object returnValue=cmd.ExecuteScalar();
   this.Close();
   return returnValue;
  }
  public IDataReader ExecuteReader(string CmdText){
   IDbCommand cmd=CreateCommand();
   cmd.CommandType=CommandType.Text;
   cmd.CommandText=CmdText;
   this.Open();
   IDataReader dataReader=cmd.ExecuteReader(CommandBehavior.CloseConnection);
   return dataReader;
  }
  public DataSet ExecuteDataSet(string CmdText){
   IDbCommand cmd=CreateCommand();
   cmd.CommandType=CommandType.Text;
   cmd.CommandText=CmdText;
   IDbDataAdapter da=CreateAdapter();
   da.SelectCommand=cmd;
   DataSet ds=new DataSet();
   this.Open();
   da.Fill(ds);
   this.Close();
   return ds;
  }
  public DataTable ExecuteDataTable(string CmdText){
   IDbCommand cmd=CreateCommand();
   cmd.CommandType=CommandType.Text;
   cmd.CommandText=CmdText;
   IDbDataAdapter da=CreateAdapter();
   da.SelectCommand=cmd;
   DataSet ds=new DataSet();
   this.Open();
   da.Fill(ds);
   this.Close();
   DataTable dt=ds.Tables[0];
   ds.Dispose();
   
   return dt;
  }
  #endregion

  #region 数据库打开与关闭**********************************************************************************
  /// <summary>
  /// 数据库基本操作.
  /// </summary>
  /// <param name="Open">方法,打开连接</param>
  /// <param name="Close">方法,关闭连接</param>
  /// <param name="Conn">属性,得到当前连接对象</param>
  /// <param name="Dispose">方法,释放连接资源</param>
  //********************************************************************************************************  
  private void Open() {
   if(conn.State ==ConnectionState.Closed){
    conn.Open();
   }
  }

  public void Close() {
   if(conn.State==ConnectionState.Open)
    conn.Close();
  }
  public IDbConnection Conn{
   get{ return conn;}
  }

  public void Dispose() {
   if (conn != null) {
    conn.Dispose();
    
   }    
  }
  #endregion

 }
}