How to Implement Asp.Net GridView Pagination Using C#.Net & VB.Net

By
Advertisement
In this article i am going to explain about How to Implement Asp.Net   GridView Pagination Using C#.Net & VB.Net. This is achieved by using the AllowPaging,PageSize and OnPageIndexChanging properties of the GridView.

In my previous article i have explained about Convert Dataset Datatable To Json Data Array In Asp.Net C#.Net & VB.Net Or How To Use JavaScriptSerializer To Convert Datatable To Json Data Array ,How To Show Tooltip On Mouse Hover In Gridview In Using JQuery In ASP.Net C#.Net & VB.Net,SQL Script To Drop Multiple Tables,Procedures At Once ,SQL Script To Drop Multiple Tables,Procedures At Once ,Convert Dataset Datatable To Json Data Array,SQL Script To Drop Multiple Tables,Procedures At Once In Sql Server,Highlight,Change Color Of Row On Mouseover In Grid View Using CSS - C#.Net ASP.Net VB.Net,Programmatically Group Gridview Column Headers and many articles in C#.Net,ASP.Net,VB.Net,Grid View,Javascript,jQuery,SQL Server and many other topics.

For explanation purpose i have created a table called UserDetails in my local database and inserted some sample records. And also i have created a procedure called GetUsersDetails which will return all the users detail from the users table. below is the sql script used.

--Creating UserDetails Table
CREATE TABLE dbo.UserDetails
(
UserId INT IDENTITY PRIMARY KEY,
UserName VARCHAR(100),
Country VARCHAR(100),
MobileNo VARCHAR(100),
JoinedDate DATE,
)
GO

--Inserting Sample records into Users table
INSERT INTO UserDetails(UserName,Country,MobileNo,JoinedDate)
VALUES('Saxena','Mumbai','India','2012-06-01')

INSERT INTO UserDetails(UserName,Country,MobileNo,JoinedDate)
VALUES('Bansal','Bangalore','India','2013-06-01')

INSERT INTO UserDetails(UserName,Country,MobileNo,JoinedDate)
VALUES('Bhoto','Tokyo','Japan','2013-06-01')

INSERT INTO UserDetails(UserName,Country,MobileNo,JoinedDate)
VALUES('Pramod','California','USA','2010-06-01')

INSERT INTO UserDetails(UserName,Country,MobileNo,JoinedDate)
VALUES('Gupta','Delhi','India','2012-06-01')

INSERT INTO UserDetails(UserName,Country,MobileNo,JoinedDate)
VALUES('Robert','Some City','Canada','2011-06-01')

INSERT INTO UserDetails(UserName,Country,MobileNo,JoinedDate)
VALUES('Kanna','Chennai','India','2009-06-01')

INSERT INTO UserDetails(UserName,Country,MobileNo,JoinedDate)
VALUES('John','Austin','Australia','2014-06-01')

INSERT INTO UserDetails(UserName,Country,MobileNo,JoinedDate)
VALUES('Anthony','Bristol','UK','2015-06-01')

INSERT INTO UserDetails(UserName,Country,MobileNo,JoinedDate)
VALUES('Azhimo','City 1','Spain','2014-06-01')
GO

-- Procedure to fetch user details from users table
CREATE PROC dbo.GetUsersDetail
AS
BEGIN
SET NOCOUNT ON
 SELECT UserId,UserName,Country,MobileNo,JoinedDate FROM dbo.UserDetails
SET NOCOUNT OFF
END
GO

Now open the visual studio and create a new website. In the default.aspx file drag and drop the gridview control from the toolbox and set the AllowPaging,PageSize and OnPageIndexChanging properties like below mentioned html markup.

HTML MarkUp:

Asp.Net Gridview Pagination using C#.Net & VB.Net by .NetPickles


Now in the web.config file include the connection string information.

Web.Config:

  


Now please import the below mentioned namespaces in your code behind file to make use of it.

C#.Net:
using System;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

VB.Net:
Imports System.Web.UI.WebControls
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

After that in the code behind file i have written the code to fetch the data from the database and bind it to the gridview. And also i have written the code for gridview pagination. Below is the code i wrote.

C#.Net:
protected void Page_Load(object sender, EventArgs e)
{
    try
    {
        BindProducts();
    }
    catch (Exception ex)
    {
        // handle error
    }
}
    
private void BindProducts()
{
    //read connection string from web.config
    string connectionString = ConfigurationManager.ConnectionStrings["mydsn"].ConnectionString;
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            //Setting connection and command text to command object
            cmd.Connection = conn;
            cmd.CommandText = "dbo.GetUsersDetail";
            cmd.CommandType = CommandType.StoredProcedure;

            //Filling dataset with data from users table
            DataSet customers = new DataSet();
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            adapter.Fill(customers, "UsersDetails");

            //Binding data to grid view
            GridViewUserDetails.DataSource = customers.Tables["UsersDetails"];
            GridViewUserDetails.DataBind();
        }
    }
}
    
protected void GridViewUserDetails_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    GridViewUserDetails.PageIndex = e.NewPageIndex;
    BindProducts();
}

VB.Net:
Protected Sub Page_Load(sender As Object, e As EventArgs)
 Try
  BindProducts()
   ' handle error
 Catch ex As Exception
 End Try
End Sub

Private Sub BindProducts()
 'read connection string from web.config
 Dim connectionString As String = ConfigurationManager.ConnectionStrings("mydsn").ConnectionString
 Using conn As New SqlConnection(connectionString)
  Using cmd As New SqlCommand()
   'Setting connection and command text to command object
   cmd.Connection = conn
   cmd.CommandText = "dbo.GetUsersDetail"
   cmd.CommandType = CommandType.StoredProcedure

   'Filling dataset with data from users table
   Dim customers As New DataSet()
   Dim adapter As New SqlDataAdapter(cmd)
   adapter.Fill(customers, "UsersDetails")

   'Binding data to grid view
   GridViewUserDetails.DataSource = customers.Tables("UsersDetails")
   GridViewUserDetails.DataBind()
  End Using
 End Using
End Sub

Protected Sub GridViewUserDetails_PageIndexChanging(sender As Object, e As GridViewPageEventArgs)
 GridViewUserDetails.PageIndex = e.NewPageIndex
 BindProducts()
End Sub

Now if you run the code then you can see the page numbers below your grid. This page numbers are generated based on the total no of records and page size mentioned in the gridview.

The output will look like below.
Output of How to Implement Asp.Net GridView Pagination Using C#.Net & VB.Net

Download SourceCode:
Sourcecode of How to Implement Asp.Net GridView Pagination Using C#.Net & VB.Net

You May Also Like...

Do you like this post. Please share with your friends. Happy coding...

0 comments:

Post a Comment

Online Casino