ordersdb.vb

visual studio.net学习资料

Imports System
Imports System.Data
Imports System.Data.SQL

Namespace Conference

    '*******************************************************
    '
    ' OrderDetails Class
    '
    ' A simple data class that encapsulates details about
    ' a particular order inside the Conference Orders 
    ' database.
    '
    '*******************************************************
    
    Public Class OrderDetails
    
        Public OrderDate As DateTime
        Public ShipDate As DateTime
        Public OrderTotal As Double
        Public OrderItems As DataSet
    
    End Class

    '*******************************************************
    '
    ' OrderHistoryDB Class
    '
    ' Business/Data Logic Class that encapsulates all data
    ' logic necessary to query past orders within the
    ' Conference Orders database.
    '
    '*******************************************************

    Public Class OrdersDB

        '*******************************************************
        '
        ' CustomerDB.GetCustomerOrders() Method <a name="GetCustomerOrders"></a>
        '
        ' The GetCustomerOrders method returns a bindable
        ' DataSet of all past orders placed by a specified
        ' customers.
        '
        ' Other relevant sources:  
        '     + <a href="srcview.aspx?path=../OrderList.src&file=docs/OrdersList.htm" style="color:green">OrdersList Stored Procedure</a>
        '
        '*******************************************************        
        
        Public Function GetCustomerOrders(customerID As String) As DataSet

            ' Create Instance of Connection and Command Object
            Dim myConnection As SQLConnection = New SQLConnection(ConferenceDB.ConnectionString)
            Dim myCommand As SQLDataSetCommand = New SQLDataSetCommand("OrdersList", myConnection)

            ' Mark the Command as a SPROC
            myCommand.SelectCommand.CommandType = CommandType.StoredProcedure

            ' Add Parameters to SPROC
            Dim parameterCustomerid As SQLParameter = New SQLParameter("@CustomerID", SQLDataType.Int, 4)
            parameterCustomerid.Value = customerID
            myCommand.SelectCommand.Parameters.Add(parameterCustomerid)

            ' Create and Fill the DataSet
            Dim myDataSet As DataSet = New DataSet()
            myCommand.FillDataSet(myDataSet, "OrderItems")

            ' Return the DataSet
            return myDataSet
            
        End Function

        '*******************************************************
        '
        ' OrdersDB.GetOrderDetails() Method <a name="GetOrderDetails"></a>
        '
        ' The GetOrderDetails method returns an OrderDetails
        ' struct containing information about the specified
        ' order.
        '
        ' Other relevant sources:  
        '     + <a href="srcview.aspx?path=../OrderDetails.src&file=docs/OrdersDetail.htm" style="color:green">OrdersDetail Stored Procedure</a>
        '
        '*******************************************************   

        Public Function GetOrderDetails(orderID As Integer) As OrderDetails

            ' Create Instance of Connection and Command Object
            Dim myConnection As SQLConnection = New SQLConnection(ConferenceDB.ConnectionString)
            Dim myCommand As SQLDataSetCommand = New SQLDataSetCommand("OrdersDetail", myConnection)

            ' Mark the Command as a SPROC
            myCommand.SelectCommand.CommandType = CommandType.StoredProcedure

            ' Add Parameters to SPROC
            Dim parameterOrderID As SQLParameter = New SQLParameter("@OrderID", SQLDataType.Int, 4)
            parameterOrderID.Value = orderID
            myCommand.SelectCommand.Parameters.Add(parameterOrderID)

            Dim parameterOrderDate As SQLParameter = New SQLParameter("@OrderDate", SQLDataType.DateTime, 8)
            parameterOrderDate.Direction = ParameterDirection.Output
            myCommand.SelectCommand.Parameters.Add(parameterOrderDate)

            Dim parameterShipDate As SQLParameter = New SQLParameter("@ShipDate", SQLDataType.DateTime, 8)
            parameterShipDate.Direction = ParameterDirection.Output
            myCommand.SelectCommand.Parameters.Add(parameterShipDate)

            Dim parameterOrderTotal As SQLParameter = New SQLParameter("@OrderTotal", SQLDataType.Float, 8)
            parameterOrderTotal.Direction = ParameterDirection.Output
            myCommand.SelectCommand.Parameters.Add(parameterOrderTotal)

            ' Create and Fill the DataSet
            Dim myDataSet As DataSet = New DataSet()
            myCommand.FillDataSet(myDataSet, "Reviews")

            ' Create and Populate OrderDetails Struct using 
            ' Output Params from the SPROC, as well as the
            ' populated dataset from the SQLDataSetCommand
            
            Dim myOrderDetails As OrderDetails = New OrderDetails()
            
            myOrderDetails.OrderDate = Ctype(parameterOrderDate.Value, DateTime)
            myOrderDetails.ShipDate = CType(parameterShipDate.Value, DateTime)
            myOrderDetails.OrderTotal = CDbl(parameterOrderTotal.Value)
            myOrderDetails.OrderItems = myDataSet

            ' Return the DataSet
            return myOrderDetails
        
        End Function
        
        '*******************************************************
        '
        ' OrdersDB.CalculateShippingDate() Method <a name="CalculateShippingDate"></a>
        '
        ' 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 As String, cartID As String) As DateTime
        
            Dim x As New Random()
            Dim myrandom As Double = CDbl(x.Next(0,3))
            return (DateTime.Now).AddDays(myrandom)
        
        End Function
        
        '*******************************************************
        '
        ' OrdersDB.PlaceOrder() Method <a name="PlaceOrder"></a>
        '
        ' The PlaceOrder method places an order within the
        ' Conference Orders Database and then clears out the current
        ' items within the shopping cart.
        '
        ' Other relevant sources:  
        '     + <a href="srcview.aspx?path=../CheckOut.src&file=docs/OrdersAdd.htm" style="color:green">OrdersAdd Stored Procedure</a>
        '
        '******************************************************* 

        Public Function PlaceOrder(customerID As String, cartID As String) As Integer

            ' Create Instance of Connection and Command Object
            Dim myConnection As SQLConnection = New SQLConnection(ConferenceDB.ConnectionString)
            Dim myCommand As SQLCommand = New SQLCommand("OrdersAdd", myConnection)

            ' Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure

            ' Add Parameters to SPROC
            Dim parameterCustomerID As SQLParameter = New SQLParameter("@CustomerID", SQLDataType.Int, 4)
            parameterCustomerID.Value = Int32.Parse(customerID)
            myCommand.Parameters.Add(parameterCustomerID)

            Dim parameterCartID As SQLParameter = New SQLParameter("@CartID", SQLDataType.NChar, 50)
            parameterCartID.Value = cartID
            myCommand.Parameters.Add(parameterCartID)

            Dim parameterShipDate As SQLParameter = New SQLParameter("@ShipDate", SQLDataType.DateTime, 8)
            parameterShipDate.Value = CalculateShippingDate(customerID, cartID)
            myCommand.Parameters.Add(parameterShipDate)

            Dim parameterOrderDate As SQLParameter = New SQLParameter("@OrderDate", SQLDataType.DateTime, 8)
            parameterOrderDate.Value = DateTime.Now
            myCommand.Parameters.Add(parameterOrderDate)
            
            Dim parameterOrderID As SQLParameter = New SQLParameter("@OrderID", SQLDataType.Int, 4)
            parameterOrderID.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(parameterOrderID)

            Try 
                ' Open the connection and execute the Command
                myConnection.Open()
                myCommand.Execute()
            Catch e As Exception
                ' An error occurred, pass the exception up
                throw e
            Finally 
                ' Close the Connection
                If myConnection.State = DBObjectState.Open then
                    myConnection.Close()
                End If
            End Try
            
            ' Return the OrderID
            return CInt(parameterOrderID.Value)
        
        End Function
    
    End Class 

End Namespace