中小系统.net DataAccess数据访问类

#region 引用对象
using system;
using system.xml ;
using system.data;
using system.data.sqlclient;
using system.web; 
#endregion
namespace sysclasslibrary
{
 /// <summary>
 /// dataaccess 的摘要说明。
 /// <author>wuchen</author>
 /// <date>2004-4-12</date>
 /// <email>[email protected]</email>
 /// <description>数据处理基类,调用方式：dataaccess.dataset((string)sqlstr);或者dataaccess.dataset((string)sqlstr,ref dataset ds); </description>
 /// </summary>
 public class dataaccess
 {

 #region  属性
   
  /// <summary>
  /// 是否必须关闭数据库连接
  /// </summary>
  public static bool mustcloseconnection
  {
   get
   {
    return _mustcloseconnection;
   }
   set
   {
    _mustcloseconnection=value;
   }
  }
  /// <summary>
  /// 连接字符串
  /// </summary>
  public static string connectionstring
  {
   get
   {
    if(_connectionstring ==string.empty)
     return sysconfig.connectionstring ;
    else
     return _connectionstring;
   }
   set
   {
    _connectionstring =value;
   }
  }
  /// <summary>
  /// 是否关闭数据库连接
  /// </summary>
  private static bool _mustcloseconnection = true; 
  private static string _connectionstring =string.empty ; 

  #endregion
 

  #region 类构造函数
  
  /// <summary>
  /// 构造函数
  /// </summary>
  public dataaccess()
  {
  }

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

  #endregion

     #region  method
  
