.NET 进行数据库访问时需要调用的类

using System;
using System.ComponentModel;
using System.Collections;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.SessionState;
using System.Web;

namespace Utilities
{

 /// <summary>
 /// DbAccess类,即进行数据库访问时需要调用的类
 /// </summary>
 public class DbaseBS
 {
  //取出数据库连接字符串
  public static readonly string conStr = ConfigurationSettings.AppSettings["ConnectionString"];  
  
  /// <summary>
  /// 得到数据库连接对象
  /// </summary>
  /// <returns>数据库连接对象</returns>
  public static SqlConnection GetConObject()
  {
   return new SqlConnection(conStr);
  }

  /// <summary>
  /// 执行操作数据库的存储过程
  /// </summary>
  /// <param name="procName">存储过程名称</param> 
  /// <returns>存储过程执行后所影响的行数</returns>
  public static int ExecuteNonQuery(string procName)
  {
   SqlCommand cmd = new SqlCommand();

   using (SqlConnection con = GetConObject())
   {
    CreateCommand(cmd, con, null, procName,null);
    int val = cmd.ExecuteNonQuery();
    cmd.Parameters.Clear();
    cmd.Dispose();
    return val;
   }
  } 

  /// <summary>
  /// 执行操作数据库的存储过程
  /// </summary>
  /// <param name="procName">存储过程名称</param>
  /// <param name="cmdParms">存储过程所使用的参数</param>
  /// <returns>存储过程执行后所影响的行数</returns>
  public static int ExecuteNonQuery(string procName, SqlParameter[] cmdParms)
  {
   SqlCommand cmd = new SqlCommand();

   using (SqlConnection con = GetConObject())
   {
    CreateCommand(cmd, con, null, procName, cmdParms);
    int val = cmd.ExecuteNonQuery();
    cmd.Parameters.Clear();
    cmd.Dispose();
    return val;
   }
  } 

  /// <summary>
  /// 执行操作数据库的存储过程(事务控制)
  /// </summary>
  /// <param name="trans">数据库连接所关联的事务对象</param>
  /// <param name="procName">存储过程名称</param>
  /// <param name="cmdParms">存储过程所使用的参数</param>
  /// <returns>存储过程执行后所影响的行数</returns>
  public static int ExecuteNonQuery(SqlTransaction trans, string procName, SqlParameter[] cmdParms)
  {
   SqlCommand cmd = new SqlCommand();
   CreateCommand(cmd, trans.Connection, trans, procName, cmdParms);
   int val = cmd.ExecuteNonQuery();
   cmd.Parameters.Clear();
   cmd.Dispose();
   return val;
  }

  /// <summary>
  /// 执行读数据集操作，以DataReader的形式返回
  /// </summary>
  /// <param name="procName">存储过程名称</param>
  /// <param name="cmdParms">存储过程所使用的参数</param>
  /// <returns>DataReader对象</returns>
  public static SqlDataReader ExecuteReader(string procName, SqlParameter[] cmdParms)
  {
   SqlCommand cmd = new SqlCommand();
   SqlConnection con = GetConObject();

   try
   {
    CreateCommand(cmd, con, null, procName, cmdParms);
    SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
    cmd.Parameters.Clear();
    return dr;
   }
   catch(Exception ex)
   {
    cmd.Dispose();
    Close(con);
    throw ex;
   }   
  }  
  
  /// <summary>
  /// 执行读数据集操作，以DataReader的形式返回
  /// </summary>
  /// <param name="procName">存储过程名称</param>  
  /// <returns>DataReader对象</returns>
  public static SqlDataReader ExecuteReader(string procName)
  {
   SqlCommand cmd = new SqlCommand();
   SqlConnection con = GetConObject();

   try
   {
    CreateCommand(cmd, con, null, procName, null);
    SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
    cmd.Parameters.Clear();
    return dr;
   }
   catch(Exception ex)
   {
    cmd.Dispose();
    Close(con);
    throw ex;
   }   
  }
  
  /// <summary>
  /// 执行读数据集操作，以DataSet的形式返回
  /// </summary>
  /// <param name="procName">存储过程名称</param>
  /// <param name="cmdParms">存储过程所使用的参数</param>
  /// <returns>DataSet对象</returns>
  public static DataSet ExecuteDataSet(string procName, SqlParameter[] cmdParms)
  {
   SqlCommand cmd = new SqlCommand();
   SqlConnection con = GetConObject();
   DataSet ds = new DataSet();

   try
   {
    CreateCommand(cmd, con, null, procName, cmdParms);
    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = cmd;
    da.Fill(ds);
    cmd.Parameters.Clear();
    return ds;
   }
   catch(Exception ex)
   {
    throw ex;
   } 
   finally
   {
    cmd.Dispose();
    Close(con);
   }
  }
  

