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

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)

2 用户控件代码：
前台：
   [%@ Control Language="c#" AutoEventWireup="false" Codebehind="PageControl.ascx.cs" Inherits="ClubMannage.Modules.PageControl" TargetSchema="http://schemas.microsoft.com/intellisense/ie5"%]
 [/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]  
    转到[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]  
    [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 = "页次" + ViewState["_PageIndex"].ToString()+ "/"+ViewState["_PageCount"].ToString()+" 每页"+ViewState["_PageSize"].ToString()+" 记录数"+ViewState["_RecordCount"].ToString()+"";
 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();
    }


   }

   }
   #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";

}