Prince Sultan University
Department of Computer & Information Sciences
CS489: Building E-Commerce Systems
Practice Sheet3.1: Building the Product Database (Phase II)
Objective:
The main purpose of this practice sheet is to complete the work we started in the last practice sheet. Last time we demonstrated how the department table may be built and accessed from the business and presentation tiers. This time we add a category table, a product table and the relationships between tables. The necessary stored procedures will be added as well.
The Data Tier:
Step 1: Create the category table:
Remember that according to the structure of our shop, each department includes several categories of products. Each category may appear only in one department, while each product may appear in more than one category. This means that the relation between departments and categories is One-to-Many, while the relations between categories and products is Many-to-Many.
The category table consists of the following fields (columns)
CategoryID int (the primary key)
DepartmentID int (Foreign Key, needed to establish relationships)
Name varchar
Description varchar
Following the procedure used to create the department table in sheet 2, create the category table.
Step 2: Creating the One-to-Many relationship between categories and departments:
1- While the category table is selected, open the Table Designer Menu and choose Relationships.
2- In the dialog box that appears press Add to get the following dialog.
3- Click on the dotted ellipse to get the following dialog box, and fill the spaces as follows:
3- In the primary key combo box that appears select Department, in the foreign key combo box select Category.
4- Choose DepartmentID as the primary key and the foreign key.
5- The relationship box should look as in the above figure.
Step 3: Creating the product table:
Using the procedure followed in previous practices, create the Product table, which consists of the following fields:
ProductID int Primary key
Name varchar
Description varchar
Price money
Image1FileName varchar
Image2FileName varchar
OnCatalogPromotion bit
OnDepartmentPromotion bit
Step 4: Creating the Many-to-Many relationship:
To create the many-to-many relationship we need an intermediate table ProductCategory which appears in the figure below:
Set both keys as primary.
The above table is used to implement a Many-to-Many relationship. It is used as a bridge between the Product and Category tables. In order to use queries on such relations, one needs to use the join clause.
Step 5: Creating the Stored Procedures:
Once the above tables are populated with actual data, we can use stored procedures to implement queries on the database. In what follows we give the details of the required stored procedures:
GetDepartmentDetails:
CREATE PROCEDURE GetDepartmentDetails
(@DepartmentID INT)
AS
SELECT Name, Description
FROM Department
WHERE DepartmentID = @DepartmentID
RETURN
GetCategoryDetails:
CREATE PROCEDURE GetCategoryDetails
(@CategoryID INT)
AS
SELECT DepartmentID, Name, Description
FROM Category
WHERE CategoryID = @CategoryID
RETURN
GetCategriesInDepartment:
CREATE PROCEDURE GetCategoriesInDepartment
(@DepartmentID INT)
AS
SELECT CategoryID, Name, Description
FROM Category
WHERE DepartmentID = @DepartmentID
RETURN
GetProductDetails
CREATE PROCEDURE GetProductDetails
(@ProductID INT)
AS
SELECT Name, Description, Price, Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
FROM Product
WHERE ProductID = @ProductID
RETURN
GetProductsInCategory:
CREATE PROCEDURE GetProductsInCategory
(@CategoryID INT,
@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),
Product.ProductID, Name,
SUBSTRING(Description, 1, @DescriptionLength) + '...' AS Description,
Price, Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
FROM Product INNER JOIN ProductCategory
ON Product.ProductID = ProductCategory.ProductID
WHERE ProductCategory.CategoryID = @CategoryID
-- 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
RETURN
GetProductsOnDepartmentPromotion
CREATE PROCEDURE GetProductsOnDepartmentPromotion
(@DepartmentID INT,
@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 ProductID) AS Row,
ProductID, Name, SUBSTRING(Description, 1, @DescriptionLength) + '...' AS Description,
Price, Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
FROM
(SELECT DISTINCT Product.ProductID, Product.Name,
SUBSTRING(Product.Description, 1, @DescriptionLength) + '...' AS Description,
Price, Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
FROM Product INNER JOIN ProductCategory
ON Product.ProductID = ProductCategory.ProductID
INNER JOIN Category
ON ProductCategory.CategoryID = Category.CategoryID
WHERE Product.OnDepartmentPromotion = 1
AND Category.DepartmentID = @DepartmentID
) AS ProductOnDepPr
-- 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
RETURN
GetProductsOnCatalogPromotion
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
RETURN
The Business Tier:
Step 1: Update the web.config file:
Add the following lines to the <appSettings> element in web.config:
<add key="ProductsPerPage" value="6"/>
<add key="ProductDescriptionLength" value="60"/>
<add key="SiteName" value="WebShop1"/>
Step 2: Update the ShopConfiguration Class as follows:
Add the following lines in the variable declaration part at the top of the class:
// Store the number of products per page
private readonly static int productsPerPage;
// Store the product description length for product lists
private readonly static int productDescriptionLength;
// Store the name of your shop
private readonly static string siteName;
Add the following lines to the ShopConfiguration constructor:
productsPerPage = Int32.Parse(ConfigurationManager.AppSettings["ProductsPerPage"]);
productDescriptionLength = Int32.Parse(ConfigurationManager.AppSettings["ProductDescriptionLength"]);
siteName = ConfigurationManager.AppSettings["SiteName"];
Add the following properties to the class:
// Returns the maximum number of products to be displayed on a page
public static int ProductsPerPage
{
get
{
return productsPerPage;
}
}
// Returns the length of product descriptions in products lists
public static int ProductDescriptionLength
{
get
{
return productDescriptionLength;
}
}
// Returns the length of product descriptions in products lists
public static string SiteName
{
get
{
return siteName;
}
}
Step 3: Update the CatalogAccess Class:
Add the following struct declarations at the top of the class:
public struct DepartmentDetails
{
public string Name;
public string Description;
}
/// <summary>
/// Wraps category details data
/// </summary>
public struct CategoryDetails
{
public int DepartmentId;
public string Name;
public string Description;
}
/// <summary>
/// Wraps product details data
/// </summary>
public struct ProductDetails
{
public string Name;
public string Description;
public decimal Price;
public string Image1FileName;
public string Image2FileName;
public bool OnDepartmentPromotion;
public bool OnCatalogPromotion;
}
Add the following functions to the CatalogAccess class
// get department details
public static DepartmentDetails GetDepartmentDetails(string departmentId)
{
// get a configured DbCommand object
DbCommand comm = GenericDataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "GetDepartmentDetails";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@DepartmentID";
param.Value = departmentId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// execute the stored procedure
DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
// wrap retrieved data into a DepartmentDetails object
DepartmentDetails details = new DepartmentDetails();
if (table.Rows.Count > 0)
{
details.Name = table.Rows[0]["Name"].ToString();
details.Description = table.Rows[0]["Description"].ToString();
}
// return department details
return details;
}
// Get category details
public static CategoryDetails GetCategoryDetails(string categoryId)
{
// get a configured DbCommand object
DbCommand comm = GenericDataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "GetCategoryDetails";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@CategoryID";
param.Value = categoryId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// execute the stored procedure
DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
// wrap retrieved data into a CategoryDetails object
CategoryDetails details = new CategoryDetails();
if (table.Rows.Count > 0)
{
details.DepartmentId = Int32.Parse(table.Rows[0]["DepartmentID"].ToString());
details.Name = table.Rows[0]["Name"].ToString();
details.Description = table.Rows[0]["Description"].ToString();
}
// return department details
return details;
}
// Get product details
public static ProductDetails GetProductDetails(string productId)
{
// get a configured DbCommand object
DbCommand comm = GenericDataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "GetProductDetails";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@ProductID";
param.Value = productId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// execute the stored procedure
DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
// wrap retrieved data into a ProductDetails object
ProductDetails details = new ProductDetails();
if (table.Rows.Count > 0)
{
// get the first table row
DataRow dr = table.Rows[0];
// get product details
details.Name = dr["Name"].ToString();
details.Description = dr["Description"].ToString();
details.Price = Decimal.Parse(dr["Price"].ToString());
details.Image1FileName = dr["Image1FileName"].ToString();
details.Image2FileName = dr["Image2FileName"].ToString();
details.OnDepartmentPromotion = bool.Parse(dr["OnDepartmentPromotion"].ToString());
details.OnCatalogPromotion = bool.Parse(dr["OnCatalogPromotion"].ToString());
}
// return department details
return details;
}
// retrieve the list of categories in a department
public static DataTable GetCategoriesInDepartment(string departmentId)
{
// get a configured DbCommand object
DbCommand comm = GenericDataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "GetCategoriesInDepartment";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@DepartmentID";
param.Value = departmentId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// execute the stored procedure
return GenericDataAccess.ExecuteSelectCommand(comm);
}
// 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 = ShopConfiguration.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 = ShopConfiguration.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)ShopConfiguration.ProductsPerPage);
// return the page of products
return table;
}
// retrieve the list of products featured for a department
public static DataTable GetProductsOnDepartmentPromotion(string departmentId, string pageNumber, out int howManyPages)
{
// get a configured DbCommand object
DbCommand comm = GenericDataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "GetProductsOnDepartmentPromotion";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@DepartmentID";
param.Value = departmentId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@DescriptionLength";
param.Value = ShopConfiguration.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 = ShopConfiguration.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)ShopConfiguration.ProductsPerPage);
// return the page of products
return table;
}
// retrieve the list of products in a category
public static DataTable GetProductsInCategory(string categoryId, string pageNumber, out int howManyPages)
{
// get a configured DbCommand object
DbCommand comm = GenericDataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "GetProductsInCategory";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@CategoryID";
param.Value = categoryId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@DescriptionLength";
param.Value = ShopConfiguration.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 = ShopConfiguration.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)ShopConfiguration.ProductsPerPage);
// return the page of products
return table;
}
Handling input and output parameters:
You may have noticed that some of the stored procedures use input and output parameters. In such cases, ADO.NET requires that you add these parameters to the parameters collection. The following are a simple examples that show how parameters are added to a command parameter collection:
Example 1:
// get a configured DbCommand object
DbCommand comm = GenericDataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "GetDepartmentDetails";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@DepartmentID";
param.Value = departmentId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
Example 2:
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@HowManyProducts";
param.Direction = ParameterDirection.Output;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
This procedure is repeated for all required parameters.
Getting Information back from output parameters:
Once the stored procedure is executed, results may be obtained from its output parameters as follows:
// 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());
7