VS2005中用存储过程实现分页控件AspNetPager(用存储过程加AspNetPager分页控件分页超快)
存储过程:(这里用到的数据库是MSSQL2000中自带的数据库Northwind中的表order)
CREATE procedure pro_order
(@pagesize int,
@pageindex int,
@docount bit)
as
set nocount on
if(@docount=1)
select count(OrderID) from Orders
else
begin
declare @indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
insert into @indextable(nid) select OrderID from Orders order by RequiredDate desc
select O.* from Orders O,@indextable t where O.OrderID=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
end
set nocount off
GO
显示页面Html代码;
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<%@ Register Assembly="AspNetPager" Namespace="Wuqi.Webdiyer" TagPrefix="webdiyer" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>无标题页</title>
</head>
<body style="text-align: center">
<form id="form1" runat="server">
<div style="text-align: center">
<div style="width: 100px; height: 88px">
<asp:GridView ID="GridView1" runat="server" Width="624px" OnRowDataBound="GridView1_RowDataBound">
</asp:GridView>
<div style="width: 624px; height: 24px">
<table style="width: 100%; height: 100%">
<tr>
<td style="height: 22px" colspan="2">
</td>
</tr>
</table>
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label></div>
</div>
</div>
<webdiyer:AspNetPager ID="AspNetPager1" runat="server" ShowInputBox="Never"
Width="480px" NumericButtonTextFormatString="[{0}]"
OnPageChanged="AspNetPager1_PageChanged" CustomInfoSectionWidth="60%" PageSize="10" Font-Bold="False">
</webdiyer:AspNetPager>
</form>
</body>
</html>
显示页面的CS代码:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
string con = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
SqlConnection dbconnection = new SqlConnection(con);
SqlCommand cmd = new SqlCommand("pro_order",dbconnection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@pagesize", 1);
cmd.Parameters.Add("@pageindex", 1);
cmd.Parameters.Add("@docount", true);
dbconnection.Open();
this.AspNetPager1.RecordCount = (int)cmd.ExecuteScalar();
dbconnection.Close();
SetDataBind();
}
}
private void SetDataBind()
{
string con = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
SqlConnection dbconnection = new SqlConnection(con);
SqlCommand cmd = new SqlCommand("pro_order", dbconnection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@pagesize", this.AspNetPager1.PageSize);
cmd.Parameters.Add("@pageindex", this.AspNetPager1.CurrentPageIndex);
cmd.Parameters.Add("@docount", false);
dbconnection.Open();
GridView1.DataSource = cmd.ExecuteReader();
GridView1.DataBind();
dbconnection.Close();
//动态设置用户自定义文本内容
AspNetPager1.CustomInfoClass = "记录总数:<font color=\"blue\"><b>" + AspNetPager1.RecordCount.ToString() + "</b></font>";
AspNetPager1.CustomInfoClass += "总页数:<font color=\"blue\"><b>" + AspNetPager1.PageCount.ToString() + "</b></font>";
AspNetPager1.CustomInfoClass += "当前页:<font color=\"red\"><b>" + AspNetPager1.CurrentPageIndex.ToString() + "</b></font>";
Label1.Text = AspNetPager1.CustomInfoClass;
}
protected void AspNetPager1_PageChanged(object src, Wuqi.Webdiyer.PageChangedEventArgs e)
{
this.AspNetPager1.CurrentPageIndex = e.NewPageIndex;
SetDataBind();
System.Text.StringBuilder sb = new System.Text.StringBuilder("<script Language=\"Javascript\"><!--\n");
sb.Append("var el=document.all;");
sb.Append(GridView1.ClientID);
sb.Append(".scrollIntoView(true);");
sb.Append("<");
sb.Append("/");
sb.Append("script>");
if (!ClientScript.IsStartupScriptRegistered("scrollScript"))
{
ClientScript.IsStartupScriptRegistered(sb.ToString());
}
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
e.Row.Attributes.Add("onMouseOver", "c=this.style.backgroundColor;this.style.backgroundColor='eafae9';");
e.Row.Attributes.Add("onMouseOut", "this.style.backgroundColor=c;");
}
}
}
CREATE procedure pro_order(@pagesize int,@pageindex int,@docount bit)asset nocount onif(@docount=1)select count(OrderID) from Orderselsebegindeclare @indextable table(id int identity(1,1),nid int)declare @PageLowerBound intdeclare @PageUpperBound intset @PageLowerBound=(@pageindex-1)*@pagesizeset @PageUpperBound=@PageLowerBound+@pagesizeset rowcount @PageUpperBoundinsert into @indextable(nid) select OrderID from Orders order by RequiredDate descselect O.* from Orders O,@indextable t where O.OrderID=t.nidand t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.idendset nocount offGO显示页面Html代码;
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %><%@ Register Assembly="AspNetPager" Namespace="Wuqi.Webdiyer" TagPrefix="webdiyer" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>无标题页</title></head><body style="text-align: center"> <form id="form1" runat="server"> <div style="text-align: center"> <div style="width: 100px; height: 88px"> <asp:GridView ID="GridView1" runat="server" Width="624px" OnRowDataBound="GridView1_RowDataBound"> </asp:GridView> <div style="width: 624px; height: 24px"> <table style="width: 100%; height: 100%"> <tr> <td style="height: 22px" colspan="2"> </td> </tr> </table> <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label></div> </div> </div> <webdiyer:AspNetPager ID="AspNetPager1" runat="server" ShowInputBox="Never" Width="480px" NumericButtonTextFormatString="[{0}]" OnPageChanged="AspNetPager1_PageChanged" CustomInfoSectionWidth="60%" PageSize="10" Font-Bold="False"> </webdiyer:AspNetPager> </form></body></html>显示页面的CS代码:
using System;using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Data.SqlClient;public partial class _Default : System.Web.UI.Page {
protected void Page_Load(object sender, EventArgs e) {
if (!this.IsPostBack) { string con = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString; SqlConnection dbconnection = new SqlConnection(con); SqlCommand cmd = new SqlCommand("pro_order",dbconnection); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@pagesize", 1); cmd.Parameters.Add("@pageindex", 1); cmd.Parameters.Add("@docount", true); dbconnection.Open(); this.AspNetPager1.RecordCount = (int)cmd.ExecuteScalar(); dbconnection.Close(); SetDataBind(); } } private void SetDataBind() { string con = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString; SqlConnection dbconnection = new SqlConnection(con); SqlCommand cmd = new SqlCommand("pro_order", dbconnection); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@pagesize", this.AspNetPager1.PageSize); cmd.Parameters.Add("@pageindex", this.AspNetPager1.CurrentPageIndex); cmd.Parameters.Add("@docount", false); dbconnection.Open(); GridView1.DataSource = cmd.ExecuteReader(); GridView1.DataBind(); dbconnection.Close(); //动态设置用户自定义文本内容 AspNetPager1.CustomInfoClass = "记录总数:<font color=\"blue\"><b>" + AspNetPager1.RecordCount.ToString() + "</b></font>"; AspNetPager1.CustomInfoClass += "总页数:<font color=\"blue\"><b>" + AspNetPager1.PageCount.ToString() + "</b></font>"; AspNetPager1.CustomInfoClass += "当前页:<font color=\"red\"><b>" + AspNetPager1.CurrentPageIndex.ToString() + "</b></font>"; Label1.Text = AspNetPager1.CustomInfoClass; } protected void AspNetPager1_PageChanged(object src, Wuqi.Webdiyer.PageChangedEventArgs e) { this.AspNetPager1.CurrentPageIndex = e.NewPageIndex; SetDataBind(); System.Text.StringBuilder sb = new System.Text.StringBuilder("<script Language=\"Javascript\"><!--\n"); sb.Append("var el=document.all;"); sb.Append(GridView1.ClientID); sb.Append(".scrollIntoView(true);"); sb.Append("<"); sb.Append("/"); sb.Append("script>"); if (!ClientScript.IsStartupScriptRegistered("scrollScript")) { ClientScript.IsStartupScriptRegistered(sb.ToString()); } } protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.DataRow) { e.Row.Attributes.Add("onMouseOver", "c=this.style.backgroundColor;this.style.backgroundColor='eafae9';"); e.Row.Attributes.Add("onMouseOut", "this.style.backgroundColor=c;"); } }}