1.1分頁

/// <summary>
  /// 執行自訂分頁返回<see cref="System.Data.DataSet">System.Data.DataSet</see>
  /// </summary>
  /// <param name="sTable_Name">要查詢的表名</param>
  /// <param name="sSign_Record">唯一標識的欄位</param>
  /// <param name="sFilter_Condition">查詢條件</param>
  /// <param name="sFind_RecordList">要查詢的欄位列表，全部欄位查詢使用Null 字元</param>
  /// <param name="iPage_Size">分頁的每頁的大小</param>
  /// <param name="iPage_Index">當前頁數</param>
  /// <param name="iTaxis_Sign">排序標誌 0正序1倒序</param>
  /// <param name="iPageCount">總頁數．輸出參數</param>
  /// <param name="iiRecord_Count">總記錄數．輸出參數</param>
  /// <returns>成功返回<see cref="System.Data.DataSet">System.Data.DataSet</see></returns>
    DataSet ExecuteCustomPage(string sTable_Name,string sSign_Record,string sFilter_Condition,string sFind_RecordList,int iPage_Size,int iPage_Index,string sTaxisField,int iTaxis_Sign,out int iPageCount,out int iiRecord_Count);
public DataSet ExecuteCustomPage(string sTable_Name,string sSign_Record,string sFilter_Condition,string sFind_RecordList,int iPage_Size,int iPage_Index,int iTaxis_Sign,out int iPageCount,out int iiRecord_Count)
  {
   try
   {
    int iRecord_Count=0;
    iiRecord_Count=0;
    string sSql="";
    if(sFilter_Condition=="")
    {
     sSql="SELECT COUNT(*) AS RC FROM "+sTable_Name;
    }
    else
    {
     sSql="SELECT COUNT(*) AS RC FROM "+sTable_Name+" WHERE "+sFilter_Condition;
    }
    DataTable dt=this.ExecuteDataSet(sSql).Tables[0];
    if(dt==null)
    {
     iPageCount=0;
     return null;
    }
    if(dt.Rows.Count<=0)
    {
     iPageCount=0;
     return null;
    }
    iRecord_Count=(int)dt.Rows[0]["RC"];
    iiRecord_Count=iRecord_Count;
    if(iRecord_Count % iPage_Size==0)
    {
     iPageCount=iRecord_Count/iPage_Size;
    }
    else
    {
     iPageCount=iRecord_Count/iPage_Size+1;
    }
    ArrayList Params=new ArrayList();
    Params.Add(new SqlParameter("@Table_Name",sTable_Name));
    Params.Add(new SqlParameter("@Sign_Record",sSign_Record));
    Params.Add(new SqlParameter("@Filter_Condition",sFilter_Condition));
    Params.Add(new SqlParameter("@Page_Size",iPage_Size));
    Params.Add(new SqlParameter("@Page_Index",iPage_Index));
    Params.Add(new SqlParameter("@Taxis_Sign",iTaxis_Sign));
    Params.Add(new SqlParameter("@Record_Count",iRecord_Count));
    Params.Add(new SqlParameter("@Find_RecordList",sFind_RecordList));
    SqlParameter paramsReturnVl=new SqlParameter("RETURN_VALUE",SqlDbType.Int);
    paramsReturnVl.Direction=ParameterDirection.ReturnValue;
    Params.Add(paramsReturnVl);
    return this.ExecuteDataSet("ProcCustomPage",Params);
   }
   catch(System.Exception ex)
   {
    iPageCount=0;
    iiRecord_Count=0;
    throw new Exception(ex.Message);
   }

  }

/// <summary>
  /// 執行自訂分頁返回<see cref="System.Data.DataSet">System.Data.DataSet</see>
  /// </summary>
  /// <param name="sTable_Name">要查詢的表名</param>
  /// <param name="sSign_Record">唯一標識的欄位</param>
  /// <param name="sFilter_Condition">查詢條件</param>
  /// <param name="sFind_RecordList">要查詢的欄位列表，全部欄位查詢使用Null 字元</param>
  /// <param name="iPage_Size">分頁的每頁的大小</param>
  /// <param name="iPage_Index">當前頁數</param>
  /// <param name="iTaxis_Sign">排序標誌 0正序1倒序</param>
  /// <param name="iPageCount">總頁數．輸出參數</param>
  /// <param name="iiRecord_Count">總記錄數．輸出參數</param>
  /// <returns>成功返回<see cref="System.Data.DataSet">System.Data.DataSet</see></returns>

public DataTable GetLines( string Condition,string tableName,string Records,int PageSize,int PageIndex,out int PageCount,out int RecordSum)
  {
   DataTable dt=new DataTable();
   dt=dataHelper.ExecuteCustomPage(tableName,"id",Condition,Records,PageSize,PageIndex,1,out PageCount,out RecordSum).Tables[0];
   return dt;
  }

