个人使用的c#数据类。

 
using System;
using System.Xml ;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Collections;

namespace DataAccess
{
 /// <summary>
 /// DataAccess 的摘要说明。
 /// </summary>
 public class DataAccess
 {
  public DataAccess()
  {
  }

  private static bool _mustCloseConnection = true;
  private static string _connectionString =string.Empty ;

  /// <summary>
  /// 是否必须关闭数据库连接
  /// </summary>
  public static bool mustCloseConnection
  {
   get
   {
    return _mustCloseConnection;
   }
   set
   {
    _mustCloseConnection=value;
   }
  }

  /// <summary>
  /// 连接字符串
  /// </summary>
  public static string connectionString
  {
   get
   {
    if(_connectionString ==string.Empty)
     return System.Configuration.ConfigurationSettings.AppSettings.Get("Connstring");
    else
     return _connectionString;
   }
   set
   {
    _connectionString =value;
   }
  }

  /// <summary>
  /// 析构函数，释放相应的对象
  /// </summary>
  ~DataAccess()
  {
  }

  /// <summary>
  /// 执行Sql查询语句
  /// </summary>
  /// <param name="sqlstr">传入的Sql语句</param>
  ///<returns >i返回受影响的行数 </returns>
  public static int ExecuteSql(string sqlstr)
  {
   int i=0;
   using (SqlConnection conn =new SqlConnection(connectionString))
   {
    SqlCommand comm=new SqlCommand();
    comm.Connection =conn;
    comm.CommandType =CommandType.Text ;
    comm.CommandText =sqlstr;
    try
    {
     conn.Open();
     i=comm.ExecuteNonQuery();
     return i;
    }
    catch(SqlException e)
    {
     throw new Exception(e.Message);
    }
    finally
    {
     conn.Close();
     comm.Dispose();
    }
   }
   
  }

  /// <summary>
  /// 执行存储过程
  /// </summary>
  /// <param name="procName">存储过程名</param>
  /// <param name="coll">SqlParameters 集合</param>
  public static void ExecutePorcedure(string procName,SqlParameter[] coll)
  {
   using (SqlConnection conn =new SqlConnection(connectionString))
   {
    SqlCommand comm=new SqlCommand();
    comm.Connection =conn;
    comm.CommandType =CommandType.StoredProcedure ;
    ExecutePorcedure(procName,coll,conn,comm);
   }
  }

  /// <summary>
  /// 执行存储过程，返回dataset
  /// </summary>
  /// <param name="procName">存储过程名</param>
  /// <param name="coll">SqlParameters 集合</param>
  /// <param name="ds">返回dataset</param>
  public static void ExecutePorcedure(string procName,SqlParameter[] coll,ref DataSet ds)
  {
   using (SqlConnection conn =new SqlConnection(connectionString))
   {
    SqlCommand comm=new SqlCommand();
    comm.Connection =conn;
    comm.CommandType =CommandType.StoredProcedure ;
    ExecutePorcedure(procName,coll,conn,comm,ref ds);
   }
  }

  /// <summary>
  /// 执行存储过程类
  /// </summary>
  /// <param name="procName">存储过程名</param>
  /// <param name="coll">SqlParameters 集合</param>
  /// <param name="conn">数据库连接</param>
  /// <param name="comm">sql执行SqlCommand</param>
  public static void ExecutePorcedure(string procName,SqlParameter[] coll,SqlConnection conn,SqlCommand comm)
  {
   if(procName ==null || procName=="")
    throw new  Exception();
   try
   {
    conn.Open();
    for(int i=0;i<coll.Length;i++)
    {
     comm.Parameters .Add(coll[i]);
    }
    comm.CommandType=CommandType.StoredProcedure ;
    comm.CommandText =procName;
    comm.ExecuteNonQuery();
   }
   catch(SqlException e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    comm.Parameters.Clear();
    conn.Close();
    comm.Dispose();
   }
  }

  /// <summary>
  /// 执行存储过程类
  /// </summary>
  /// <param name="procName">存储过程名</param>
  /// <param name="coll">SqlParameters 集合</param>
  /// <param name="conn">数据库连接</param>
  /// <param name="comm">sql执行SqlCommand</param>
  /// <param name="ds">返回dataset</param>
  public static void ExecutePorcedure(string procName,SqlParameter[] coll,SqlConnection conn,SqlCommand comm,ref DataSet ds)
  {
   if(procName ==null || procName=="")
    throw new Exception();
   try
   {
    SqlDataAdapter da =new SqlDataAdapter();
    conn.Open();
    for(int i=0;i<coll.Length;i++)
    {
     comm.Parameters .Add(coll[i]);
    }
    comm.CommandType=CommandType.StoredProcedure ;
    comm.CommandText =procName;
    da.SelectCommand = comm;
    da.Fill(ds);
   }
   catch(SqlException e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    comm.Parameters.Clear();
    conn.Close();
    comm.Dispose();
   }
  }

