Introduction
In this post we will learn how to implement Custom paging in Asp.net and how to overcome the bulk data binding issue with Asp.net grid. Load data faster in GridView or load a lac of records one by one in asp.net grid using custom paging.
Previous Updates
In previous articles we have learnt Encrypt Connection String in Web.config file. Convert DataSet to List or GenericList in Asp.Net. Top 30 Asp.Net interview question and answers.
Description
ASP.Net GridView fetches all the records and then displays one page from the fetched records. Thus for example if your table has 50,000 records and you need to display only 50 records per page, GridView will fetch all 50,000 records but display max 50 records on its page and discard all other 49,950 records based on the page index selected by the users.
Thus the above approach is quite inefficient in terms of both bandwidth and performance. With custom pagination we will fetch records per page based on the page index. Thus if our table has 50,000 records and we need to display only 50 records per page, then we will fetch only 50 records based on page index. Thus this will boost the performance of the application.
For this first and biggest approach is to write a stored Procedure which returns given range of data for our Grid Paging.
Stored Procedure For Pagination
ROW_NUMBER() keyword came with SQL Server 2005 that allows us to give row numbers to the records that we select from the table. Using this row number we can implement our custom pagination logic within the our Stored Procedure.
--
=============================================
CREATE PROCEDURE
SP_GetEmployee_PageWise
@PageIndex
INT = 1
,@PageSize INT = 10
,@RecordCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER
(
ORDER
BY [EmpID] ASC
)AS RowNumber
,[EmployeeCode]
,[EmployeeName]
,[Address]
INTO #TableData
FROM [Employee]
SELECT @RecordCount = COUNT(*)
FROM #TableData
SELECT * FROM #TableData
WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
DROP TABLE #TableData
END
GO
|
Aspx Code View
Here in aspx we need a Grid to display the data and a repeater for representing the Page numbers on the bottom of the Grid.
<div>
<asp:GridView ID="EmpGrid" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField HeaderText="Employee Code" DataField="EmployeeCode" />
<asp:BoundField HeaderText="Employee Name" DataField="EmployeeName" />
<asp:BoundField HeaderText="Address" DataField="Address" />
</Columns>
</asp:GridView>
<br />
<asp:Repeater ID="rptPaging" runat="server">
<ItemTemplate>
<asp:LinkButton ID="lnkPage" runat="server" Text='<%#Eval("Text") %>'
CommandArgument='<%# Eval("Value") %>' Enabled='<%# Eval("Enabled") %>' OnClick="Page_Changed"></asp:LinkButton>
</ItemTemplate>
</asp:Repeater>
</div>
|
Implement the Custom Paging In C#
Here i am sharing the code of Grid Binding. Change your connection string accordingly.
private void GetEmployee_PageWise(int pageIndex)
{
string constring= "Data Source=DESKTOP-2EL2H60\\SQLEXPRESS; Initial Catalog=TsetDB; Integrated Security=true;";
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("SP_GetEmployee_PageWise", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
cmd.Parameters.AddWithValue("@PageSize", 4);
cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
con.Open();
IDataReader idr = cmd.ExecuteReader();
EmpGrid.DataSource = idr;
EmpGrid.DataBind();
idr.Close();
con.Close();
int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
this.PopulatePager(recordCount, pageIndex);
}
}
}
|
Populate the Pager
populate the pager Repeater control based on the record count that we fetch from the database
private void PopulatePager(int recordCount, int currentPage)
{
double dblPageCount = (double)((decimal)recordCount / 4);
int pageCount = (int)Math.Ceiling(dblPageCount);
List<ListItem> pages = new List<ListItem>();
if (pageCount > 0)
{
pages.Add(new ListItem("First", "1", currentPage > 1));
for (int i = 1; i <= pageCount; i++)
{
pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
}
pages.Add(new ListItem("Last", pageCount.ToString(),
currentPage < pageCount));
}
rptPaging.DataSource = pages;
rptPaging.DataBind();
}
|
On Paging Click
Finally the below event is executed when the page number LinkButton is clicked. This event makes a database call to get new set of records based on the PageIndex and PageSize
protected void Page_Changed(object sender, EventArgs e)
{
int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
this.GetEmployee_PageWise(pageIndex);
}
|
After following all these steps now run your application and Output will looks like this.
Output
0 comments:
Post a Comment