import System; import System.Configuration; import System.Data; import System.Data.SqlClient; package IBuySpy { //******************************************************* // // OrderDetails Class // // A simple data class that encapsulates details about // a particular order inside the IBuySpy Orders // database. // //******************************************************* public class OrderDetails { public var OrderDate : DateTime; public var ShipDate : DateTime; public var OrderTotal : decimal; public var OrderItems : DataSet; } //******************************************************* // // OrderHistoryDB Class // // Business/Data Logic Class that encapsulates all data // logic necessary to query past orders within the // IBuySpy Orders database. // //******************************************************* public class OrdersDB { //******************************************************* // // CustomerDB.GetCustomerOrders() Method // // The GetCustomerOrders method returns a struct containing // a forward-only, read-only DataReader. This displays a list of all // past orders placed by a specified customer. // 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: // + OrdersList Stored Procedure // //******************************************************* public function GetCustomerOrders(customerID: String) : SqlDataReader { // Create Instance of Connection and Command Object var myConnection : SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]); var myCommand : SqlCommand = new SqlCommand("OrdersList", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC var parameterCustomerid : SqlParameter = new SqlParameter("@CustomerID", SqlDbType.Int, 4); parameterCustomerid.Value = Int32.Parse(customerID); myCommand.Parameters.Add(parameterCustomerid); // Execute the command myConnection.Open(); var result : SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection); // Return the datareader result return result; } //******************************************************* // // OrdersDB.GetOrderDetails() Method // // The GetOrderDetails method returns an OrderDetails // struct containing information about the specified // order. // // Other relevant sources: // + OrdersDetail Stored Procedure // //******************************************************* public function GetOrderDetails(orderID: int, customerID: String) : OrderDetails { // Create Instance of Connection and Command Object var myConnection : SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]); var myCommand : SqlDataAdapter = new SqlDataAdapter("OrdersDetail", myConnection); // Mark the Command as a SPROC myCommand.SelectCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC var parameterOrderID : SqlParameter = new SqlParameter("@OrderID", SqlDbType.Int, 4); parameterOrderID.Value = orderID; myCommand.SelectCommand.Parameters.Add(parameterOrderID); var parameterCustomerID : SqlParameter = new SqlParameter("@CustomerID", SqlDbType.Int, 4); parameterCustomerID.Value = Int32.Parse(customerID); myCommand.SelectCommand.Parameters.Add(parameterCustomerID); var parameterOrderDate : SqlParameter = new SqlParameter("@OrderDate", SqlDbType.DateTime, 8); parameterOrderDate.Direction = ParameterDirection.Output; myCommand.SelectCommand.Parameters.Add(parameterOrderDate); var parameterShipDate : SqlParameter = new SqlParameter("@ShipDate", SqlDbType.DateTime, 8); parameterShipDate.Direction = ParameterDirection.Output; myCommand.SelectCommand.Parameters.Add(parameterShipDate); var parameterOrderTotal : SqlParameter = new SqlParameter("@OrderTotal", SqlDbType.Money, 8); parameterOrderTotal.Direction = ParameterDirection.Output; myCommand.SelectCommand.Parameters.Add(parameterOrderTotal); // Create and Fill the DataSet var myDataSet : DataSet = new DataSet(); myCommand.Fill(myDataSet, "OrderItems"); // ship date is null if order doesn't exist, or belongs to a different user if (parameterShipDate.Value != DBNull.Value) { // Create and Populate OrderDetails Struct using // Output Params from the SPROC, as well as the // populated dataset from the SqlDataAdapter var myOrderDetails : OrderDetails = new OrderDetails(); myOrderDetails.OrderDate = parameterOrderDate.Value; myOrderDetails.ShipDate = parameterShipDate.Value; myOrderDetails.OrderTotal = parameterOrderTotal.Value; myOrderDetails.OrderItems = myDataSet; // Return the DataSet return myOrderDetails; } else return null; } //******************************************************* // // OrdersDB.CalculateShippingDate() Method // // The CalculateShippingDate method would be where you would // place all of the code necessary to calculate the shipping // ETA. For now, we are just making up a random date. // //******************************************************* public function CalculateShippingDate(customerID: String, cartID: String) : DateTime { var x : Random = new Random(); var myrandom : double = x.Next(0,3); return DateTime.Now.AddDays(myrandom); } //******************************************************* // // OrdersDB.PlaceOrder() Method // // The PlaceOrder method places an order within the // IBuySpy Orders Database and then clears out the current // items within the shopping cart. // // Other relevant sources: // + OrdersAdd Stored Procedure // //******************************************************* public function PlaceOrder(customerID: String, cartID: String) : int { // Create Instance of Connection and Command Object var myConnection : SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]); var myCommand : SqlCommand = new SqlCommand("OrdersAdd", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC var parameterCustomerID : SqlParameter = new SqlParameter("@CustomerID", SqlDbType.Int, 4); parameterCustomerID.Value = Int32.Parse(customerID); myCommand.Parameters.Add(parameterCustomerID); var parameterCartID : SqlParameter = new SqlParameter("@CartID", SqlDbType.NVarChar, 50); parameterCartID.Value = cartID; myCommand.Parameters.Add(parameterCartID); var parameterShipDate : SqlParameter = new SqlParameter("@ShipDate", SqlDbType.DateTime, 8); parameterShipDate.Value = CalculateShippingDate(customerID, cartID); myCommand.Parameters.Add(parameterShipDate); var parameterOrderDate : SqlParameter = new SqlParameter("@OrderDate", SqlDbType.DateTime, 8); parameterOrderDate.Value = DateTime.Now; myCommand.Parameters.Add(parameterOrderDate); var parameterOrderID : SqlParameter = new SqlParameter("@OrderID", SqlDbType.Int, 4); parameterOrderID.Direction = ParameterDirection.Output; myCommand.Parameters.Add(parameterOrderID); // Open the connection and execute the Command myConnection.Open(); myCommand.ExecuteNonQuery(); myConnection.Close(); // Return the OrderID return parameterOrderID.Value; } } }