一个用存储过程的基本分页及其调用

1 存储过程：
CREATE           PROCEDURE C_PF_GetRecordFromPage
   @tblName      varchar(255),       -- 表名
    @fldName      varchar(255),       -- 字段名
    @PageSize     int = 10,           -- 页尺寸
    @PageIndex    int = 1,            -- 页码
    @IsCount      bit = 0,            -- 返回记录总数, 非 0 值则返回
    @OrderType    bit = 0,            -- 设置排序类型, 非 0 值则降序
    @strWhere     varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
AS

declare @strSQL   varchar(2000)     -- 主语句
declare @strTmp   varchar(1000)     -- 临时变量
declare @strOrder varchar(1000)       -- 排序类型

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

set @strSQL = 'select top ' + str(@PageSize) + ' * 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) + ' * 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) + ' * from '
        + @tblName + '' + @strTmp + ' ' + @strOrder
end

if @IsCount != 0
    set @strSQL = 'select count(*) as Total from ' + @tblName + ' where (' + @strWhere + ')'
exec (@strSQL)

GO

2 用户控件代码：
前台：
   [%@ Control Language="c#" AutoEventWireup="false" Codebehind="PageControl.ascx.cs" Inherits="ClubMannage.Modules.PageControl" TargetSchema="http://schemas.microsoft.com/intellisense/ie5"%]
&nbsp;[/FONT]
[TABLE id="Table1" cellSpacing="1" cellPadding="1" width="100%" border="0"]
[TR]
   [TD]
    [asp:Label id="lb_PageMes" runat="server"]Label[/asp:Label][/TD]
   [TD align="right"]
    [asp:Button id="btn_F" runat="server" CssClass="Button1" Text="首页"][/asp:Button]
    [asp:Button id="btn_P" runat="server" CssClass="Button1" Text="上页"][/asp:Button]&nbsp;&nbsp;
    转到[asp:textbox id="tb_PageIndex" runat="server" CssClass="txtinput" Width="40px"][/asp:textbox]
    [asp:Button id="btn_go" runat="server" CssClass="Button1" Text="跳转"][/asp:Button]&nbsp;&nbsp;
    [asp:Button id="btn_N" runat="server" CssClass="Button1" Text="下页"][/asp:Button]
    [asp:Button id="btn_L" runat="server" CssClass="Button1" Text="尾页"][/asp:Button][/TD]
[/TR]
[/TABLE]

后台：
namespace ClubMannage.Modules
{
using System;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

/// <summary>
///   PageControl 的摘要说明。
/// </summary>
public class PageControl : System.Web.UI.UserControl
{
   protected System.Web.UI.WebControls.Label lb_PageMes;
   protected System.Web.UI.WebControls.TextBox tb_PageIndex;
   protected System.Web.UI.WebControls.Button btn_F;
   protected System.Web.UI.WebControls.Button btn_P;
   protected System.Web.UI.WebControls.Button btn_go;
   protected System.Web.UI.WebControls.Button btn_N;
   protected System.Web.UI.WebControls.Button btn_L;

   protected static string strSQL;
   protected static string strConn = System.Configuration.ConfigurationSettings.AppSettings["strConnection"];

   private int      _PageSize;       //PageSize
   public int PageSize
   {
    get
    {
     return _PageSize;
    }
    set
    {
     _PageSize = value;
    }
   }

   private int      _PageIndex;       //_PageIndex
   public int PageIndex
   {
    get
    {
     return _PageIndex;
    }
    set
    {
     _PageIndex = value;
    }
   }

   private int      _PageCount;
   private int      _RecordCount;
  
   private string _PF_Name;           //存储过程名
   public string PF_Name
   {
    get
    {
     return _PF_Name;
    }
    set
    {
     _PF_Name = value;
    }
   }

   private string _TableName;           //表名
   public string TableName
   {
    get
    {
     return _TableName;
    }
    set
    {
     _TableName = value;
    }
   }
   private string _strWhere;           //条件，不加where
   public string strWhere
   {
    get
    {
     return _strWhere;
    }
    set
    {
     _strWhere = value;
    }
   }

   private string _OrderFieldName; //排序字段名
   public string OrderFieldName
   {
    get
    {
     return _OrderFieldName;
    }
    set
    {
     _OrderFieldName = value;
    }
   }

