自訂分頁控制項用於DataGrid（使用SQL儲存過程）

分頁儲存過程：

  網上找的一個改了一下！

/*
  函數名稱: GetRecordFromPage
  函數功能: 擷取指定頁的資料
  參數說明:
 @tblName       包含資料的表名
            @PKName       關鍵字段名
 @strGotFields 要擷取的欄位
            @PageSize      每頁記錄數
            @PageIndex     要擷取的頁碼
            @OrderType     排序類型, 0 - 升序, 1 - 降序
            @strWhere      查詢條件 (注意: 不要加 where)
 @isCount 是否取得記錄條數 , 0 - 不取 , 1 - 擷取
  @strSort 排序欄位
*/
CREATE PROCEDURE GetRecordFromPage
    @tblName      varchar(255),        -- 表名
    @PKName      varchar(255),        -- 欄位名
    @strGotFields varchar(1000) = '*' ,  --查詢欄位名
    @PageSize     int = 10,             -- 頁尺寸
    @PageIndex    int = 1,              -- 頁碼
    @OrderType    bit = 0,              -- 設定排序類型, 非 0 值則降序
    @strWhere     varchar(2000) = '' ,  -- 查詢條件 (注意: 不要加 where)
    @isCount bit = 1,   --取得記錄條數
    @strSort varchar(255) = ''  --排序欄位
AS
declare @strSQL   varchar(6000)       -- 主語句
declare @strTmp   varchar(1000)        -- 臨時變數
declare @strOrder varchar(500)          -- 排序類型
declare @strCount varchar(1000)
declare @fldName varchar(255)
declare @sortName varchar(255)
declare @countSQL varchar(1000)

set @fldName = @PKName
if @strSort != ''
begin
    set @sortName = @strSort
end
else
begin
    set @sortName = @PKName
end

if @isCount = 1
begin
 if @strWhere != ''
 begin
     set @countSQL = 'select count(' + @fldName + ') from ' + @tblName  + ' where ' + @strWhere
 end
 else
 begin
     set @countSQL = 'select count(' + @fldName + ') from ' + @tblName
 end
 exec (@countSQL)
 return
end
else
begin

if @OrderType != 0
begin
    set @strTmp = '<(select min'
    set @strOrder = ' order by ' + @sortName + ' desc'
end
else
begin
    set @strTmp = '>(select max'
    set @strOrder = ' order by ' + @sortName +' asc'
end

set @strSQL = 'select top ' + str(@PageSize) + @strGotFields + '  from '
    + @tblName + ' where ' + @fldName + '' + @strTmp + '('
    + @fldName + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '
    + @fldName + ' from ' + @tblName + '' + @strOrder + ') as tblTmp)'
    + @strOrder

if @strWhere != ''
    set @strSQL = 'select top ' + str(@PageSize) + @strGotFields + '  from '
        + @tblName + ' where ' + @fldName + '' + @strTmp + '('
        + @fldName + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '
        + @fldName + ' from ' + @tblName + ' where ' + @strWhere + ' '
        + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder

if @PageIndex = 1
begin
    set @strTmp = ''
    if @strWhere != ''
        set @strTmp = ' where (' + @strWhere + ')'

    set @strSQL = 'select top ' + str(@PageSize) + @strGotFields + '  from '
        + @tblName + '' + @strTmp + ' ' + @strOrder
end

exec (@strSQL)
end
GO

分頁控制項

在網上也查看了一些分頁控制項代碼，大多都是做成通用的，對我所用的項目不太適合

自已寫了一個功能不多，只分頁與排序，剛合適合當前項目！

using System;
using System.Text;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections.Specialized;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;

