Introduction

Paging is an important thing for thousands of data if we want to develop a scalable application. Sometimes, it may need customized user interface and also with paging feature. ASP.NET
ListView is a control that can use to provide user interface or layout with paging supports.

Background

I will develop an ASP.NET web application with List View control to show how to build scalable application using paging and sorting effectively.

Profile_GET stored procedure takes PageSize, CurrentPage and SortExpression as input parameter.
PageSize - is the number of records that will display in ListView as a page at a time. CurrentPage – which page you are in currently
in ListView.

SortExpression – sort records in a page by which field. All of these above parameters will be passed by ListView control
and DataPage form web page when the application is running.

The above two equations will calculate upper bound and lower bound for a page. Lower band means the starting row position for a page and upper band means top row position for a page. Say, you have current page 5 and your page size is 5. So the result for the above two equations are:

LowerBand = (5 -1) * 5 = 20
UpperBand = (5 * 5) + 1 = 26

So records that hold position number 21-25 (because I used greater than and less than in where condition later in this procedure) will return by those equations.

The above SQL statement will add an additional column RowNumber in a temporary table tempProfile and assign a sequential number for each
record in temporary table after sort in ascending order by sorting expression parameter. This RowNumber column will be used for paging later.

Figure 3

Some records from temporary tempProfile table where records have been sorted by Profile Id. Now filter records as per request that have come
by PageSize, CurrentPage and SortExpresssion parameters.

Write body for TotalRowCount method. Parameters of this method will pass by object data source control later. This will execute Ptofile_Total stored procedure that we have already created and will return total number of records to object data source control.

Write body for GetProfileData method. Parameters of this method will be passed by object data source control. This will execute Profile_GET stored procedure that we have already created with parameter to get paged records for each request. Finally, this method will return a data table to object data source control.

Set some properties EnablePaging to true for paging, MaximumRowsParameterName to pageSize for current page size, StartRowIndexParameterName to startRowIndex for start row number position, TypeName to VTS.Web.UI.ProfileDataSource for Data object with namespace, SelectMethod to GetProfileData to get paged records, SelectCountMethod to TotalRowCount to get total records from underlining table will be used for paging, SortParameterName to sortExpression for sorting.

How It Works

Open SQL Profile from SQL Management Studio under Tools menu to diagnose how things work behind effective paging.

Open New Trace from File menu

Login using your credential

Select Run

Click Clear Trace Window under Edit menu to clear existing trace

Minimize SQL Server Profiler

Run the Application from Visual Studio to browse PagingWithListView.aspx page

After you run the application for the first time, you will get the trace like below:

Figure 9

By clicking next numeric button:

Figure 10

In Profiler:

Figure 11

By clicking column head to sort:

Figure 12

In profiler:

Figure 13

Continue these steps for all of the pages that are available in the bottom of the ProfileView also for sorting by clicking header of each column. You will get 5 records for each request. So there is no change to load all the records from the underling table. Only the number of records load that you have set to pageSize property in Listview. Records load faster than earlier normal paging. It will take less system resource. It is very effective for thousands of thousand data.

Conclusion

Effective paging is important for applications that handle large number of records to build scalable applications. This demonstration will help you to create scalable applications.

I downloaded your source code and run it, when i go to the records (6-10 or page no. 2 etc.) and click on sorting Arrows on head, It sort but the paging change to page no. 1. please tell me the solution,

OR

I want to do paging Only via query not with procedure, so please tell me the query to execute.

Your link to "Effective Paging with Grid View control in ASP.NET" is broken.

I am curious to ask you, and I would not vote on your article, since I do not have personal experience in this area, but: the people I know who are dealing with "paged" display of selected information (in "big chunks" brought back from queries) from large databases, are using controls (often non-MS third party) that support paging (virtualisation) out-of-the-box, and even some MS presentation controls do.

I am aware that the ASP.NET GridView control does have some form of paging ability.

Even in WinForms, the ListView control has a 'VirtualMode' boolean property, and an integer 'VirtualListSize property ... the DataGridView control has a 'VirtualMode property: all the way back to FrameWork 2.0.

So, it leaves me wondering why you are using the ASP.ListView: if this is an irrelevant question: I apologize !

good luck, best, Bill

"One of the few good things about modern times: If you die horribly on television, you will not have died in vain. You will have entertained us." Kurt Vonnegut