   private int _OrderType;
// 排序方式
   public int OrderType
   {
    get
    {
     return _OrderType;
    }
    set
    {
     _OrderType = value;
    }
   }

   private DataGrid _BindControl;        //绑定控件
   public DataGrid BindControl
   {
    get
    {
     return this._BindControl;
    }
    set
    {
     this._BindControl = value;
    }
   }
  
   private void Page_Load(object sender, System.EventArgs e)
   {
    if(!Page.IsPostBack)
    {
     BinderDG();
    }
   
   }

   #region Web 窗体设计器生成的代码
   override protected void OnInit(EventArgs e)
   {
    //
    // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
    //
    InitializeComponent();
    base.OnInit(e);
   }
  
   /// <summary>
   ///   设计器支持所需的方法 - 不要使用代码编辑器
   ///   修改此方法的内容。
   /// </summary>
   private void InitializeComponent()
   {
    this.btn_F.Click += new System.EventHandler(this.btn_F_Click);
    this.btn_P.Click += new System.EventHandler(this.btn_P_Click);
    this.btn_go.Click += new System.EventHandler(this.btn_go_Click);
    this.btn_N.Click += new System.EventHandler(this.btn_N_Click);
    this.btn_L.Click += new System.EventHandler(this.btn_L_Click);
    this.Load += new System.EventHandler(this.Page_Load);

   }
   #endregion
   public void ReBinderDataSource()
   {
    BinderDG();
   }
  