  /// <summary>
  /// 执行Sql查询语句并返回第一行的第一条记录,返回值为object 使用时需要拆箱操作 -> Unbox
  /// </summary>
  /// <param name="sqlstr">传入的Sql语句</param>
  /// <returns>object 返回值 </returns>
  public static object ExecuteScalar(string sqlstr)
  {
   if(sqlstr ==null || sqlstr =="")
    throw new Exception();

   object obj=new object();
   using (SqlConnection conn =new SqlConnection(connectionString))
   {
    SqlCommand comm=new SqlCommand();
    comm.Connection =conn;
    comm.CommandType =CommandType.Text;
    try
    {
     conn.Open();
     comm.CommandText =sqlstr;
     obj=comm.ExecuteScalar();
     return obj;
    }
    catch(SqlException e)
    {
     throw new Exception(e.Message);
    }
    finally
    {
     conn.Close();
     comm.Dispose();
    }
   }
   
  }

  /// <summary>
  /// 执行Sql查询语句,同时进行事务处理
  /// </summary>
  /// <param name="sqlstr">传入的Sql语句</param>
  public static void ExecuteSqlWithTransaction(string sqlstr)
  {
   if(sqlstr ==null || sqlstr =="")
    throw new Exception();
  
   using(SqlConnection conn=new SqlConnection(connectionString))
   {
    //可以在事务中创建一个保存点，同时回滚到保存点
    SqlTransaction trans ;
    conn.Open();
    trans=conn.BeginTransaction();
    SqlCommand comm=new SqlCommand();
    comm.Connection =conn;
    comm.Transaction =trans;
    try
    {
     comm.CommandType =CommandType.Text ;
     comm.CommandText =sqlstr;
     comm.ExecuteNonQuery();
     trans.Commit();
    }
    catch
    {
     trans.Rollback();
    }
    finally
    {
     trans.Dispose();
    
     conn.Close();
     comm.Dispose();
    }
   }
  }

  /// <summary>
  /// 返回指定Sql语句的SqlDataReader，请注意，在使用后请关闭本对象，同时将自动调用closeConnection()来关闭数据库连接
  /// 方法关闭数据库连接
  /// </summary>
  /// <param name="sqlstr">传入的Sql语句</param>
  /// <returns>SqlDataReader对象</returns>
  public static SqlDataReader dataReader(string sqlstr)
  {
   SqlDataReader _dataReader =null ;
   dataReader(sqlstr,ref _dataReader);
   return _dataReader;
  }

  /// <summary>
  /// 返回指定Sql语句的SqlDataReader，请注意，在使用后请关闭本对象，同时将自动调用closeConnection()来关闭数据库连接
  /// 方法关闭数据库连接
  /// </summary>
  /// <param name="sqlstr">传入的Sql语句</param>
  /// <param name="dr">传入的ref DataReader 对象</param>
  public static void dataReader(string sqlstr,ref SqlDataReader _dataReader)
  {
   if(sqlstr ==null || sqlstr =="")
    throw new Exception();
   try
   {
    SqlConnection conn=new SqlConnection(connectionString);
    SqlCommand comm=new SqlCommand();
    comm.Connection =conn;
    comm.CommandText =sqlstr;
    comm.CommandType =CommandType.Text ;
    conn.Open();
    if(_mustCloseConnection)
    {
     _dataReader=comm.ExecuteReader(CommandBehavior.CloseConnection);
    }
    else
    {
     _dataReader= comm.ExecuteReader();
    }
   }
   catch(SqlException e)
   {
    _dataReader =null;
    //输出错误原因
    throw e;
   }
  }

  /// <summary>
  /// 返回指定Sql语句的DataSet
  /// </summary>
  /// <param name="sqlstr">传入的Sql语句</param>
  /// <returns>DataSet</returns>
  public static DataSet dataSet(string sqlstr)
  {
   if(sqlstr ==null || sqlstr =="")
    throw new Exception();
   DataSet ds= new DataSet();
   SqlDataAdapter da=new SqlDataAdapter();
   using (SqlConnection conn=new SqlConnection(connectionString))
   {
    SqlCommand comm=new SqlCommand();
    comm.Connection =conn;
    try
    {
     conn.Open();
     comm.CommandType =CommandType.Text ;
     comm.CommandText =sqlstr;
     da.SelectCommand =comm;
     da.Fill(ds);
     return ds;
    }
    catch(SqlException e)
    {
     throw new Exception(e.Message);
    }
    finally
    {
     conn.Close();
    }
   }
   
  }

