Implement Pagination in ASP.Net Gridview Control

In this article I am explaining How to implement paging in ASP.Net GridView control in a simple and easy way.

The following properties of the GridView come into play when Paging needs to be added.

1. PageSize - Sets the Size of the Page for GridView control

2. PageIndexChanging – The event that will be triggered when the page link is clicked by the user.

Connection String

Below is the connection string used to connect to the SQL Server Database. In this example I am using the NorthWind Database which can be downloaded from here.

<connectionStrings>

   <add name="conString" connectionString="Data Source=.\SQLExpress;

    database=Northwind;Integrated Security=true"/>

</connectionStrings>

Below is the markup of the GridView control

<asp:GridView ID="GridView1" runat="server"

    AutoGenerateColumns = "false" Font-Names = "Arial"

    Font-Size = "11pt" AlternatingRowStyle-BackColor = "#C2D69B" 

    HeaderStyle-BackColor = "green" AllowPaging ="true"  

    OnPageIndexChanging = "OnPaging"

    PageSize = "10" >

   <Columns>

    <asp:BoundField ItemStyle-Width = "150px"

      DataField = "CustomerID" HeaderText = "CustomerID" />

    <asp:BoundField ItemStyle-Width = "150px"

      DataField = "City" HeaderText = "City"/>

    <asp:BoundField ItemStyle-Width = "150px"

      DataField = "Country" HeaderText = "Country"/>

    <asp:BoundField ItemStyle-Width = "150px"

      DataField = "PostalCode" HeaderText = "PostalCode"/>

   </Columns>

   <AlternatingRowStyle BackColor="#C2D69B"  />

</asp:GridView>    

    

As you can see above there are four columns in the GridView

1. Customer ID

2. City

3. Country

4. Postal Code

Below is the function that will execute the Query and retrieve the records from the database

C#

private DataTable GetData(SqlCommand cmd)

{

    DataTable dt = new DataTable();

    String strConnString = System.Configuration.ConfigurationManager

        .ConnectionStrings["conString"].ConnectionString;

    SqlConnection con = new SqlConnection(strConnString);

    SqlDataAdapter sda = new SqlDataAdapter();

    cmd.CommandType = CommandType.Text;

    cmd.Connection = con;

    try

    {

        con.Open();

        sda.SelectCommand = cmd;

        sda.Fill(dt);

        return dt;

    }

    catch (Exception ex)

    {

        throw ex;

    }

    finally

    {

        con.Close();

        sda.Dispose();

        con.Dispose();

    }

}

    

   

VB.Net

Private Function GetData(ByVal cmd As SqlCommand) As DataTable

    Dim dt As New DataTable()

    Dim strConnString As String = System.Configuration. _

     ConfigurationManager.ConnectionStrings("conString").ConnectionString()

    Dim con As New SqlConnection(strConnString)

    Dim sda As New SqlDataAdapter()

    cmd.CommandType = CommandType.Text

    cmd.Connection = con

    Try

       con.Open()

       sda.SelectCommand = cmd

       sda.Fill(dt)

    Return dt

    Catch ex As Exception

       Throw ex

    Finally

       con.Close()

       sda.Dispose()

       con.Dispose()

    End Try

 End Function

The above function simply executes the query and returns the set of rows in a datatable.

Finally I am calling the above function in Page Load event of the page and binding the returned dataset to the GridView control

C#

protected void Page_Load(object sender, EventArgs e)

{

    string strQuery = "select CustomerID,City,Country,PostalCode" +

        " from customers";

    SqlCommand cmd = new SqlCommand(strQuery);

    DataTable dt = GetData(cmd);

    GridView1.DataSource=dt;

    GridView1.DataBind(); 

}

    

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load

  Dim strQuery As String = "select CustomerID,City,Country,PostalCode" _

    & " from customers"

  Dim cmd As New SqlCommand(strQuery)

  Dim dt As DataTable = GetData(cmd)

  GridView1.DataSource = dt

  GridView1.DataBind()

End Sub

And now in order make the paging functionality work we will need to add the OnPageIndexChanging event of the GridView control

C#

protected void OnPaging(object sender, GridViewPageEventArgs e)

{

    GridView1.PageIndex = e.NewPageIndex;

    GridView1.DataBind();

}

VB.Net

Protected Sub OnPaging(ByVal sender As Object, ByVal e As GridViewPageEventArgs) HandlesGridView1.PageIndexChanging

  GridView1.PageIndex = e.NewPageIndex

  GridView1.DataBind()

End Sub

As you can see above I am assigning a new Page index to the GridView and the rebinding the GridView control.

The figure below displays the GridView with Paging enabled.

You can download the complete source in VB.Net and C# here

PaginginGridViewControl.zip (3.86 kb)

And the NorthWind Database can be downloaded from here.