  /// <summary>
  /// 执行读数据集操作，以DataSet的形式返回
  /// </summary>
  /// <param name="procName">存储过程名称</param>  
  /// <returns>DataReader对象</returns>
  public static DataSet ExecuteDataSet(string procName)
  {
   SqlCommand cmd = new SqlCommand();
   SqlConnection con = GetConObject();
   DataSet ds = new DataSet();

   try
   {
    CreateCommand(cmd, con, null, procName, null);
    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = cmd;
    da.Fill(ds);
    cmd.Parameters.Clear();
    return ds;
   }
   catch(Exception ex)
   {
    throw ex;
   } 
   finally
   {
    cmd.Dispose();
    Close(con);
   }
  } 

  /// <summary>
  /// 创建数据库执行命令
  /// </summary>
  /// <param name="cmd">数据库执行命令对象</param>
  /// <param name="con">数据库连接对象</param>
  /// <param name="trans">数据库事务对象</param>
  /// <param name="procName">存储过程名称</param>
  /// <param name="cmdParms">存储过程所使用的参数数组</param>
  public  static void CreateCommand(SqlCommand cmd, SqlConnection con, SqlTransaction trans, string procName, SqlParameter[] cmdParms)
  {
   if (con.State != ConnectionState.Open)
    con.Open();

   cmd.Connection = con;
   cmd.CommandText = procName;

   if (trans != null)
   {
    cmd.Transaction = trans;
   }

   cmd.CommandType = CommandType.StoredProcedure;

   if (cmdParms != null)
   {
    foreach(SqlParameter parm in cmdParms)
     cmd.Parameters.Add(parm);
   }
  }
  
  /// <summary>
  /// 准备输入参数.
  /// </summary>
  /// <param name="paramName">参数名称</param>
  /// <param name="dbType">参数数据类型</param>
  /// <param name="size">参数大小</param>
  /// <param name="value">参数值</param>
  /// <returns>输入参数</returns>
  public static SqlParameter MakeInParam(string paramName, SqlDbType dbType, int size, object inValue)
  {
   return MakeParam(paramName, dbType, size, ParameterDirection.Input, inValue);    
  }  

  /// <summary>
  /// 准备输出参数.
  /// </summary>
  /// <param name="paramName">参数名称</param>
  /// <param name="dbType">参数数据类型</param>
  /// <param name="size">参数大小</param>  
  /// <returns>输出参数</returns>
  public static SqlParameter MakeOutParam(string paramName, SqlDbType dbType, int size)
  {
   return MakeParam(paramName, dbType, size, ParameterDirection.Output, null);
  }  

  /// <summary>
  /// 准备存储过程的参数.
  /// </summary>
  /// <param name="paramName">参数名称</param>
  /// <param name="dbType">参数数据类型</param>
  /// <param name="size">参数大小</param>  
  /// <param name="direction">参数方向</param>
  /// <param name="inValue">参数值</param>
  /// <returns>输入或输出参数</returns>
  public static SqlParameter MakeParam(string paramName, SqlDbType dbType, int size, ParameterDirection direction, object inValue)
  {
   SqlParameter param;

   if(size > 0)
    param = new SqlParameter(paramName,dbType,size);
   else
    param = new SqlParameter(paramName,dbType);

   param.Direction = direction;

   if (!(direction == ParameterDirection.Output && inValue == null))
    param.Value = inValue;

   return param;
  } 
 
  /// <summary>
  /// 关闭数据库连接
  /// </summary>
  /// <param name="myConn">数据库连接对象</param>
  public static void Close(SqlConnection myConn)
  {
   if((myConn != null) && (myConn.State == ConnectionState.Open))
   {
    myConn.Close();
   } 
  }

  /// <summary>
  /// 直接执行sql语句返回数据集
  /// </summary>
  /// <param name="strSql">sql语句</param>
  /// <returns>查询的数据集</returns>
  public static DataSet ExecuteDataSetSql(string strSql)
  { 
   SqlCommand cmd = new SqlCommand();
   SqlConnection con = GetConObject();
   DataSet ds = new DataSet();
   SqlDataAdapter da = null;

   try
   {
    da = new SqlDataAdapter(strSql,con);
    da.Fill(ds);
    return ds;
   }
   catch(Exception ex)
   {
    throw ex;
   } 
   finally
   {
    if(da != null)
     da.Dispose();

    cmd.Dispose();
    Close(con);
   }
  } 

  
  /// <summary>
  /// 直接运行sql语句
  /// </summary>
  /// <param name="trans">事务</param>
  /// <param name="strSql">sql语句</param>
  public static void ExecuteSql( SqlTransaction trans,string strSql)
  { 
   SqlCommand cmd = new SqlCommand();
   SqlConnection con = GetConObject();

   try
   {
    if (con.State != ConnectionState.Open)
     con.Open();

    cmd.Connection = trans.Connection;
    cmd.Transaction = trans;
    cmd.CommandText = strSql;

    cmd.ExecuteNonQuery();
   }
   catch(Exception ex)
   {
    throw ex;
   } 
   finally
   {
    cmd.Dispose();
    Close(con);
   }
  } 
 }
}