  /// <summary>
  /// 返回指定Sql语句的DataSet
  /// </summary>
  /// <param name="sqlstr">传入的Sql语句</param>
  /// <param name="ds">传入的引用DataSet对象</param>
  public static void dataSet(string sqlstr,ref DataSet ds)
  {
   if(sqlstr ==null || sqlstr =="")
    throw new Exception();
   using (SqlConnection conn=new SqlConnection(connectionString))
   {
    SqlDataAdapter da=new SqlDataAdapter();
    SqlCommand comm=new SqlCommand();
    comm.Connection =conn;
    try
    {
     conn.Open();
     comm.CommandType =CommandType.Text ;
     comm.CommandText =sqlstr;
     da.SelectCommand =comm;
     da.Fill(ds);
    }
    catch(SqlException e)
    {
     throw new Exception(e.Message);
    }
    finally
    {
     conn.Close();
    }
   }
  }

  /// <summary>
  /// 返回指定Sql语句的DataTable
  /// </summary>
  /// <param name="sqlstr">传入的Sql语句</param>
  /// <returns>DataTable</returns>
  public static DataTable dataTable(string sqlstr)
  {
   if(sqlstr ==null || sqlstr =="")
    throw new Exception();
   SqlDataAdapter da=new SqlDataAdapter();
   DataTable datatable=new DataTable();
   using (SqlConnection conn=new SqlConnection(connectionString))
   {
    SqlCommand comm=new SqlCommand();
    comm.Connection =conn;
    try
    {
     conn.Open();
     comm.CommandType =CommandType.Text ;
     comm.CommandText =sqlstr;
     da.SelectCommand =comm;
     da.Fill(datatable);
     return datatable;
    }
    catch(SqlException e)
    {
     throw new Exception(e.ToString());
    }
    finally
    {
     conn.Close();
    }
   } 
  }

  /// <summary>
  /// 执行指定Sql语句,同时给传入DataTable进行赋值
  /// </summary>
  /// <param name="sqlstr">传入的Sql语句</param>
  /// <param name="dt">ref DataTable dt </param>
  public static void dataTable(string sqlstr,ref DataTable dt)
  {
   if(sqlstr ==null || sqlstr =="")
    throw new Exception();
   if(dt ==null)
    dt=new DataTable();
   SqlDataAdapter da=new SqlDataAdapter();
   using (SqlConnection conn=new SqlConnection(connectionString))
   {
    SqlCommand comm=new SqlCommand();
    comm.Connection =conn;
    try
    {
     conn.Open();
     comm.CommandType =CommandType.Text ;
     comm.CommandText =sqlstr;
     da.SelectCommand =comm;
     da.Fill(dt);
    }
    catch(SqlException e)
    {
     throw new Exception(e.Message);
    }
    finally
    {
     conn.Close();
    }
   
   }
  }

  /// <summary>
  /// 执行带参数存储过程并返回数据集合
  /// </summary>
  /// <param name="procName">存储过程名称</param>
  /// <param name="parameters">SqlParameterCollection 输入参数</param>
  /// <returns></returns>
  public static DataTable dataTable(string procName,SqlParameterCollection parameters)
  {
   if(procName ==null || procName =="")
    throw new Exception();
   SqlDataAdapter da=new SqlDataAdapter();
   DataTable datatable=new DataTable();
   using (SqlConnection conn=new SqlConnection(connectionString))
   {
    SqlCommand comm=new SqlCommand();
    comm.Connection =conn;
    try
    {
     comm.Parameters.Clear();
     comm.CommandType=CommandType.StoredProcedure ;
     comm.CommandText =procName;
     foreach(SqlParameter para in parameters)
     {
      SqlParameter p=(SqlParameter)para;
      comm.Parameters.Add(p);
     }
     conn.Open();

     da.SelectCommand =comm;
     da.Fill(datatable);
     return datatable;
    }
    catch(SqlException e)
    {
     throw new Exception(e.Message);
    }
    finally
    {
     conn.Close();
    }
   }
  
   
  }

  /// <summary>
  /// 执行sql语句，返回DataView
  /// </summary>
  /// <param name="sqlstr">传入的Sql语句</param>
  /// <returns>返回DefaultView</returns>
  public static DataView dataView(string sqlstr)
  {
   if(sqlstr ==null || sqlstr =="")
    throw new Exception();
   SqlDataAdapter da=new SqlDataAdapter();
   DataView dv=new DataView();
   DataSet ds=new DataSet();
   dataSet(sqlstr,ref ds);
   dv=ds.Tables[0].DefaultView;
   return dv;
  }

     }
}