Designing the solution
Since we've decided how to implement the solution, we need to examine the backend data structures. This is necessary to learn what data will be required and where it lives. We may need to call more than one table, and we will develop stored procedures to process the information request. The use of stored procedures provides better performance since they are compiled by the database server and it places a portion of the work on the database server. A perusal of the data structure yields the following information:

• Products table: Contains product information with the product id serving as the key. The user should be able to search products by product id, product name, supplier id, supplier name, category name, and category id.
• Suppliers table: Used to return or search supplier information via product requested. It is joined with the products table.
• Categories table: Used to return or search category information via product request. It is joined with the products table.

Now that we have an understanding of the data structures involved we will develop and test the stored procedures that will be utilised by Web services to server customer requests. We will use six stored procedures:

• sp_GetProductByID: Returns all products matching the product id parameter.
• sp_GetProductByName: Returns all products matching the product name parameter.
• sp_GetProductByCategoryID: Returns all products with the category id parameter.
• sp_GetProductByCategoryName: Returns all products with the category name parameter.
• sp_GetProductBySupplierID: Returns all products with the supplier id parameter.
• sp_GetProductBySupplierName: Returns all products with the supplier name parameter.

The stored procedures are relatively simple. They each accept a lone parameter and use a basic SELECT clause to return all matching records according to the tables and value used. You can find the source for each stored procedure in Listing A.

Listing A
CREATE PROCEDURE sp_GetProductByCategoryID @CategoryIDint AS SELECT c.CategoryName, p.ProductName, s.CompanyName, p.UnitPrice FROM Products p, Suppliers s, Categories c WHERE c.CategoryID = @CategoryID AND p.CategoryID = c.CategoryID ORDER BY ProductID GO CREATE PROCEDURE sp_GetProductByCategoryName @CategoryNamenvarchar(15) AS SELECT c.CategoryName, p.ProductName, s.CompanyName, p.UnitPrice FROM Products p, Suppliers s, Categories c WHERE c.CategoryName = @CategoryName AND p.CategoryID = c.CategoryID ORDER BY c.CategoryName GO CREATE PROCEDURE sp_GetProductByID @ProductIDint AS SELECT p.ProductID, p.ProductName, s.CompanyName, p.UnitPrice FROM Products p, Suppliers s WHERE p.ProductID = @ProductID AND p.SupplierID = s.SupplierID ORDER BY ProductID GO CREATE PROCEDURE sp_GetProductByName @ProductNamenvarchar(40) AS SELECT p.ProductID, p.ProductName, s.CompanyName, p.UnitPrice FROM Products p, Suppliers s WHERE p.ProductName = @ProductName AND p.SupplierID = s.SupplierID ORDER BY p.ProductName GO CREATE PROCEDURE sp_GetProductBySupplierID @SupplierIDint AS SELECT p.ProductID, p.ProductName, s.CompanyName, p.UnitPrice FROM Products p, Suppliers s WHERE p.SupplierID = @SupplierID AND p.SupplierID = s.SupplierID ORDER BY s.SupplierID GO CREATE PROCEDURE sp_GetProductBySupplierName @SupplierNamenvarchar(40) AS SELECT p.ProductID, p.ProductName, s.CompanyName, p.UnitPrice FROM Products p, Suppliers s WHERE s.CompanyName = @SupplierName AND p.SupplierID = s.SupplierID ORDER BY s.CompanyName GO

Once we create and save the stored procedures, we test them using the SQL Server Query Analyzer client. We'll assume that each of the procedures runs without problems, so we'll move to the next phase of development.

The next step
With the database tier set up, we advance to the creation of the actual Web services to interface with the database. The Web service will provide an interface to the database while hiding the ugly details of connecting and retrieving data from the requesting client. The code will utilise the Microsoft Data Access Application Block (DAAB) to interface with SQL Server. We will dive into the details of each service in an upcoming article.