Paging Through Large Amounts of Data Using SQL Query
with searching and sorting
CREATE TABLE
USE
[AdminPanel]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AdminUser](
[AdminID] [int] IDENTITY(1,1) NOT NULL,
[EmailAddress] [varchar](100) NULL,
[Password] [varchar](50) NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[CreatedOn] [datetime] NULL,
[CreatedBy] [int] NULL,
[ModifiedOn] [datetime] NULL,
[ModifiedBy] [int] NULL,
[IsStatus] [int] NULL,
CONSTRAINT
[PK_AdminUser] PRIMARY KEY
CLUSTERED
(
[AdminID] ASC
)WITH
(PAD_INDEX
= OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SQL Pagging Query
USE
[AdminPanel]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PS_AdminUserSelectAll]
(
@SortBy VARCHAR(50) = 'EmailAddress',
@SortOrder VARCHAR(4) = 'ASC',
@SearchValue VARCHAR(50) = '',
@IsStatus int = -1,
@PageNo int = 1,
@PageSize int = 10
)
AS
BEGIN
SET NOCOUNT
ON;
BEGIN TRY
BEGIN TRAN
------------------------- Get
Total Count -------------------------
SELECT COUNT(AU.AdminID) as TotalRecord
From AdminUser AU
WHERE AU.IsStatus = CASE WHEN @IsStatus >=
0 THEN @IsStatus ELSE
AU.IsStatus END
AND
(AU.EmailAddress LIKE +'%'+ @SearchValue + '%' OR
AU.FirstName LIKE +'%'+ @SearchValue + '%' OR
AU.LastName LIKE +'%'+ @SearchValue + '%' )
--------------------------
Select Data ----------------------------
SELECT *
FROM
(
SELECT ROW_NUMBER()over
(
ORDER BY
CASE WHEN @SortBy = 'EmailAddress' AND
@SortOrder = 'ASC'
then EmailAddress END
ASC,
CASE WHEN @SortBy = 'EmailAddress' AND
@SortOrder = 'DESC'
then EmailAddress END
DESC,
CASE WHEN @SortBy = 'FirstName' AND
@SortOrder = 'ASC'
then FirstName END
ASC,
CASE WHEN @SortBy = 'FirstName' AND
@SortOrder = 'DESC'
then FirstName END
DESC,
CASE WHEN @SortBy = 'LastName' AND
@SortOrder = 'ASC'
then LastName END
ASC,
CASE WHEN @SortBy = 'LastName' AND
@SortOrder = 'DESC'
then LastName END
DESC
) as RowNumber,[AdminID],[EmailAddress],[Password],[FirstName],[LastName]
,[CreatedOn],[CreatedBy],[ModifiedOn],[ModifiedBy],[IsStatus]
,[Status]
= CASE AU.IsStatus
WHEN 0 THEN 'InActive'
WHEN 1 THEN 'Active'
ELSE 'InActive' END
FROM [AdminUser] AU
WHERE AU.IsStatus = CASE WHEN @IsStatus >=
0 THEN @IsStatus ELSE
AU.IsStatus END
AND
(AU.EmailAddress LIKE +'%'+ @SearchValue + '%' OR
AU.FirstName
LIKE +'%'+ @SearchValue + '%' OR
AU.LastName
LIKE +'%'+ @SearchValue + '%' )
)WAU
WHERE WAU.RowNumber BETWEEN ((@PageNo - 1) * @PageSize + 1) AND (@PageNo * @PageSize)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
END
ASP.net Page
<div class="portlet
box light-grey">
<div class="portlet-title">
<div class="caption">
Search Option</div>
</div>
<div class="form-body">
<div class="row">
<div class="col-md-6">
<div class="form-group">
<label class="control-label
col-md-3">
KeyWord</label>
<div class="col-md-6">
<asp:TextBox ID="txtSerachKeyword"
runat="server"
class="form-control"
placeholder="Serch
by KeyWord"></asp:TextBox>
</div>
</div>
</div>
<!--/span-->
<div class="col-md-4">
<div class="form-group ">
<label class="control-label
col-md-6">
Status</label>
<div class="col-md-4">
<asp:DropDownList ID="ddlStatus"
runat="server"
class="form-control">
<asp:ListItem Value="-1">All</asp:ListItem>
<asp:ListItem Value="0">InActive</asp:ListItem>
<asp:ListItem Value="1">Active</asp:ListItem>
</asp:DropDownList>
</div>
</div>
</div>
<!--/span-->
</div>
</div>
<div class="form-actions right margin-top-10">
<div class="col-md-offset-3 col-md-9">
<asp:Button ID="btnSearch" runat="server" CssClass="btn blue" Text="Search" OnClick="btnSearch_Click" />
<asp:Button ID="btnReset" runat="server" CssClass="btn default" Text="Reset" OnClick="btnReset_Click" />
</div>
</div>
</div>
<div class="portlet box light-grey">
<div class="portlet-title">
<div class="caption">
Admin Users</div>
</div>
<div class="portlet-body">
<div class="table-toolbar">
<div class="btn-group">
<a href="AddAdminUser.aspx">
<button class="btn green"
onclick="javascript:window.location='AddAdminUser.aspx'">
Add New <i class="icon-plus"></i>
</button>
</a>
</div>
<div class="btn-group pull-right">
<button class="btn dropdown-toggle" data-toggle="dropdown">
Option <i class="icon-angle-down"></i>
</button>
<ul class="dropdown-menu pull-right">
<li>
<asp:LinkButton ID="btnActive"
OnClick="btnActive_Click"
runat="server"
Text="Active
All" />
</li>
<li>
<asp:LinkButton ID="btnInActive"
OnClick="btnInActive_Click"
runat="server"
Text="InActive
All" />
</li>
<li>
<asp:LinkButton ID="btnDelete"
OnClick="btnDelete_Click"
runat="server"
Text="Delete
All" />
</li>
</ul>
</div>
</div>
<div id="divList" runat="server" class="dataTables_wrapper form-inline" role="grid">
<div class="row">
<div class="col-md-12">
<div id="sample_1_length" class="dataTables_length">
<table width="100%">
<tr>
<td style="width: 75px;">
<asp:DropDownList ID="ddlPageSize"
AutoPostBack="true"
runat="server"
class="form-control"
OnSelectedIndexChanged="ddlPageSize_SelectedIndexChanged">
<asp:ListItem Value="10" Selected="True">10</asp:ListItem>
<asp:ListItem Value="20">20</asp:ListItem>
<asp:ListItem Value="30">30</asp:ListItem>
<asp:ListItem Value="40">40</asp:ListItem>
<asp:ListItem Value="50">50</asp:ListItem>
<asp:ListItem Value="60">60</asp:ListItem>
<asp:ListItem Value="70">70</asp:ListItem>
<asp:ListItem Value="80">80</asp:ListItem>
<asp:ListItem Value="90">90</asp:ListItem>
<asp:ListItem Value="100">100</asp:ListItem>
</asp:DropDownList>
<td>
records per page
</td>
</td>
</tr>
</table>
</div>
</div>
</div>
<asp:Repeater ID="rptRepeater" runat="server" OnItemCommand="rptRepeater_ItemCommand"
OnItemDataBound="rptRepeater_ItemDataBound">
<HeaderTemplate>
<table class="table
table-striped table-bordered table-hover dataTable">
<thead>
<tr role="row">
<th style="width: 20px;">
<asp:CheckBox ID="chkCheckALL"
runat="server"
OnCheckedChanged="chkCheckALL_CheckedChanged"
AutoPostBack="true"
/>
</th>
<th class="sortable">
<asp:LinkButton ID="btnEmail"
OnClick="btnSorting_Click"
runat="server"
Text="Email"
CommandName="EmailAddress"
/>
</th>
<th class="hidden-480
sortable">
<asp:LinkButton ID="btnFirstName"
OnClick="btnSorting_Click"
runat="server"
Text="First
Name"
CommandName="FirstName"
/>
</th>
<th class="hidden-480
sortable">
<asp:LinkButton ID="btnLastName"
OnClick="btnSorting_Click"
runat="server"
Text="Last
Name"
CommandName="LastName"
/>
</th>
<th style="width: 70px;" class="hidden-480 sorting_disabled">
Status
</th>
<th style="width: 115px;" class="sorting_disabled text-center">
Option
</th>
</tr>
</thead>
<tbody>
</HeaderTemplate>
<ItemTemplate>
<tr id="trdataitem" runat="server">
<td style="width: 20px;">
<asp:CheckBox ID="chkBox" runat="server"
value='<%# Eval("AdminID")%>' />
</td>
<td class=" ">
<%# Eval("EmailAddress")%>
</td>
<td class="hidden-480
">
<%# Eval("FirstName")%>
</td>
<td class="hidden-480
">
<%# Eval("LastName")%>
</td>
<td class="hidden-480">
<span id="lblStatus"
runat="server">
<%# Eval("Status")%></span>
</td>
<td class="text-center">
<div class="btn-group">
<asp:Button ID="btnEdit"
runat="server"
CssClass="btn
btn-sm blue btn-editable" CommandName="EditRecord"
CommandArgument='<%#
Eval("AdminID")%>' Text="Edit" />
<asp:Button ID="btnDelete"
runat="server"
CssClass="btn
btn-sm red btn-removable"
CommandName="DeleteRecord"
CommandArgument='<%#
Eval("AdminID")%>' Text="Delete" />
</div>
</td>
</tr>
</ItemTemplate>
<FooterTemplate>
</tbody> </table>
</FooterTemplate>
</asp:Repeater>
<div class="row">
<div class="col-md-6 margin-bottom-10">
<div class="dataTables_info" id="lblDispRecordmsg"
runat="server">
</div>
</div>
<div class="col-md-6 margin-bottom-10">
<div class="dataTables_paginate
paging_bootstrap pull-right">
<asp:Button runat="server"
ID="btnPageFirst"
CssClass="btn
btn-default " Text="<<"
OnClick="btnPageFirst_Click"
/>
<asp:Button runat="server"
ID="btnPagePrevious"
CssClass="btn
btn-default " Text="<"
OnClick="btnPagePrevious_Click"
/>
<asp:DropDownList ID="ddCurrentPage"
runat="server"
AutoPostBack="True"
CssClass="form-control
input-xsmall"
Style="display: inline;" OnSelectedIndexChanged="ddCurrentPage_SelectedIndexChanged">
</asp:DropDownList>
<asp:Button runat="server"
ID="btnPageNext"
CssClass="btn btn-default
" Text=">"
OnClick="btnPageNext_Click"
/>
<asp:Button runat="server"
ID="btnPageLast"
CssClass="btn
btn-default " Text=">>"
OnClick="btnPageLast_Click"
/>
</div>
</div>
</div>
</div>
<div id="divNoRecord" runat="server">
</div>
</div>
</div>
CODE Behind
#region
"Declaration"
Pro_AdminUser objPro_AdminUser = null;
DAL_AdminUser objDAL_AdminUser = null;
#endregion
#region Properties
private string SortBy
{
get { return
string.IsNullOrEmpty(Convert.ToString(ViewState["SortBy"])) ?
string.Empty : Convert.ToString(ViewState["SortBy"]); }
set { ViewState["SortBy"] =
value; }
}
private string SortOrder
{
get { return
string.IsNullOrEmpty(Convert.ToString(ViewState["SortOrder"])) ?
string.Empty : Convert.ToString(ViewState["SortOrder"]); }
set { ViewState["SortOrder"]
= value; }
}
private int PageSize
{
get { return string.IsNullOrEmpty(Convert.ToString(ViewState["PageSize"]))
? 10 : Convert.ToInt32(ViewState["PageSize"]); }
set { ViewState["PageSize"] =
value; }
}
private int PageNo
{
get { return
string.IsNullOrEmpty(Convert.ToString(ViewState["PageNo"])) ? 1 :
Convert.ToInt32(ViewState["PageNo"]); }
set { ViewState["PageNo"] =
value; }
}
#endregion
#region PageEvent
protected void Page_Load(object sender,
EventArgs e)
{
if (!IsPostBack)
{
SortBy = string.Empty;
SortOrder = string.Empty;
if
(Request.QueryString["mode"] != null)
{
string strMsg = string.Empty;
if
(Convert.ToString(Request.QueryString["mode"]).Equals("INSERT"))
strMsg =
MessageHelper.RecordAddedSuccessfully;
else
strMsg =
MessageHelper.RecordUpdatedSuccessfully;
HelperFunctions.DisplayMessage(strMsg,
HelperFunctions.MessageType.SUCCESS, this);
}
HelperFunctions.Header("Admin
Users", "Manage Admin Users", this);
HelperFunctions.BreadCrumb("Admin",this);
BindData();
}
}
#endregion
#region ButtonEvent
protected void btnActive_Click(object
sender, EventArgs e)
{
try
{
if
(MultiProcess("MULTIACTIVE"))
HelperFunctions.DisplayMessage(MessageHelper.RecordActivatedSuccessfully,
HelperFunctions.MessageType.SUCCESS, this);
}
catch (Exception ex)
{
HelperFunctions.DisplayMessage(ex.Message,
HelperFunctions.MessageType.ERROR, this);
}
}
protected void btnInActive_Click(object
sender, EventArgs e)
{
try
{
if
(MultiProcess("MULTIINACTIVE"))
HelperFunctions.DisplayMessage(MessageHelper.RecordInActivatedSuccessfully,
HelperFunctions.MessageType.SUCCESS, this);
}
catch (Exception ex)
{
HelperFunctions.DisplayMessage(ex.Message,
HelperFunctions.MessageType.ERROR, this);
}
}
protected void btnDelete_Click(object
sender, EventArgs e)
{
try
{
if
(MultiProcess("MULTIDELETE"))
HelperFunctions.DisplayMessage(MessageHelper.RecordDeletedSuccessfully,
HelperFunctions.MessageType.SUCCESS, this);
}
catch (Exception ex)
{
HelperFunctions.DisplayMessage(ex.Message,
HelperFunctions.MessageType.ERROR, this);
}
}
protected void
ddlPageSize_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
PageSize =
Convert.ToInt32(ddlPageSize.SelectedValue);
PageNo = 1;
BindData();
}
catch (Exception ex)
{
HelperFunctions.DisplayMessage(ex.Message,
HelperFunctions.MessageType.ERROR, this);
}
}
#endregion
#region "Repeater Events"
protected void
rptRepeater_ItemCommand(object source, RepeaterCommandEventArgs e)
{
if (e.CommandName ==
"EditRecord")
{
Response.Redirect("AddAdminUser.aspx?type=edit&id="
+ Convert.ToString(e.CommandArgument));
}
else if (e.CommandName ==
"DeleteRecord")
{
try
{
objDAL_AdminUser = new
DAL_AdminUser();
objDAL_AdminUser.Delete(Convert.ToInt32(e.CommandArgument));
HelperFunctions.DisplayMessage(MessageHelper.RecordDeletedSuccessfully,
HelperFunctions.MessageType.SUCCESS, this);
}
catch (Exception ex)
{
HelperFunctions.DisplayMessage(ex.Message,
HelperFunctions.MessageType.ERROR, this);
}
finally
{
objDAL_AdminUser = null;
}
BindData();
}
}
bool RowFlag = true;
protected void
rptRepeater_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
if (e.Item.ItemType ==
ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
Pro_AdminUser dataitem =
(Pro_AdminUser)e.Item.DataItem;
HtmlGenericControl lblStatus =
(HtmlGenericControl)e.Item.FindControl("lblStatus");
HtmlTableRow trdataitem =
(HtmlTableRow)e.Item.FindControl("HtmlTableRow");
if (lblStatus != null &&
dataitem != null)
{
if (dataitem.IsStatus == 0)
lblStatus.Attributes.Add("class", "label label-sm
label-danger");
else if (dataitem.IsStatus ==
1)
lblStatus.Attributes.Add("class", "label label-sm
label-success");
else
lblStatus.Attributes.Add("class", "label label-sm
label-default");
}
if (trdataitem != null)
{
if (RowFlag)
{
RowFlag = false;
trdataitem.Attributes.Add("class", "gradeX odd");
}
else
{
RowFlag = true;
trdataitem.Attributes.Add("class",
"gradeX even");
}
}
}
}
protected void
chkCheckALL_CheckedChanged(object sender, EventArgs e)
{
CheckBox chkCheckALL =
(CheckBox)sender;
for (int i = 0; i <
rptRepeater.Items.Count; i++)
{
CheckBox chkBox =
(CheckBox)rptRepeater.Items[i].FindControl("chkBox");
if (chkBox != null)
chkBox.Checked =
chkCheckALL.Checked;
}
}
protected void btnSorting_Click(object
sender, EventArgs e)
{
LinkButton btn = (LinkButton)sender;
if (SortBy.ToString() ==
btn.CommandName)
if (SortOrder.ToString() ==
"ASC")
SortOrder = "DESC";
else
SortOrder = "ASC";
else
SortOrder = "ASC";
SortBy = btn.CommandName;
BindData();
}
#endregion
#region PaggingEvent
protected void btnPageFirst_Click(object
sender, EventArgs e)
{
PageNo = 1;
BindData();
}
protected void btnPagePrevious_Click(object
sender, EventArgs e)
{
PageNo = PageNo - 1;
BindData();
}
protected void
ddCurrentPage_SelectedIndexChanged(object sender, EventArgs e)
{
PageNo =
Convert.ToInt32(ddCurrentPage.SelectedValue);
BindData();
}
protected void btnPageNext_Click(object
sender, EventArgs e)
{
PageNo = PageNo + 1;
BindData();
}
protected void btnPageLast_Click(object
sender, EventArgs e)
{
PageNo = ddCurrentPage.Items.Count;
BindData();
}
#endregion
#region "HelperFunction"
private void BindData()
{
try
{
int TotalRecord = 0;
objPro_AdminUser = new
Pro_AdminUser();
objDAL_AdminUser = new
DAL_AdminUser();
if
(string.IsNullOrEmpty(Convert.ToString(SortBy))) SortBy =
"EmailAddress";
if
(string.IsNullOrEmpty(Convert.ToString(SortOrder))) SortOrder =
"ASC";
objPro_AdminUser.SortBy =
SortBy.ToString();
objPro_AdminUser.SortOrder =
SortOrder.ToString();
objPro_AdminUser.SearchValue =
txtSerachKeyword.Text.Trim();
objPro_AdminUser.PageNo = PageNo;
objPro_AdminUser.PageSize =
PageSize;
objPro_AdminUser.IsStatus =
Convert.ToInt32(ddlStatus.SelectedValue);
List<Pro_AdminUser> objList =
objDAL_AdminUser.GetAllAdminUser(objPro_AdminUser, ref TotalRecord);
rptRepeater.DataSource = objList;
rptRepeater.DataBind();
if (objList.Count > 0)
{
int listCout = objList.Count;
/*--------------------------For
Pager Control----------------------*/
ddCurrentPage.Items.Clear();
float Mod = TotalRecord %
PageSize;
for (int i = 0; Mod == 0 ? i
< TotalRecord / PageSize : i <= TotalRecord / PageSize; i++)
{
ListItem li = new
ListItem();
li.Value = (i +
1).ToString();
li.Text = (i +
1).ToString();
ddCurrentPage.Items.Add(li);
}
ddCurrentPage.SelectedValue =
PageNo.ToString();
btnPageFirst.Enabled = true;
btnPagePrevious.Enabled = true;
btnPageLast.Enabled = true;
btnPageNext.Enabled = true;
if (TotalRecord <= PageSize)
{
btnPageFirst.Enabled =
false;
btnPagePrevious.Enabled =
false;
btnPageLast.Enabled =
false;
btnPageNext.Enabled =
false;
}
if (PageNo == 1)
{
btnPageFirst.Enabled =
false;
btnPagePrevious.Enabled =
false;
}
else if (PageNo ==
ddCurrentPage.Items.Count)
{
btnPageLast.Enabled =
false;
btnPageNext.Enabled =
false;
}
/*--------------------------For
Pager Control----------------------*/
/*--------------------------For
Record Message Control----------------------*/
int startRec, EndRec;
startRec =
((Convert.ToInt32(PageNo - 1) * Convert.ToInt32(PageSize)) + 1);
EndRec = ((PageNo + 1) *
Convert.ToInt32(ddlPageSize.SelectedValue));
lblDispRecordmsg.InnerHtml =
"Showing " + "<b>" + startRec.ToString() + " -
" + (startRec + listCout - 1).ToString() +
"</b>" +
" of " + "<b>" + TotalRecord.ToString() +
"</b> record(s).";
/*--------------------------For
Record Message Control----------------------*/
/*--------------------------For
List Control----------------------*/
divList.Visible = true;
divNoRecord.Visible = false;
btnActive.Visible = true;
btnDelete.Visible = true;
btnInActive.Visible = true;
/*--------------------------For
List Control----------------------*/
}
else
{
divList.Visible = false;
divNoRecord.Visible = true;
divNoRecord.InnerHtml = MessageHelper.NoRecordFound;
btnActive.Visible = false;
btnDelete.Visible = false;
btnInActive.Visible = false;
}
}
catch (Exception ex)
{
HelperFunctions.DisplayMessage(ex.Message,
HelperFunctions.MessageType.ERROR, this);
}
finally
{
objPro_AdminUser = null;
objDAL_AdminUser = null;
}
}
private bool MultiProcess(string strMode)
{
bool rtnFlag = false;
try
{
int ItemCount =
rptRepeater.Items.Count;
string MultiIDs = "";
for (int i = 0; i < ItemCount;
i++)
{
CheckBox chk =
(CheckBox)rptRepeater.Items[i].FindControl("chkBox");
if (chk.Checked)
MultiIDs +=
chk.Attributes["value"] + ",";
}
MultiIDs =
HelperFunctions.RemoveLastChar(MultiIDs);
if (MultiIDs != "")
{
objDAL_AdminUser = new
DAL_AdminUser();
objDAL_AdminUser.MultiProcess(strMode, MultiIDs);
PageNo = 1;
BindData();
rtnFlag = true;
}
else
{
HelperFunctions.DisplayMessage(MessageHelper.OneRecordRequired,
HelperFunctions.MessageType.ERROR, this);
rtnFlag = false;
}
}
catch (Exception ex)
{
HelperFunctions.DisplayMessage(ex.Message,
HelperFunctions.MessageType.ERROR, this);
}
finally
{
objDAL_AdminUser = null;
}
return rtnFlag;
}
#endregion
#region SearchOptionEvent
protected void btnSearch_Click(object
sender, EventArgs e)
{
PageNo = 1;
BindData();
}
protected void btnReset_Click(object
sender, EventArgs e)
{
ddlStatus.SelectedValue =
"-1";
txtSerachKeyword.Text = "";
BindData();
}
#endregion
Comments
Post a Comment