- Using the ROW_NUMBER function find all the products Where Row>=6 and Row<=10
- Create Stored Procedure
- with parameter @PageNumber INT, @ProductsPerPage INT, @HowManyProducts INT OUTPUT
- Create a table variable that will holds the product list
- Populate the table with INSERT INTO
- Return the OUT parameter @HowManyProducts
- Extract the request page of products
- Stored Procedure method with OUTPUT parameter @HowManyPages in .cs (class) file:
- It will pass all the input and output parameter
- Find the value of @HowManyProducts: int HowManyProducts = Int32.Parse(comm.Parameters ["@HowManyProducts"].Value.ToString());
- Calculate the HowManyPages = @HowManyProducts/ProductsPerPage
- Assign the data source to DataList or GridView
- In the .ASPX.CS file
- Call method created using stored procedure in STEP 3 with àinput parameter PageNumber and OUTPUT parameter @HowManyPages
- Now display paging controls or pager: Previous, Page Numbers, Next
Stored procedure:
ALTER procedure
[dbo].[GetPastAttendance]
(@PageNumber int,
@ItemsPerPage int,
@HowManyItems int
output)
AS
--declare a table variable
declare @Student Table
(RowNumber int,
AdmissionNumber int,
Surname varchar(30),
Forename varchar(30)
)
--populate the table
insert into @Student
select ROW_NUMBER() over (order by STUD_ID),
STUD_AdmissionNumber, STUD_Surname,STUD_Forename
from STUDStudentCurrent
--return the value of
@HowManyStudent
select @HowManyItems=count(AdmissionNumber) from @Student
--extract the requested page of
studetns
select AdmissionNumber,
Surname, Forename
from @Student
where RowNumber>=(@PageNumber-1)*@ItemsPerPage
and
RowNumber<=@PageNumber*@ItemsPerPage
Stored procedure method in class file:
//Retrieve student list of past
attendance for specific page
public static DataTable GetPastAttendance(int page, out int howManyPages)
{
// get a configured DbCommand object
DbCommand comm=GenericDataAccess.CreateCommand();
//set the stored procedure and return the
results
comm.CommandText="GetPastAttendance";
//create new parameter
DbParameter param =
comm.CreateParameter();
param.ParameterName = "@PageNumber";
param.Value = page;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new
parameter
param = comm.CreateParameter();
param.ParameterName = "@ItemsPerPage";
param.Value = Config.ItemsPerPage;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@HowManyItems";
param.Direction = ParameterDirection.Output;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// execute the stored procedure and save the
results in a DataTable
DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
int howManyItems = Int32.Parse(comm.Parameters["@HowManyItems"].Value.ToString());
howManyPages = (int)Math.Ceiling((double)howManyItems/(double)Config.ItemsPerPage);
return table;
}
In the web.config:
<!--application settings-->
<appSettings>
<add key="ItemsPerPage" value="20"/>
<add key="NumberOfPagerLinks" value="10"/>
<add key="ItemDescriptionLength" value="60"/>
</appSettings>
In
the Config.cs class file:
using System;
using
System.Data;
using
System.Data.Common;
using
System.Configuration;
/// <summary>
///
Summary description for WebConfig
/// </summary>
public static class Config
{
//The
major similarity between the readonly and const fields is that you aren’t
allowed to change their values
//inside
class methods or properties. The main difference is that whereas for constants
you need to set their value at
//the
time you write the code (their values must be known at compile-time), with
readonly fields you are allowed to
//dynamically
set their values in the class constructor.
private
readonly static
int itemsPerPage; //
Store the number of Items per page
private
readonly static
int itemDescriptionLength; // Store the Item description length for Item lists
public
static bool
pagerSettings;
private
static int
numberOfPagerLinks;
//Static
class members (such as static properties and static methods) can be called by
external
//classes
without creating an instance of the class first; instead, they can be called
directly using
//the
class name.
static
Config() //constructor
{
itemsPerPage = Int32.Parse(ConfigurationManager.AppSettings["ItemsPerPage"]);
itemDescriptionLength =
Int32.Parse(ConfigurationManager.AppSettings["ItemDescriptionLength"]);
pagerSettings = false;//used for pager
links
numberOfPagerLinks= Int32.Parse(ConfigurationManager.AppSettings["NumberOfPagerLinks"]);
}
//
Properties
//
Returns the maximum number of Items to be displayed on a page
public
static int
ItemsPerPage
{
get
{
return itemsPerPage;
}
}
//
Returns the length of Item descriptions in products lists
public
static int
ItemDescriptionLength
{
get
{
return itemDescriptionLength;
}
}
//get
& set the pager settings
public
static bool
PagerSettings
{
get
{
return pagerSettings;
}
set
{
pagerSettings = true;
}
}
public
static int
NumberOfPagerLinks
{
get
{
return numberOfPagerLinks;
}
}
}
In the user
control .aspx file:
<asp:Panel ID="pagerPanel"
runat="server">
<table id="pagerTable" cellpadding="0" cellspacing="5px" class="pagerTable">
<tr>
<td>
<asp:HyperLink ID="firstLink"
CssClass="pagerLink"
runat="server">First</asp:HyperLink>
</td>
<td >
<asp:HyperLink ID="previousLink"
CssClass="pagerLink"
runat="server">Previous</asp:HyperLink>
</td>
<td>
<asp:PlaceHolder ID="pagerPlaceHolder"
runat="server"></asp:PlaceHolder>
</td>
<td >
<asp:HyperLink ID="nextLink"
CssClass="pagerLink"
runat="server">Next</asp:HyperLink>
</td>
<td>
<asp:HyperLink ID="lastLink"
CssClass="pagerLink"
runat="server">Last</asp:HyperLink>
</td>
</tr>
</table>
</asp:Panel>
<br />
<asp:GridView ID="printListGridView"
runat="server"
AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="AdmissionNumber" HeaderText="AdmissionNumber"
SortExpression="AdmissionNumber"
/>
<asp:BoundField DataField="Surname" HeaderText="Surname"
SortExpression="Surname"
/>
</Columns>
</asp:GridView>
In the user
control .aspx.cs file:
protected void Page_Load(object
sender, EventArgs e)
{
if (!IsPostBack)
{
PopulateData();
}
}
private
void PopulateData()
{
string strPage = Request.QueryString["page"];
//currentPage=1 will help to display the pager when no page is selected
int currentPage = 1;
int howManyPages = 1;
//isChar character will determine if page number is numeric or not
bool isChar = false;
if (strPage == null)
strPage = "1";
try
{
currentPage = int.Parse(strPage);
}
catch
{
isChar = true;
}
//if isChar is true that means page number is charater then it won't
show any data
if (isChar == false)
{
DataTable table = StudentDataAccess.GetPastAttendance(currentPage,
out howManyPages);
//show the pager if there is data to display
pagerPanel.Visible = true;
LoadPager(strPage, currentPage, howManyPages);
//display data in GridView
BindGrid(table);
}
if (isChar == true)
{
//hide the pager if the is no data to display
pagerPanel.Visible
= false;
}
}
private void BindGrid(DataTable table)
{
printListGridView.DataSource = table;
printListGridView.DataBind();
}
private
void LoadPager(string
strPage, int currentPage, int howManyPages )
{
//set the firstLink url
NameValueCollection query = Request.QueryString;
string paramName, newQueryString = "?";
for (int i = 0; i <
query.Count; i++)
if (query.AllKeys[i] != null)
if ((paramName =
query.AllKeys[i].ToString()).ToUpper() != "PAGE")
newQueryString += paramName + "=" + query[i] + "&";
firstLink.NavigateUrl =
Request.Url.AbsolutePath + newQueryString
+ "Page=1" ;
//set the lastLink url
lastLink.NavigateUrl =
Request.Url.AbsolutePath + newQueryString
+ "Page=" + howManyPages;
//display pager in the beginning when there is no page query string (or
no page is selected).
if (strPage == null)
{
for (int i = 1; i
<=Config.NumberOfPagerLinks; i++)
{
HyperLink h = new HyperLink();
h.NavigateUrl =
Request.Url.ToString() + "&page="
+ i;
h.ID =
i.ToString();
h.Text =
i.ToString();
if (currentPage == i)
h.CssClass = "pagerLinkSelected";
else
h.CssClass
= "pagerLinkUnselected";
pagerPlaceHolder.Controls.Add(h);
pagerPlaceHolder.Controls.Add(new LiteralControl("
"));
}
}
//pager login for how many pager page links should be displayed
if (howManyPages>1 && currentPage<=howManyPages)
{
int biggerHalf, smallerHalf;
biggerHalf = ((int)Math.Ceiling((double)Config.NumberOfPagerLinks
/ 2));
smallerHalf = Config.NumberOfPagerLinks - biggerHalf;
//part of the logic so that selected page number appear in
the middle
if(currentPage<biggerHalf)
for (int i = 1 ; i
<= Config.NumberOfPagerLinks && i <= howManyPages; i++)
{
HyperLink h = new HyperLink();
query =
Request.QueryString;
newQueryString = "?";
for (int j = 0; j
< query.Count; j++)
if (query.AllKeys[j] != null)
if ((paramName =
query.AllKeys[j].ToString()).ToUpper() != "PAGE")
newQueryString += paramName + "="
+ query[j] + "&";
h.NavigateUrl = Request.Url.AbsolutePath + newQueryString
+ "Page=" + i;
h.ID =
i.ToString();
h.Text =
i.ToString();
if (currentPage == i)
h.CssClass = "pagerLinkSelected";
else
h.CssClass = "pagerLinkUnselected";
pagerPlaceHolder.Controls.Add(h);
pagerPlaceHolder.Controls.Add(new LiteralControl("
"));
}
//part of the logic so that selected page number appear in
the middle
if (currentPage >= biggerHalf)
{
//in the beginning when smallerHalf, biggerHalf and
currentPage have same value then
//loop need to go round once more
int start = currentPage - smallerHalf;
int end = currentPage + biggerHalf;
if (start<= 0)
{
start = 1;
end = currentPage + biggerHalf + 1;
}
//end if
for (int i = start; i
< end && i <= howManyPages; i++)
{
HyperLink h = new HyperLink();
query = Request.QueryString;
newQueryString = "?";
for (int j = 0; j
< query.Count; j++)
if (query.AllKeys[j] != null)
if ((paramName =
query.AllKeys[j].ToString()).ToUpper() != "PAGE")
newQueryString += paramName + "="
+ query[j] + "&";
h.NavigateUrl = Request.Url.AbsolutePath + newQueryString
+ "Page=" + i;
h.ID =
i.ToString();
h.Text =
i.ToString();
if (currentPage == i)
h.CssClass = "pagerLinkSelected";
else
h.CssClass = "pagerLinkUnselected";
pagerPlaceHolder.Controls.Add(h);
pagerPlaceHolder.Controls.Add(new LiteralControl("
"));
}
}
// create the Previous link
if (currentPage == 1)
previousLink.Enabled = false;
else
{
query =
Request.QueryString;
newQueryString
= "?";
for (int i = 0; i
< query.Count; i++)
if (query.AllKeys[i] != null)
if ((paramName =
query.AllKeys[i].ToString()).ToUpper() != "PAGE")
newQueryString += paramName + "="
+ query[i] + "&";
previousLink.NavigateUrl = Request.Url.AbsolutePath + newQueryString
+ "Page=" + (currentPage - 1).ToString();
}
// create the Next link
if (currentPage == howManyPages)
nextLink.Enabled = false;
else
{
query =
Request.QueryString;
newQueryString
= "?";
for (int i = 0; i
< query.Count; i++)
if (query.AllKeys[i] != null)
if ((paramName =
query.AllKeys[i].ToString()).ToUpper() != "PAGE")
newQueryString += paramName + "="
+ query[i] + "&";
nextLink.NavigateUrl = Request.Url.AbsolutePath + newQueryString +
"Page=" + (currentPage + 1).ToString();
}
}
}
No comments:
Post a Comment