ASP. NET + SQL Server Stored Procedures implement paging sorting

Question:

Data is frequently queried in applications. When the data size of the query results is large, it takes a lot of time to retrieve the results and display the interface. To avoid this problem, only part of the data should be retrieved at a time, that is, the common paging method should be used for processing. The paging problem seems very simple in Asp.net. You only need to enable paging in the gridview. After pagination is enabled, the gridview associated data source control still loads all data. This solution is just to "Hide your ears and ears", which will lead to low query efficiency in the case of large data volumes.

Solution:

Use the custom paging function of the gridview. Using the custom paging function requires two Logics: getting the total number of result sets and searching for custom range data.

For more information about pagination in the gridview, see Scott Mitchell's article efficiently paging through large amounts of data.

 

This topic describes how to obtain paging data by sorting using SQL Server.

1. subquery + TOP keyword

If object_id ('getstudentpaged') is not null Drop procedure getstudentpaged; Go /************************************ * Description: searches for records within a specified range. * Parameter: @ startrow actually records * @ Maximumrows: Maximum number of records *************************************/ Create procedure getstudentpaged @ Startrow int, @ Maxmimumrows int As Select top (@ maxmimumrows) * from student Where studentid not in (select top (@ startrow) studentid from student) Go

If you need to search by conditions, the conditions may be different. Pass the where condition to the stored procedure through parameters. Because the WHERE clause does not support variables, You need to combine SQL statements in the stored procedure and execute them in dynamic SQL mode.

Create procedure getstudentpaged @ Startrow int, @ Maxmimumrows int, @ Whereexpression nvarchar (512) As -- Store the SQL statement string Declare @ SQL nvarchar (max) Set @ SQL = n' select top ('+ ltrim (STR (@ maxmimumrows) + N ')*' Set @ SQL = @ SQL + N' from student where' -- Determine whether query conditions exist If @ whereexpression is not null and @ whereexpression <> n'' Begin Set @ SQL = @ SQL + @ whereexpression + N' and' End Set @ SQL = @ SQL + N' studentid not in (' Set @ SQL = @ SQL + N' select top ('+ ltrim (STR (@ startrow) +') studentid from student' If @ whereexpression is not null and @ whereexpression <> n'' Begin Set @ SQL = @ SQL + N 'where' + @ whereexpression End Set @ SQL = @ SQL + ')' -- Print @ SQL Execute sp_executesql @ SQL Go

 

Now we can implement paging in case of conditional search. The following problem is that if you need to support the sorting function when using the gridview, you also need to add support for sorting. In fact, this problem is relatively simple. You only need to add a sort parameter.

Create procedure getstudentpaged @ Startrow int, @ Maxmimumrows int, @ Whereexpression nvarchar (512 ), @ Sortexpression nvarchar (512) As -- Store the SQL statement string Declare @ SQL nvarchar (max) Set @ SQL = n' select top ('+ ltrim (STR (@ maxmimumrows) + N ')*' Set @ SQL = @ SQL + N' from student where' -- Determine whether query conditions exist If @ whereexpression is not null and @ whereexpression <> n'' Begin Set @ SQL = @ SQL + @ whereexpression + N' and' End Set @ SQL = @ SQL + N' studentid not in (' Set @ SQL = @ SQL + N' select top ('+ ltrim (STR (@ startrow) +') studentid from student' If @ whereexpression is not null and @ whereexpression <> n'' Begin Set @ SQL = @ SQL + N 'where' + @ whereexpression End If @ sortexpression is not null and @ sortexpression <> n'' Begin Set @ SQL = @ SQL + n'order by' + @ sortexpression End Set @ SQL = @ SQL + ')' If @ sortexpression is not null and @ sortexpression <> n'' Begin Set @ SQL = @ SQL + n'order by' + @ sortexpression End -- Print @ SQL Execute sp_executesql @ SQL Go

 

The student table definition is provided here.

Create Table student ( Studentid uniqueidentifier not null, Name varchar (128) not null, Sex bit not null, Birthdate datetime not null, Nation varchar (128) not null, Nativeplace varchar (128) not null, Address varchar (256) null, Photo image null, Memo varchar (512) null, Studentstatus varchar (56) null ) Go

Use T-SQL to generate large volumes of data

Declare @ I int Set @ I = 0; While (@ I <10000) Begin Insert into student (studentid, [name], sex, birthdate, Nation, nativeplace, [address], photo, memo, studentstatus) Values (newid (), 'rain' + STR (@ I), floor (RAND (2), dateadd (year,-10, getdate (), 'hangzhou ', 'sichuan ', 'Now address', null, 'test', 'read ') Set @ I = @ I + 1 End

 

2. Use a temporary table

Store the Qualified Data to the temporary table based on the filtering and sorting conditions, and add an auto-increment secondary column to the temporary table to obtain data within the specified range.

Use teachmis; Go If object_id ('getstudentpaged') is not null Drop procedure getstudentpaged; Go Create procedure getstudentpaged @ Startrow int, @ Maxmimumrows int, @ Whereexpression nvarchar (512 ), @ Sortexpression nvarchar (512) As If (object_id ('tempdb .. # student ') is not null) Drop table # student; Go -- Generate a temporary table with no records Select Identity (INT, 1, 1) as rowid , [Studentid] , [Name] , [Sex] , [Birthdate] , [Nation] , [Nativeplace] , [Address] , [Photo] , [Memo] , [Studentstatus] Into # student From [Student] where 1 = 0 Declare @ SQL nvarchar (max) Set @ SQL = 'insert into # student Select [Studentid] , [Name] , [Sex] , [Birthdate] , [Nation] , [Nativeplace] , [Address] , [Photo] , [Memo] , [Studentstatus] From [Student]' If @ whereexpression is not null and @ whereexpression <>'' Begin Set @ SQL = @ SQL + 'where' + @ whereexpression End If @ sortexpression is not null and @ sortexpression <>'' Begin Set @ SQL = @ SQL + 'ORDER BY' + @ sortexpression End Execute sp_executesql @ SQL Select [Studentid] , [Name] , [Sex] , [Birthdate] , [Nation] , [Nativeplace] , [Address] , [Photo] , [Memo] , [Studentstatus] From # student Where rowid between @ startrow and @ startrow + @ maximumrows-1 Order by rowid Go

 

3. Use the SQL Server 2005 CTE expression.

For details, refer to Scott Mitchell's article efficiently paging through large amounts of data