import System; import System.Configuration; import System.Data; import System.Data.SqlClient; package IBuySpy { //******************************************************* // // ProductDetails Class // // A simple data class that encapsulates details about // a particular product inside the IBuySpy Product // database. // //******************************************************* public class ProductDetails { public var ModelNumber : String; public var ModelName : String; public var ProductImage : String; public var UnitCost : decimal; public var Description : String; } //******************************************************* // // ProductsDB Class // // Business/Data Logic Class that encapsulates all data // logic necessary to query products within // the IBuySpy Products database. // //******************************************************* public class ProductsDB { //******************************************************* // // ProductsDB.GetProductCategories() Method // // The GetProductCategories method returns a DataReader that exposes all // product categories (and their CategoryIDs) within the IBuySpy Products // database. The SQLDataReaderResult struct also returns the // SQL connection, which must be explicitly closed after the // data from the DataReader is bound into the controls. // // Other relevant sources: // + ProductCategoryList Stored Procedure // //******************************************************* public function GetProductCategories() : SqlDataReader { // Create Instance of Connection and Command Object var myConnection : SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]); var myCommand : SqlCommand = new SqlCommand("ProductCategoryList", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Execute the command myConnection.Open(); var result : SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection); // Return the datareader result return result; } //******************************************************* // // ProductsDB.GetProducts() Method // // The GetProducts method returns a struct containing a forward-only, // read-only DataReader. This displays all products within a specified // product category. The SQLDataReaderResult struct also returns the // SQL connection, which must be explicitly closed after the // data from the DataReader is bound into the controls. // // Other relevant sources: // + ProductsByCategory Stored Procedure // //******************************************************* public function GetProducts(categoryID: int) : SqlDataReader { // Create Instance of Connection and Command Object var myConnection : SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]); var myCommand : SqlCommand = new SqlCommand("ProductsByCategory", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC var parameterCategoryID : SqlParameter = new SqlParameter("@CategoryID", SqlDbType.Int, 4); parameterCategoryID.Value = categoryID; myCommand.Parameters.Add(parameterCategoryID); // Execute the command myConnection.Open(); var result : SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection); // Return the datareader result return result; } //******************************************************* // // ProductsDB.GetProductDetails() Method // // The GetProductDetails method returns a ProductDetails // struct containing specific details about a specified // product within the IBuySpy Products Database. // // Other relevant sources: // + ProductDetail Stored Procedure // //******************************************************* public function GetProductDetails(productID: int) : ProductDetails { // Create Instance of Connection and Command Object var myConnection : SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]); var myCommand : SqlCommand = new SqlCommand("ProductDetail", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC var parameterProductID : SqlParameter = new SqlParameter("@ProductID", SqlDbType.Int, 4); parameterProductID.Value = productID; myCommand.Parameters.Add(parameterProductID); var parameterUnitCost : SqlParameter = new SqlParameter("@UnitCost", SqlDbType.Money, 8); parameterUnitCost.Direction = ParameterDirection.Output; myCommand.Parameters.Add(parameterUnitCost); var parameterModelNumber : SqlParameter = new SqlParameter("@ModelNumber", SqlDbType.NVarChar, 50); parameterModelNumber.Direction = ParameterDirection.Output; myCommand.Parameters.Add(parameterModelNumber); var parameterModelName : SqlParameter = new SqlParameter("@ModelName", SqlDbType.NVarChar, 50); parameterModelName.Direction = ParameterDirection.Output; myCommand.Parameters.Add(parameterModelName); var parameterProductImage : SqlParameter = new SqlParameter("@ProductImage", SqlDbType.NVarChar, 50); parameterProductImage.Direction = ParameterDirection.Output; myCommand.Parameters.Add(parameterProductImage); var parameterDescription : SqlParameter = new SqlParameter("@Description", SqlDbType.NVarChar, 3800); parameterDescription.Direction = ParameterDirection.Output; myCommand.Parameters.Add(parameterDescription); // Open the connection and execute the Command myConnection.Open(); myCommand.ExecuteNonQuery(); myConnection.Close(); // Create and Populate ProductDetails Struct using // Output Params from the SPROC var myProductDetails : ProductDetails = new ProductDetails(); myProductDetails.ModelNumber = parameterModelNumber.Value; myProductDetails.ModelName = parameterModelName.Value; myProductDetails.ProductImage = (parameterProductImage.Value).Trim(); myProductDetails.UnitCost = parameterUnitCost.Value; myProductDetails.Description = (parameterDescription.Value).Trim(); return myProductDetails; } //******************************************************* // // ProductsDB.GetProductsAlsoPurchased() Method // // The GetPGetProductsAlsoPurchasedroducts method returns a struct containing // a forward-only, read-only DataReader. This displays a list of other products // also purchased with a specified product The SQLDataReaderResult struct also // returns the SQL connection, which must be explicitly closed after the // data from the DataReader is bound into the controls. // // Other relevant sources: // + CustomerAlsoBought Stored Procedure // //******************************************************* public function GetProductsAlsoPurchased(productID: int) : SqlDataReader { // Create Instance of Connection and Command Object var myConnection : SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]); var myCommand : SqlCommand = new SqlCommand("CustomerAlsoBought", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC var parameterProductID : SqlParameter = new SqlParameter("@ProductID", SqlDbType.Int, 4); parameterProductID.Value = productID; myCommand.Parameters.Add(parameterProductID); // Execute the command myConnection.Open(); var result : SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection); // Return the datareader result return result; } //******************************************************* // // ProductsDB.GetMostPopularProductsOfWeek() Method // // The GetMostPopularProductsOfWeek method returns a struct containing a // forward-only, read-only DataReader containing the most popular products // of the week within the IBuySpy Products database. // The SQLDataReaderResult struct also returns the // SQL connection, which must be explicitly closed after the // data from the DataReader is bound into the controls. // // Other relevant sources: // + ProductsMostPopular Stored Procedure // //******************************************************* public function GetMostPopularProductsOfWeek() : SqlDataReader{ // Create Instance of Connection and Command Object var myConnection : SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]); var myCommand : SqlCommand = new SqlCommand("ProductsMostPopular", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Execute the command myConnection.Open(); var result : SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection); // Return the datareader result return result; } //******************************************************* // // ProductsDB.SearchProductDescriptions() Method // // The SearchProductDescriptions method returns a struct containing // a forward-only, read-only DataReader. This displays a list of all // products whose name and/or description contains the specified search // string. The SQLDataReaderResult struct also returns the SQL connection, // which must be explicitly closed after the data from the DataReader // is bound into the controls. // // Other relevant sources: // + ProductSearch Stored Procedure // //******************************************************* public function SearchProductDescriptions(searchString : String) : SqlDataReader { // Create Instance of Connection and Command Object var myConnection : SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]); var myCommand : SqlCommand = new SqlCommand("ProductSearch", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC var parameterSearch : SqlParameter = new SqlParameter("@Search", SqlDbType.NVarChar, 255); parameterSearch.Value = searchString; myCommand.Parameters.Add(parameterSearch); // Execute the command myConnection.Open(); var result : SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection); // Return the datareader result return result; } } }