自己写的一个数据访问类,请高手帮忙看看!谢谢!

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace SQLServerDAL
{
 /// <summary>
 /// SQLDAL 的摘要说明。
 /// </summary>
 public class SQLDAL
 {
  #region 属性
  private static string connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
  private static SqlConnection con = new SqlConnection();
  private static SqlCommand cmd = new SqlCommand();
  #endregion
  public SQLDAL()
  {
   //
   // TODO: 在此处添加构造函数逻辑
   //
  }

  #region 打开当前数据库连接
  /// <summary>
  /// 打开当前数据库连接
  /// </summary>
  private static void openConnection()
  {
   if(con.State == ConnectionState.Closed)
   {
    con.ConnectionString = connectionString;
    cmd.Connection = con;
   }
   try
   {
    con.Open();
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
  }
  #endregion

  #region 关闭当前数据库连接
  /// <summary>
  /// 关闭当前数据库连接
  /// </summary>
  private static void closeConnection()
  {
   if(con.State == ConnectionState.Open)
   {
    cmd.Dispose();
    con.Close();
    con.Dispose();
   }
  }
  #endregion

  #region 执行sql语句，并返回影响的记录数
  /// <summary>
  /// 执行sql语句，并返回影响的记录数
  /// </summary>
  /// <param name="strsql"></param>
  /// <returns></returns>
  public static int ExecuteSql(string strsql)
  {
   int count = 0;
   try
   {
    openConnection();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = strsql;
    count = cmd.ExecuteNonQuery();
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    closeConnection();
   }
   return count;
  }
  #endregion

  #region 执行sql语句并返回第一行的第一条记录，返回值为object时需要拆箱操作
  /// <summary>
  /// 执行sql语句并返回第一行的第一条记录，返回值为object时需要拆箱操作
  /// </summary>
  /// <param name="strsql"></param>
  /// <returns></returns>
  public static object ExecuteScalar(string strsql)
  {
   object obj = new object();
   try
   {
    openConnection();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = strsql;
    obj = cmd.ExecuteScalar();
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    closeConnection();
   }
   return obj;
  }
  #endregion
 
  #region 执行一条sql语句，同时进行事务处理
  /// <summary>
  /// 执行一条sql语句，同时进行事务处理
  /// </summary>
  /// <param name="strsql"></param>
  public static void ExecuteSqlWithTransaction(string strsql)
  {
   SqlTransaction Trans;
   Trans = con.BeginTransaction();
   cmd.Transaction = Trans;
   try
   {
    openConnection();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = strsql;
    cmd.ExecuteNonQuery();

    Trans.Commit();
   }
   catch(Exception e)
   {
    Trans.Rollback();
    throw new Exception(e.Message);
   }
   finally
   {
    closeConnection();
   }
  }
  #endregion

  #region 执行多条sql语句，同时进行事务处理
  /// <summary>
  /// 执行多条sql语句，同时进行事务处理
  /// </summary>
  /// <param name="strsql">传入的存储sql语句的数组</param>
  public static void ExecuteSqlWithTransaction(string [] strsql)
  {
   SqlTransaction Trans;
   Trans = con.BeginTransaction();
   cmd.Transaction = Trans;
   try
   {
    openConnection();
    cmd.CommandType = CommandType.Text;
    foreach(string sql in strsql)
    {
     cmd.CommandText = sql;
     cmd.ExecuteNonQuery();
    }
    Trans.Commit();
   }
   catch(Exception e)
   {
    Trans.Rollback();
    throw new Exception(e.Message);
   }
   finally
   {
    closeConnection();
   }
  }
  #endregion

  #region 返回指定sql语句的SqlDataReader
  /// <summary>
  /// 返回指定sql语句的SqlDataReader
  /// </summary>
  /// <param name="strsql">传入的sql语句</param>
  /// <returns>SqlDataReader</returns>
  public static SqlDataReader dataReader(string strsql)
  {
   SqlDataReader dr = null;
   try
   {
    openConnection();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = strsql;
    dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
   }
   catch
   {
    try
    {
     dr.Close();
     closeConnection();
    }
    catch
    {}
   }
   return dr;
  }
  #endregion

  #region 返回指定sql语句的引用的SqlDataReader
  /// <summary>
  /// 返回指定sql语句的引用的SqlDataReader
  /// </summary>
  /// <param name="strsql">传入的sql语句</param>
  /// <param name="dr">引用的SqlDataReader</param>
  public static void dataReader(string strsql,ref SqlDataReader dr)
  {
   try
   {
    openConnection();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = strsql;
    dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
   }
   catch
   {
    try
    {
     if(dr != null && !dr.IsClosed)
     {
      dr.Close();
     }
    }
    catch
    {}
    finally
    {
     closeConnection();
    }
   }
  }
  #endregion

  #region 执行不带参数的存储过程，并返回影响的记录数
  /// <summary>
  /// 执行不带参数的存储过程，并返回影响的记录数
  /// </summary>
  /// <param name="procName">存储过程名</param>
  /// <returns></returns>
  public static int ExecuteProcedure(string procName)
  {
   int count;
   try
   {
    openConnection();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = procName;
    count = cmd.ExecuteNonQuery();
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    closeConnection();
   }
   return count;
  }
  #endregion

  #region 执行带多个参数的存储过程
  /// <summary>
  /// 执行带多个参数的存储过程
  /// </summary>
  /// <param name="procName">存储过程名</param>
  /// <param name="coll">参数数组</param>
  public static void ExecuteProcedure(string procName,SqlParameter [] coll)
  {
   try
   {
    openConnection();
    cmd.Parameters.Clear();
    for(int i=0;i < coll.Length;i++)
    {
     cmd.Parameters.Add(coll[i]);
    }
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = procName;
    cmd.ExecuteNonQuery();
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    cmd.Parameters.Clear();
    closeConnection();
   }

  }
  #endregion

  #region 执行带多个参数的存储过程，并返回引用的DataSet

  public static void ExecuteProcedure(string procName,SqlParameter [] coll,ref DataSet ds)
  {
   try
   {
    SqlDataAdapter da = new SqlDataAdapter();
    for(int i=0;i<coll.Length;i++)
    {
     cmd.Parameters.Add(coll[i]);
    }
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = procName;

    da.SelectCommand = cmd;
    da.Fill(ds);
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    cmd.Parameters.Clear();
    closeConnection();
   }
  }
  #endregion

  #region 执行一条sql语句并返回指定的DataSet
  /// <summary>
  /// 执行一条sql语句并返回指定的DataSet
  /// </summary>
  /// <param name="strsql">传入的sql语句</param>
  /// <returns></returns>
  public static DataSet dataSet(string strsql)
  {
   DataSet ds = new DataSet();
   SqlDataAdapter da = new SqlDataAdapter();
   try
   {
    openConnection();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = strsql;

    da.SelectCommand = cmd;
    da.Fill(ds);
   }
            catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    closeConnection();
   }
   return ds;
  }
  #endregion

  #region 执行一条sql语句并返回引用的DataSet
  /// <summary>
  /// 执行一条sql语句并返回引用的DataSet
  /// </summary>
  /// <param name="strsql">传入的sql语句</param>
  /// <param name="ds">引用的DataSet</param>
  public static void dataSet(string strsql,ref DataSet ds)
  {
   SqlDataAdapter da = new SqlDataAdapter();
   try
   {
    openConnection();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = strsql;
    
    da.SelectCommand = cmd;
    da.Fill(ds);
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    closeConnection();
   }
  }
  #endregion

  #region 返回指定sql语句的DataTable

  public static DataTable dataTable(string strsql)
  {
   DataTable dt = new DataTable();
   SqlDataAdapter da = new SqlDataAdapter();
   try
   {
    openConnection();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = strsql;

    da.SelectCommand = cmd;
    da.Fill(dt);
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    closeConnection();
   }
   return dt;
  }
  #endregion

  #region 返回指定sql语句的引用的DataTable
  /// <summary>
  /// 返回指定sql语句的引用的DataTable
  /// </summary>
  /// <param name="strsql">传入的sql语句</param>
  /// <param name="dt">引用的DataTable</param>
  public static void dataTable(string strsql,ref DataTable dt)
  {
   SqlDataAdapter da = new SqlDataAdapter();
   try
   {
    openConnection();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = strsql;

    da.SelectCommand = cmd;
    da.Fill(dt);
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    closeConnection();
   }
  }
  #endregion

  #region 执行带参数的存储过程并返回DataTable
  /// <summary>
  /// 执行带参数的存储过程并返回数据集合
  /// </summary>
  /// <param name="procName">存储过程名</param>
  /// <param name="parameters">SqlParameterCollection输入参数</param>
  /// <returns>DataTable</returns>
  public static  DataTable dataTable(string procName,SqlParameterCollection parameters)
  {
   SqlDataAdapter da = new SqlDataAdapter();
   DataTable dt = new DataTable();
   try
   {
    openConnection();
    cmd.Parameters.Clear();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = procName;
    foreach(SqlParameter parameter in parameters)
    {
     SqlParameter p = (SqlParameter)parameter;
     cmd.Parameters.Add(p);
    }
    da.SelectCommand = cmd;
    da.Fill(dt);
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    closeConnection();
   }
   return dt;
  }
  #endregion

  #region 执行sql语句并返回DataView
  /// <summary>
  /// 执行sql语句并返回DataView
  /// </summary>
  /// <param name="strsql">传入的sql语句</param>
  /// <returns>DataView</returns>
  public static DataView dataView(string strsql)
  {
   SqlDataAdapter da = new SqlDataAdapter();
   DataSet ds = new DataSet();
   DataView dv = new DataView();
   try
   {
    openConnection();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = strsql;

    da.SelectCommand = cmd;
    da.Fill(ds);
    dv = ds.Tables[0].DefaultView;
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    closeConnection();
   }
   return dv;
  }
  #endregion

  #region 执行sql语句并返回引用的DataView
  /// <summary>
  /// 执行sql语句并返回引用的DataView
  /// </summary>
  /// <param name="strsql">传入的sql语句</param>
  /// <param name="dv">传入的引用的DataView</param>
  public static void dataView(string strsql,ref DataView dv)
  {
   DataSet ds = new DataSet();
   SqlDataAdapter da = new SqlDataAdapter();
   try
   {
    openConnection();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = strsql;
    da.SelectCommand = cmd;
    da.Fill(ds);

    dv = ds.Tables[0].DefaultView;
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    closeConnection();
   }
  }
  #endregion

 }
}