Pages

Saturday 26 January 2013

Paging in ASP.Net GridView Example

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

No comments:

Post a Comment