  /// <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();
    }
    catch(sqlexception e)
    {
     new errorlog().savedataaccesserror(e);
    }
    finally
    {
     conn.close();
     comm.dispose();
    }
   }
   return i;
  }

  /// <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);
   }
  }

  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"></param>
  /// <param name="conn"></param>
  /// <param name="comm"></param>
  public static void executeporcedure(string procname,sqlparameter[] coll,sqlconnection conn,sqlcommand comm)
  {
   if(procname ==null || procname=="")
    throw new sqlnullexception();
   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)
   {
    new errorlog().savedataaccesserror(e);
   }
   finally
   {
    comm.parameters.clear();
    conn.close();
    comm.dispose();
   }
  }

  public static void executeporcedure(string procname,sqlparameter[] coll,sqlconnection conn,sqlcommand comm,ref dataset ds)
  {
   if(procname ==null || procname=="")
    throw new sqlnullexception();
   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)
   {
    new errorlog().savedataaccesserror(e);
   }
   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 sqlnullexception();
   
   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(); 
    }
    catch(sqlexception e)
    {
     new errorlog().savedataaccesserror(e);
    }
    finally
    {
     conn.close();
     comm.dispose();
    }
   }
   return obj;
  }

  /// <summary>
  /// 执行sql查询语句,同时进行事务处理
  /// </summary>
  /// <param name="sqlstr">传入的sql语句</param>
  public static void executesqlwithtransaction(string sqlstr)
  {
   if(sqlstr ==null || sqlstr =="")
    throw new sqlnullexception();
   
   using(sqlconnection conn=new sqlconnection(connectionstring))
   {
    //可以在事务中创建一个保存点，同时回滚到保存点
    sqltransaction trans ;
    trans=conn.begintransaction();
    sqlcommand comm=new sqlcommand();
    comm.connection =conn;
    comm.transaction =trans;
    try
    {
     conn.open();
     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 sqlnullexception();
   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 sqlnullexception();
   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);
    }
    catch(sqlexception e)
    {
     new errorlog().savedataaccesserror(e);
    }
    finally
    {
     conn.close();
    }
   }
   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)
  {
   if(sqlstr ==null || sqlstr =="")
    throw new sqlnullexception();
   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)
    {
     new errorlog().savedataaccesserror(e);
    }
    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 sqlnullexception();
   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);
    }
    catch(sqlexception e)
    {
     new errorlog().savedataaccesserror(e);
    }
    finally
    {
     conn.close();
    }
   }
  
   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)
  {
   if(sqlstr ==null || sqlstr =="")
    throw new sqlnullexception();
   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)
    {
     new errorlog().savedataaccesserror(e);
    }
    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 sqlnullexception();
   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);
    }
    catch(sqlexception e)
    {
     new errorlog().savedataaccesserror(e);
    }
    finally
    {
     conn.close();
    }
   }
   
   return datatable;
  }
  /// <summary>
  /// dataview
  /// </summary>
  /// <param name="sqlstr"></param>
  /// <returns></returns>
  public static dataview dataview(string sqlstr)
  {
   if(sqlstr ==null || sqlstr =="")
    throw new sqlnullexception();
   sqldataadapter da=new sqldataadapter();
   dataview dv=new dataview();
   dataset ds=new dataset();
   dataset(sqlstr,ref ds);
   dv=ds.tables[0].defaultview;
   return dv;
  }

  #endregion
 }

 #region  异常类，记录出错信息
 /// <summary>
 /// 异常类
 /// </summary>
 public class sqlnullexception:applicationexception
 {
  /// <summary>
  /// 构造函数
  /// </summary>
  public sqlnullexception(){
   new sqlnullexception("dataaccess类中静态成员 参数不能为空。可能是sqlstr =null");
  }

  /// <summary>
  /// 重载出错信息
  /// </summary>
  /// <param name="message"></param>
  public sqlnullexception(string message)
  {
   //保存出错信息
   try
   {
    //err.savedataaccesserror(message);
    httpcontext.current.response.write(message);
   }
   catch
   {
    throw;
   }
  }
  /// <summary>
  /// 重载出错信息
  /// </summary>
  /// <param name="e"></param>
  public sqlnullexception(sqlexception e)
  {
   //保存出错信息
   try
   {
    httpcontext.current.response.write(e.message);
    //err.savedataaccesserror(e);
   }
   catch
   {
    throw;
   }
  }

  /// <summary>
  /// 析构函数
  /// </summary>
  ~ sqlnullexception()
  {
      
  }
  private errorlog err=new errorlog();
 }
 #endregion

 #region errorlog 错误日志捕获
 /// <summary>
 /// errorlog 的摘要说明。
 /// </summary>
 public class errorlog
 {
  /// <summary>
  /// ctr
  /// </summary>
  public errorlog()
  {
   //
   // todo: 在此处添加构造函数逻辑
   //
  }

  /// <summary>
  /// 数据库访问出错日志
  /// </summary>
  /// <param name="e">错误信息 </param>
  public void savedataaccesserror(sqlexception e)
  {
   //生成的错误行号
//   int linenumber = e.linenumber ;
//   string message=  e.message;
//   int number =e.number;
//   string procedure=e.procedure ;
//   string source=e.source ;
//   
//   string errmessage ="linenumber："+linenumber.tostring() + " ---- procedure："+ procedure.tostring()   ;
//   string errsource =source ;
//   string errtargetsite ="错误号："+number ;
//   string url =httpcontext.current.request.urlreferrer.absolutepath  ;
//   string ip  = httpcontext.current.request.userhostaddress ;
//   try
//   {
//    sysclasslibrary.dataaccess.mustcloseconnection =true;
//    sysclasslibrary.dataaccess.executesql(string.format("insert into sys_errorlog(errmessage,errsource,errtargetsite,url,ip)values('{0}','{1}','{2}','{3}','{4}') ",errmessage,errsource,errtargetsite,url,ip));
//   }
//   catch
//   {
//   }
  }
  /// <summary>
  /// 数据库访问出错日志
  /// </summary>
  /// <param name="message">出错信息</param>
  public void savedataaccesserror(string message)
  {
   //生成的错误行号
   
//   string errmessage =message;
//   string errsource ="" ;
//   string errtargetsite ="";
//   string url =httpcontext.current.request.urlreferrer.absolutepath  ;
//   string ip  = httpcontext.current.request.userhostaddress ;
//   try
//   {
//    sysclasslibrary.dataaccess.mustcloseconnection =true;
//    sysclasslibrary.dataaccess.executesql(string.format("insert into sys_errorlog(errmessage,errsource,errtargetsite,url,ip)values('{0}','{1}','{2}','{3}','{4}') ",errmessage,errsource,errtargetsite,url,ip));
//   }
//   catch
//   {
//   }
  }
 }
 #endregion
}