import System; import System.Configuration; import System.Data; import System.Data.SqlClient; package IBuySpy { //******************************************************* // // ShoppingCartDB Class // // Business/Data Logic Class that encapsulates all data // logic necessary to add/remove/update/purchase items // within an IBuySpy shopping cart. // //******************************************************* public class ShoppingCartDB { //******************************************************* // // ShoppingCartDB.GetItems() Method // // The GetItems method returns a struct containing // a forward-only, read-only DataReader. This displays a list of all // items within a shopping cart. 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: // + ShoppingCartList Stored Procedure // //******************************************************* public function GetItems(cartID: String) : SqlDataReader { // Create Instance of Connection and Command Object var myConnection : SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]); var myCommand : SqlCommand = new SqlCommand("ShoppingCartList", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC var parameterCartID : SqlParameter = new SqlParameter("@CartID", SqlDbType.NVarChar, 50); parameterCartID.Value = cartID; myCommand.Parameters.Add(parameterCartID); // Execute the command myConnection.Open(); var result : SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection); // Return the datareader result return result; } //******************************************************* // // ShoppingCartDB.AddItem() Method // // The AddItem method adds an item into a shopping cart. // // Other relevant sources: // + ShoppingCartAddItem Stored Procedure // //******************************************************* public function AddItem(cartID: String, productID: int, quantity: int) : void { // Create Instance of Connection and Command Object var myConnection : SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]); var myCommand : SqlCommand = new SqlCommand("ShoppingCartAddItem", 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 parameterCartID : SqlParameter = new SqlParameter("@CartID", SqlDbType.NVarChar, 50); parameterCartID.Value = cartID; myCommand.Parameters.Add(parameterCartID); var parameterQuantity : SqlParameter = new SqlParameter("@Quantity", SqlDbType.Int, 4); parameterQuantity.Value = quantity; myCommand.Parameters.Add(parameterQuantity); // Open the connection and execute the Command myConnection.Open(); myCommand.ExecuteNonQuery(); myConnection.Close(); } //******************************************************* // // ShoppingCartDB.UpdateItem() Method // // The UpdateItem method updates the quantity of an item // in a shopping cart. // // Other relevant sources: // + ShoppingCartUpdate Stored Procedure // //******************************************************* public function UpdateItem(cartID: String, productID: int, quantity: int) : void { // throw an exception if quantity is a negative number if (quantity < 0) { throw new Exception("Quantity cannot be a negative number"); } // Create Instance of Connection and Command Object var myConnection : SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]); var myCommand : SqlCommand = new SqlCommand("ShoppingCartUpdate", 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 parameterCartID : SqlParameter = new SqlParameter("@CartID", SqlDbType.NVarChar, 50); parameterCartID.Value = cartID; myCommand.Parameters.Add(parameterCartID); var parameterQuantity : SqlParameter = new SqlParameter("@Quantity", SqlDbType.Int, 4); parameterQuantity.Value = quantity; myCommand.Parameters.Add(parameterQuantity); // Open the connection and execute the Command myConnection.Open(); myCommand.ExecuteNonQuery(); myConnection.Close(); } //******************************************************* // // ShoppingCartDB.RemoveItem() Method // // The RemoveItem method removes an item from a // shopping cart. // // Other relevant sources: // + ShoppingCartRemoveItem Stored Procedure // //******************************************************* public function RemoveItem(cartID: String, productID: int) : void { // Create Instance of Connection and Command Object var myConnection : SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]); var myCommand : SqlCommand = new SqlCommand("ShoppingCartRemoveItem", 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 parameterCartID : SqlParameter = new SqlParameter("@CartID", SqlDbType.NVarChar, 50); parameterCartID.Value = cartID; myCommand.Parameters.Add(parameterCartID); // Open the connection and execute the Command myConnection.Open(); myCommand.ExecuteNonQuery(); myConnection.Close(); } //******************************************************* // // ShoppingCartDB.GetItemCount() Method // // The GetItemCount method returns the number of items // within a shopping cart. // // Other relevant sources: // + ShoppingCartItemCount Stored Procedure // //******************************************************* public function GetItemCount(cartID: String) : int { // Create Instance of Connection and Command Object var myConnection : SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]); var myCommand : SqlCommand = new SqlCommand("ShoppingCartItemCount", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; var parameterCartID : SqlParameter = new SqlParameter("@CartID", SqlDbType.NVarChar, 50); parameterCartID.Value = cartID; myCommand.Parameters.Add(parameterCartID); // Add Parameters to SPROC var parameterItemCount : SqlParameter = new SqlParameter("@ItemCount", SqlDbType.Int, 4); parameterItemCount.Direction = ParameterDirection.Output; myCommand.Parameters.Add(parameterItemCount); // Open the connection and execute the Command myConnection.Open(); myCommand.ExecuteNonQuery(); myConnection.Close(); // Return the ItemCount (obtained as out paramter of SPROC) return (parameterItemCount.Value); } //******************************************************* // // ShoppingCartDB.GetTotal() Method // // The GetTotal method returns the total price of all // items within the shopping cart. // // Other relevant sources: // + ShoppingCartTotal Stored Procedure // //******************************************************* public function GetTotal(cartID: String) : decimal { // Create Instance of Connection and Command Object var myConnection : SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]); var myCommand : SqlCommand = new SqlCommand("ShoppingCartTotal", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC var parameterCartID : SqlParameter = new SqlParameter("@CartID", SqlDbType.NVarChar, 50); parameterCartID.Value = cartID; myCommand.Parameters.Add(parameterCartID); var parameterTotalCost : SqlParameter = new SqlParameter("@TotalCost", SqlDbType.Money, 8); parameterTotalCost.Direction = ParameterDirection.Output; myCommand.Parameters.Add(parameterTotalCost); // Open the connection and execute the Command myConnection.Open(); myCommand.ExecuteNonQuery(); myConnection.Close(); // Return the Total if (parameterTotalCost.Value.ToString() != "") { return parameterTotalCost.Value; } else { return 0; } } //******************************************************* // // ShoppingCartDB.MigrateCart() Method // // The MigrateCart method migrates the items from one // cartId to another. This is used during the login // and/or registration process to transfer a user's // temporary cart items to a permanent account. // // Other relevant sources: // + ShoppingCartMigrate Stored Procedure // //******************************************************* public function MigrateCart(oldCartId: String, newCartId: String) : void { // Create Instance of Connection and Command Object var myConnection : SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]); var myCommand : SqlCommand = new SqlCommand("ShoppingCartMigrate", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC var cart1 : SqlParameter = new SqlParameter("@OriginalCartId ", SqlDbType.NVarChar, 50); cart1.Value = oldCartId; myCommand.Parameters.Add(cart1); var cart2 : SqlParameter = new SqlParameter("@NewCartId ", SqlDbType.NVarChar, 50); cart2.Value = newCartId; myCommand.Parameters.Add(cart2); // Open the connection and execute the Command myConnection.Open(); myCommand.ExecuteNonQuery(); myConnection.Close(); } //******************************************************* // // ShoppingCartDB.EmptyCart() Method // // The EmptyCart method removes all current items within // the shopping cart. // // Other relevant sources: // + ShoppingCartEmpty Stored Procedure // //******************************************************* public function EmptyCart(cartID: String) : void { // Create Instance of Connection and Command Object var myConnection : SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]); var myCommand : SqlCommand = new SqlCommand("ShoppingCartEmpty", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC var cartid : SqlParameter = new SqlParameter("@CartID", SqlDbType.NVarChar, 50); cartid.Value = cartID; myCommand.Parameters.Add(cartid); // Open the connection and execute the Command myConnection.Open(); myCommand.ExecuteNonQuery(); myConnection.Close(); } //******************************************************* // // ShoppingCartDB.GetShoppingCartId() Method // // The GetShoppingCartId method is used to calculate the // "ShoppingCart" ID key used for a tracking a browser. // // The ShoppingCartID value is either the User's Identity // Name (if they are a registered and authenticated user), // or a random GUID calculated for guest visitors or // customers who have not yet logged in. // //******************************************************* public function GetShoppingCartId() : String { // Obtain current HttpContext of ASP+ Request var context : System.Web.HttpContext = System.Web.HttpContext.Current; // If the user is authenticated, use their customerId as a permanent shopping cart id if (context.User.Identity.Name != "") { return context.User.Identity.Name; } // If user is not authenticated, either fetch (or issue) a new temporary cartID if (context.Request.Cookies["IBuySpy_CartID"] != null) { return context.Request.Cookies["IBuySpy_CartID"].Value; } else { // Generate a new random GUID using System.Guid Class var tempCartId : Guid = Guid.NewGuid(); // Send tempCartId back to client as a cookie context.Response.Cookies["IBuySpy_CartID"].Value = tempCartId.ToString(); // Return tempCartId return tempCartId.ToString(); } } } }