/*****************************************************
 * 文 件 名：dbobject.cs
 * 功能描述：定義數據層基類。
 * 創 建 人：夏春濤 [email protected] qq:23106676
 * 創建時間：2004-08-11 11：05
 *****************************************************/

using system;
using system.data;
using system.data.sqlclient;
using system.configuration;

namespace xd.xlb.jbc.jbinfo.webmodules.data
{
 /// <summary>
 /// 數據層基類，提供對底層數據的基本操作
 /// </summary>
 public class dbobject
 {  
  private sqlconnection connection;
  
  #region 構造函數
  /// <summary>
  /// 構造函數，初始化數據連接對象
  /// </summary>
  public dbobject()
  {
   string connectionstring = configurationsettings.appsettings.get("connectionstring");//從web.config中取得的連接字符串
   connection = new sqlconnection(connectionstring);
  }

  /// <summary><table >
  /// <tr><td><b>功能描述</b>：構造函數，根據指定的數據連接字符串，初始化數據連接對象</td></tr>
  /// <tr><td><b>創 建 人</b>：夏春濤 </td></tr>
  /// <tr><td><b>創建時間</b>：2005-05-28 </td></tr>
  /// </table></summary>
  /// <param name="newconnectionstring">數據連接字符串</param>
  public dbobject( string newconnectionstring )
  {
   string connectionstring = newconnectionstring;
   connection = new sqlconnection( connectionstring );
  }
  #endregion

  /// <summary>
  /// 數據連接對象（只讀）
  /// </summary>  
  public sqlconnection connection
  {
   get
   {
    return connection;
   }
   set
   {
    connection = value;
   }
  }  

  //-----------------------------------------------------------------------------------------
  //以下是從《asp.net web站點高級編程》中copy的（夏春濤）------------------------------------
  //-----------------------------------------------------------------------------------------
  /// <summary>
  /// 創建一個sqlcommand對象，用于獲取存儲過程的返回值
  /// </summary>
  /// <param name="storedprocname">存儲過程名稱</param>
  /// <param name="parameters">存儲過程的參數對象列表（數組）</param>
  /// <returns>sqlcommand對象</returns>
  private sqlcommand buildintcommand(string storedprocname, idataparameter[] parameters)
  {
   sqlcommand command = buildquerycommand( storedprocname, parameters );   

   command.parameters.add( new sqlparameter ( "returnvalue",
    sqldbtype.int,
    4, /* size */
    parameterdirection.returnvalue,
    false, /* is nullable */
    0, /* byte precision */
    0, /* byte scale */
    string.empty,
    datarowversion.default,
    null ));

   return command;
  }

  /// <summary>
  /// 創建一個sqlcommand對象，用于生成sqldatareader
  /// </summary>
  /// <param name="storedprocname">存儲過程名稱</param>
  /// <param name="parameters">存儲過程的參數對象列表（數組）</param>
  /// <returns>sqlcommand對象</returns>
  private sqlcommand buildquerycommand(string storedprocname, idataparameter[] parameters)
  {
   sqlcommand command = new sqlcommand( storedprocname, connection );
   command.commandtype = commandtype.storedprocedure;

   foreach (sqlparameter parameter in parameters)
   {
    command.parameters.add( parameter );
   }

   return command;

  }

  /// <summary>
  /// 運行存儲過程，獲取影響數，返回存儲過程運行結果
  /// </summary>
  /// <param name="storedprocname">存儲過程名稱</param>
  /// <param name="parameters">存儲過程的參數對象列表（數組）</param>
  /// <param name="rowsaffected">出參：執行存儲過程所影響的記錄行數</param>
  /// <returns>存儲過程的運行結果</returns>
  public object runprocedure(string storedprocname, idataparameter[] parameters, out int rowsaffected )
  {
   object result;

   if(connection.state.tostring() == "closed")
    connection.open();
   sqlcommand command = buildintcommand( storedprocname, parameters );
   rowsaffected = command.executenonquery();
   result = command.parameters["returnvalue"].value;
   connection.close();
   return result;
  }

  /// <summary>
  /// 運行存儲過程，返回產生的sqldatareader對象
  /// </summary>
  /// <param name="storedprocname">存儲過程名稱</param>
  /// <param name="parameters">存儲過程的參數對象列表（數組）</param>
  /// <returns>sqldatareader對象</returns>
  public sqldatareader runprocedure(string storedprocname, idataparameter[] parameters )
  {
   sqldatareader returnreader;

   connection.open();
   sqlcommand command = buildquerycommand( storedprocname, parameters );
   command.commandtype = commandtype.storedprocedure;

   returnreader = command.executereader();
   //connection.close();
   return returnreader;
  }