CREATE  PROCEDURE ProcCustomPage
   (
      @Table_Name               varchar(1000),       /* 查詢的表名 */
      @Sign_Record              varchar(50),        /* 標誌欄位 */
      @Filter_Condition         varchar(1000),       /* 過濾條件 */
      @Page_Size                int,                /* 每頁記錄數 */
      @Page_Index               int,                /* 頁號 */
      @Taxis_Sign               int,                /* 排序標誌 0:正序 1:倒序 */
                    @Find_RecordList          varchar(1000),       /* 查詢的欄位,欄位間用,分割 空為查詢全部*/
      @Record_Count             int                 /* 總記錄數 */
   )
   AS
   BEGIN
   DECLARE  @Start_Number          int
   DECLARE  @End_Number            int
   DECLARE  @TopN_Number           int
   DECLARE  @sSQL                  varchar(5000)
                 if(@Find_RecordList='')
                 BEGIN
                      SELECT @Find_RecordList='*'
                 END
   SELECT @Start_Number =(@Page_Index-1) * @Page_Size
   IF @Start_Number<=0
   SElECT @Start_Number=0
   SELECT @End_Number=@Start_Number+@Page_Size
   IF @End_Number>@Record_Count
   SELECT @End_Number=@Record_Count
   SELECT @TopN_Number=@End_Number-@Start_Number
   IF @TopN_Number<=0
   SELECT @TopN_Number=0
   print @TopN_Number
   print @Start_Number
   print @End_Number
   print @Record_Count
   IF @Taxis_Sign=0
   BEGIN
   IF @Filter_Condition=''
   BEGIN
    SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+'
       WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
       WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
       ORDER BY '+@Sign_Record+') order by '+@Sign_Record+' DESC)order by '+@Sign_Record+' DESC'
   END
   ELSE
   BEGIN
    SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+'
       WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
       WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
       WHERE '+@Filter_Condition+' ORDER BY '+@Sign_Record+') and '+@Filter_Condition+' order by '+@Sign_Record+' DESC) and '+@Filter_Condition+' order by '+@Sign_Record+' DESC'
   END
   END
   ELSE
    BEGIN
   IF @Filter_Condition=''
    BEGIN
     SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+'
           WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
           WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
           ORDER BY '+@Sign_Record+' DESC) order by '+@Sign_Record+')order by '+@Sign_Record+' DESC'
       END
   ELSE
   BEGIN
    SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+'
       WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
       WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
       WHERE '+@Filter_Condition+' ORDER BY '+@Sign_Record+' DESC) and '+@Filter_Condition+' order by '+@Sign_Record+') and '+@Filter_Condition+' order by '+@Sign_Record+' DESC'
   END
   END
   EXEC (@sSQL)
   IF @@ERROR<>0
   RETURN -3               /* 查詢記錄出錯 */
   RETURN 0
   END

GO

/// <summary>
  /// 執行自訂分頁返回<see cref="System.Data.DataSet">System.Data.DataSet</see>
  /// </summary>
  /// <param name="sTable_Name">要查詢的表名</param>
  /// <param name="sSign_Record">唯一標識的欄位</param>
  /// <param name="sFilter_Condition">查詢條件</param>
  /// <param name="sFind_RecordList">要查詢的欄位列表，全部欄位查詢使用Null 字元</param>
  /// <param name="iPage_Size">分頁的每頁的大小</param>
  /// <param name="iPage_Index">當前頁數</param>
  /// <param name="sTaxisField">排序的欄位</param>
  /// <param name="iTaxis_Sign">排序標誌 0正序1倒序</param>
  /// <param name="iPageCount">總頁數．輸出參數</param>
  /// <param name="iiRecord_Count">總記錄數．輸出參數</param>
  /// <returns>成功返回<see cref="System.Data.DataSet">System.Data.DataSet</see></returns>
