Custom Paging / Page Navigation Theory

In Short:

1. Using the ROW_NUMBER function find all the products Where Row>=6 and Row<=10
2. 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
3. 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
4. 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

In Broad:
SQL Server 2008 has a ROW_NUMBER function that assigns consecutive row numbers, starting
with 1, for each row returned by a SELECT statement. Because numbering can only be guaranteed
to be consistent if a sorting criteria applies to the query, when using ROW_NUMBER, you also need
to specify a column on which the rows are ordered prior to being numbered:

SELECT ROW_NUMBER() OVER (ORDER BY ProductID) AS Row, Name
FROM Product


To retrieve five products, namely the sixth to the tenth products of the list, you transform
the previous query into a subquery and filter the results on the WHERE clause of the main query.


SELECT Row, Name
FROM(
SELECT ROW_NUMBER() OVER (ORDER BY ProductID) AS Row, Name
FROM Product
) AS ProductsWithRowNumbers
WHERE Row >= 6 AND Row <= 10



Using Table Variables
If you get a set of data that you need to make further operations on, you’re likely to need to save
it either as a temporary table or in a TABLE variable. Both temporary tables and TABLE variables
can be used just like normal tables, and are very useful for storing temporary data within the
scope of a stored procedure.
In the stored procedures that return pages of products, you’ll save the complete list of
products in a TABLE variable, allowing you to count the total number of products (so you can
tell the visitor the number of pages of products) before returning the specified page.
The code listing that follows shows you how to create a TABLE variable named @Products:

-- declare a new TABLE variable
DECLARE @Products TABLE
(RowNumber INT,
ProductID INT,
Name VARCHAR(50),
Description VARCHAR(5000))

After creating this variable, you’ll populate it with data using INSERT INTO:
-- populate the table variable with the complete list of products
INSERT INTO @Products
SELECT ROW_NUMBER() OVER (ORDER BY Product.ProductID) AS Row,
ProductID, Name, Description
FROM Product

You can then retrieve data from this table object like this:
-- extract the requested page of products
SELECT Name, Description FROM @Products
WHERE RowNumber >= 6 AND RowNumber <= 10



GetProductsOnCatalogPromotion
GetProductsOnCatalogPromotion returns a page of products that are on catalog promotion
(have the OnCatalogPromotion bit field set to 1). This stored procedure employs much of the
theory presented earlier in this chapter:
• The stored procedure saves the total number of products into the @HowManyProducts
variable.
• A TABLE variable holds the complete list of products.
• The ROW_NUMBER function implements paging.

CREATE PROCEDURE GetProductsOnCatalogPromotion
(@DescriptionLength INT,
@PageNumber INT,
@ProductsPerPage INT,
@HowManyProducts INT OUTPUT)
AS

-- declare a new TABLE variable
DECLARE @Products TABLE
(RowNumber INT,
ProductID INT,
Name VARCHAR(50),
Description VARCHAR(5000),
Price MONEY,
Image1FileName VARCHAR(50),
Image2FileName VARCHAR(50),
OnDepartmentPromotion bit,
OnCatalogPromotion bit)

-- populate the table variable with the complete list of products
INSERT INTO @Products
SELECT ROW_NUMBER() OVER (ORDER BY Product.ProductID),
ProductID, Name,
SUBSTRING(Description, 1, @DescriptionLength) + '...' AS Description, Price,
Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
FROM Product
WHERE OnCatalogPromotion = 1

-- return the total number of products using an OUTPUT variable
SELECT @HowManyProducts = COUNT(ProductID) FROM @Products

-- extract the requested page of products
SELECT ProductID, Name, Description, Price, Image1FileName,
Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
FROM @Products
WHERE RowNumber > (@PageNumber - 1) * @ProductsPerPage
AND RowNumber <= @PageNumber * @ProductsPerPage

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.

Add this method to the CatalogAccess class:

// Retrieve the list of products on catalog promotion

public static DataTable GetProductsOnCatalogPromotion(string pageNumber,

out int howManyPages)

{

// get a configured DbCommand object

DbCommand comm = GenericDataAccess.CreateCommand();

// set the stored procedure name

comm.CommandText = "GetProductsOnCatalogPromotion";

// create a new parameter

DbParameter param = comm.CreateParameter();

param.ParameterName = "@DescriptionLength";

param.Value = BalloonShopConfiguration.ProductDescriptionLength;

param.DbType = DbType.Int32;

comm.Parameters.Add(param);

// create a new parameter

param = comm.CreateParameter();

param.ParameterName = "@PageNumber";

param.Value = pageNumber;

param.DbType = DbType.Int32;

comm.Parameters.Add(param);

// create a new parameter

param = comm.CreateParameter();

param.ParameterName = "@ProductsPerPage";

param.Value = BalloonShopConfiguration.ProductsPerPage;

param.DbType = DbType.Int32;

comm.Parameters.Add(param);

// create a new parameter

param = comm.CreateParameter();

param.ParameterName = "@HowManyProducts";

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);

// calculate how many pages of products and set the out parameter

int howManyProducts = Int32.Parse(comm.Parameters

["@HowManyProducts"].Value.ToString());

howManyPages = (int)Math.Ceiling((double)howManyProducts /

(double)BalloonShopConfiguration.ProductsPerPage);

// return the page of products

return table;

}

// Retrieve list of products on catalog promotion
list.DataSource =CatalogAccess.GetProductsOnCatalogPromotion(page, out howManyPages);
list.DataBind();

When visitor will click on the page number on the browser it will browse to the following address:

http://localhost/BalloonShop/Catalog.aspx?DepartmentID=1
http://localhost/BalloonShop/Catalog.aspx?DepartmentID=1&Page=1
http://localhost/BalloonShop/Catalog.aspx?DepartmentID=1&Category=5&Page=2



// Retrieve Page from the query string
string page = Request.QueryString["Page"];
if (page == null) page = "1";



Add this in ASPX.CS file 
// display paging controls

if (howManyPages > 1)

{

// have the current page as integer

int currentPage = Int32.Parse(page);

// make controls visible

pagingLabel.Visible = true;

previousLink.Visible = true;

nextLink.Visible = true;

// set the paging text

pagingLabel.Text = "Page " + page + " of " + howManyPages.ToString();

// create the Previous link

if (currentPage == 1)

previousLink.Enabled = false;

else

{

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] + "&";

previousLink.NavigateUrl = Request.Url.AbsolutePath + newQueryString

+ "Page=" + (currentPage - 1).ToString();

}

// create the Next link

if (currentPage == howManyPages)

nextLink.Enabled = false;

else

{

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] + "&";

nextLink.NavigateUrl = Request.Url.AbsolutePath + newQueryString +

"Page=" + (currentPage + 1).ToString();

}

}

Exercise: Adding New Configuration SettingsExercise: Adding New Configuration Settings

Open web.config and add the following entries to the <appSettings> node:

<appSettings>

<add key="MailServer" value="localhost" />

<add key="EnableErrorLogEmail" value="true" />

<add key="ErrorLogEmail" value="errors@yourballoonshopxyz.com" />

<add key="ProductsPerPage" value="6"/>

<add key="ProductDescriptionLength" value="60"/>

<add key="SiteName" value="BalloonShop"/>

</appSettings>

Note:
NameValueCollection allows many values for one key. It is found in System.Collections.Specialized. The NameValueCollection defines an AllKeys property. We use it in a foreach-loop to see all the keys in the collection.



/scripts/directory-lookup.asp?name=fred&age=22 
If the following script is used:
The unparsed query string is:  <%=Request.QueryString %>

The output is:
The unparsed query string is: name=fred&age=22