利用SQL2005新特性实现分页

最新推荐文章于 2024-10-09 15:04:16 发布

原创最新推荐文章于 2024-10-09 15:04:16 发布 · 522 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #image #string #null #query #table

Asp.net 同时被 2 个专栏收录

115 篇文章

订阅专栏

Mssql

33 篇文章

订阅专栏

本文介绍如何使用SQL Server 2005的ROW_NUMBER()功能实现高效分页查询，包括创建存储过程、业务层调用及表示层展示数据的具体步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在SQL2000中做一个查询，仅仅返回一个结果记录集，而在SQL2005中，增加了一个ROW_NUMBER的功能用来在记录查询结果中个条记录的序号，利用ROW_NUMBER，就可以很轻松的实现分页效果，每次只要说明当前页面需要获取的的几条到的几条的记录就可以。

简单举个例子

例如有这样一张Product表

CREATE TABLE Product(

ProductID INT IDENTITY(1,1) NOT NULL,

Name VARCHAR(50) NOT NULL,

Description VARCHAR(5000) NOT NULL,

Price MONEY NOT NULL,

Image1FileName VARCHAR(50) NULL,

Image2FileName VARCHAR(50) NULL,

OnCatalogPromotion BIT NOT NULL,

OnDepartmentPromotion BIT NOT NULL,

CONSTRAINT PK_Product PRIMARY KEY CLUSTERED (ProductID ASC)

)

利用ROW_NUMBER做一个查询

select row_number() over (order by ProductID) as Row,Name from Product

可以看到返回结果中每条记录都有一个唯一的表示其序列号的标志。

例如我们在分页中要获取的6到第10条记录就可以采用下面的方法

select Row,Name from

(select ROW_NUMBER() over(order by ProductID) as Row,Name from Product)

as ProductsWithRowNumbers

where Row >= 6 and Row <=10

返回相关的记录。

完整的例子：

首先设计一个存储过程

create proc GetProductsInCategory

(

@CategoryID int,

@DescriptionLength int,

@PageNumber int,

@ProductsPerPage int,

@HowManyProducts int output

)

declare @Products table

(

RowNumber int,

ProductID int,

Name varchar(50),

Description varchar(5000),

Price MONEY,

Image1FileName varchar(50),

Image2FileName varchar(50),

OnDepartmentPromotion bit,

OnCatalogPromotion bit

)

insert into @Products

select ROW_NUMBER() OVER (ORDER BY Product.ProductID),

Product.ProductID,Name,

Substring(Description,1,@DescriptionLength)+'...' as Description,

Price,Image1FileName,Image2FileName,OnDepartmentPromotion,OnCatalogPromotion

from Product inner join ProductCategory

on Product.ProductID = ProductCategory.ProductID

where ProductCategory.CategoryID = @CategoryID

select @HowManyProducts = Count(ProductID) from @Products

select ProductID,Name,Description,Price,Image1FileName,Image2FileName,OnDepartmentPromotion,OncatalogPromotion

from @Products

where RowNumber > (@PageNumber-1) * @ProductsPerPage

and RowNumber <= @pageNumber * @productsPerPage

在业务层调用存储过程

public static DataTable GetProductsInCategory(string categoryId, string pageNumber, out int howManyPages)

{

DbCommand comm = GenericDataAccess.CreateCommand();

comm.CommandText = "GetProductsInCategory";

DbParameter param = comm.CreateParameter();

param.ParameterName = "@CategoryID";

param.Value = categoryId;

param.DbType = DbType.Int32;

comm.Parameters.Add(param);

param = comm.CreateParameter();

param.ParameterName = "@DescriptionLength";

param.Value = BalloonShopConfiguration.ProductDescriptionLength;

param.DbType = DbType.Int32;

comm.Parameters.Add(param);

param = comm.CreateParameter();

param.ParameterName = "@PageNumber";

param.Value = pageNumber;

param.DbType = DbType.Int32;

comm.Parameters.Add(param);

param = comm.CreateParameter();

param.ParameterName = "@ProductsPerPage";

param.Value = BalloonShopConfiguration.ProductsPerPage;

param.DbType = DbType.Int32;

comm.Parameters.Add(param);

param = comm.CreateParameter();

param.ParameterName = "@HowManyProducts";

param.Direction = ParameterDirection.Output;

param.DbType = DbType.Int32;

comm.Parameters.Add(param);

DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);

int howManyProducts = Int32.Parse(comm.Parameters["@HowManyProducts"].Value.ToString());

howManyPages = (int)Math.Ceiling((double)howManyProducts / (double)BalloonShopConfiguration.ProductsPerPage);

return table;

}

在表示层当中显示数据（list表示一个DataList）

string departmentId = Request.QueryString["DepartmentID"];

string categoryId = Request.QueryString["CategoryID"];

string page = Request.QueryString["Page"];

list.DataSource = CatalogAccess.GetProductsInCategory(categoryId, page, out howManyPages);

list.DataBind();

处理分页

if (howManyPages > 1)

{

int currentPage = Int32.Parse(page);

this.pagingLabel.Visible = true;

this.previousLink.Visible = true;

this.nextLink.Visible = true;

this.pagingLabel.Text = "Page " + page + " of " + howManyPages.ToString();

if (currentPage == 1)

{

this.previousLink.Enabled = false;

}

else

{

NameValueCollection query = Request.QueryString;

string paramName, newQueryString = "?";

for (int i = 0; i < query.Count; i++)

{

if (query.AllKeys[i] != null)

{

paramName = query.AllKeys[i].ToString();

if (paramName.ToUpper() != "PAGE")

{

newQueryString += paramName + "=" + query[i] + "&";

}

this.previousLink.NavigateUrl = Request.Url.AbsolutePath;

this.previousLink.NavigateUrl += newQueryString;

this.previousLink.NavigateUrl += "Page=" + (currentPage-1).ToString();

}

if (currentPage == howManyPages)

{

this.nextLink.Enabled = false;

}

else

{

NameValueCollection query = Request.QueryString;

string paramName, newQueryString = "?";

for (int i = 0; i < query.Count; i++)

{

if (query.AllKeys[i] != null)

{

paramName = query.AllKeys[i].ToString();

if (paramName.ToUpper() != "PAGE")

{

newQueryString += paramName + "=" + query[i] + "&";

}

this.nextLink.NavigateUrl = Request.Url.AbsolutePath;

this.nextLink.NavigateUrl += newQueryString;

this.nextLink.NavigateUrl += "Page=" + (currentPage + 1).ToString();

}

一个分页效果就是现了