#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
}