ASP.NET 数据访问类 Seeko0 [原作] 供参考

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

namespace SysClassLibrary
{
 /// <summary>
 /// DataAccess 的摘要说明。
 /// <description>数据处理基类,调用方式:DataAccess.DataSet((string)sqlstr);或者DataAccess.DataSet((string)sqlstr,ref DataSet ds); </description>
 /// </summary>
 public class DataAccess
 {
  #region  属性
  protected static SqlConnection conn=new SqlConnection();
  protected static SqlCommand    comm=new SqlCommand();
  #endregion
  public DataAccess()
  {
   //init();
  }
  #region 内部函数  静态方法中不会执行DataAccess()构造函数

  /// <summary>
  /// 打开数据库连接
  /// </summary>
  private static void openConnection()
  {
   if (conn.State == ConnectionState.Closed)
   {
    //SysConfig.ConnectionString 为系统配置类中连接字符串,如:"server=localhost;database=databasename;uid=sa;pwd=;"

    conn.ConnectionString = SysConfig.ConnectionString ;
    comm.Connection =conn;
    try
    {
     conn.Open();
    }
    catch(Exception e)
    {
     throw new Exception(e.Message);
    }
   }
  }
  /// <summary>
  /// 关闭当前数据库连接
  /// </summary>
  private static void closeConnection()
  {
   if(conn.State == ConnectionState.Open)
    conn.Close();
   conn.Dispose();
   comm.Dispose();
  }
  #endregion
  /// <summary>
  /// 执行Sql查询语句
  /// </summary>
  /// <param name="sqlstr">传入的Sql语句</param>
  public static void ExecuteSql(string sqlstr)
  {
   try
   {
    openConnection();
    comm.CommandType =CommandType.Text ;
    comm.CommandText =sqlstr;
    comm.ExecuteNonQuery(); 
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    closeConnection();
   }
  }

  /// <summary>
  /// 执行存储过程
  /// </summary>
  /// <param name="procName">存储过程名</param>
  /// <param name="coll">SqlParameters 集合</param>
  public static void ExecutePorcedure(string procName,SqlParameter[] coll)
  {
   try
   {
    openConnection();
    for(int i=0;i<coll.Length;i++)
    {
     comm.Parameters .Add(coll[i]);
    }
    comm.CommandType=CommandType.StoredProcedure ;
    comm.CommandText =procName;
    comm.ExecuteNonQuery();
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    comm.Parameters.Clear();
    closeConnection();
   }
  }