namespace WebAppInc
{
 /// <summary>
 /// /*
 ///函數名稱: GetRecordFromPage
 ///函數功能: 擷取指定頁的資料
 ///參數說明:
 ///@tblName       包含資料的表名
 ///@PKName       關鍵字段名
 ///@strGotFields 要擷取的欄位
 ///@PageSize      每頁記錄數
 ///@PageIndex     要擷取的頁碼
 ///@OrderType     排序類型, 0 - 升序, 1 - 降序
 ///@strWhere      查詢條件 (注意: 不要加 where)
 ///@isCount   是否取得記錄條數 , 0 - 不取 , 1 - 擷取
 /// @strSort  排序欄位
 ///*/
 /// </summary>
 [DefaultProperty("EnableCustomerStyle"),
 ToolboxData("<{0}:CusPageCtrl runat=server></{0}:CusPageCtrl>")]
 public class CusPageCtrl:System.Web.UI.Control,IPostBackDataHandler, IPostBackEventHandler
 {
  private int _PageSize;
  private int _PageCount;
  private string _TableName;
  private string _SelectFields;
  private string _SortField;
  private int _CurPageIndex;
  private string _btDGrid;
  private DataView _datasource;
  private OrderType itype;
  private string _SelectWhere;
  private string _DataKeyField;
  private string _selectClass;
  private bool isCount = true;
  private string _itemClass;

  public enum OrderType
  {
   Asc,Desc
  }
  public string PageCssClass
  {
   get{ return _itemClass; }
   set{ _itemClass = value;}
  }
  public string CurPageCssClass
  {
   get{ return _selectClass; }
   set{ _selectClass = value;}
  }
  /// <summary>
  /// 排序方式
  /// </summary>
  [Bindable(true),Category("Appearance"),DefaultValue("")]
  public OrderType Order
  {
   get{ return itype; }
   set{ itype = value;}
  }

  /// <summary>
  /// 分頁數
  /// </summary>
  public int PageCount
  {
   get{ return _PageCount; }
  }
  /// <summary>
  /// 關鍵字段
  /// </summary>
  [Bindable(true),Category("Appearance"),DefaultValue("")]
  public string SelectKeyField
  {
   get{ return _DataKeyField; }
   set{ _DataKeyField = value;}
  }
  /// <summary>
  /// 杳詢條件
  /// </summary>
  [Bindable(true),Category("Appearance"),DefaultValue("")]
  public string SelectWhere
  {
   get{ return _SelectWhere; }
   set{ _SelectWhere = value; }
  }
  /// <summary>
  /// 資料來源
  /// </summary>
  public DataView DataSource
  {
   get{ return _datasource; }
   //set{ _datasource = value;}
  }
  /// <summary>
  /// 要綁定的 DataGrid 控制項
  /// </summary>
  [Bindable(true),Category("Appearance"),DefaultValue("")]
  public string BindToDataGrid
  {
   get{ return _btDGrid;  }
   set{ _btDGrid = value; }
  }
  /// <summary>
  /// 當前頁碼索引
  /// </summary>
  [Bindable(true),Category("Appearance"),DefaultValue("")]
  public int CurPageIndex
  {
   get{ return _CurPageIndex;  }
   set{ _CurPageIndex = value; }
  }
  /// <summary>
  /// 排序欄位
  /// </summary>
  [Bindable(true),Category("Appearance"),DefaultValue("")]
  public string SortField
  {
   get{ return _SortField; }
   set{ _SortField = value;}
  }
  /// <summary>
  /// 分頁記錄條數
  /// </summary>
  [Bindable(true),Category("Appearance"),DefaultValue("25")]
  public int PageSize
  {
   get{ return _PageSize; }
   set{ _PageSize = value; }
  }
  /// <summary>
  /// 查詢資料來源表格
  /// </summary>
  [Bindable(true),Category("Appearance"),DefaultValue("")]
  public string TableName
  {
   get{ return _TableName; }
   set{ _TableName = value;}
  }
  /// <summary>
  /// 查詢欄位
  /// </summary>
  [Bindable(true),Category("Appearance"),DefaultValue("")]
  public string SelectFields
  {
   get{ return _SelectFields; }
   set{ _SelectFields = value; }
  }

  public bool LoadPostData(string postDataKey, NameValueCollection values)
  {

   _CurPageIndex = Int32.Parse(values[this.UniqueID]);
   return false;
  }

  public void RaisePostDataChangedEvent()
  {

   // IPostBackDataHandler 協定的一部分。如果曾經從 LoadPostData 方法返回真
   // （表示需要引發更改通知），則被調用。由於
   // 始終返回假，則此方法只是一個空操作。
  }
  public void RaisePostBackEvent(string eventArgument)
  {
           this.CurPageIndex = Convert.ToInt32(eventArgument);
     GetDataSource();
  }

  protected override void OnPreRender(EventArgs e)
  {
   //Page.RegisterPostBackScript();
   //Page.Response.Write("2");
  }

