一个用存储过程的基本分页及其调用
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"%]
[/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 = "页次<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";
}
