This is manual paging in asp.net using c# and SQl server.
HTML Code:
==========
<div id="iddivtotal" runat="server" style="font-size: 16px;font-weight: 700; color: #3a3f9a;">Total record: <label id="lbltotalcount" runat="server"></label></div>
<asp:HiddenField ID="hdfPageNo" runat="server" />
<div class="bradcrum" id="Divbradcrum" runat="server">
<div>
<div><asp:LinkButton ID="lnk_back" runat="server" OnClick="lnk_back_Click">
<img src="../Content/images/Previous_Off.png" class="imgp" runat="server" id="idPrevious_Off" />
<img src="../Content/images/Previous_On.png" class="imgp" runat="server" id="idPrevious_On" /> </asp:LinkButton> |
<asp:DropDownList ID="ddlImageIndexing" runat="server" OnSelectedIndexChanged="ddlImageIndexing_SelectedIndexChanged" AutoPostBack="true" style="font-size: 12px;margin-bottom: 2px; width: 52px; padding-right: 10px;"></asp:DropDownList> |
<asp:LinkButton ID="lnk_next" OnClick="lnk_next_Click" runat="server">
<img src="../Content/images/Next_Off.png" class="imgp" runat="server" id="idNext_Off"/>
<img src="../Content/images/Next_On.png" class="imgp" runat="server" id="idNext_On" />
</asp:LinkButton>
</div>
</div>
</div>
Css Code:
==========
.imgp{ margin-top: 6.7px !important; }
.bradcrum { float:left; padding-top: 10px; padding-bottom: 10px;}
.displayr {margin-left: 21px; margin-top: 10px; margin-bottom: 2px; color: black; font-weight: 600;}
C# Code:
==========
public void studentReportlist(int pageindex)
{
int SupervisorId = Convert.ToInt32(Session["SupervisorAccountId"]);
int PageSize = Convert.ToInt32(ConfigurationManager.AppSettings["pagingsize"]);
DataTable dtResult = objSuperAdminDl.GetAllStudentReport(SupervisorId, pageindex, PageSize);
if (dtResult != null)
{
if (dtResult.Rows.Count > 0)
{
iddivtotal.Visible = true;
Divbradcrum.Visible = true;
ddlImageIndexing.Items.Clear();
int totalCount = Convert.ToInt32(dtResult.Rows[0]["totalrecord"]);
int totalpage = int.Parse(Math.Ceiling((Convert.ToDecimal(totalCount) / Convert.ToDecimal(PageSize))).ToString("0"));
for (int i = 1; i <= totalpage; i++)
{
ddlImageIndexing.Items.Add(new ListItem(i.ToString(), i.ToString()));
}
lbltotalcount.InnerText = totalCount.ToString();
ddlImageIndexing.SelectedValue = pageindex.ToString();
int rowNo = 1;
tblReportList += "<tr class='titlebar'><td class='width5' style='width:3%'></td><td class='width40' style='color: #fff'>اسم الطالب</td><td class='width10' align='center' style='color: #fff'>كتاب مقروء</td><td class='width10' align='center' style='color: #fff'>كتاب مرفوض</td><td class='width10' align='center' style='color: #fff'>قيد المراجعة</td><td class='width17' align='center' style='color: #fff'>المجموع الكلي للكتب</td></tr>";
foreach (DataRow dr in dtResult.Rows)
{
tblReportList += "<tr><td>" + Convert.ToString(dr["RowNum"]) + "-</td>" +
"<td>" + Convert.ToString(dr["Name"]) + "</td>" +
"<td align='center'>" + Convert.ToString(dr["Aproved"]) + "</td>" +
"<td align='center'>" + Convert.ToString(dr["Rejected"]) + "</td>" +
"<td align='center'>" + Convert.ToString(dr["Pending"]) + "</td>" +
"<td align='center'>" + Convert.ToString(dr["totalbook"]) + "</td>";
rowNo = rowNo + 1;
}
tbodyAllStudentreport.InnerHtml = tblReportList;
}
else
{
iddivtotal.Visible = false;
Divbradcrum.Visible = false;
tbodyAllStudentreport.InnerHtml = "<tr><td style='text-align: center;'>لا توجد أي سجلات مدرجة في الوقت الحالي.</td></tr>";
}
dtResult.Dispose();
}
DisableEnableLinkbutton();
}
#region Listing method
protected void lnk_back_Click(object sender, EventArgs e)
{
int Pageindex = Convert.ToInt32(ddlImageIndexing.SelectedIndex - 1) + 1;
studentReportlist(Pageindex);
}
protected void lnk_next_Click(object sender, EventArgs e)
{
int Pageindex = Convert.ToInt32(ddlImageIndexing.SelectedIndex + 1) + 1;
studentReportlist(Pageindex);
}
protected void ddlImageIndexing_SelectedIndexChanged(object sender, EventArgs e)
{
int Pageindex = Convert.ToInt32(ddlImageIndexing.SelectedValue);
studentReportlist(Pageindex);
}
protected void DisableEnableLinkbutton()
{
if (ddlImageIndexing.SelectedIndex == ddlImageIndexing.Items.Count - 1)
{
lnk_next.Enabled = false;
idNext_On.Visible = false;
idNext_Off.Visible = true;
}
else
{
lnk_next.Enabled = true;
idNext_Off.Visible = false;
idNext_On.Visible = true;
}
if (ddlImageIndexing.SelectedIndex == 0)
{
lnk_back.Enabled = false;
idPrevious_On.Visible = false;
idPrevious_Off.Visible = true;
}
else
{
lnk_back.Enabled = true;
idPrevious_Off.Visible = false;
idPrevious_On.Visible = true;
}
}
#endregion
BAL
==========
public DataTable GetAllStudentReport(int supperviosrid,int pageindex,int PageSize)
{
try
{
SqlParameter[] parameter = {
new SqlParameter("@supperviosrid",supperviosrid),
new SqlParameter("@Page", pageindex),
new SqlParameter("@Pagesize", PageSize)
};
dtResult = SqlHelper.ExecuteReader("Get_eachstudentreportlist", parameter);
return dtResult;
}
catch (Exception ex)
{
throw;
}
}
public static DataTable ExecuteReader(string cmdText, params SqlParameter[] commandParameters)
{
using (var connection = new SqlConnection(connectionString))
{
using (var command = new SqlCommand(cmdText, connection))
{
try
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(commandParameters);
connection.Open();
var dataReader = command.ExecuteReader();
DataTable dtResult = new DataTable();
dtResult.Load(dataReader);
return dtResult;
}
finally
{
connection.Dispose();
command.Dispose();
connection.Close();
}
}
}
}
SQL Procedure
==========
--select * from Tbl_StudentBook
CREATE proc [dbo].[Get_eachstudentreportlist] --2
@supperviosrid int,
@Page int =1,
@Pagesize int=100
as
begin
declare @totalrecored int
select * into #tempreport from
(
select ts.student_Id, ts.FirstName +' '+ts.SecondName as Name,count(1) totalbook,
(select count(1) from Tbl_StudentBook where student_Id=ts.student_Id and IsStatus='A') as Aproved
, (select count(1) from Tbl_StudentBook where student_Id=ts.student_Id and IsStatus='R') as Rejected
, (select count(1) from Tbl_StudentBook where student_Id=ts.student_Id and IsStatus='P') as Pending
,ROW_NUMBER() OVER (ORDER BY cast(ts.student_Id as int) asc) AS RowNum
from Tbl_StudentBook tsb
inner join tbl_student ts on ts.student_Id=tsb.student_Id
where ts.Supervisor_id=@supperviosrid and ts.IsActive=1 group by ts.student_Id ,ts.FirstName,ts.SecondName )myreporttbl
select @totalrecored =count(1) from #tempreport
select *,@totalrecored as totalrecord from #tempreport where #tempreport.RowNum BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)
end