  protected override void OnInit(EventArgs e)
  {
   DataGrid dGrid = (DataGrid)this.Parent.FindControl(this.BindToDataGrid);
   dGrid.SortCommand += new DataGridSortCommandEventHandler(dGrid_SortCommand);
   GetDataSource();    //擷取資料來源
   GetDataSourcePageCount();  //擷取頁碼數量
   base.OnInit (e);
  }

  protected override void Render(HtmlTextWriter output)
  {
   //Page.Response.Write("3：" + this.PageCount.ToString());
   int max;
   int min;
   int i,len;
   int ShowSize = 10;
   string tmp = null;
   min = Convert.ToInt32(Math.Floor((this.CurPageIndex + 1) / ShowSize)) * ShowSize;
   max = min + ShowSize;
   len = this.PageCount;
   if(max > len)
   {
    max = len;
   }
   output.Write("<Div class=/"" + PageCssClass + "/">");
   if(min >= ShowSize)
   {
    output.Write("<a style=/"cursor:hand/" onClick=/"jscript:"+ Page.GetPostBackEventReference(this, (min-2).ToString())+ "/" title=/"向前翻頁/"><font face=webdings>9</font></a> ");
   }
   for(i = min;i < max ;i++ )
   {
    if(i == this.CurPageIndex)
    {
     tmp = "<font class=/"" + this.CurPageCssClass + "/">" + (i+1).ToString() + "</font>";
    }

    tmp = (i+1).ToString();
    if(i < max - 1)
    {
     output.Write("<a style=/"cursor:hand/" onclick=/"jscript:"+ Page.GetPostBackEventReference(this, i.ToString())+ "/">" + tmp + "</a>");
    }
    
    if(i < max - 2)
    {
     output.Write(" | ");
    }
    else if(i == max - 1 && (max == min + 10))
    {
     output.Write(" <a style=/"cursor:hand/" onClick=/"jscript:"+ Page.GetPostBackEventReference(this, i.ToString())+ "/" title=/"向後翻頁/"><font face=webdings>:</font></a>");
    }
   }
   output.Write("</Div>");
   output.Write("<input type=/"hidden/" name=" + this.UniqueID + " type=text value=" + this.CurPageIndex + ">");
   if(max <= 0)
   {
    output.Write("無資料！");
   }
  }
  private void GetDataSourcePageCount()
  {
   if(ViewState["CurPageCount"] == null)
   {
    this.isCount = true;
   }
   else
   {
    return;
   }
   try
   {
    SqlParameter[] arParams = new SqlParameter[9];
    arParams[0] = new SqlParameter("@tblName",SqlDbType.VarChar,255);
    arParams[0].Value = this.TableName;
    arParams[1] = new SqlParameter("@strGotFields",SqlDbType.VarChar,1000);
    arParams[1].Value = this.SelectFields;
    arParams[2] = new SqlParameter("@strSort",SqlDbType.VarChar,200);
    arParams[2].Value = this.SelectKeyField;
    arParams[3] = new SqlParameter("@OrderType",SqlDbType.Bit);
    arParams[3].Value = true;
    arParams[4] = new SqlParameter("@PageSize",SqlDbType.Int);
    arParams[4].Value = this.PageSize;
    arParams[5] = new SqlParameter("@PageIndex",SqlDbType.Int);
    arParams[5].Value = 1;
    arParams[6] = new SqlParameter("@strWhere",SqlDbType.VarChar,2000);
    arParams[6].Value = this.SelectWhere;
    arParams[7] = new SqlParameter("@isCount",SqlDbType.Bit);
    arParams[7].Value = this.isCount;
    arParams[8] = new SqlParameter("@PKName",SqlDbType.VarChar,200);
    arParams[8].Value = this.SelectKeyField;
    ViewState["CurPageCount"] = Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteScalar(ForDataBase.ConnectionString,CommandType.StoredProcedure,"GetRecordFromPage",arParams).ToString();
    this._PageCount = Convert.ToInt32(Math.Ceiling(Convert.ToInt32(ViewState["CurPageCount"]) / this.PageSize));
    this._PageCount = Convert.ToInt32(ViewState["CurPageCount"]) / this.PageSize;
   }
   catch(Exception ex)
   {
    Page.Response.Write(ex.Message);
   }
   //Page.Response.Write(ViewState["CurPageCount"].ToString());
  }
  private void GetDataSource()
  {
   bool tmp = true;   
   DataSet ds = new DataSet();
   if(itype == OrderType.Desc)
   {
    tmp = false;
   }
   this.isCount = false;
   SqlParameter[] arParams = new SqlParameter[9];
   arParams[0] = new SqlParameter("@tblName",SqlDbType.VarChar,255);
   arParams[0].Value = this.TableName;
   arParams[1] = new SqlParameter("@strGotFields",SqlDbType.VarChar,1000);
   arParams[1].Value = this.SelectFields;
   arParams[2] = new SqlParameter("@strSort",SqlDbType.VarChar,200);
   arParams[2].Value = this.SortField;
   arParams[3] = new SqlParameter("@OrderType",SqlDbType.Bit);
   arParams[3].Value = tmp;
   arParams[4] = new SqlParameter("@PageSize",SqlDbType.Int);
   arParams[4].Value = this.PageSize;
   arParams[5] = new SqlParameter("@PageIndex",SqlDbType.Int);
   arParams[5].Value = this.CurPageIndex + 1;
   arParams[6] = new SqlParameter("@strWhere",SqlDbType.VarChar,2000);
   arParams[6].Value = this.SelectWhere;
   arParams[7] = new SqlParameter("@isCount",SqlDbType.Bit);
   arParams[7].Value = this.isCount;
   arParams[8] = new SqlParameter("@PKName",SqlDbType.VarChar,200);
   arParams[8].Value = this.SelectKeyField;
   ds = Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(WebAppInc.ForDataBase.ConnectionString,CommandType.StoredProcedure,"GetRecordFromPage",arParams);
   this._datasource = ds.Tables[0].DefaultView;
   Page.Response.Write(Parent.FindControl(this.BindToDataGrid).ToString());
   DataGrid dGrid = (DataGrid)this.Parent.FindControl(this.BindToDataGrid);
   if(this.SortField != String.Empty)
   {
    ViewState["Sort"] = this.SortField;
    if(tmp == true)
    {
     ViewState["Order"] = "asc";
    }
    else
    {
     ViewState["Order"] = "desc";
    }
    //DataGrid 控制項排序樣式
    if(ViewState["Sort"] != null)
    {
     int i=0;
     string strtmp = null;
     for(i=0;i<dGrid.Columns.Count;i++)
     {
      strtmp = dGrid.Columns[i].HeaderText;
      strtmp = strtmp.Replace("<font face=/"Webdings/">6</font>","");
      strtmp = strtmp.Replace("<font face=/"Webdings/">5</font>","");
      dGrid.Columns[i].HeaderText = strtmp;
      if(dGrid.Columns[i].SortExpression == ViewState["Sort"].ToString())
      {
       if(ViewState["Order"].ToString() == "desc")
       {
        dGrid.Columns[i].HeaderText = strtmp + "<font face=/"Webdings/">6</font>";
       }
       else
       {
        dGrid.Columns[i].HeaderText = strtmp + "<font face=/"Webdings/">5</font>";
       }
      }
     }
    }
   }

   dGrid.DataSource = this._datasource;
   dGrid.DataBind();
   //Page.Response.Write("<br>Exe Sort：" + SortField);
  }
  private void dGrid_SortCommand(object source, System.Web.UI.WebControls.DataGridSortCommandEventArgs e)
  {
   this.SortField = e.SortExpression;
   string sort = e.SortExpression;
   if(ViewState["Sort"] != null)
   {
    if(ViewState["Sort"].ToString() != sort)
    {
     ViewState["Sort"] = sort;
     ViewState["Order"] = "desc";
    }
    else
    {
     if(ViewState["Order"].ToString() == "desc")
     {
      ViewState["Order"] = "asc";
     }
     else
     {
      ViewState["Order"] = "desc";
     }
    }
   }
   else
   {
    ViewState["Sort"] = sort;
    ViewState["Order"] = "desc";
   }
   if(ViewState["Order"].ToString() == "desc")
   {
    this.Order = OrderType.Desc;
   }
   else
   {
    this.Order = OrderType.Asc;
   }
   GetDataSource();
   //Page.Response.Write("<br>Sort：" + SortField);
  }
 }
}