Introduction:
Loading large sets of data efficiently is a common challenge in web development. In this article, we'll explore a practical approach to enhance the performance of an ASP.NET GridView by implementing a mechanism to load data based on selected page numbers. We'll use SQL Server's NorthWind and Pubs sample databases to illustrate the process.
Download Database:
Before diving into the implementation, ensure you have the necessary databases. You can download the NorthWind database from here
Create Stored Procedure
To efficiently retrieve data, we'll use the proc_getCustomerLoading stored procedure. This procedure takes two parameters, @pageIndex (representing the page number) and @pageSize (indicating the number of records per page). The procedure utilizes the ROW_NUMBER() function to paginate the results and optimize the data retrieval process.
Create procedure [dbo].[proc_getCustomerLoading](
@pageIndex int,
@pageSize int)
As
Begin
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER ( ORDER BY [CustomerID] ASC) AS RowNo,
[CustomerID],
[CompanyName],
[ContactName],
[City],
[Country]
INTO #LoadCustomer
FROM Customers;
DECLARE @recordCount AS INT;
SELECT @recordCount = count(*) FROM #LoadCustomer;
SELECT * FROM #LoadCustomer
WHERE RowNo BETWEEN (@pageIndex - 1) * @pageSize + 1 AND (((@pageIndex - 1) * @pageSize + 1) + @pageSize) - 1;
DROP TABLE #LoadCustomer;
End
HTML Markup - Adding Page Elements:
In the design page, we need to add a DropDownList and a GridView control. The DropDownList is populated with page numbers, and the GridView will display the paginated data based on user selection.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="GridviewLoadingData.aspx.cs" Inherits="LoadingData_Application.GridviewLoadingData" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div style="width:100%; padding-left:50px;">
<table>
<tr>
<td>
Page Number is :
<asp:DropDownList ID="ddl_PageIndex" runat="server" style="width:150px; padding:3px;" AutoPostBack="true" OnSelectedIndexChanged="ddl_PageIndex_SelectedIndexChanged">
<asp:ListItem Text="1"></asp:ListItem>
<asp:ListItem Text="2"></asp:ListItem>
<asp:ListItem Text="3"></asp:ListItem>
<asp:ListItem Text="4"></asp:ListItem>
<asp:ListItem Text="5"></asp:ListItem>
<asp:ListItem Text="6"></asp:ListItem>
<asp:ListItem Text="7"></asp:ListItem>
<asp:ListItem Text="8"></asp:ListItem>
<asp:ListItem Text="9"></asp:ListItem>
<asp:ListItem Text="10"></asp:ListItem>
<asp:ListItem Text="11"></asp:ListItem>
<asp:ListItem Text="12"></asp:ListItem>
<asp:ListItem Text="13"></asp:ListItem>
<asp:ListItem Text="14"></asp:ListItem>
<asp:ListItem Text="15"></asp:ListItem>
<asp:ListItem Text="16"></asp:ListItem>
<asp:ListItem Text="17"></asp:ListItem>
<asp:ListItem Text="18"></asp:ListItem>
<asp:ListItem Text="19"></asp:ListItem>
<asp:ListItem Text="20"></asp:ListItem>
</asp:DropDownList>
</td>
</tr>
</table>
</div>
<div style="height:300px; overflow:auto; width:100%;">
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White" BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px" CellPadding="4">
<Columns>
<asp:TemplateField HeaderText="Row Number">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bindundefined"RowNo") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bindundefined"RowNo") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="CustomerID">
<EditItemTemplate>
<asp:TextBox ID="TextBox2" runat="server" Text='<%# Bindundefined"CustomerID") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bindundefined"CustomerID") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Company Name">
<EditItemTemplate>
<asp:TextBox ID="TextBox3" runat="server" Text='<%# Bindundefined"CompanyName") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bindundefined"CompanyName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Contact Name">
<EditItemTemplate>
<asp:TextBox ID="TextBox4" runat="server" Text='<%# Bindundefined"ContactName") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Bindundefined"ContactName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="City">
<EditItemTemplate>
<asp:TextBox ID="TextBox5" runat="server" Text='<%# Bindundefined"City") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label5" runat="server" Text='<%# Bindundefined"City") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Country">
<EditItemTemplate>
<asp:TextBox ID="TextBox6" runat="server" Text='<%# Bindundefined"Country") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label6" runat="server" Text='<%# Bindundefined"Country") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
<HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
<PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" />
<RowStyle BackColor="White" ForeColor="#003399" />
<SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
<SortedAscendingCellStyle BackColor="#EDF6F6" />
<SortedAscendingHeaderStyle BackColor="#0D4AC4" />
<SortedDescendingCellStyle BackColor="#D6DFDF" />
<SortedDescendingHeaderStyle BackColor="#002876" />
</asp:GridView>
<br />
</div>
</form>
</body>
</html>
C# Coding - Handling Data Loading:
Now, let's implement the C# code to handle data loading, starting with the necessary namespaces and the Page_Load event.
//Namespaces
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Web.Services;
namespace LoadingData_Application
{
public partial class GridviewLoadingData : System.Web.UI.Page
{
static SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbCon"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
GridView1.DataSource = GetCustomerLoadingData(1, 10);
GridView1.DataBind();
}
}
}
}
Continuing with the C# code, let's implement the method to retrieve data based on page numbers and handle the DropDownList's SelectedIndexChanged event.
private static DataSet GetCustomerLoadingData(int gridpageIndex, int totalpageSize)
{
SqlCommand cmd = new SqlCommand("proc_getCustomerLoading", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@pageIndex", gridpageIndex);
cmd.Parameters.AddWithValue("@pageSize", totalpageSize);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
protected void ddl_PageIndex_SelectedIndexChanged(object sender, EventArgs e)
{
int no = Convert.ToInt16(ddl_PageIndex.SelectedItem.Text);
DataSet ds = GetCustomerLoadingData(no, 10);
if (ds.Tables[0].Rows.Count > 0)
{
GridView1.DataSource = ds;
GridView1.DataBind();
}
else
{
GridView1.EmptyDataText = " No Record Found....";
GridView1.DataBind();
}
}
}
}
Conclusion:
Efficiently loading data in ASP.NET GridView based on selected page numbers is crucial for a smooth user experience, especially when dealing with large datasets. By using stored procedures and proper pagination techniques, we can optimize data retrieval and enhance the overall performance of our web application.
0 Comments