The request was aborted: Could not create SSL/TLS secure channel. DNG - SQL Server Paging

Skip to: site menu | section menu | main content

DotNetGenerics.com

Anything and everything about Microsoft .NET technology ...
Currently viewing: Skip Navigation LinksDotNetGenerics.com » Tricks & Tips » SQL Server » SQL Server Paging

DotNetGenerics.com - Anything and everything about Microsoft .NET technology ...

Menu:

You have not logged in.

Advt:

Links:

Malaika Consultants LLC
Microsoft .NET
ASP.NET
MSDN
Microsoft SQL Server
C#.NET
VB.NET
Visual Studio.NET

Email: Contact Us

SQL Server Paging

.NET 2.0

Do you want to do paging on the database side, rather than get the entire dataset and then implement paging on the client (like ASP.NET) side?

Many of the ASP.NET controls like the datagrid and the new gridview have capabilities to implement paging and you should be able to get a simple paging solution in a small amount of time using these controls. So why use paging on the database side? Reason is quite simple : when you are doing paging on the client side (like using the above mentioned controls), even though you are displaying a limited number of records (say you have defined your PageSize as 20 -- it means you are displaying 20 records at a time , if there are 100 records you will have 5 pages each displaying 20 records), you are still grabbing the entire dataset (100 records in our example) and then limiting the controls to display 20 records out of these 100. This works well when you are working with small datasets. However if your query is to return 1 million records and you have displaying say just 100 records at a time, it does not make sense to query the database and obtain all of the 1 million records (which might lead to time-out issues)

The solution is fairly simple. We will be using the pubs database that is shipped by Microsoft with SQL server.
Suppose we need to find all the authors from the state of California:
SELECT au_id,au_lname FROM authors WHERE state = 'CA'
This has 15 records, but say we just need paged result of 5 records

Let us define our variables
DECLARE @PageNumber INT --Page number starting from 1
DECLARE @RecsPerPage INT -- Page size
DECLARE @TotalRecords INT --this can be the output param if using a SP


Use a table variable to store our entire data set :
DECLARE @authors TABLE (record_id INT NOT NULL IDENTITY(1,1), au_id VARCHAR(20), au_lname VARCHAR(20))

Now add dataset to this table variable
INSERT INTO @authors
SELECT au_id,au_lname FROM authors WHERE state = 'CA'


Now we will set the paging. Let us find out the first and the last record for this page :
DECLARE @FirstRec int, @LastRec INT
SELECT @FirstRec = (@PageNumber - 1) * @RecsPerPage
SELECT @LastRec = (@PageNumber * @RecsPerPage + 1)


Next get the total records of the dataset in case it is required by the client to know whether to show the next button or not
SELECT @TotalRecords = COUNT(record_id) FROM @authors

Now we will remove the records that are not needed from our data set, in other words just get the records for this page. If we are on page # 2, just get records from 6 to 10
DELETE FROM @authors
WHERE au_id NOT IN
(SELECT au_id
FROM @authors
WHERE record_id > @FirstRec AND record_id < @LastRec)


Now return the dataset - this will be a data set just containing our paged result set
SELECT au_id,au_lname FROM @authors

Download the associated code here

Comments:

ozgurbilisim @ 3/21/2007 11:27:03 AM

very nice and clear article.

Login to add your comments