一个SQL DB操作的类

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Xml;
using System.Xml.Xsl;
using System.Xml.XPath;
using System.Collections;

namespace EDNP2.webservice
{
 /// <summary>
 /// Database connect and execute class.
 /// </summary>
 public class DB
 {
  public static SqlConnection DbConn;
       
  public DB()
  {
         
  }

  /// <summary>
  ///  Connect to database.
  /// </summary>
  private void Conn()
  {
   //if (HttpContext.Current.Session["DB"] != null)   
   // DBConn = (SqlConnection)HttpContext.Current.Session["DB"];   
   //else
    DbConn = new SqlConnection(HttpContext.Current.Application["strConnection"].ToString());  
  }

  /// <summary>
  /// get dataset
  /// </summary>
  /// <param name="strSQL">(string)</param>
  /// <returns>(DataSet)</returns>
  public DataSet AdoGetDataSet(string strSql)
  {
   Conn();
   DataSet ds = new DataSet();
   SqlDataAdapter sda = new SqlDataAdapter();
   SqlCommand myCmd = new SqlCommand(strSql,DbConn);       
   try
   { 
    
    sda.SelectCommand = myCmd;
    sda.Fill(ds,"ds");
    return ds;
   }
   catch(System.Data.SqlClient.SqlException e)
   {    
    throw new Exception(e.Message);
   }
   finally
   {   
    myCmd.Dispose();
    DbConn.Close();    
   }
  }

  /// <summary>
  /// execute sql query
  /// </summary>
  /// <param name="strSQL">(string)</param>
  /// <returns>The affected records count</returns>
  public int AdoExecuteSQL(string strSql)
  { 
   Conn();
   
   SqlCommand myCmd = new SqlCommand(strSql,DbConn);
   try
   { 
    DbConn.Open();
    return myCmd.ExecuteNonQuery();
   }
   catch(System.Data.SqlClient.SqlException e)
   {    
    throw new Exception(e.Message);
   }
   finally
   {
    myCmd.Dispose() ;
    DbConn.Close();    
   }
  }

  /// <summary>
  /// Execute stored procedure
  /// </summary>
  /// <param name="strSQLs">procedure name</param>
  /// <param name=" typeArray1">procedure parameters name array</param>
  /// <param name=" typeArray2">procedure parameters value type array</param>
  /// <param name=" typeArray3">procedure parameters value length array</param>
  /// <param name=" valueArray">procedure parameters value array</param>
  /// <returns>(int)</returns>
  public int AdoExecuteSp(string strSql,ArrayList typeArray1,ArrayList typeArray2,ArrayList typeArray3,ArrayList valueArray)
  {
   Conn();
   SqlCommand myCmd = new SqlCommand(strSql,DbConn);
   myCmd.CommandType =CommandType.StoredProcedure ;

   for (int i=0;i< valueArray.Count;i++)
   {
    myCmd.Parameters.Add(new SqlParameter((string)typeArray1[i],(SqlDbType)typeArray2[i],(int)typeArray3[i]));
    myCmd.Parameters[(string)typeArray1[i]].Value = valueArray[i];
   }
   try
   {
    DbConn.Open();
    return myCmd.ExecuteNonQuery() ;
   }
   catch(System.Data.SqlClient.SqlException er)
   {
    throw new Exception(er.Message);
   }
   finally
   {
    myCmd.Dispose();
    DbConn.Close();
   }
  }

