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

namespace dataproxy
{
 /// <summary>
 /// 說明:  本類主要實現對數據庫的操作(查詢|sp)
 /// 建立者:  黃宗銀
 /// 建立時間: 2004-12-4
 /// </summary>
 public class dataproxy
 {
  #region 讀取數據
  /// <summary>
  /// 從數據庫查詢數據
  /// </summary>
  /// <param name="rowscount">返回最 top 的記錄數</param>
  /// <param name="columns">查詢列名</param>
  /// <param name="target">查詢目標</param>
  /// <param name="condition">查詢條件</param>
  /// <param name="orderby">排序</param>
  /// <param name="connect">連接字符串</param>
  /// <param name="ex">異常信息</param>
  /// <returns>查詢結果</returns>
  public static dataset getdbdata( int rowscount, string columns, string target, string condition, string orderby, string connect, ref string ex )
  {
   ex = string.empty;
   sqlconnection sqlconnection = new sqlconnection( connect );
   try
   {
    string[] arrcolumns = columns.split( ',' );
    string strquery = "select top " + rowscount + " " + ((arrcolumns[0].trim() == "*") ? " *" : " [" + arrcolumns[0].trim() + "]");
    for( int i = 1; i < arrcolumns.length; i++ )
    {
     strquery += ", [" + arrcolumns[i].trim() + "]";
    }
    strquery += " from [" + target + "]";
    
    if( condition != null && condition.trim() != string.empty )
    {
     strquery += " where " + condition;
    }

    if( orderby != null && orderby.trim() != string.empty )
    {
     strquery += " order by " + orderby;
    }

    sqldataadapter sqldataadapter = new sqldataadapter( strquery, sqlconnection );

    dataset ds = new dataset();
    sqldataadapter.fill( ds );

    sqlconnection.close();
    return ds;
   }
   catch( sqlexception ex )
   {
    sqlconnection.close();
    ex = ex.message;
    return null;
   }
  }

  /// <summary>
  /// 從數據庫查詢數據
  /// </summary>
  /// <param name="columns">查詢列名</param>
  /// <param name="target">查詢目標</param>
  /// <param name="condition">查詢條件</param>
  /// <param name="orderby">排序</param>
  /// <param name="connect">連接字符串</param>
  /// <param name="ex">異常信息</param>
  /// <returns>查詢結果</returns>
  public static dataset getdbdata( string columns, string target, string condition, string orderby, string connect, ref string ex )
  {
   ex = string.empty;
   sqlconnection sqlconnection = new sqlconnection( connect );
   try
   {
    string[] arrcolumns = columns.split( ',' );
    string strquery = "select";
    int nstart = 0;
    if( arrcolumns[0].trim() == "*" )
    {
     strquery += " *,";
     nstart = 1;
    }
    for( int i = nstart; i < arrcolumns.length; i++ )
    {
     string[] arrcolumn = arrcolumns[i].trim().split( ' ' );
     strquery += " [" + arrcolumn[0].trim() + "]";
     if( arrcolumn.length > 1 )
     {
      strquery += " [" + arrcolumn[1].trim() + "]";
     }
     strquery += ",";
    }
    strquery = strquery.substring( 0, strquery.length - 1 ) + " from [" + target + "]";

    if( condition != null && condition.trim() != string.empty )
    {
     strquery += " where " + condition;
    }

    if( orderby != null && orderby.trim() != string.empty )
    {
     strquery += " order by " + orderby;
    }

    sqldataadapter sqldataadapter = new sqldataadapter( strquery, sqlconnection );

    dataset ds = new dataset();
    sqldataadapter.fill( ds );

    sqlconnection.close();
    return ds;
   }
   catch( sqlexception ex )
   {
    sqlconnection.close();
    ex = ex.message;
    return null;
   }
  }

  /// <summary>
  /// 從數據庫查詢數據
  /// </summary>
  /// <param name="columns">查詢列名</param>
  /// <param name="target">查詢目標</param>
  /// <param name="condition">查詢條件</param>
  /// <param name="connect">連接字符串</param>
  /// <param name="ex">異常信息</param>
  /// <returns>查詢結果</returns>
  public static dataset getdbdata( string columns, string target, string condition, string connect, ref string ex )
  {
   ex = string.empty;

   dataset ds = getdbdata( columns, target, condition, null, connect, ref ex );

   if( ex != string.empty )
   {
    return null;
   }

   return ds;
  }

  /// <summary>
  /// 從數據庫查詢數據
  /// </summary>
  /// <param name="columns">查詢列名</param>
  /// <param name="target">查詢目標</param>
  /// <param name="pkcolumn">主鍵列名</param>
  /// <param name="pkvalue">主鍵值</param>
  /// <param name="q">是否加引號</param>
  /// <param name="connect">連接字符串</param>
  /// <param name="ex">異常信息</param>
  /// <returns>返回查詢結果</returns>
  public static datatable getdbdata( string columns, string target, string pkcolumn, string pkvalue, bool q, string connect, ref string ex )
  {
   ex = string.empty;
   string strcondition = null;

   if( pkcolumn != null )
   {
    strcondition = "[" + pkcolumn + "] = ";
    if( q )
    {
     strcondition += "'" + pkvalue + "'";
    }
    else
    {
     strcondition += pkvalue;
    }
   }

   dataset ds = getdbdata( columns, target, strcondition, connect, ref ex );

   if( ex != string.empty )
   {
    return null;
   }

   return ds.tables[0];
  }
  #endregion