  /// <summary>
  /// 運行存儲過程，創建一個dataset對象，
  /// 將運行結果存入指定的datatable中，返回dataset對象
  /// </summary>
  /// <param name="storedprocname">存儲過程名稱</param>
  /// <param name="parameters">存儲過程的參數對象列表（數組）</param>
  /// <param name="tablename">數據表名稱</param>
  /// <returns>dataset對象</returns>
  public dataset runprocedure(string storedprocname, idataparameter[] parameters, string tablename )
  {
   dataset dataset = new dataset();
   connection.open();
   sqldataadapter sqlda = new sqldataadapter();
   sqlda.selectcommand = buildquerycommand( storedprocname, parameters );
   sqlda.fill( dataset, tablename );
   connection.close();

   return dataset;
  }

  /// <summary>
  /// 運行存儲過程，將運行結果存入已有dataset對象的指定表中，無返回值
  /// </summary>  
  /// <param name="storedprocname">存儲過程名稱</param>
  /// <param name="parameters">存儲過程的參數對象列表（數組）</param>
  /// <param name="dataset">dataset對象</param>
  /// <param name="tablename">數據表名稱</param>
  public void runprocedure(string storedprocname, idataparameter[] parameters, dataset dataset, string tablename )
  {
   connection.open();
   sqldataadapter sqlda = new sqldataadapter();
   sqlda.selectcommand = buildintcommand( storedprocname, parameters );
   sqlda.fill( dataset, tablename );
   connection.close();   
  }

  //-----------------------------------------------------------------------------------------
  //以下是自建的（夏春濤）-------------------------------------------------------------------
  //-----------------------------------------------------------------------------------------
  /// <summary>
  /// 運行與寫數據庫相關的sql語句，返回影響行數**********************************************
  /// </summary>  
  /// <param name="sqlstring">sql語句</param>
  /// <returns>影響行數</returns>
  public int exenonquery(string sqlstring)
  {
   int rowaffected;
   if(connection.state.tostring() == "closed")
    connection.open();
   sqlcommand command = new sqlcommand( sqlstring, connection );
   rowaffected = command.executenonquery();
   //connection.close();
 
   return rowaffected;

  }

  /// <summary>
  /// 運行sql語句，返回sqldatareader對象
  /// </summary>
  /// <param name="sqlstring">sql語句</param>
  /// <returns>sqldatareader對象</returns>
  public sqldatareader exesqlstring(string sqlstring)
  {
   sqldatareader returnreader;

   if(connection.state.tostring() == "closed")
    connection.open();
   sqlcommand command = new sqlcommand( sqlstring, connection );
   returnreader = command.executereader();
   //connection.close();

   return returnreader;
  }

  /// <summary>
  /// 運行sql語句，返回dataset對象
  /// </summary>
  /// <param name="string">sql語句</param>
  /// <param name="tablename">數據表名稱</param>
  /// <returns>dataset對象</returns>
  public dataset exesqlstring(string sqlstring, string tablename )
  {
   dataset dataset = new dataset();
   if (connection.state.tostring() == "closed")
                connection.open();
   sqldataadapter sqlda = new sqldataadapter();
   sqlda.selectcommand = new sqlcommand( sqlstring, connection );
   sqlda.fill( dataset, tablename );
   connection.close();

   return dataset;
  }

  /// <summary>
  /// 運行sql語句，將運行結果存入已有dataset對象的指定表中，無返回值
  /// </summary>  
  /// <param name="sqlstring">sql語句</param>
  /// <param name="dataset">dataset對象</param>
  /// <param name="tablename">數據表名稱</param>
  public void exesqlstring(string sqlstring, dataset dataset, string tablename )
  {
   if (connection.state.tostring() == "closed")
    connection.open();
   sqldataadapter sqlda = new sqldataadapter();
   sqlda.selectcommand = new sqlcommand( sqlstring, connection );
   sqlda.fill( dataset, tablename );
   connection.close();  
  }

  
  /// <summary>
  /// 運行sql語句，返回查詢結果的第一行的第一列，忽略其它行或列
  /// </summary>  
  /// <param name="sqlstring">sql語句</param>
  /// <returns>影響行數</returns>
  public object exescalar(string sqlstring)
  {
   object returnscalar;
   if (connection.state.tostring() == "closed")
    connection.open();
   sqlcommand command = new sqlcommand( sqlstring, connection );
   returnscalar = command.executescalar();
   //connection.close();
   
   return returnscalar;
  }
  
  ~dbobject()
  {
   if(connection.state.tostring() == "open")
                connection.close();
   connection.dispose();
  }

 }
}