Stored Procedure
CREATE PROCEDURE [dbo].[GetCertificate_Admin]
@StartIndex int,
@PageSize int,
@kiaid int,
@TotalCount int OutPut
as
select @TotalCount=count(1) from vw_Student_certificate ;
WITH CTE AS
(
select top(@startIndex+@PageSize-1) ROW_NUMBER() OVER(ORDER BY id ) RowNumber,Student_registration_id,Name,TP_Name,certificatepath,level_name,Course_Code,Course_name,Father_Name,Mother_Name,Gender,Adharcard_No,Catagory_name
from vw_Student_certificate
)
select * from CTE where RowNumber between @startIndex and (@startIndex+@PageSize-1)
--------------------------------
ASPX.CS
public void bindGrid(int currentPage)
{
DataSet ds = new DataSet();
try
{
int pageSize = 100;
int _TotalRowCount = 0;
string _ConStr = Luminious.Connection.Configuration.ConnectionString;
using (SqlConnection con = new SqlConnection(_ConStr))
{
SqlCommand cmd = new SqlCommand("[GetCertificate_Admin]", con);
cmd.CommandType = CommandType.StoredProcedure;
int startRowNumber = ((currentPage - 1) * pageSize) + 1;
cmd.Parameters.AddWithValue("@StartIndex", startRowNumber);
cmd.Parameters.AddWithValue("@PageSize", pageSize);
cmd.Parameters.AddWithValue("@kiaid", Session["CAId"] != null ? Convert.ToInt32(Session["CAId"].ToString()) : 0);
SqlParameter parTotalCount = new SqlParameter("@TotalCount", SqlDbType.Int);
parTotalCount.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parTotalCount);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
_TotalRowCount = Convert.ToInt32(parTotalCount.Value);
grd_certificate.DataSource = ds;
grd_certificate.DataBind();
generatePager(_TotalRowCount, pageSize, currentPage);
}
}
catch (SqlException ex)
{
ExceptionHandler.WriteException(ex.Message);
}
catch (Exception ex)
{
ExceptionHandler.WriteException(ex.Message);
}
finally
{
if (ds != null)
{
ds.Dispose();
}
}
}
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));
}
}
------------------------------
ASPX page
<asp:DataList CellPadding="5" CssClass="table table-bordered table-condensed" 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>
<asp:GridView ID="grd_certificate" AutoGenerateColumns="false" runat="server" CssClass="table table-bordered table-condensed table-hover table-responsive"
OnRowCommand="grd_certificate_RowCommand">
</asp:GridView>
reference:- aspsnippets.com/Articles/Paging-in-SQL-Server-Stored-Procedure-using-Common-Table-Expression-CTE.aspx
No comments:
Post a Comment