  #region 執行存儲過程
  /// <summary>
  /// 執行某個存儲過程通過參數返回值
  /// </summary>
  /// <param name="p">存儲過程名</param>
  /// <param name="xsd">數據集對象</param>
  /// <param name="connect">連接字符串</param>
  /// <param name="ex">異常信息</param>
  /// <returns>執行結果</returns>
  public static void execparam( string p, dataset xsd, string connect, ref string ex )
  {
   sqlconnection sqlconnection = new sqlconnection( connect );

   try
   {
    sqlcommand sqlcommand = new sqlcommand( p, sqlconnection );
    sqlcommand.commandtype = commandtype.storedprocedure;

    if( xsd.tables["in"] != null )
    {
     foreach( datacolumn dc in xsd.tables["in"].columns )
     {
      sqlcommand.parameters.add( "@" + dc.columnname, dc.datatype );
      sqlcommand.parameters["@" + dc.columnname].value = dc.table.rows[0][dc.columnname];
     }
    }

    if( xsd.tables["out"] != null )
    {
     foreach( datacolumn dc in xsd.tables["out"].columns )
     {
      sqlparameter sqlparameter = new sqlparameter( "@" + dc.columnname, dc.datatype );
      sqlparameter.direction = parameterdirection.inputoutput;
      sqlparameter.value = system.dbnull.value;
      if( xsd.tables["out"].rows.count > 0 )
      {
       sqlparameter.value = dc.table.rows[0][dc.columnname];
      }
      sqlcommand.parameters.add( sqlparameter );
     }
    }

    sqlconnection.open();
    sqlcommand.executenonquery();

    if( xsd.tables["out"] != null )
    {
     datarow dr = xsd.tables["out"].newrow();
     xsd.tables["out"].rows.insertat( dr, 0 );
     for( int i = 0; i < xsd.tables["out"].columns.count; i++ )
     {
      string strcolumnname = xsd.tables["out"].columns[i].columnname;
      xsd.tables["out"].rows[0][i] = sqlcommand.parameters["@" + strcolumnname].value;
     }
    }
   }
   catch( sqlexception ex )
   {
    ex = ex.message;
   }
   finally
   {
    sqlconnection.close();
   }
  }
  #endregion

  #region 四種基本語句

  /// <summary>
  /// 執行select
  /// </summary>
  /// <param name="text">select后的文本</param>
  /// <param name="ds">輸出查詢結果</param>
  /// <param name="srctable">用于表映射的源表的名稱</param>
  /// <param name="connect">連接字符串</param>
  /// <param name="ex">異常信息</param>
  /// <returns>返回行數</returns>
  public static int select( string text, ref dataset ds, string srctable, string connect, ref string ex )
  {
   return fill( "select " + text, ref ds, srctable,  connect, ref ex );
  }

  public static int select( string text, ref dataset ds, string connect, ref string ex )
  {
   return fill( "select " + text, ref ds, connect, ref ex );
  }

  /// <summary>
  /// 執行一串sql語句
  /// </summary>
  /// <param name="text">sql文本</param>
  /// <param name="ds">輸出查詢結果</param>
  /// <param name="srctable">用于表映射的源表的名稱</param>
  /// <param name="connect">連接字符串</param>
  /// <param name="ex">異常信息</param>
  /// <returns>返回行數</returns>
  public static int fill( string text, ref dataset ds, string srctable, string connect, ref string ex )
  {
   ex = string.empty;
   int nfill = 0;
   try
   {
    sqldataadapter sqldataadapter = new sqldataadapter( text, connect );
    nfill = sqldataadapter.fill( ds, srctable );
    return nfill;
   }
   catch( sqlexception ex )
   {
    ex = ex.message;
    return nfill;
   }
   catch( exception ex )
   {
    throw ex;
   }
  }

  public static int fill( string text, ref dataset ds, string connect, ref string ex )
  {
   return fill( text, ref ds, "table",  connect, ref ex );
  }

  /// <summary>
  /// 執行insert
  /// </summary>
  /// <param name="text">insert后的文本</param>
  /// <param name="connect">連接字符串</param>
  /// <param name="ex">異常信息</param>
  /// <returns>返回行數</returns>
  public static int insert( string text, string connect, ref string ex )
  {
   return executenonquery( "insert " + text, connect, ref ex );
  }

  /// <summary>
  /// 執行update
  /// </summary>
  /// <param name="text">update后的文本</param>
  /// <param name="connect">連接字符串</param>
  /// <param name="ex">異常信息</param>
  /// <returns>返回行數</returns>
  public static int update( string text, string connect, ref string ex )
  {
   return executenonquery( "update " + text, connect, ref ex );
  }

  /// <summary>
  /// 執行delete
  /// </summary>
  /// <param name="text">delete后的文本</param>
  /// <param name="connect">連接字符串</param>
  /// <param name="ex">異常信息</param>
  /// <returns>返回行數</returns>
  public static int delete( string text, string connect, ref string ex )
  {
   return executenonquery( "delete " + text, connect, ref ex );
  }

  /// <summary>
  /// 執行某sql語句(非select子句)
  /// </summary>
  /// <param name="text">sql文本</param>
  /// <param name="connect">連接字符串</param>
  /// <param name="ex">異常信息</param>
  /// <returns>返回行數</returns>
  public static int executenonquery( string text, string connect, ref string ex )
  {
   int nfill = 0;
   ex = string.empty;
   sqlconnection sqlconnection = new sqlconnection( connect );
   try
   {
    sqlcommand sqlcommand = new sqlcommand( text, sqlconnection );
    sqlconnection.open();
    nfill = sqlcommand.executenonquery();
    return nfill;
   }
   catch( sqlexception ex )
   {
    sqlconnection.close();
    ex = ex.message;
    return nfill;
   }
   catch( exception ex )
   {
    sqlconnection.close();
    throw ex;
   }
   finally
   {
    sqlconnection.close();
   }
  }
  #endregion
 }
}