IBuySpy Store, Part II: JScript Components
IBuySpy Store uses stored procedures extensively. These are SQL scripts that are stored permanently in the MSSQL database, and are called from within the JScript .NET code. In fact, all interactions with the IBuySpy database are accomplished via stored procedures. There are several advantages to using stored procedures instead of writing SQL queries in the JScript .NET code. Performance and ease of maintenance are two obvious reasons to use stored procedures.
Performance is better because you don't have to pass the procedure from the Web server to the MSSQL server every time you need to retrieve records from the database (usually the MSSQL server is different from the Web server).
Maintenance is easier because the MSSQL code is separate from the JScript .NET code. A JScript .NET expert can maintain the JScript .NET code without having to know too much about MSSQL and how to write SQL queries. An MSSQL expert can maintain the stored procedures without having to know too much about JScript .NET coding. You can use a new version of a stored procedure without touching the JScript .NET code that calls this procedure (assuming the input and output parameters did not change, of course). You can also write a new version of the JScript .NET component without touching the stored procedure. Aside from matching the input and output parameters, the JScript .NET code and the stored procedures are two different entities that can be developed and maintained independently. This separation contributes a lot to code maintainability.
To view IBuySpy's stored procedures, you need to open MSSQL
Enterprise, and look under the
StoreDOCJS database for the
Stored Procedures entry. You will find 23 stored procedures. Double click one of the stored procedures to view its content. Here is the
CustomerAlsoBought stored procedure:
CREATE Procedure CustomerAlsoBought ( @ProductID int ) As /* We want to take the top 5 products contained in the orders where someone has purchased the given Product */ SELECT TOP 5 OrderDetails.ProductID, Products.ModelName, SUM(OrderDetails.Quantity) as TotalNum FROM OrderDetails INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID WHERE OrderID IN ( /* This inner query should retrieve all orders that have contained the productID */ SELECT DISTINCT OrderID FROM OrderDetails WHERE ProductID = @ProductID ) AND OrderDetails.ProductID != @ProductID GROUP BY OrderDetails.ProductID, Products.ModelName ORDER BY TotalNum DESC GO
This stored procedure expects one input parameter,
ProductID. It selects records from the database according to this
ProductID, and returns the records found by the query.
Next: How to specify authorization and authentication