Custom Paging in Asp.NET with Data Grid or Grid View

Purpose:

When ever in any large application lots of data are there, and we want to display all the data on the page so long scroll will come, and might be some alignment issue can arise. For that purpose to display some specific no of data on the page and at the bottom or top of the page display page no list so user can move easily on the specific page quickly.

But Grid view and Data grid is giving in built paging facility. So there is no need to develop it independently right? No it’s wrong; Let me come to the point, suppose we are developing any shopping cart application. In that application user is searching for particular item and that item is suppose mobile device. Now in store there are 1000 different models. But you are displaying only 20,30 or 50 models on the page and then paging is there. But when ever you are retrieving data from database all 1000 record will come into result. On the client end rest of data will be eliminated by grid and display particular no of data on the page. So why we are retrieving extra data from database?, why we will use network bandwidth?, why we will create overhead on server, database and network? For that we need custom page to retrieve data effectively.

What is custom paging?

Custom paging in the sense implement the paging facility in web page by programmatically. In this procedure how can we achieve this functionality just see it here. In our application when ever large amount of data is there to be displayed. Then write a Stored Procedure and pass two parameter one for page id and second for no of data. And from procedure retrieve specific no of data that’s it. It’s very simple.

Here is the sample code for stored procedure.

CREATE Procedure [dbo].[CustomePaging]

-- Declare parameters.

@CurrPage As int,

@PageSize As int

As

Set NoCount On

-- Declare variables.

Declare @FRec int

Declare @LRec int

-- Initialize variables.

Set @FRec = (@CurrPage - 1) * @PageSize

Set @LRec = (@CurrPage * @PageSize + 1)

-- Create a temporarily table to hold the current page of data

--Guess we are retrieving from emp table name and age

Create Table #Temp
(
              RecId int IDENTITY PRIMARY KEY,
              Emp_Name varchar(100),
              Emp_Age int
)

--Fill the temp table with the reminders

Insert Into #Temp
(
     Emp_Name, Emp_Age
)

Select Emp_Name,Emp_Age from emp Order By Emp_Id Desc -- Assume Emp_Id is primary key

--Select one page of data based on the record numbers above

Select * From #Temp Where RecId > @FRec And RecId < @LRec

Drop Table #Temp




In above procedure we are passing two parameters CurrentPage and PageSize. On base upon we set First Record and Last record No. Then we create Temp Table select all the data from original table and insert into that temp table. From there we are retrieving all the data between First and Last Record.

In webpage set CurrentPage=1 during page load event then put two arrow button on page like previous and next and on the click of that button set current page index either CurrentPage -1 or CurrentPage +1 and then again retrieve data and bind to grid or datagrid.

Happy coding...