Require for Custom Pagination
ASP.Net GridView fetches all the records and then displays one page from the fetched records. Thus for illustration if your table has 1000 records and you want to display only 50 records per page, GridView will fetch all 1000 records throw away the 950 records and display the 50 records based on the page index selected by the users.
In Stored Procedure
SQL Server 2005 came up with the new ROW_NUMBER() keyword that allows us to give row numbers to the records that we select from the table. Using this row number we can apply our custom pagination logic within the SQL Server Stored Procedure.
I am using the Customers table Microsoft’s NorthWind Database for this article and below is the Stored Procedure that allows us to fetch records page wise.
ASP.Net GridView fetches all the records and then displays one page from the fetched records. Thus for illustration if your table has 1000 records and you want to display only 50 records per page, GridView will fetch all 1000 records throw away the 950 records and display the 50 records based on the page index selected by the users.
In Stored Procedure
SQL Server 2005 came up with the new ROW_NUMBER() keyword that allows us to give row numbers to the records that we select from the table. Using this row number we can apply our custom pagination logic within the SQL Server Stored Procedure.
I am using the Customers table Microsoft’s NorthWind Database for this article and below is the Stored Procedure that allows us to fetch records page wise.
Above I am passing the PageIndex, PageSize as input parameters so that we can get the records for the preferred page index. And for populating the Pager in front end we will need the total number of records in the table which we are fetching using the RecordCount parameter.
HTML Markup
The HTML markup is quite simple it has a GridView, a DropDownLists selecting the Page Size and a Repeater which will be used for populating the pager.
Implementing the Custom Pagination
Now let’s start implementing the custom pagination in the code behind. First you will need to import the following namespaces
C#
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
VB.Net
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Data
Binding the GridView with Data
Below is the technique that will implement the stored procedure and bind the data to the ASP.Net GridView Control
In the last line of the above method we are calling the method described below to populate the pager Repeater control based on the record count that we fetch from the database
Below is the event that is raised when the Page Size DropDownList is changed. This method simply calls the GetCustomersPageWise() method.
At last the below event is executed when the page number LinkButton is clicked. This event makes a database call to get new set of records based on the PageIndex and PageSize
Posts
0 comments:
Post a Comment