  /// <summary>
  /// 执行存储过程并返回数据集
  /// </summary>
  /// <param name="procName">存储过程名称</param>
  /// <param name="coll">SqlParameter集合</param>
  /// <param name="ds">DataSet </param>
  public static void ExecutePorcedure(string procName,SqlParameter[] coll,ref DataSet ds)
  {
   try
   {
    SqlDataAdapter da=new SqlDataAdapter();
    openConnection();
    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(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    comm.Parameters.Clear();
    closeConnection();
   }
  }

  /// <summary>
  /// 执行Sql查询语句并返回第一行的第一条记录,返回值为object 使用时需要拆箱操作 -> Unbox
  /// </summary>
  /// <param name="sqlstr">传入的Sql语句</param>
  /// <returns>object 返回值 </returns>
  public static object ExecuteScalar(string sqlstr)
  {
   object obj=new object();
   try
   {
    openConnection();
    comm.CommandType =CommandType.Text ;
    comm.CommandText =sqlstr;
    obj=comm.ExecuteScalar(); 
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    closeConnection();
   }
   return obj;
  }

  /// <summary>
  /// 执行Sql查询语句,同时进行事务处理
  /// </summary>
  /// <param name="sqlstr">传入的Sql语句</param>
  public static void ExecuteSqlWithTransaction(string sqlstr)
  {
   SqlTransaction trans ;
   trans=conn.BeginTransaction();
   comm.Transaction =trans;
   try
   {
    openConnection();
    comm.CommandType =CommandType.Text ;
    comm.CommandText =sqlstr;
    comm.ExecuteNonQuery(); 
    trans.Commit();
   }
   catch
   {
    trans.Rollback();
   }
   finally
   {
    closeConnection();
   }
  }

  /// <summary>
  /// 返回指定Sql语句的SqlDataReader,请注意,在使用后请关闭本对象,同时将自动调用closeConnection()来关闭数据库连接
  /// 方法关闭数据库连接
  /// </summary>
  /// <param name="sqlstr">传入的Sql语句</param>
  /// <returns>SqlDataReader对象</returns>
  public static SqlDataReader dataReader(string sqlstr)
  {
   SqlDataReader dr=null;
   try
   {
    openConnection();
    comm.CommandText =sqlstr;
    comm.CommandType =CommandType.Text ;
    dr=comm.ExecuteReader(CommandBehavior.CloseConnection); 
   }
   catch
   {
    try
    {
     dr.Close();
     closeConnection();
    }
    catch
    {
    }
   }
   return dr;
  }
  /// <summary>
  /// 返回指定Sql语句的SqlDataReader,请注意,在使用后请关闭本对象,同时将自动调用closeConnection()来关闭数据库连接
  /// 方法关闭数据库连接
  /// </summary>
  /// <param name="sqlstr">传入的Sql语句</param>
  /// <param name="dr">传入的ref DataReader 对象</param>
  public static void dataReader(string sqlstr,ref SqlDataReader dr)
  {
   try
   {
    openConnection();
    comm.CommandText =sqlstr;
    comm.CommandType =CommandType.Text ;
    dr=comm.ExecuteReader(CommandBehavior.CloseConnection); 
   }
   catch
   {
    try
    {
     if(dr!=null && !dr.IsClosed)
      dr.Close();
    }
    catch
    {
    }
    finally
    {
     closeConnection();
    }
   }
  }


  /// <summary>
  /// 返回指定Sql语句的DataSet
  /// </summary>
  /// <param name="sqlstr">传入的Sql语句</param>
  /// <returns>DataSet</returns>
  public static DataSet dataSet(string sqlstr)
  {
   DataSet ds= new DataSet();
   SqlDataAdapter da=new SqlDataAdapter();
   try
   {
    openConnection();
    comm.CommandType =CommandType.Text ;
    comm.CommandText =sqlstr;
    da.SelectCommand =comm;
    da.Fill(ds);
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    closeConnection();
   }
   return ds;
  }

  /// <summary>
  /// 返回指定Sql语句的DataSet
  /// </summary>
  /// <param name="sqlstr">传入的Sql语句</param>
  /// <param name="ds">传入的引用DataSet对象</param>
  public static void dataSet(string sqlstr,ref DataSet ds)
  {
   SqlDataAdapter da=new SqlDataAdapter();
   try
   {
    openConnection();
    comm.CommandType =CommandType.Text ;
    comm.CommandText =sqlstr;
    da.SelectCommand =comm;
    da.Fill(ds);
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    closeConnection();
   }
  }
  /// <summary>
  /// 返回指定Sql语句的DataTable
  /// </summary>
  /// <param name="sqlstr">传入的Sql语句</param>
  /// <returns>DataTable</returns>
  public static DataTable dataTable(string sqlstr)
  {
   SqlDataAdapter da=new SqlDataAdapter();
   DataTable datatable=new DataTable();
   try
   {
    openConnection();
    comm.CommandType =CommandType.Text ;
    comm.CommandText =sqlstr;
    da.SelectCommand =comm;
    da.Fill(datatable);
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    closeConnection();
   }
   return datatable;
  }

  /// <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)
  {
   SqlDataAdapter da=new SqlDataAdapter();
   try
   {
    openConnection();
    comm.CommandType =CommandType.Text ;
    comm.CommandText =sqlstr;
    da.SelectCommand =comm;
    da.Fill(dt);
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    closeConnection();
   }
  }
  /// <summary>
  /// 执行带参数存储过程并返回数据集合
  /// </summary>
  /// <param name="procName">存储过程名称</param>
  /// <param name="parameters">SqlParameterCollection 输入参数</param>
  /// <returns></returns>
  public static DataTable dataTable(string procName,SqlParameterCollection parameters)
  { 
   SqlDataAdapter da=new SqlDataAdapter();
   DataTable datatable=new DataTable();
   try
   {
    openConnection();
    comm.Parameters.Clear();
    comm.CommandType=CommandType.StoredProcedure ;
    comm.CommandText =procName;
    foreach(SqlParameter para in parameters)
    {
     SqlParameter p=(SqlParameter)para;
     comm.Parameters.Add(p);
    }
    da.SelectCommand =comm;
    da.Fill(datatable);
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    closeConnection();
   }
   return datatable;
  }

  public static DataView dataView(string sqlstr)
  {
   SqlDataAdapter da=new SqlDataAdapter();
   DataView dv=new DataView();
   DataSet ds=new DataSet();
   try
   {
    openConnection();
    comm.CommandType=CommandType.Text;
    comm.CommandText =sqlstr;
    da.SelectCommand =comm;
    da.Fill(ds);
    dv=ds.Tables[0].DefaultView;
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    closeConnection();
   }
   return dv;
  }
 }


}

对该文的评论
ico_pencil.gifCSDN 网友 ( 2004-06-16)
现在还用这种东西吗?
ico_pencil.gifseeko0 ( 2004-06-16)
需要MS SqlHelper 的朋友请点此连接下载
http://25h.bigwww.com/download/dataaccessv2.rar
ico_pencil.gifseeko0 ( 2004-06-11)
hi yongjie37 
留个Email 我给你发一份,或者给我发个Email:
scwuchen@263.net
ico_pencil.gifYONGJIE37 ( 2004-06-10)
我的怎麼沒有 MS SQLHELPER,請問如何得到這個類
ico_pencil.gifseeko0 ( 2004-06-09)
以前测试过,如果不主动关闭连接,在master.processes中的连接数会增加。响应网络访问数据库资源宝贵的号召,采用一个过程中处理完数据后马上断开数据库连接,可能感觉上是不是很舒服,看了MS SqlHelper,感觉不是在一个层面的东东。
阅读更多

ASP.NET 数据访问类2

05-14

rn/// rn/// 返回指定Sql语句的SqlDataReader,请注意,在使用后请关闭本对象,同时将自动调用closeConnection()来关闭数据库连接 rn/// 方法关闭数据库连接 rn/// rn/// 传入的Sql语句 rn/// SqlDataReader对象 rnpublic static SqlDataReader dataReader(string sqlstr) rn rnSqlDataReader dr=null; rntry rn rnopenConnection(); rncomm.CommandText =sqlstr; rncomm.CommandType =CommandType.Text ; rndr=comm.ExecuteReader(CommandBehavior.CloseConnection); rn rncatch rn rntry rn rndr.Close(); rncloseConnection(); rn rncatch rn rn rn rnreturn dr; rn rn/// rn/// 返回指定Sql语句的SqlDataReader,请注意,在使用后请关闭本对象,同时将自动调用closeConnection()来关闭数据库连接 rn/// 方法关闭数据库连接 rn/// rn/// 传入的Sql语句 rn/// 传入的ref DataReader 对象 rnpublic static void dataReader(string sqlstr,ref SqlDataReader dr) rn rntry rn rnopenConnection(); rncomm.CommandText =sqlstr; rncomm.CommandType =CommandType.Text ; rndr=comm.ExecuteReader(CommandBehavior.CloseConnection); rn rncatch rn rntry rn rnif(dr!=null && !dr.IsClosed) rndr.Close(); rn rncatch rn rn rnfinally rn rncloseConnection(); rn rn rn rnrnrn/// rn/// 返回指定Sql语句的DataSet rn/// rn/// 传入的Sql语句 rn/// DataSet rnpublic static DataSet dataSet(string sqlstr) rn rnDataSet ds= new DataSet(); rnSqlDataAdapter da=new SqlDataAdapter(); rntry rn rnopenConnection(); rncomm.CommandType =CommandType.Text ; rncomm.CommandText =sqlstr; rnda.SelectCommand =comm; rnda.Fill(ds); rn rncatch(Exception e) rn rnthrow new Exception(e.Message); rn rnfinally rn rncloseConnection(); rn rnreturn ds; rn rnrn/// rn/// 返回指定Sql语句的DataSet rn/// rn/// 传入的Sql语句 rn/// 传入的引用DataSet对象 rnpublic static void dataSet(string sqlstr,ref DataSet ds) rn rnSqlDataAdapter da=new SqlDataAdapter(); rntry rn rnopenConnection(); rncomm.CommandType =CommandType.Text ; rncomm.CommandText =sqlstr; rnda.SelectCommand =comm; rnda.Fill(ds); rn rncatch(Exception e) rn rnthrow new Exception(e.Message); rn rnfinally rn rncloseConnection(); rn rn rn/// rn/// 返回指定Sql语句的DataTable rn/// rn/// 传入的Sql语句 rn/// DataTable rnpublic static DataTable dataTable(string sqlstr) rn rnSqlDataAdapter da=new SqlDataAdapter(); rnDataTable datatable=new DataTable(); rntry rn rnopenConnection(); rncomm.CommandType =CommandType.Text ; rncomm.CommandText =sqlstr; rnda.SelectCommand =comm; rnda.Fill(datatable); rn rncatch(Exception e) rn rnthrow new Exception(e.Message); rn rnfinally rn rncloseConnection(); rn rnreturn datatable; rn rnrn/// rn/// 执行指定Sql语句,同时给传入DataTable进行赋值 rn/// rn/// 传入的Sql语句 rn/// ref DataTable dt rnpublic static void dataTable(string sqlstr,ref DataTable dt) rn rnSqlDataAdapter da=new SqlDataAdapter(); rntry rn rnopenConnection(); rncomm.CommandType =CommandType.Text ; rncomm.CommandText =sqlstr; rnda.SelectCommand =comm; rnda.Fill(dt); rn rncatch(Exception e) rn rnthrow new Exception(e.Message); rn rnfinally rn rncloseConnection(); rn rn rn/// rn/// 执行带参数存储过程并返回数据集合 rn/// rn/// 存储过程名称 rn/// SqlParameterCollection 输入参数 rn/// rnpublic static DataTable dataTable(string procName,SqlParameterCollection parameters) rn rnSqlDataAdapter da=new SqlDataAdapter(); rnDataTable datatable=new DataTable(); rntry rn rnopenConnection(); rncomm.Parameters.Clear(); rncomm.CommandType=CommandType.StoredProcedure ; rncomm.CommandText =procName; rnforeach(SqlParameter para in parameters) rn rnSqlParameter p=(SqlParameter)para; rncomm.Parameters.Add(p); rn rnda.SelectCommand =comm; rnda.Fill(datatable); rn rncatch(Exception e) rn rnthrow new Exception(e.Message); rn rnfinally rn rncloseConnection(); rn rnreturn datatable; rn rnrnpublic static DataView dataView(string sqlstr) rn rnSqlDataAdapter da=new SqlDataAdapter(); rnDataView dv=new DataView(); rnDataSet ds=new DataSet(); rntry rn rnopenConnection(); rncomm.CommandType=CommandType.Text; rncomm.CommandText =sqlstr; rnda.SelectCommand =comm; rnda.Fill(ds); rndv=ds.Tables[0].DefaultView; rn rncatch(Exception e) rn rnthrow new Exception(e.Message); rn rnfinally rn rncloseConnection(); rn rnreturn dv; rn rn rn

换一批

ASP.NET 数据访问类1

05-14

using System; rnusing System.Data; rnusing System.Data.SqlClient; rnrnnamespace SysClassLibrary rn rn/// rn/// DataAccess 的摘要说明。 rn/// 数据处理基类,调用方式:DataAccess.DataSet((string)sqlstr);或者DataAccess.DataSet((string)sqlstr,ref DataSet ds); rn/// rnpublic class DataAccess rn rn#region 属性 rnprotected static SqlConnection conn=new SqlConnection(); rnprotected static SqlCommand comm=new SqlCommand(); rn#endregion rnpublic DataAccess() rn rn//init(); rn rn#region 内部函数 静态方法中不会执行DataAccess()构造函数 rnrn/// rn/// 打开数据库连接 rn/// rnprivate static void openConnection() rn rnif (conn.State == ConnectionState.Closed) rn rn//SysConfig.ConnectionString 为系统配置类中连接字符串,如:"server=localhost;database=databasename;uid=sa;pwd=;" rnrnconn.ConnectionString = SysConfig.ConnectionString ; rncomm.Connection =conn; rntry rn rnconn.Open(); rn rncatch(Exception e) rn rnthrow new Exception(e.Message); rn rn rn rn/// rn/// 关闭当前数据库连接 rn/// rnprivate static void closeConnection() rn rnif(conn.State == ConnectionState.Open) rnconn.Close(); rnconn.Dispose(); rncomm.Dispose(); rn rn#endregion rn/// rn/// 执行Sql查询语句 rn/// rn/// 传入的Sql语句 rnpublic static void ExecuteSql(string sqlstr) rn rntry rn rnopenConnection(); rncomm.CommandType =CommandType.Text ; rncomm.CommandText =sqlstr; rncomm.ExecuteNonQuery(); rn rncatch(Exception e) rn rnthrow new Exception(e.Message); rn rnfinally rn rncloseConnection(); rn rn rnrn/// rn/// 执行存储过程 rn/// rn/// 存储过程名 rn/// SqlParameters 集合 rnpublic static void ExecutePorcedure(string procName,SqlParameter[] coll) rn rntry rn rnopenConnection(); rnfor(int i=0;i rn/// 执行存储过程并返回数据集 rn/// rn/// 存储过程名称 rn/// SqlParameter集合 rn/// DataSet rnpublic static void ExecutePorcedure(string procName,SqlParameter[] coll,ref DataSet ds) rn rntry rn rnSqlDataAdapter da=new SqlDataAdapter(); rnopenConnection(); rnfor(int i=0;i rn/// 执行Sql查询语句并返回第一行的第一条记录,返回值为object 使用时需要拆箱操作 -> Unbox rn/// rn/// 传入的Sql语句 rn/// object 返回值 rnpublic static object ExecuteScalar(string sqlstr) rn rnobject obj=new object(); rntry rn rnopenConnection(); rncomm.CommandType =CommandType.Text ; rncomm.CommandText =sqlstr; rnobj=comm.ExecuteScalar(); rn rncatch(Exception e) rn rnthrow new Exception(e.Message); rn rnfinally rn rncloseConnection(); rn rnreturn obj; rn rnrn/// rn/// 执行Sql查询语句,同时进行事务处理 rn/// rn/// 传入的Sql语句 rnpublic static void ExecuteSqlWithTransaction(string sqlstr) rn rnSqlTransaction trans ; rntrans=conn.BeginTransaction(); rncomm.Transaction =trans; rntry rn rnopenConnection(); rncomm.CommandType =CommandType.Text ; rncomm.CommandText =sqlstr; rncomm.ExecuteNonQuery(); rntrans.Commit(); rn rncatch rn rntrans.Rollback(); rn rnfinally rn rncloseConnection(); rn rn rn

ASP.net中的数据访问类的设计问题讨论

09-07

静态方法和单件模式具体区别在哪里 ? rnhttp://community.csdn.net/Expert/topic/3300/3300427.xml?temp=.4662287rn看了上面那位大哥的帖子以后,我也有点疑问 。rn对于我的数据访问类如下 :rnpublic class DBConstrn rn public DBConst()rn rn //rn // TODO: 在此处添加构造函数逻辑rn //rn rnrn public DataView select_const(string strComm)rn rn SqlConnection myConn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["connectionString"]);rn SqlDataAdapter myCommand=new SqlDataAdapter(strComm ,myConn);rn DataSet ds = new DataSet();rn myCommand.Fill(ds, "const");rn return ds.Tables["const"].DefaultView;rn rn rn对于这样的类 ,设计成工具类是不是更好 ?因为它并没有涉及到类的数据成员 ,那么设计成静态方法不是更好吗 ? 设计成静态方法就不用实例化 DBConst 类就可以调用数据访问方法select_const ( ) 了 。rnrn如果把上面的类不设计成静态方法 ,而是设计成单件模式 ,和上面的设计方法比较 ,那一种更好 ?rnrnrn另:rn对于 SqlConnection myConn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["connectionString"]);rn这样的数据库连接操作 ,ADO.net 是自动的把连接放入到连接缓冲池中的 ,那么我如果连续执行select / update 等操作 ,每一个操作都要连接一次相同数据库 ,那么对于已经放到连接缓冲池中的连接 ,我要耗费的时间和资源还多吗 ?是不是要注意 ? 是不是最好把所有的select / update 等操作放到数据库存储过程中呢 ?(不是太想放到数据库存储过程中)

没有更多推荐了,返回首页