public DataSet ExecuteCustomPage(string sTable_Name,string sSign_Record,string sFilter_Condition,string sFind_RecordList,int iPage_Size,int iPage_Index,string sTaxisField,int iTaxis_Sign,out int iPageCount,out int iiRecord_Count)
  {
   try
   {
    int iRecord_Count=0;
    iiRecord_Count=0;
    string sSql="";
    if(sFilter_Condition=="")
    {
     sSql="SELECT COUNT(*) AS RC FROM "+sTable_Name;
    }
    else
    {
     sSql="SELECT COUNT(*) AS RC FROM "+sTable_Name+" WHERE "+sFilter_Condition;
    }
    DataTable dt=this.ExecuteDataSet(sSql).Tables[0];
    if(dt==null)
    {
     iPageCount=0;
     return null;
    }
    if(dt.Rows.Count<=0)
    {
     iPageCount=0;
     return null;
    }
    iRecord_Count=(int)dt.Rows[0]["RC"];
    iiRecord_Count=iRecord_Count;
    if(iRecord_Count % iPage_Size==0)
    {
     iPageCount=iRecord_Count/iPage_Size;
    }
    else
    {
     iPageCount=iRecord_Count/iPage_Size+1;
    }
    ArrayList Params=new ArrayList();
    Params.Add(new SqlParameter("@Table_Name",sTable_Name));
    Params.Add(new SqlParameter("@Sign_Record",sSign_Record));
    Params.Add(new SqlParameter("@Filter_Condition",sFilter_Condition));
    Params.Add(new SqlParameter("@Page_Size",iPage_Size));
    Params.Add(new SqlParameter("@Page_Index",iPage_Index));
    Params.Add(new SqlParameter("@TaxisField",sTaxisField));
    Params.Add(new SqlParameter("@Taxis_Sign",iTaxis_Sign));
    Params.Add(new SqlParameter("@Record_Count",iRecord_Count));
    Params.Add(new SqlParameter("@Find_RecordList",sFind_RecordList));
    SqlParameter paramsReturnVl=new SqlParameter("RETURN_VALUE",SqlDbType.Int);
    paramsReturnVl.Direction=ParameterDirection.ReturnValue;
    Params.Add(paramsReturnVl);
    return this.ExecuteDataSet("ProcCustomTaxisPage",Params);
   }
   catch(System.Exception ex)
   {
    iPageCount=0;
    iiRecord_Count=0;
    throw new Exception(ex.Message);
   }
  }
  
  DataSet ExecuteCustomPage(string sTable_Name,string sSign_Record,string sFilter_Condition,string sFind_RecordList,int iPage_Size,int iPage_Index,string sTaxisField,int iTaxis_Sign,out int iPageCount,out int iiRecord_Count);
CREATE  PROCEDURE ProcCustomTaxisPage
   (
      @Table_Name               varchar(5000),       /* 查詢的表名 */
      @Sign_Record              varchar(50),        /* 標誌欄位 */
      @Filter_Condition         varchar(1000),       /* 過濾條件 */
      @Page_Size                int,                /* 每頁記錄數 */
      @Page_Index               int,                /* 頁號 */
                    @TaxisField               varchar(1000),
      @Taxis_Sign               int,                /* 排序標誌 0:正序 1:倒序 */
                    @Find_RecordList          varchar(1000),       /* 查詢的欄位,欄位間用,分割 空為查詢全部*/
      @Record_Count             int                 /* 總記錄數 */
   )
   AS
   BEGIN
   DECLARE  @Start_Number          int
   DECLARE  @End_Number            int
   DECLARE  @TopN_Number           int
   DECLARE  @sSQL                  varchar(8000)
                 if(@Find_RecordList='')
                 BEGIN
                      SELECT @Find_RecordList='*'
                 END
   SELECT @Start_Number =(@Page_Index-1) * @Page_Size
   IF @Start_Number<=0
   SElECT @Start_Number=0
   SELECT @End_Number=@Start_Number+@Page_Size
   IF @End_Number>@Record_Count
   SELECT @End_Number=@Record_Count
   SELECT @TopN_Number=@End_Number-@Start_Number
   IF @TopN_Number<=0
   SELECT @TopN_Number=0
   print @TopN_Number
   print @Start_Number
   print @End_Number
   print @Record_Count
                 IF @TaxisField=''
                 begin
                    select  @TaxisField=@Sign_Record
                 end
   IF @Taxis_Sign=0
     BEGIN
     IF @Filter_Condition=''
     BEGIN
      SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+'
          WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+'

'+@Sign_Record+' FROM '+@Table_Name+'
          WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+'

'+@Sign_Record+' FROM '+@Table_Name+'
         ORDER BY '+@TaxisField+') order by '+@TaxisField+' DESC)order by '+@TaxisField
     END
    ELSE
    BEGIN
    SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+'
       WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM

'+@Table_Name+'
       WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM

'+@Table_Name+'
       WHERE '+@Filter_Condition+' ORDER BY '+@TaxisField+') and '+@Filter_Condition+' order by '+@TaxisField+'

DESC) and '+@Filter_Condition+' order by '+@TaxisField
     END
   END
  ELSE
   BEGIN
   IF @Filter_Condition=''
    BEGIN
     SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+'
           WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM

'+@Table_Name+'
           WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM

'+@Table_Name+'
           ORDER BY '+@TaxisField+' DESC) order by '+@TaxisField+')order by '+@TaxisField+' DESC'
       END
   ELSE
   BEGIN
    SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+'
       WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM

'+@Table_Name+'
       WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM

'+@Table_Name+'
       WHERE '+@Filter_Condition+' ORDER BY '+@TaxisField+' DESC) and '+@Filter_Condition+' order by

'+@TaxisField+') and '+@Filter_Condition+' order by '+@TaxisField+' DESC'
   END
   END
   EXEC (@sSQL)
   IF @@ERROR<>0
   RETURN -3               /* 查詢記錄出錯 */
   RETURN 0
   END
GO