  /// <summary>
  /// Execute stored procedure for dataset
  /// </summary>
  /// <param name="strSQLs">procedure name</param>
  /// <param name=" typeArray1">procedure parameters name array</param>
  /// <param name=" typeArray2">procedure parameters value type array</param>
  /// <param name=" typeArray3">procedure parameters value length array</param>
  /// <param name=" valueArray">procedure parameters value array</param>
  /// <returns>(dataset)</returns>
  public DataSet AdoExecuteSp4Ds(string strSql,ArrayList typeArray1,ArrayList typeArray2,ArrayList typeArray3,ArrayList valueArray)
  {
   Conn();
   SqlCommand myCmd = new SqlCommand(strSql,DbConn);
   myCmd.CommandType =CommandType.StoredProcedure ;
   SqlDataAdapter sda = new SqlDataAdapter();
   DataSet ds = new DataSet(); 

   for (int i=0;i< valueArray.Count;i++)
   {
    myCmd.Parameters.Add(new SqlParameter((string)typeArray1[i],(SqlDbType)typeArray2[i],(int)typeArray3[i]));
    myCmd.Parameters[(string)typeArray1[i]].Value = valueArray[i];
   }
   try
   { 
    sda.SelectCommand = myCmd;    
    sda.Fill(ds);
    return ds;
   }
   catch(System.Data.SqlClient.SqlException er)
   {
    throw new Exception(er.Message);
   }
   finally
   {
    myCmd.Dispose();
    DbConn.Close();
   }
  }

  /// <summary>
  /// Execute stored procedure for dataReader
  /// </summary>
  /// <param name="strSQLs">procedure name</param>
  /// <param name=" typeArray1">procedure parameters name array</param>
  /// <param name=" typeArray2">procedure parameters value type array</param>
  /// <param name=" typeArray3">procedure parameters value length array</param>
  /// <param name=" valueArray">procedure parameters value array</param>
  /// <returns>(dataReader)</returns>
  public SqlDataReader AdoExecuteSp4Dr(string strSql,ArrayList typeArray1,ArrayList typeArray2,ArrayList typeArray3,ArrayList valueArray)
  {
   Conn();
   SqlCommand myCmd = new SqlCommand(strSql,DbConn);
   myCmd.CommandType =CommandType.StoredProcedure;
   

   for (int i=0;i< valueArray.Count;i++)
   {
    myCmd.Parameters.Add(new SqlParameter((string)typeArray1[i],(SqlDbType)typeArray2[i],(int)typeArray3[i]));
    myCmd.Parameters[(string)typeArray1[i]].Value = valueArray[i];
   }
   try
   { 
    SqlDataReader dr = myCmd.ExecuteReader();
    return dr;
   }
   catch(System.Data.SqlClient.SqlException er)
   {
    throw new Exception(er.Message);
   }
   finally
   {
    myCmd.Dispose();
    DbConn.Close();
   }
  }

  /// <summary>
  /// Execute stored procedure with output
  /// </summary>
  /// <param name="strSQLs">procedure name</param>
  /// <param name=" typeArray1">procedure parameters name array</param>
  /// <param name=" typeArray2">procedure parameters value type array</param>
  /// <param name=" typeArray3">procedure parameters value length array</param>
  /// <param name=" valueArray">procedure parameters value array</param>
  /// <param name=" directionArray">input or output</param>
  /// <returns>(int)</returns>
  public SqlCommand AdoExecuteSp(string strSql,ArrayList typeArray1,ArrayList typeArray2,ArrayList typeArray3,ArrayList valueArray,ArrayList directionArray)
  {
   Conn();
   SqlCommand myCmd = new SqlCommand(strSql,DbConn);
   myCmd.CommandType =CommandType.StoredProcedure ;   

   for (int i=0;i< valueArray.Count;i++)
   {
    myCmd.Parameters.Add(new SqlParameter((string)typeArray1[i],(SqlDbType)typeArray2[i],(int)typeArray3[i]));
    myCmd.Parameters[(string)typeArray1[i]].Value = valueArray[i];
    if (directionArray[i].ToString().ToLower() == "out")
     myCmd.Parameters[(string)typeArray1[i]].Direction =ParameterDirection.Output;
    else if (directionArray[i].ToString().ToLower() == "return")
     myCmd.Parameters[(string)typeArray1[i]].Direction =ParameterDirection.ReturnValue;
    else
     myCmd.Parameters[(string)typeArray1[i]].Direction = ParameterDirection.Input;
   }
   try
   {
    DbConn.Open();
    myCmd.ExecuteNonQuery();
    return myCmd;
   }
   catch(System.Data.SqlClient.SqlException er)
   {
    throw new Exception(er.Message);
   }
   finally
   {
    myCmd.Dispose();
    DbConn.Close();
   }
  }
 }
}