Introduction:
Sometimes we may need to build our own data objects instead of using DataSets & DataTables. But in this case, we will lose all paging and sorting facilities that is represented using DataSets & DataTables.
To overcome this small issue we'll have to write our own custom paging feature as well as with sorting.
In this article, we will go through writing custom data object (NWProductItem) and data object collection (NWProductItemCollection) that represents our database table records in northwind database. The data object will support sorting, through implementing the generic IComparer interface (NWProductItemComparer) that will be implicitly used by the data object collection that should be built using generics.
Also for supporting paging, we will write a custom stored procedure that returns paged record sets. We will used ObjectDataSource to exmine its facilities for paging using our Data Object classes.
This article will require you to know basics of Data Access Application block introduced in Microsoft Enterprise Library 2.0 as well as using of data controls.
Building Custom Stored Procedure:
The requirements of this procedure is based on the existing of sequence number in the records to enable paging the records, for example, display records that has id between 1 & 10, but what if record with id 9 is not there?! this will cause only 9 records to be displayed not 10. To workaround this problem we will use temporary table with identity column just to verify that our data is correct and there are not gaps in the data. The stored procedure is base on sending the first sequence number on each page beside defining page size to calculate range of sequences to be retrieved.
Listing 1: usp_GetProductsPage
CREATE PROC usp_GetProductsPage
(
@startNdx int,
@count int,
@catId int = NULL
)
AS
-- create a temporary table with the columns we are interested in
CREATE TABLE #ProductsPage
(
RecordID int IDENTITY PRIMARY KEY,
ProductID int,
ProductName nvarchar(40),
UnitPrice money,
UnitsInStock smallint,
CategoryName nvarchar(15),
CompanyName nvarchar(40),
QuantityPerUnit nvarchar(20),
UnitsOnOrder smallint,
ReorderLevel smallint,
Discontinued bit,
CategoryId int,
SupplierId int
)
-- fill the temp table with all the employees
INSERT INTO #ProductsPage
(
ProductID,
ProductName,
UnitPrice,
UnitsInStock,
CategoryName,
CompanyName,
QuantityPerUnit,
UnitsOnOrder,
ReorderLevel,
Discontinued,
CategoryID,
SupplierID
)
SELECT ProductID,ProductName,UnitPrice,UnitsInStock,CategoryName,
CompanyName,QuantityPerUnit,UnitsOnOrder,ReorderLevel,Discontinued,
CategoryID,SupplierID
FROM vw_ProductsList
--Where Clause should enable get all products or filtered by category (@catId IS NULL OR @catId=-1)= All Products
WHERE CategoryID = @catId OR @catId IS NULL OR @catId=-1
ORDER BY ProductID
-- declare two variables to calculate the range of records
-- to extract for the specified page
DECLARE @fromId int
DECLARE @toId int
SET @fromId = @startNdx
SET @toId = @startNdx + @count - 1
-- select the page of records
SELECT * FROM #ProductsPage
WHERE (RecordID BETWEEN @fromId AND @toId)
AND (CategoryID = @catId OR @catId IS NULL OR @catId=-1)
GO
Note: I'm using a view here named vw_ProductsList to simplify the query. This view is included with the source code of this article.
This procedure also support filtering the data by category, so you can get all products paged, or a specific category with and retrieve its paged products.
Later on this article we will discuss this procedure's parameters as it is related to ObjectDataSource control.
As we are going to do custom paging, it will be required to build another stored procedure that return row count of the retrieved products.
Listing 2: usp_GetProductsPage
CREATE PROC usp_GetProductsCount
(
@catId int = null
)
AS
SELECT COUNT(*) FROM vw_ProductsList
WHERE CategoryID = @catId OR @catId IS NULL
GO
Again we need to return the total row count, so this procedure should allow filtering using CategoryID.
Building Data Object Classes:
First of all we need to build a class that represent product record, we'll call it NWProductItem. This class will only contains properties that represent product fields in the database. Class full code is included in the downloadable code.
To build the Data Object Collection we will use generic. We wanted as simple as the following:
Listing 3: NWProductItemCollection Class
public class NWProductItemCollection : List<NWProductItem>
{
}
To make our NWProductItem class with its collection NWProductItemCollection support sorting, we have to implement IComparer interface as the following
Listing 4: NWProductItemComparer Class
public class NWProductItemComparer : System.Collections.Generic.IComparer<NWProductItem>
{
private string _sortBy = "PRODUCTNAME";
public NWProductItemComparer()
{
_sortBy = "PRODUCTNAME";
}
public NWProductItemComparer(string sortBy)
{
_sortBy = sortBy;
}
#region IComparer<NWProductItem> Members
public int Compare(NWProductItem x, NWProductItem y)
{
switch (_sortBy.ToUpper())
{
case "PRODUCTNAME":
case "PRODUCTNAME DESC":
return x.ProductName.ToLower().CompareTo(y.ProductName.ToLower());
case "QUANTITYPERUNIT":
case "QUANTITYPERUNIT DESC":
return x.QuantityPerUnit.ToLower().CompareTo(y.QuantityPerUnit.ToLower());
case "COMPANYNAME":
case "COMPANYNAME DESC":
return x.CompanyName.ToLower().CompareTo(y.CompanyName.ToLower());
case "DISCONTINUED":
case "DISCONTINUED DESC":
return x.Discontinued.CompareTo(y.Discontinued);
case "UNITPRICE":
case "UNITPRICE DESC":
return x.UnitPrice.CompareTo(y.UnitPrice);
case "UNITSINSTOCK":
case "UNITSINSTOCK DESC":
return x.UnitsInStock.CompareTo(y.UnitsInStock);
case "CATEGORYNAME":
case "CATEGORYNAME DESC":
return x.CategoryName.ToLower().CompareTo(y.CategoryName.ToLower());
default:
return x.ProductId.CompareTo(y.ProductId);
}
}
#endregion
}
In the constructor we are passing the sort expression which will be used by Compare method to implement the sorting as shown above.
After we are ready with our core class NWProductItem and its utilities (NWProductItemCollection & NWProductItemComparer), we will proceed to build the Data Object class that will retrieved the data from the database using the supplied stored procedures mentioned above.
We will call our class NWProductsBrl and it is a static class that uses Microsoft Enterprise Library's Data Application Block. It contain 2 static methods (GetProducts & GetProductsCount). The class code is as the following:
Listing 5: NWProductsBrl Class
[System.ComponentModel.DataObject()]
public static class NWProductsBrl
{
private static Database _db; //Database Class is a Data Application Block Class
static NWProductsBrl()
{
//Enterprise Library Data Application Block
//NWDB is configured in the dataConfiguration Section in Web.Config and a connection string
//is also configured in the ConnectionStrings Section
_db = DatabaseFactory.CreateDatabase("NWDB");
}
//Note Method parameters, categoryId, strtNdx & pageSize parameters are sent to the stored procedure
[System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)]
public static NWProductItemCollection GetProducts(int? categoryId, string sortBy, int strtNdx, int pageSize)
{
IDataReader reader = null;
try
{
//Retrieve Data from the database
reader = _db.ExecuteReader("usp_GetProductsPage", strtNdx, pageSize, categoryId);
//Create NWProductItemCollection
NWProductItemCollection itms = new NWProductItemCollection();
while (reader.Read())
{
//Fill record by record
NWProductItem itm = new NWProductItem();
itm.ProductId = reader.GetInt32(1);
itm.ProductName = reader.GetString(2);
itm.UnitPrice = (!reader.IsDBNull(3)) ? reader.GetDecimal(3) : 0;
itm.UnitsInStock = (!reader.IsDBNull(4)) ? (int)reader.GetInt16(4) : 0;
itm._categoryName = (!reader.IsDBNull(5)) ? reader.GetString(5) : "N/A";
itm._supplierName = (!reader.IsDBNull(6)) ? reader.GetString(6) : "N/A";
itm.QuantityPerUnit = (!reader.IsDBNull(7)) ? reader.GetString(7) : "N/A";
itm.UnitsOnOrder = (!reader.IsDBNull(8)) ? (int)reader.GetInt16(8) : 0;
itm.ReorderLevel = (!reader.IsDBNull(9)) ? (int)reader.GetInt16(9) : 0;
itm.Discontinued = (!reader.IsDBNull(10)) ? reader.GetBoolean(10) : false;
itm.CategoryId = (!reader.IsDBNull(11)) ? reader.GetInt32(11) : (int?)null;
itm.SupplierId = (!reader.IsDBNull(12)) ? reader.GetInt32(12) : (int?)null;
//Add record to the NWProductItemCollection
itms.Add(itm);
}
//Apply Sorting
itms.Sort(new NWProductItemComparer(sortBy));
//Reverse Sorting incase of DESC
if (sortBy.Contains("DESC"))
itms.Reverse();
return itms;
}
catch (Exception e)
{
throw e;
}
finally
{
if (reader != null && !reader.IsClosed)
{
reader.Dispose();
}
}
}
public static int GetProductsCount(int? categoryId, string sortBy, int strtNdx, int pageSize)
{
// sortBy, strtNdx & pageSize are dummy parameters but
//they have to be added to be used by ObjectDataSource
return (int)_db.ExecuteScalar("[usp_GetProductsCount]", categoryId);
}
}
Configuring ObjectDataSource:
Assuming we all know how to create a web form, adding grid view to it and configuring this grid view to be page-able and sort-able, we will proceed on how to configure our ObjectDataSource as show in the following screen shots.
Screenshot 1: Configuring select method and Sort option
Note that we are using a DropDownList here named cmbCategories which contains categories list. For simplicity, I built it using SqlDataSource just to focus on my subject so it is out of this article scope.
As you can see to configure sorting by entering SortParameterName which is the name of sort parameter (sortBy) in our GetProducts Method.
Screenshot 2: Configuring ObjectDataSource for paging
For paging, first we need to EnablePaging, then set MaximumRowParameterName which means pageSize in our GetProducts method, set the StartRowIndexParameterName to be strtNdx parameter in our method. Finally you need to specify the SelectCountMethod which should be GetProductsCount method. this method should have the same signature as the GetProductsMethod. if not it will produce a runtime error, you can try and see.
A final touch, when you test your page and select All from Categories drop down list, then select page number that big like 7 or 8 for example, then select another Category that have small product list which may not exceed 2 or 1 page, you'll notice that your grid disappear. To resolve this issue add an even handler to your cmbCategories DropDownList SelectedIndexChanged even and set your grid view PageIndex property to 0, for example gvProducts.PageIndex = 0;
Conclusion:
Custom paging is important in many cases, as paging using DataSet will require getting all records from the database and then apply paging. In our example we only retrieved the set of record set we need.
The way the custom paging procedure made is not unique, there are some other way like this:
DECLARE @pageSize int
SET @pageSize = 100
DECLARE @pageIndex int
SET @pageIndex = 1;
DECLARE @sql nvarchar(500)
DECLARE @pz nvarchar(3)
DECLARE @pi nvarchar(3)
SET @pz= CONVERT(nvarchar(3), @pageSize)
SET @pi = CONVERT(nvarchar(3), @pageSize*ABS(@pageIndex-1))
SET @sql = 'SELECT TOP ' + @pz + ' * FROM Production.Product '
SET @sql = @sql + ' WHERE ProductId NOT IN (SELECT TOP ' + @pi + ' ProductId FROM Production.Product) ORDER BY ProductId'
exec(@sql)
But I think the way we used in our article is more effective.
Anjum RizwieditedPosted Mar 12, 2008, 6:25 AMEdited Mar 12, 2008, 6:27 AM
Plz help me in implement custome pageing using oracle? Since rownum generated during select but when u apply order by it goes in disorder. there is no top key word in oracle. Plz help us.