Beginning ASP.NET 2.0 E-Commerce in C#

CREATE DATABASE BalloonShop GO USE BalloonShop GO CREATE TABLE Department( DepartmentID INT IDENTITY(1,1) NOT NULL, Name VARCHAR(50) NOT NULL, Description VARCHAR(1000) NULL, CONSTRAINT PK_Department PRIMARY KEY CLUSTERED (DepartmentID ASC) ) GO CREATE PROCEDURE GetDepartments AS SELECT DepartmentID, Name, Description FROM Department GO INSERT INTO Department(Name, Description) VALUES ('Anniversary Balloons', 'These sweet balloons are the perfect gift for someone you love.') GO INSERT INTO Department(Name, Description ) VALUES ('Balloons for Children', 'The colorful and funny balloons will make any child smile!') GO CREATE TABLE Category( CategoryID INT IDENTITY(1,1) NOT NULL, DepartmentID INT NOT NULL, Name VARCHAR(50) NOT NULL, Description VARCHAR(1000) NULL, CONSTRAINT PK_Category_1 PRIMARY KEY CLUSTERED(CategoryID ASC) ) GO ALTER TABLE Category ADD CONSTRAINT FK_Category_Department FOREIGN KEY(DepartmentID) REFERENCES Department (DepartmentID) GO CREATE TABLE Product( ProductID INT IDENTITY(1,1) NOT NULL, Name VARCHAR(50) NOT NULL, Description VARCHAR(5000) NOT NULL, Price MONEY NOT NULL, Image1FileName VARCHAR(50) NULL, Image2FileName VARCHAR(50) NULL, OnCatalogPromotion BIT NOT NULL, OnDepartmentPromotion BIT NOT NULL, CONSTRAINT PK_Product PRIMARY KEY CLUSTERED (ProductID ASC) ) GO CREATE TABLE ProductCategory( ProductID INT NOT NULL, CategoryID INT NOT NULL, CONSTRAINT PK_ProductCategory PRIMARY KEY CLUSTERED (ProductID ASC, CategoryID ASC) ) GO ALTER TABLE ProductCategory WITH CHECK ADD CONSTRAINT FK_ProductCategory_Category FOREIGN KEY(CategoryID) REFERENCES Category (CategoryID) GO ALTER TABLE ProductCategory WITH CHECK ADD CONSTRAINT FK_ProductCategory_Product FOREIGN KEY(ProductID) REFERENCES Product (ProductID) GO CREATE PROCEDURE GetCategoriesInDepartment (@DepartmentID INT) AS SELECT CategoryID, Name, Description FROM Category WHERE DepartmentID = @DepartmentID GO CREATE PROCEDURE GetDepartmentDetails (@DepartmentID INT) AS SELECT Name, Description FROM Department WHERE DepartmentID = @DepartmentID GO CREATE PROCEDURE GetCategoryDetails (@CategoryID INT) AS SELECT DepartmentID, Name, Description FROM Category WHERE CategoryID = @CategoryID GO 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 GO 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 GO 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 GO CREATE PROCEDURE GetProductDetails (@ProductID INT) AS SELECT Name, Description, Price, Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion FROM Product WHERE ProductID = @ProductID RETURN GO INSERT INTO Category (DepartmentID, Name, Description ) VALUES (1, 'Love & Romance', 'Here''s our collection of balloons with romantic messages.') GO INSERT INTO Category (DepartmentID, Name, Description ) VALUES (1, 'Birthdays', 'Tell someone "Happy Birthday" with one of these wonderful balloons!') GO INSERT INTO Category (DepartmentID, Name, Description ) VALUES (1, 'Weddings', 'Going to a wedding? Here''s a collection of balloons for that special event!') GO INSERT INTO Category (DepartmentID, Name, Description ) VALUES (2, 'Message Balloons', 'Why write on paper, when you can deliver your message on a balloon?') GO INSERT INTO Category (DepartmentID, Name, Description ) VALUES (2, 'Cartoons', 'Buy a balloon with your child''s favorite cartoon character!') GO INSERT INTO Category (DepartmentID, Name, Description ) VALUES (2, 'Miscellaneous', 'Various baloons that your kid will most certainly love!') GO INSERT INTO Product(Name, Description, Price, Image1FileName, Image2FileName, OnCatalog