ASP Snippets

Categories






Alerts

Free Alerts

Your email will always be
private and will not be shared.




Follow us on twitter.




Zoom In | Zoom Out


Author is awarded Most Valuable Professional award by Microsoft ASP/ASP.Net

Implement Pagination in ASP.Net Gridview Control

Author:Mudassar Khan

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) Handles GridView1.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.



Figure displaying 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.

Posted: May 05 2009, 19:21 by Mudassar Khan | Comments (0) RSS comment feed |
Filed under: ASP.Net | C# | GridView | VB.Net

Views: 5341
Page copy protected against web site content infringement by Copyscape


If you like this article, help us grow by bookmarking this page on any social bookmarking site.
Bookmark and Share













Community News





Web Hosting SpotLight


Consulting


For consulting and work related queries click here.



Advertise


Advertise with us. For more details click here.


Suggestions


Please provide your valuable suggesstions here.

This Site is hosted on

Lunarpages.com Web Hosting