   protected static DataSet ExecuteSql4PF(string CommText,string[] ParamsArr,object[] ValuesArr)
   {
    SqlConnection myCn = new SqlConnection(strConn);   
    SqlCommand myCmd = new SqlCommand();
    myCmd.Connection = myCn;
    myCmd.CommandText =CommText;
    myCmd.CommandType = CommandType.StoredProcedure;
    if(ParamsArr!=null)  
    {
　　    for(int i=0;i<ParamsArr.Length;i++)
      myCmd.Parameters.Add(ParamsArr[i],ValuesArr[i]);
    }
    try
    {
     myCn.Open();
     SqlDataAdapter sda = new SqlDataAdapter(myCmd);
     DataSet ds = new DataSet("ds");
     sda.Fill(ds);
     return ds;
    }
    catch(System.Data.SqlClient.SqlException e)
    {    
     throw new Exception(e.Message);
    }
    finally
    {
     myCn.Close();
    }
   }
   public void Search(String WhereString)
   {
    ViewState["_strWhere"] = WhereString;
    ViewState["_PageIndex"] = "1";
    BinderDG();
   }
   //得到记录数
   private int GetRecordCount()
   {
    String [] SqlParameterArray = new String[7];
    String [] SqlParameterValuesArray = new String[7];

    SqlParameterArray[0] = "@tblName";
    SqlParameterValuesArray[0] = ViewState["_TableName"].ToString();

    SqlParameterArray[1] = "@fldName";
    SqlParameterValuesArray[1] = ViewState["_OrderFieldName"].ToString();

    SqlParameterArray[2] = "@PageSize";
    SqlParameterValuesArray[2] = ViewState["_PageSize"].ToString();

    SqlParameterArray[3] = "@PageIndex";
    SqlParameterValuesArray[3] = ViewState["_PageIndex"].ToString();

    SqlParameterArray[4] = "@IsCount"; //1 返回记录数
    SqlParameterValuesArray[4] = "1";

    SqlParameterArray[5] = "@OrderType";
    SqlParameterValuesArray[5] = "1";

    SqlParameterArray[6] = "@strWhere";
    SqlParameterValuesArray[6] = ViewState["_strWhere"].ToString();
    _RecordCount = int.Parse( ExecuteSql4PF(ViewState["_PF_Name"].ToString(),SqlParameterArray,SqlParameterValuesArray).Tables[0].Rows[0][0].ToString() );
    ViewState["_RecordCount"] = _RecordCount.ToString();
    return _RecordCount;
   }
   //得到页数
   private int GetPageCount()
   {
    _RecordCount = GetRecordCount();
    int iPageSize = int.Parse(ViewState["_PageSize"].ToString());
   
    if(_RecordCount%iPageSize == 0)
    {
     _PageCount = _RecordCount/iPageSize;
    
    }
    else
    {
     _PageCount = _RecordCount/iPageSize+1;
    }
    ViewState["_PageCount"] = _PageCount.ToString();
    return _PageCount;
   }
//   public void ResetPageCount(int nPageCount)
//   {
//    _RecordCount = GetRecordCount();
//    int iPageSize = nPageCount ;
//   
//    if(_RecordCount%iPageSize == 0)
//    {
//     _PageCount = _RecordCount/iPageSize;
//    
//    }
//    else
//    {
//     _PageCount = _RecordCount/iPageSize+1;
//    }
//    ViewState["_PageCount"] = _PageCount.ToString();
//   }
   private void BinderDG()
   {
    String [] SqlParameterArray = new String[7];
    String [] SqlParameterValuesArray = new String[7];

    SqlParameterArray[0] = "@tblName";
    if(Object.Equals(ViewState["_TableName"],null))
    {
     SqlParameterValuesArray[0] = _TableName.ToString();
     ViewState["_TableName"] = _TableName.ToString();
    }
    else
    {
     SqlParameterValuesArray[0] = ViewState["_TableName"].ToString();
    }

    SqlParameterArray[1] = "@fldName";
    if(Object.Equals(ViewState["_OrderFieldName"],null))
    {
     SqlParameterValuesArray[1] = _OrderFieldName.ToString();
     ViewState["_OrderFieldName"] = _OrderFieldName.ToString();
    }
    else
    {
     SqlParameterValuesArray[1] = ViewState["_OrderFieldName"].ToString();
    }

    SqlParameterArray[2] = "@PageSize";
   
    if(Object.Equals(ViewState["_PageSize"],null))
    {
     SqlParameterValuesArray[2] = _PageSize.ToString();
     ViewState["_PageSize"] = _PageSize.ToString();
    }
    else
    {
     SqlParameterValuesArray[2] = ViewState["_PageSize"].ToString();
    }

    SqlParameterArray[3] = "@PageIndex";
    if(Object.Equals(ViewState["_PageIndex"],null))
    {
     SqlParameterValuesArray[3] = _PageIndex.ToString();
     ViewState["_PageIndex"] = _PageIndex.ToString();
    }
    else
    {
     SqlParameterValuesArray[3] = ViewState["_PageIndex"].ToString();
    }

    SqlParameterArray[4] = "@IsCount";
    SqlParameterValuesArray[4] = "0";

    SqlParameterArray[5] = "@OrderType";
    if(Object.Equals(ViewState["_OrderType"],null))
    {
     SqlParameterValuesArray[5] = _OrderType.ToString();
     ViewState["_OrderType"] = _OrderType.ToString();
    }
    else
    {
     SqlParameterValuesArray[5] = ViewState["_OrderType"].ToString();
    }

    SqlParameterArray[6] = "@strWhere";
    if(Object.Equals(ViewState["_strWhere"],null))
    {
     SqlParameterValuesArray[6] = _strWhere.ToString();
     ViewState["_strWhere"] = _strWhere.ToString();
    }
    else
    {
     SqlParameterValuesArray[6] = ViewState["_strWhere"].ToString();
    }
    if(Object.Equals(ViewState["_PF_Name"],null))
    {
     ViewState["_PF_Name"] = _PF_Name;
    }
   
     _BindControl.DataSource = ExecuteSql4PF(ViewState["_PF_Name"].ToString(),SqlParameterArray,SqlParameterValuesArray);
     _BindControl.DataBind();

   
    ShowPageMes();

    btn_P.Enabled = true;
    btn_F.Enabled = true;
    btn_N.Enabled = true;
    btn_L.Enabled = true;
    if(ViewState["_PageIndex"].ToString() == "1"){ btn_P.Enabled = false; btn_F.Enabled = false; }
    if(ViewState["_PageIndex"].ToString() == ViewState["_PageCount"].ToString() ){ btn_N.Enabled = false; btn_L.Enabled = false;}
   }
   private void ShowPageMes()
   {
    GetPageCount();
    String mes = "页次<b>" + ViewState["_PageIndex"].ToString()+ "</b><b>/"+ViewState["_PageCount"].ToString()+"</b> 每页<b>"+ViewState["_PageSize"].ToString()+"</b> 记录数<b>"+ViewState["_RecordCount"].ToString()+"</b>";
    lb_PageMes.Text = mes;
   }

