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