【编者按】本文作者为来自巴基斯坦的软件开发工程师 Aqeeel,主要介绍了在 SQL 查询层面实现 ASP.NET 应用的分页方法。
本文系OneAPM 工程师编译呈现,以下为正文。
GridView 提供了一种实现分页的方法。但是,随着记录的不断扩大,我们需要在查询层面进行优化。
在 SQL 查询层面实现 ASP.NET 程序分页,而不借助 GridView。
无可否认,GridView 是在 ASP.NET Web 表单展示数据的强大工具,它能在结果集较大时实现分页。然而,后端会获取完整的数据,抽取出相关数据,然后通过 GridView 展示在 Web 表单中。在这种情况下,相关数据只是完全抽取数据的一小部分。这些多余的数据造成了处理能力、内存空间以及时间的极大浪费。在本文中,我们将展示如何仅从数据库抽取所需数据,从而避免这些浪费。
下图展示了从数据库中获取完整数据的方式。在渲染阶段,相关数据会被抽取出来,填充到 GridView 中。
下图展示了从数据库中抽取过滤或相关数据的方式,进而得到更小的数据集。在 Web 应用中,同样的数据集无需经过进一步抽取,就可以填充到 GridView 中。
本例借助 SQL Server 2014 与 Visual Studio 2015 实现。2012 之前的 SQL Server 版本不支持 FETCH,但是使用 ROW NUMBER 可以达到同样的效果。
创建存储过程(Stored Procedures,简称 SP),用于从数据库获取数据。请注意,笔者创建了两个存储过程,名字分别为 SelectCitiesWithPaging 与 SelectCitiesWithPagingOldSQLVersions。由于笔者是在 SQL Server 2014 中实现该解决方案的,在第一个 SP 中,笔者使用了 OFFSET FETCH 声明。对于更早的版本,比如 SQL Server 2005 与 SQL Server 2008,则应该使用 ROW_NUMBER() 函数而非 OFFSET FETCH。因此,请创建与开发环境相适合的 SP。与传统的 SP 不同,此处创建的 SP 将包含三个参数,细节如下:
@PageNumber 为将会返回的页码数(Page Number) @RowsPerPage 为每页的行数(Number of Rows) @TotalResords(输出参数)为总的记录
-- CREATE DATABASE CREATE DATABASE TestPagingInASPNET; GO -- CREATE FIRST TABLE CREATE TABLE AdministrativeUnits ( AdministrativeUnitID INT PRIMARY KEY IDENTITY(1, 1), Name VARCHAR(50) ); GO -- CREATE SECOND TABLE CREATE TABLE Cities ( CityID INT PRIMARY KEY IDENTITY(1, 1), AdministrativeUnitID INT, Name VARCHAR(50) ); GO -- CREATE THE STORED PROCEDURE CREATE PROCEDURE SelectCitiesWithPaging @PageNumber INT, @RowsPerPage INT, @TotalRows INT OUTPUT AS BEGIN SET NOCOUNT ON; SELECT @TotalRows = COUNT(*) FROM [AdministrativeUnits] [AU] INNER JOIN [Cities] [C] ON [AU].[AdministrativeUnitID] = [C].[AdministrativeUnitID] SELECT [AU].[Name] [Administrative Unit], [C].[Name] [City] FROM [AdministrativeUnits] [AU] INNER JOIN [Cities] [C] ON [AU].[AdministrativeUnitID] = [C].[AdministrativeUnitID] ORDER BY [AU].[Name], [C].[Name] OFFSET ((@PageNumber - 1) * @RowsPerPage) ROWS FETCH NEXT @RowsPerPage ROWS ONLY END GO -- CREATE THE STORED PROCEDURE CREATE PROCEDURE SelectCitiesWithPagingOldSQLVersions @PageNumber INT, @RowsPerPage INT, @TotalRows INT OUTPUT AS BEGIN SET NOCOUNT ON; SELECT @TotalRows = COUNT(*) FROM [AdministrativeUnits] [AU] INNER JOIN [Cities] [C] ON [AU].[AdministrativeUnitID] = [C].[AdministrativeUnitID] SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [AU].[Name], [C].[Name]) NUMBER, [AU].[Name] [Administrative Unit], [C].[Name] [City] FROM [AdministrativeUnits] [AU] INNER JOIN [Cities] [C] ON [AU].[AdministrativeUnitID] = [C].[AdministrativeUnitID] ) tbl WHERE Number BETWEEN ((@PageNumber - 1) * @RowsPerPage + 1) AND (@PageNumber * @RowsPerPage) END GO
ASPX
<table style="width:100%;"> <tr> <td> <asp:GridView ID="GridView1" runat="server"></asp:GridView> </td> </tr> <tr> <td style="text-align:center;"> <asp:Button ID="btnGridViewPrevious" runat="server" OnClick="btnGridViewPrevious_Click" Text="<" /> <asp:TextBox ID="txtGridViewPageNumber" runat="server"></asp:TextBox> <asp:Button ID="btnGridViewGoToPageNumber" runat="server" Text="Go to Page" OnClick="btnGridViewGoToPageNumber_Click" /> <asp:Button ID="btnGridViewNext" runat="server" OnClick="btnGridViewNext_Click" Text=">" /> </td> </tr> <tr> <td style="text-align:center;" runat="server" id="tdPage"> </td> </tr> </table>
ASPX.cs
在 .aspx.cs 文件中,我们会创建两个函数。
GetAndBindData()
CreatePager()
private void GetAndBindData(Int32 PageNumber, Int32 RowsPerPage) { SqlConnection con = new SqlConnection(ConnectionString); SqlCommand cmd = new SqlCommand(); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandText = "SelectProjects"; cmd.Connection = con; SqlParameter par1 = new SqlParameter(); par1.ParameterName = "PageNumber"; par1.DbType = System.Data.DbType.Int32; par1.Direction = System.Data.ParameterDirection.Input; par1.Value = PageNumber; cmd.Parameters.Add(par1); SqlParameter par2 = new SqlParameter(); par2.ParameterName = "RowsPerPage"; par2.DbType = System.Data.DbType.Int32; par2.Direction = System.Data.ParameterDirection.Input; par2.Value = RowsPerPage; cmd.Parameters.Add(par2); SqlParameter par3 = new SqlParameter(); par3.ParameterName = "TotalRows"; par3.DbType = System.Data.DbType.Int32; par3.Direction = System.Data.ParameterDirection.Output; cmd.Parameters.Add(par3); SqlDataAdapter adp = new SqlDataAdapter(); adp.SelectCommand = cmd; DataSet ds = new DataSet(); con.Open(); adp.Fill(ds); Session["TotalRows"] = par3.Value.ToString(); GridView1.DataSource = ds.Tables[0]; GridView1.DataBind(); } private void CreatePager(Int32 TotalRecords, Int32 PageNumber, Int32 RowsPerPage) { Int32 intIndex; Int32 intPageNumber; tdPage.InnerHtml = ""; intPageNumber = 1; for (intIndex = 1; intIndex <= TotalRecords; intIndex+=10) { tdPage.InnerHtml += " <a href=''>" + intPageNumber.ToString() + "</a> "; intPageNumber++; } if (TotalRecords > intIndex) { tdPage.InnerHtml += " <a href=''>" + intIndex.ToString() + "</a> "; } } protected void btnGridViewNext_Click(object sender, EventArgs e) { Int32 NewPageNumber = Convert.ToInt32(Session["PageNumber"]); NewPageNumber++; Session["PageNumber"] = NewPageNumber; txtGridViewPageNumber.Text = Session["PageNumber"].ToString(); GetAndBindData(Convert.ToInt32(Session["PageNumber"]), 10); btnGridViewPrevious.Enabled = true; } protected void btnGridViewGoToPageNumber_Click(object sender, EventArgs e) { Int32 NewPageNumber = Convert.ToInt32(txtGridViewPageNumber.Text); Session["PageNumber"] = NewPageNumber; txtGridViewPageNumber.Text = Session["PageNumber"].ToString(); GetAndBindData(Convert.ToInt32(Session["PageNumber"]), 10); btnGridViewPrevious.Enabled = true; }
通过此方法,在用户改变页面索引时,开发者可以只获取相关数据进行展示,而非完整的数据集。这样,不仅可以从数据库中选出相关数据,在 GridView 中过滤数据所需的步骤也可以避免。从而切实提高并优化应用性能。
原文地址: http://www.codeproject.com/Articles/1078662/How-to-implement-Paging-in-ASP-NET-at-SQL-Query-Le
OneAPM 助您轻松锁定.NET 应用性能瓶颈,通过强大的 Trace 记录逐层分析,直至锁定行级问题代码。以用户角度展示系统响应速度,以地域和浏览器维度统计用户使用情况。想阅读更多技术文章,请访问OneAPM 官方博客。