   private void btn_F_Click(object sender, System.EventArgs e)
   {
    _PageIndex = 1;
    ViewState["_PageIndex"] = "1";
    BinderDG();
   }

   private void btn_P_Click(object sender, System.EventArgs e)
   {
    int iPageIndex = int.Parse(ViewState["_PageIndex"].ToString());
    if(iPageIndex>1)
    {
     iPageIndex -- ;
     _PageIndex = iPageIndex;
     ViewState["_PageIndex"] = _PageIndex;
     BinderDG();
    }
   }

   private void btn_N_Click(object sender, System.EventArgs e)
   {
   
    int iPageIndex = int.Parse(ViewState["_PageIndex"].ToString());
    int iPageCount = int.Parse(ViewState["_PageCount"].ToString());
    if(iPageIndex<iPageCount)
    {
     iPageIndex ++ ;
     _PageIndex = iPageIndex;
     ViewState["_PageIndex"] = _PageIndex;
     BinderDG();
    }
   }

   private void btn_L_Click(object sender, System.EventArgs e)
   {
    int iPageCount = int.Parse(ViewState["_PageCount"].ToString());
    _PageIndex = iPageCount;
    ViewState["_PageIndex"] = iPageCount.ToString();
    BinderDG();
   }

   private void btn_go_Click(object sender, System.EventArgs e)
   {
    int iPageIndex = int.Parse(ViewState["_PageIndex"].ToString());
    int iPageCount = int.Parse(ViewState["_PageCount"].ToString());
    int GotoPage = 1;
    try
    {
     GotoPage = Convert.ToInt32(tb_PageIndex.Text.Trim());
    }
    catch
    {
     GotoPage = 1;
    }
    if(GotoPage>=1&&GotoPage<=iPageCount)
    {
     _PageIndex = GotoPage;
     ViewState["_PageIndex"] = _PageIndex.ToString();
     BinderDG();
    }
    if(GotoPage<1)
    {
     _PageIndex = 1;
     ViewState["_PageIndex"] = _PageIndex.ToString();
     BinderDG();
    }
    if(GotoPage>iPageCount)
    {
     _PageIndex = iPageCount;
     ViewState["_PageIndex"] = _PageIndex.ToString();
     BinderDG();
    }
   }

}
}


应用：
[uc1:PageControl id="PageControl1" runat="server"][/uc1:PageControl]
    [asp:Button id="Button1" runat="server" Text="Button"][/asp:Button]
    [asp:DataGrid id="DataGrid1" runat="server"][/asp:DataGrid]

public class WebForm1 : System.Web.UI.Page
{
   protected System.Web.UI.WebControls.DataGrid DataGrid1;
   protected System.Web.UI.WebControls.Button Button1;
   protected ClubMannage.Modules.PageControl PageControl1;

   private void Page_Load(object sender, System.EventArgs e)
   {
   
    PageControl1.BindControl = DataGrid1;
    if(!Page.IsPostBack)
    {
     InitPageControl();
    }
   
   
   }

   #region Web 窗体设计器生成的代码
   override protected void OnInit(EventArgs e)
   {
    //
    // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
    //
    InitializeComponent();
    base.OnInit(e);
   }
  
   /// <summary>
   /// 设计器支持所需的方法 - 不要使用代码编辑器修改
   /// 此方法的内容。
   /// </summary>
   private void InitializeComponent()
   {   
    this.Button1.Click += new System.EventHandler(this.Button1_Click);
    this.Load += new System.EventHandler(this.Page_Load);

   }
   #endregion
   private void InitPageControl()
   {
   
    PageControl1.TableName = "Tab_AritcleRecommend";
    PageControl1.OrderFieldName = "R_ID";
    PageControl1.PageSize = 25;
    PageControl1.OrderType = 1;
    PageControl1.PageIndex = 1;
    PageControl1.PF_Name = "C_PF_GetRecordFromPage";
    PageControl1.strWhere = "1=1";

   }