using System; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Globalization; /*===================================================================== File: ShoppingCartDb.cs for Adventure Works Cycles Storefront Sample Summary: Middle tier component for manipulating lists of pending items to be purchased. Date: June 16, 2003 --------------------------------------------------------------------- This file is part of the Microsoft SQL Server Code Samples. Copyright (C) Microsoft Corporation. All rights reserved. This source code is intended only as a supplement to Microsoft Development Tools and/or on-line documentation. See these other materials for detailed information regarding Microsoft code samples. THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. ======================================================= */ namespace Microsoft.Samples.SqlServer { //******************************************************* // // ShoppingCartDB Class // // Business/Data Logic Class that encapsulates all data // logic necessary to add/remove/update/purchase items // within an AdventureWorks shopping cart. // //******************************************************* public class ShoppingCartDB { //******************************************************* // // ShoppingCartDB.GetItems() Method <a name="GetItems"></a> // // The GetItems method returns a // a forward-only, read-only DataReader. This returns a list of all // items within a shopping cart. // // Other relevant sources: // + <a href="usp_ShoppingList.htm" style="color:green">usp_ShoppingList Stored Procedure</a> // //******************************************************* public SqlDataReader GetItems(string cartID) { // Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection( Properties.Settings.Default.ConnectionString); SqlCommand myCommand = myConnection.CreateCommand(); myCommand.CommandText = "usp_ShoppingList"; // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC SqlParameter parameterCartID = new SqlParameter("@CartID", SqlDbType.NVarChar, 50); parameterCartID.Value = cartID; myCommand.Parameters.Add(parameterCartID); SqlParameter parameterCulture = new SqlParameter("@Culture", SqlDbType.NVarChar, 10); parameterCulture.Value = CultureInfo.CurrentUICulture.Name; myCommand.Parameters.Add(parameterCulture); // Return the datareader result myConnection.Open(); return myCommand.ExecuteReader(CommandBehavior.CloseConnection); } //******************************************************* // // ShoppingCartDB.AddItem() Method <a name="AddItem"></a> // // The AddItem method adds an item into a shopping cart. // // Other relevant sources: // + <a href="usp_ShoppingCartAddItem.htm" style="color:green">usp_ShoppingCartAddItem Stored Procedure</a> // //******************************************************* public void AddItem(string cartID, int productID, int quantity) { // Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection( Properties.Settings.Default.ConnectionString); SqlCommand myCommand = new SqlCommand("usp_ShoppingCartAddItem", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC SqlParameter parameterProductID = new SqlParameter("@ProductID", SqlDbType.Int, 4); parameterProductID.Value = productID; myCommand.Parameters.Add(parameterProductID); SqlParameter parameterCartID = new SqlParameter("@CartID", SqlDbType.NVarChar, 50); parameterCartID.Value = cartID; myCommand.Parameters.Add(parameterCartID); SqlParameter parameterQuantity = new SqlParameter("@Quantity", SqlDbType.Int, 4); parameterQuantity.Value = quantity; myCommand.Parameters.Add(parameterQuantity); // Open the connection and execute the Command try { myConnection.Open(); myCommand.ExecuteNonQuery(); } finally { myConnection.Close(); } } //******************************************************* // // ShoppingCartDB.UpdateItem() Method <a name="UpdateItem"></a> // // The UpdateItem method updates the quantity of an item // in a shopping cart. // // Other relevant sources: // + <a href="usp_ShoppingCartUpdate.htm" style="color:green">usp_ShoppingCartUpdate Stored Procedure</a> // //******************************************************* public void UpdateItem(string cartID, int productID, int quantity) { // 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 SqlConnection myConnection = new SqlConnection( Properties.Settings.Default.ConnectionString); SqlCommand myCommand = new SqlCommand("usp_ShoppingCartUpdate", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC SqlParameter parameterProductID = new SqlParameter("@ProductID", SqlDbType.Int, 4); parameterProductID.Value = productID; myCommand.Parameters.Add(parameterProductID); SqlParameter parameterCartID = new SqlParameter("@CartID", SqlDbType.NVarChar, 50); parameterCartID.Value = cartID; myCommand.Parameters.Add(parameterCartID); SqlParameter parameterQuantity = new SqlParameter("@Quantity", SqlDbType.Int, 4); parameterQuantity.Value = quantity; myCommand.Parameters.Add(parameterQuantity); // Open the connection and execute the Command try { myConnection.Open(); myCommand.ExecuteNonQuery(); } finally { myConnection.Close(); } } //******************************************************* // // ShoppingCartDB.RemoveItem() Method <a name="RemoveItem"></a> // // The RemoveItem method removes an item from a // shopping cart. // // Other relevant sources: // + <a href="usp_ShoppingCartRemoveItem.htm" style="color:green">usp_ShoppingCartRemoveItem Stored Procedure</a> // //******************************************************* public void RemoveItem(string cartID, int productID) { // Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection( Properties.Settings.Default.ConnectionString); SqlCommand myCommand = new SqlCommand("usp_ShoppingCartRemoveItem", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC SqlParameter parameterProductID = new SqlParameter("@ProductID", SqlDbType.Int, 4); parameterProductID.Value = productID; myCommand.Parameters.Add(parameterProductID); SqlParameter parameterCartID = new SqlParameter("@CartID", SqlDbType.NVarChar, 50); parameterCartID.Value = cartID; myCommand.Parameters.Add(parameterCartID); // Open the connection and execute the Command try { myConnection.Open(); myCommand.ExecuteNonQuery(); } finally { myConnection.Close(); } } //******************************************************* // // ShoppingCartDB.GetItemCount() Method <a name="GetItemCount"></a> // // The GetItemCount method returns the number of items // within a shopping cart. // // Other relevant sources: // + <a href="usp_ShoppingCartItemCount.htm" style="color:green">usp_ShoppingCartItemCount Stored Procedure</a> // //******************************************************* public int GetItemCount(string cartID) { // Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection( Properties.Settings.Default.ConnectionString); SqlCommand myCommand = new SqlCommand("usp_ShoppingCartItemCount", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; SqlParameter parameterCartID = new SqlParameter("@CartID", SqlDbType.NVarChar, 50); parameterCartID.Value = cartID; myCommand.Parameters.Add(parameterCartID); // Add Parameters to SPROC SqlParameter parameterItemCount = new SqlParameter("@ItemCount", SqlDbType.Int, 4); parameterItemCount.Direction = ParameterDirection.Output; myCommand.Parameters.Add(parameterItemCount); // Open the connection and execute the Command try { myConnection.Open(); myCommand.ExecuteNonQuery(); } finally { myConnection.Close(); } // Return the ItemCount (obtained as out paramter of SPROC) return ((int)parameterItemCount.Value); } //******************************************************* // // ShoppingCartDB.GetTotal() Method <a name="GetTotal"></a> // // The GetTotal method returns the total price of all // items within the shopping cart. // // Other relevant sources: // + <a href="usp_ShoppingCartTotal.htm" style="color:green">usp_ShoppingCartTotal Stored Procedure</a> // //******************************************************* // TODO: Switch back to Currency when this works. public String GetTotal(string cartID) { // Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection( Properties.Settings.Default.ConnectionString); SqlCommand myCommand = new SqlCommand("usp_ShoppingCartTotal", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC SqlParameter parameterCartID = new SqlParameter("@CartID", SqlDbType.NVarChar, 50); parameterCartID.Value = cartID; myCommand.Parameters.Add(parameterCartID); SqlParameter parameterCulture = new SqlParameter("@Culture", SqlDbType.NVarChar, 10); parameterCulture.Value = CultureInfo.CurrentUICulture.Name; myCommand.Parameters.Add(parameterCulture); // TODO: Switch back to Currency when this works. SqlParameter parameterTotalCost = new SqlParameter("@TotalCost", SqlDbType. NVarChar, 20); parameterTotalCost.Direction = ParameterDirection.Output; //parameterTotalCost.UdtTypeName = "[AdventureWorks.Currency]"; myCommand.Parameters.Add(parameterTotalCost); // Open the connection and execute the Command try { myConnection.Open(); myCommand.ExecuteNonQuery(); } finally { if (myConnection.State != ConnectionState.Closed) myConnection.Close(); } // Return the Total return (String)parameterTotalCost.Value; } //******************************************************* // // ShoppingCartDB.MigrateCart() Method <a name="MigrateCart"></a> // // 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: // + <a href="usp_SetShoppingCartID.htm" style="color:green">usp_SetShoppingCartID Stored Procedure</a> // //******************************************************* public void MigrateCart(String oldCartId, String newCartId) { // Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection( Properties.Settings.Default.ConnectionString); SqlCommand myCommand = new SqlCommand("usp_SetShoppingCartID", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC SqlParameter cart1 = new SqlParameter("@OriginalCartId ", SqlDbType.NVarChar, 50); cart1.Value = oldCartId; myCommand.Parameters.Add(cart1); SqlParameter cart2 = new SqlParameter("@NewCartId ", SqlDbType.NVarChar, 50); cart2.Value = newCartId; myCommand.Parameters.Add(cart2); // Open the connection and execute the Command try { myConnection.Open(); myCommand.ExecuteNonQuery(); } finally { myConnection.Close(); } } //******************************************************* // // ShoppingCartDB.EmptyCart() Method <a name="EmptyCart"></a> // // The EmptyCart method removes all current items within // the shopping cart. // // Other relevant sources: // + <a href="usp_ShoppingCartEmpty.htm" style="color:green">usp_ShoppingCartEmpty Stored Procedure</a> // //******************************************************* public void EmptyCart(string cartID) { // Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection( Properties.Settings.Default.ConnectionString); SqlCommand myCommand = new SqlCommand("usp_ShoppingCartEmpty", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC SqlParameter cartid = new SqlParameter("@CartID", SqlDbType.NVarChar, 50); cartid.Value = cartID; myCommand.Parameters.Add(cartid); // Open the connection and execute the Command try { myConnection.Open(); myCommand.ExecuteNonQuery(); } finally { myConnection.Close(); } } //******************************************************* // // ShoppingCartDB.GetShoppingCartId() Method <a name="GetShoppingCartId"></a> // // 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 String GetShoppingCartId() { // Obtain current HttpContext of ASP+ Request System.Web.HttpContext context = System.Web.HttpContext.Current; // If the user is authenticated, use their customerId as a permanent shopping cart id if (context.User.Identity.Name.Length != 0) { return context.User.Identity.Name; } // If user is not authenticated, either fetch (or issue) a new temporary cartID if (context.Request.Cookies["AdventureWorks_CartID"] != null) { return context.Request.Cookies["AdventureWorks_CartID"].Value; } else { // Generate a new random GUID using System.Guid Class Guid tempCartId = Guid.NewGuid(); // Send tempCartId back to client as a cookie context.Response.Cookies["AdventureWorks_CartID"].Value = tempCartId. ToString(); // Return tempCartId return tempCartId.ToString(); } } public SqlDataReader GetProductRecommendationsFromCart(String cartID) { SqlConnection conn = new SqlConnection(Properties.Settings.Default.ConnectionString); SqlCommand myCommand = conn.CreateCommand(); myCommand.CommandText = "usp_ShoppingCartGetProductRecommendations"; myCommand.CommandType = CommandType.StoredProcedure; SqlParameter cartParam = new SqlParameter("@CartID", SqlDbType.NVarChar, 50); cartParam.Value = cartID; myCommand.Parameters.Add(cartParam); conn.Open(); return myCommand.ExecuteReader(CommandBehavior.CloseConnection); } } }