' ************************************************************************
        ' *                                                                      *
        ' *  Insert this code into the Wizard FinishButtonClick event handler    *
        ' *                                                                      *
        ' *  Remember to import the data access namespaces                       *
        ' *  System.Data and System.Data.SqlClient                               *
        ' *  at the top of the page.                                             *
        ' *                                                                      *
        ' ************************************************************************
    
        ' Insert the order and order lines into the database
        Dim conn As SqlConnection = Nothing
        Dim trans As SqlTransaction = Nothing
        Dim cmd As SqlCommand
        Dim cart As ShoppingCart = StoredShoppingCart.Read()

        ' if there is not shopping cart, then something has gone wrong between the start of 
        ' the checkout and the finish
        If cart Is Nothing OrElse cart.Items.Count = 0 Then
            e.Cancel = True
            Return
        End If


        ' try / catch protects us against exceptions
        Try
            Dim OrderID As Integer

            ' Create and open a new connection to the database
            conn = New SqlConnection(ConfigurationManager.ConnectionStrings("PPQ_DataConnectionString1").ConnectionString)
            conn.Open()

            ' Start a new transaction
            ' This means that we can perform multiple database operations as though they were one, 
            ' and if there were any problems they will all be cancelled. This stops the order being inserted,
            ' but not the order items, or vica-versa, or even the order and some (but not all) of the
            ' items being created (nothing worse than the order being delivered to find they've missed off the beer!).
            trans = conn.BeginTransaction

            ' create a new command - this will be used to executed the stored procedures
            ' The command has the connection set to the existing open connection, and 
            ' the transaction set to the transaction object just created
            cmd = New SqlCommand()
            cmd.Connection = conn
            cmd.Transaction = trans

            ' Set the order details: the name and type of the command, and the parameters and their types
            cmd.CommandText = "usp_InsertOrder"
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add("@Name", Data.SqlDbType.VarChar, 25)
            cmd.Parameters.Add("@Address", Data.SqlDbType.VarChar, 255)
            cmd.Parameters.Add("@ZipCode", Data.SqlDbType.VarChar, 15)
            cmd.Parameters.Add("@OrderDate", Data.SqlDbType.DateTime)
            cmd.Parameters.Add("@DeliveryCharge", Data.SqlDbType.Money)
            cmd.Parameters.Add("@TotalValue", Data.SqlDbType.Money)
            cmd.Parameters.Add("@OrderID", SqlDbType.Int)

            ' Set the values for the parameters
            ' The name and address come from text boxes in a wizard step, while
            ' the cart items come from the shopping cart
            ' Note that the last parameter is an Output parameter - this means that the value
            ' is supplied by the stored procedure, and it will be the ID of the order, 
            ' as the ID is automatically generated by the database
            cmd.Parameters("@Name").Value = CType(Wizard1.FindControl("txtName"), TextBox).Text
            cmd.Parameters("@Address").Value = CType(Wizard1.FindControl("txtAddress"), TextBox).Text
            cmd.Parameters("@ZipCode").Value = CType(Wizard1.FindControl("txtZipCode"), TextBox).Text
            cmd.Parameters("@OrderDate").Value = DateTime.Now()
            cmd.Parameters("@DeliveryCharge").Value = cart.DeliveryCharge
            cmd.Parameters("@TotalValue").Value = cart.Total
            cmd.Parameters("@OrderID").Direction = ParameterDirection.Output

            ' Execute the query and fetch the output parameter - the order ID
            cmd.ExecuteNonQuery()
            OrderID = Convert.ToInt32(cmd.Parameters("@OrderID").Value)


            ' Change the query and parameters for the order lines
            cmd.CommandText = "usp_InsertOrderItem"
            cmd.Parameters.Clear()
            cmd.Parameters.Add("@fkOrderID", Data.SqlDbType.Int)
            cmd.Parameters.Add("@MenuItemID", Data.SqlDbType.Int)
            cmd.Parameters.Add("@ItemSize", Data.SqlDbType.VarChar, 50)
            cmd.Parameters.Add("@ItemName", Data.SqlDbType.VarChar, 50)
            cmd.Parameters.Add("@Quantity", Data.SqlDbType.Int)
            cmd.Parameters.Add("@LineValue", Data.SqlDbType.Money)
            cmd.Parameters("@fkOrderID").Value = OrderID

            ' Loop through the items in the shopping cart, setting the 
            ' parameter values to the cart values
            For Each item As CartItem In cart.Items
                cmd.Parameters("@MenuItemID").Value = item.MenuItemID
                cmd.Parameters("@ItemSize").Value = item.ItemSize
                cmd.Parameters("@ItemName").Value = item.ItemName
                cmd.Parameters("@Quantity").Value = item.Quantity
                cmd.Parameters("@LineValue").Value = item.LineValue

                ' Execute the query, inserting the cart item
                cmd.ExecuteNonQuery()
            Next

            ' Commit the transaction
            ' If we've got to this point then there have been no errors and all
            ' database operations have succeeded. Committing the transaction tells
            ' the database "everything is OK, so go ahead and accept all of those changes we've done"
            trans.Commit()

            ' tell the user that the order is on the way
            CreateOrderSuccessLabel.Visible = True

        Catch SqlEx As SqlException
            ' some form of error - rollback the transaction 
            ' and rethrow the exception
            If trans IsNot Nothing Then
                ' Rolling back the transaction tells the database
                ' "something was wrong, so ignore all of the changes we've done"
                trans.Rollback()

                ' After the rollback we are at the same place as we were at the beginning 
                ' of this event procedure so neither the order, nor the order items have been
                ' added to the database
            End If

            ' Log the exception
            'Tools.log("An error occurred while creating the order", SqlEx)
            Throw New Exception("An error occurred while creating the order", SqlEx)

            ' Show the "something went wrong" label and return from the procedure
            CreateOrderErrorLabel.Visible = True

            ' We haven't cleared the items from the cart,
            ' so returning here leaves them intact
            Return
        Finally
            ' If the connection object is valid then close the connection
            If conn IsNot Nothing Then
                conn.Close()
            End If

        End Try

        ' we will only reach here if the order has been created sucessfully
        ' so clear the cart
        cart.Items.Clear()