AspNetPager控件分页（使用控件自动生成的存储过程）

最新推荐文章于 2024-09-14 19:00:09 发布

刘岩Tony

最新推荐文章于 2024-09-14 19:00:09 发布

阅读量710

点赞数 1

分类专栏： ASP.NET 文章标签： ASP.NET WebForm 分页技术 sql

本文链接：https://blog.csdn.net/yanlovehan/article/details/10525841

版权

ASP.NET 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

AspNetPager控件分页

准备工作

创建表

CREATE TABLE [TestTable] (
 [ID] [int] IDENTITY (1, 1) NOT NULL ,
 [FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [LastName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [Country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [Note] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

插入数据（插入20万条数据，2分钟完成）

SET IDENTITY_INSERT TestTable ON

declare @i int
set @i=1
while @i<=200000
begin
    insert into TestTable([id], FirstName, LastName, Country,Note) values(@i, 'FirstName_XXX','LastName_XXX','Country_XXX','Note_XXX')
    set @i=@i+1
end

SET IDENTITY_INSERT TestTable OFF

1.下载控件文件

点击进入下载地址

2.添加AspNetPager.dll

将AspNetPager.dll文件添加到工具箱中，这样就可以拖拽使用了

3.在.aspx文件中添加控件

1.添加一个GridView到文件中

2.添加刚刚导入的AspNetPager.dll生成的AspNetPager控件到文件中

文件代码如下：

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="version_1.aspx.cs" Inherits="splitPage.version_1" %>

<%@ 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>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>
        
        <webdiyer:AspNetPager ID="AspNetPager1" runat="server" 
            onpagechanged="AspNetPager1_PageChanged">
        </webdiyer:AspNetPager>
        <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
    </div>
    </form>
</body>
</html>

4.自动生成存储过程

1.进入.aspx文件的Design界面，选中AspNetPager控件，点击后面的指向右的标识，如图：

2.点击“分页存储过程生成工具”，

分为两种形式2000版本和2005版本，即2005版本及以上会使用ROW_NUMBER函数来进行分页，我只测试了20万条的数据，两者比较2005版本明显比2000的快，不会产生明显的卡顿，而2000版本会有一些卡顿，一秒左右的时间，也可以接受，如果数据量不是很大的情况下可以使用(以上的结论只是针对单表进行分页),如图：

选择一种版本，点击“生成存储过程并复制到剪贴板”，代码如下：

SQL Server 2000

create procedure FenPageTestTable 
(
	@startIndex int,
	@endIndex int,
	@docount bit
)
as
	set nocount on
		if(@docount=1)
			select count(*) from TestTable
		else
			begin
				declare @indextable table(id int identity(1,1),nid int)
				set rowcount @endIndex
				insert into @indextable(nid) select ID from TestTable  order by ID asc
				select * from TestTable O,@indextable t where O.ID=t.nid
					and t.id between @startIndex and @endIndex order by t.id
			end
	set nocount off

SQL Server 2005

create procedure FenPageTestTable 
(
@startIndex int,
@endIndex int,
@docount bit
)
as
	if(@docount=1)
		select count(*) from TestTable
	else
		begin
			with temptbl as (
			SELECT ROW_NUMBER() OVER (ORDER BY ID asc)AS Row, * from TestTable )
			SELECT * FROM temptbl where Row between @startIndex and @endIndex
		end

3.选择其中一种版本将其粘贴到数据库并运行，创建存储过程

5.在.aspx.cs中添加代码

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

namespace splitPage
{
    public partial class version_1 : System.Web.UI.Page
    {
        //数据库连接字符串
        string strConn = @"Data Source=WIN-KJ8CM60DBLC\SQLEXPRESS;Initial Catalog=NORTHWND;Integrated Security=True";
        //表中数据总条数的变量
        int count;
        protected void Page_Load(object sender, EventArgs e)
        {
            //设置每也显示的记录条数 
            this.AspNetPager1.PageSize = 20;
            //只在页面第一次加载时起作用 
            if (!Page.IsPostBack)
            {
                //获得要使用表的记录总数 
                using (SqlConnection conn = new SqlConnection(strConn))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        //cmd.CommandText = "select count(*) from Orders";
                        cmd.CommandText = "select count(*) from TestTable";
                        cmd.Connection = conn;
                        cmd.CommandType = CommandType.Text;
                        count = (int)cmd.ExecuteScalar();
                    }
                }

                //获取或设置需要分页的所有记录的总数
                this.AspNetPager1.RecordCount = count;

                this.BindData();
            }
        }
        private void BindData()
        {
            DataSet ds = FenPage();
            this.GridView1.DataSource = ds;
            this.GridView1.DataBind();
        }

        private DataSet FenPage()
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = conn;

                    cmd.CommandText = "FenPageTestTable";
                                        
                    SqlParameter[] parameters = { new SqlParameter("@startIndex",SqlDbType.Int),
                                                new SqlParameter ("@endIndex",SqlDbType.Int),
                                                new SqlParameter ("@docount",SqlDbType.Bit)};
                    parameters[0].Value = this.AspNetPager1.StartRecordIndex;
                    parameters[1].Value = this.AspNetPager1.EndRecordIndex;
                    parameters[2].Value = 0;

                    cmd.Parameters.AddRange(parameters);
                    DataSet ds = new DataSet();
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(ds);
                    return ds;
                }
            }
        }

        protected void AspNetPager1_PageChanged(object sender, EventArgs e)
        {
            this.BindData();
        }
    }
}