It is very time consuming process when there is more than 3000 records and you need to show in grid. if you use inbuilt pagination of the grid view, this will surely performance issue in it. To overcome this situation we need to implement the custom paging.
In .aspx page your gridview will look similar like this.
You need to add the repeater control in it.
In repeater control you need to add one link button.
<asp:LinkButton ID="lnkPage" runat="server" Text='' CommandArgument=''
Enabled='' OnClick="Page_Changed" Style="color: #006BBB;">
In PageLoad Call the following method, First Argument is PageIndex and Second Argument is Change in Goto (if combobox of multiple pages then you need to pass this argument)
GetCompanyPageWise(1, true);
private void GetCompanyPageWise(int pageIndex, bool changeinGoto)
{
string serachCondition = "";
serachCondition = "WHERE IsActive = 1";
SqlParameter[] sqlParameterArray = new SqlParameter[4];
sqlParameterArray[0] = new SqlParameter("@WhereCondition", serachCondition);
sqlParameterArray[1] = new SqlParameter("@PageIndex", pageIndex);
sqlParameterArray[2] = new SqlParameter("@PageSize", int.Parse(ddlPaging.SelectedValue));
SqlParameter param = new SqlParameter("@RecordCount", SqlDbType.Int, 4);
param.Direction = ParameterDirection.Output;
sqlParameterArray[3] = param;
DataSet dtset = new DataSet();
dtset = CommonLogic.ExecuteDataset(CommandType.StoredProcedure, "", sqlParameterArray);
DataTable dt = new DataTable();
dt = dtset.Tables[0];
ViewState["mainData"] = dt;
int RecordCount = Convert.ToInt32(param.Value);
gridView.DataSource = dt;
gridView.DataBind();
double dblPageCount = (double)((decimal)RecordCount / decimal.Parse(ddlPaging.SelectedValue));
int pageCount = (int)Math.Ceiling(dblPageCount);
if (RecordCount == 0)
{
}
else if (RecordCount <= int.Parse(ddlPaging.SelectedValue))
{
lblShowingRecords.Text = "Showing 1 to " + RecordCount + " of " + RecordCount;
}
else if (pageIndex == 1)
{
lblShowingRecords.Text = "Showing 1 to " + ddlPaging.SelectedValue + " of " + RecordCount;
}
else if (pageIndex != 1 && pageIndex < (pageCount - 1))
{
lblShowingRecords.Text = "Showing " + ((pageIndex - 1) * int.Parse(ddlPaging.SelectedValue) + 1) + " to " + (pageIndex * int.Parse(ddlPaging.SelectedValue)) + " of " + RecordCount;
}
else if (pageIndex == pageCount)
{
lblShowingRecords.Text = "Showing " + ((pageIndex - 1) * int.Parse(ddlPaging.SelectedValue) + 1) + " to " + RecordCount + " of " + RecordCount;
}
PopulatePager(RecordCount, pageIndex, changeinGoto);
}
Next you need to create the method for the PopulatePager. This method will create a Pager Control through which u will going Change the Page.
private void PopulatePager(int recordCount, int currentPage, bool changeinGoto)
{
double dblPageCount = (double)((decimal)recordCount / decimal.Parse(ddlPaging.SelectedValue));
int pageCount = (int)Math.Ceiling(dblPageCount);
List pages = new List();
if (pageCount > 0)
{
for (int i = 1; i <= pageCount; i++)
{
pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
}
}
rptPager.DataSource = pages;
if ((pageCount - currentPage) 5)
{
rptPager.DataSource = pages.Skip(currentPage - 5).Take(10).ToList();
}
else
{
rptPager.DataSource = pages.Take(10).ToList();
}
rptPager.DataBind();
if (changeinGoto)
{
if (ddlGoto.DataSource != null)
{
ddlGoto.DataSource = null;
ddlGoto.DataBind();
}
ddlGoto.DataSource = pages;
ddlGoto.DataBind();
}
}
Here, rptPager is a repeater control which contains a linkbutton to navigate different page.
Next you need to create the function that will change the page the function called when you click on the page is as follows.
protected void Page_Changed(object sender, EventArgs e)
{
int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
ddlGoto.SelectedValue = pageIndex.ToString();
GetCompanyPageWise(pageIndex, false);
}
So that’s it all you need to do from code behind is completed, now you need to create an sql query regarding this.
The pagination query is as follows(It is a Stored Procedure and Where Condition will be passed as per the requirements.)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[proc_GetCompanyNameInPopup]
@PageIndex INT = 1,
@PageSize INT = 10,
@WhereCondition AS varchar(MAX),
@RecordCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Query as nVARCHAR(MAX)
DECLARE @RangeFrom as int
DECLARE @RangeTo as int
SET @RangeFrom = (@PageIndex-1) * @PageSize + 1
SET @RangeTo = (((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
SET @Query = 'Select CompanyId, CompanyName,
(select CampaignCode from Campaign_Master where id=tblCompany.[CompaignCode]) as CompaignCode,
[lookup].[name] as countryName,
ROW_NUMBER() OVER ( ORDER BY tblCompany.[CompanyId] DESC ) AS RowNumber INTO #ResultsAdmin1
FROM tblCompany left join [lookup]
ON tblCompany.CountryId = [Lookup].id ' + @WhereCondition + ' ORDER BY CompanyID DESC
SELECT @TotalCount = count(*) FROM #ResultsAdmin1
SELECT *
FROM #ResultsAdmin1
WHERE RowNumber BETWEEN ' + Convert(varchar(10), @RangeFrom) + ' AND ' + Convert(varchar(10), @RangeTo) + '
DROP TABLE #ResultsAdmin1 '
EXECUTE sp_executesql @Query, N'@TotalCount int OUTPUT', @TotalCount = @RecordCount OUTPUT
END
Sagar Rawal 