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

Namespace Conference

    '*******************************************************
    '
    ' ShoppingCartDB Class
    '
    ' Business/Data Logic Class that encapsulates all data
    ' logic necessary to add/remove/update/purchase items
    ' within an Conference shopping cart.
    '
    '*******************************************************

    Public Class ShoppingCartDB 
 
        '*******************************************************
        '
        ' ShoppingCartDB.GetItems() Method <a name="GetItems"></a>
        '
        ' The GetItems method returns a bindable
        ' DataSet of all items within a shopping cart.
        '
        ' Other relevant sources:  
        '     + <a href="srcview.aspx?path=../ShoppingCart.src&file=docs/ShoppingCartList.htm" style="color:green">ShoppingCartList Stored Procedure</a>
        '
        '*******************************************************   
 
        Public Function GetItems(cartID 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("ShoppingCartList", myConnection)

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

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

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

            ' Return the DataSet
            return myDataSet
        
        End Function

        '*******************************************************
        '
        ' ShoppingCartDB.AddItem() Method <a name="AddItem"></a>
        '
        ' The AddItem method adds an item into a shopping cart.
        '
        ' Other relevant sources:  
        '     + <a href="srcview.aspx?path=../AddToCart.src&file=docs/ShoppingCartAddItem.htm" style="color:green">ShoppingCartAddItem Stored Procedure</a>
        '
        '******************************************************* 

        Public Sub AddItem(cartID As String, productID As Integer, quantity As Integer) 
                        
            ' Create Instance of Connection and Command Object
            Dim myConnection As SQLConnection = New SQLConnection(ConferenceDB.ConnectionString)
            Dim myCommand As SQLCommand = New SQLCommand("ShoppingCartAddItem", myConnection)

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

            ' Add Parameters to SPROC
            Dim parameterProductID As SQLParameter = New SQLParameter("@ProductID", SQLDataType.Int, 4)
            parameterProductID.Value = productID
            myCommand.Parameters.Add(parameterProductID)

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

            Dim parameterQuantity As SQLParameter = New SQLParameter("@Quantity", SQLDataType.Int, 4)
            parameterQuantity.Value = quantity
            myCommand.Parameters.Add(parameterQuantity)

            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
        
        End Sub

        '*******************************************************
        '
        ' 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="srcview.aspx?path=../ShoppingCart.src&file=docs/ShoppingCartUpdate.htm" style="color:green">ShoppingCartUpdate Stored Procedure</a>
        '
        '******************************************************* 

        Public Sub UpdateItem(cartID As String, productID As Integer, quantity As Integer) 
            
            ' Create Instance of Connection and Command Object
            Dim myConnection As SQLConnection = New SQLConnection(ConferenceDB.ConnectionString)
            Dim myCommand As SQLCommand = New SQLCommand("ShoppingCartUpdate", myConnection)

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

            ' Add Parameters to SPROC
            Dim parameterProductID As SQLParameter = New SQLParameter("@ProductID", SQLDataType.Int, 4)
            parameterProductID.Value = productID
            myCommand.Parameters.Add(parameterProductID)

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

            Dim parameterQuantity As SQLParameter = New SQLParameter("@Quantity", SQLDataType.Int, 4)
            parameterQuantity.Value = quantity
            myCommand.Parameters.Add(parameterQuantity)

            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
        
        End Sub

        '*******************************************************
        '
        ' ShoppingCartDB.RemoveItem() Method <a name="RemoveItem"></a>
        '
        ' The RemoveItem method removes an item from a 
        ' shopping cart.
        '
        ' Other relevant sources:  
        '     + <a href="srcview.aspx?path=../ShoppingCart.src&file=docs/ShoppingCartRemoveItem.htm" style="color:green">ShoppingCartRemoveItem Stored Procedure</a>
        '
        '******************************************************* 

        Public Sub RemoveItem(cartID As String, productID As Integer) 

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

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

            ' Add Parameters to SPROC
            Dim parameterProductID As SQLParameter = New SQLParameter("@ProductID", SQLDataType.Int, 4)
            parameterProductID.Value = productID
            myCommand.Parameters.Add(parameterProductID)

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

            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
        
        End Sub

        '*******************************************************
        '
        ' ShoppingCartDB.GetItemCount() Method <a name="GetItemCount"></a>
        '
        ' The GetItemCount method returns the number of items
        ' within a shopping cart.
        '
        ' Other relevant sources:  
        '     + <a href="srcview.aspx?path=../ShoppingCart.src&file=docs/ShoppingCartItemCount.htm" style="color:green">ShoppingCartItemCount Stored Procedure</a>
        '
        '******************************************************* 

        Public Function GetItemCount(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("ShoppingCartItemCount", myConnection)