Store Procedure Code :-
CREATE PROCEDURE getDeals
@StartIndex int,
@PageSize int,
@TotalCount int OutPut
as
select @TotalCount=count(1) from mstrDeals;
WITH CTE AS
(
select top(@startIndex+@PageSize-1) ROW_NUMBER() OVER(ORDER BY creationdate) RowNumber,dealid,dealTitle
from mstrDeals
)
select * from CTE where RowNumber between @startIndex and (@startIndex+@PageSize-1)
Binding Grid Control Code:-
public void bindGrid(int currentPage)
{
int pageSize = 10;
int _TotalRowCount = 0;
string _ConStr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
using (SqlConnection con = new SqlConnection(_ConStr))
{
SqlCommand cmd = new SqlCommand("getDeals", con);
cmd.CommandType = CommandType.StoredProcedure;
int startRowNumber = ((currentPage - 1) * pageSize) + 1;
cmd.Parameters.AddWithValue("@StartIndex", startRowNumber);
cmd.Parameters.AddWithValue("@PageSize", pageSize);
SqlParameter parTotalCount = new SqlParameter("@TotalCount", SqlDbType.Int);
parTotalCount.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parTotalCount);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
_TotalRowCount = Convert.ToInt32(parTotalCount.Value);
grdCustomPagging.DataSource = ds;
grdCustomPagging.DataBind();
generatePager(_TotalRowCount, pageSize, currentPage);
}
}
<asp:GridView Width="500" runat="server" ID="grdCustomPagging">
</asp:GridView>
Generate Pager Code :
public void generatePager(int totalRowCount, int pageSize, int currentPage)
{
int totalLinkInPage = 5;
int totalPageCount = (int)Math.Ceiling((decimal)totalRowCount / pageSize);
int startPageLink = Math.Max(currentPage - (int)Math.Floor((decimal)totalLinkInPage / 2), 1);
int lastPageLink = Math.Min(startPageLink + totalLinkInPage - 1, totalPageCount);
if ((startPageLink + totalLinkInPage - 1) > totalPageCount)
{
lastPageLink = Math.Min(currentPage + (int)Math.Floor((decimal)totalLinkInPage / 2), totalPageCount);
startPageLink = Math.Max(lastPageLink - totalLinkInPage + 1, 1);
}
List<ListItem> pageLinkContainer = new List<ListItem>();
if (startPageLink != 1)
pageLinkContainer.Add(new ListItem("First", "1", currentPage != 1));
for (int i = startPageLink; i <= lastPageLink; i++)
{
pageLinkContainer.Add(new ListItem(i.ToString(), i.ToString(), currentPage != i));
}
if (lastPageLink != totalPageCount)
pageLinkContainer.Add(new ListItem("Last", totalPageCount.ToString(), currentPage != totalPageCount));
dlPager.DataSource = pageLinkContainer;
dlPager.DataBind();
}
protected void dlPager_ItemCommand(object source, DataListCommandEventArgs e)
{
if (e.CommandName == "PageNo")
{
bindGrid(Convert.ToInt32(e.CommandArgument));
}
}
<asp:DataList CellPadding="5" RepeatDirection="Horizontal" runat="server" ID="dlPager"
onitemcommand="dlPager_ItemCommand">
<ItemTemplate>
<asp:LinkButton Enabled='<%#Eval("Enabled") %>' runat="server" ID="lnkPageNo" Text='<%#Eval("Text") %>' CommandArgument='<%#Eval("Value") %>' CommandName="PageNo"></asp:LinkButton>
</ItemTemplate>
</asp:DataList>
