orders.vb

编程之道VB.NETt程序设计入门-589M.zip

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes

Imports Nozama.General

Namespace Nozama.DataAccess

    Public Class Orders
        Implements IDisposable
 
        Private insertOrderComm As SqlCommand
        Private insertOrderDetailComm As SqlCommand

        Public Sub New()
            MyBase.New()
        End Sub

        Public Sub Dispose() Implements IDisposable.Dispose
            Dispose(True)
            GC.SuppressFinalize(True)
        End Sub

        Protected Overridable Sub Dispose(ByVal disposing As Boolean)
            If Not disposing Then
                Exit Sub
            End If

            If Not insertOrderComm Is Nothing Then
                If Not insertOrderComm.Connection Is Nothing Then
                    insertOrderComm.Connection.Dispose()
                End If
                insertOrderComm.Dispose()
                insertOrderComm = Nothing
            End If

            If Not insertOrderDetailComm Is Nothing Then
                If Not insertOrderDetailComm.Connection Is Nothing Then
                    insertOrderDetailComm.Connection.Dispose()
                End If
                insertOrderDetailComm.Dispose()
                insertOrderDetailComm = Nothing
            End If
        End Sub

        Private Function BuildInsertCommand() As SqlCommand
            If insertOrderComm Is Nothing Then
                Dim conn As New SqlConnection(Settings.ConnectionString)
                conn.Open()
                insertOrderComm = New SqlCommand("InsertOrder", conn)
                insertOrderComm.CommandType = CommandType.StoredProcedure

                With insertOrderComm.Parameters
                    .Add(New SqlParameter("@UID", SqlDbType.Int)).Direction = ParameterDirection.Output
                    .Add(New SqlParameter("@OrderDate", SqlDbType.DateTime))
                    .Add(New SqlParameter("@CustomerName", SqlDbType.NVarChar))
                    .Add(New SqlParameter("@Address", SqlDbType.NVarChar))
                    .Add(New SqlParameter("@TotalPrice", SqlDbType.Money))
                    .Add(New SqlParameter("@CreditCardNumber", SqlDbType.NVarChar))
                End With
            End If

            Return insertOrderComm
        End Function

        Private Function BuildInsertDetailCommand() As SqlCommand
            If insertOrderDetailComm Is Nothing Then
                Dim conn As New SqlConnection(Settings.ConnectionString)
                conn.Open()
                insertOrderDetailComm = New SqlCommand("InsertOrderDetail", conn)
                insertOrderDetailComm.CommandType = CommandType.StoredProcedure

                With insertOrderDetailComm.Parameters
                    .Add(New SqlParameter("@OrderID", SqlDbType.Int))
                    .Add(New SqlParameter("@BookID", SqlDbType.Int))
                    .Add(New SqlParameter("@Price", SqlDbType.Money))
                    .Add(New SqlParameter("@Quantity", SqlDbType.Int))
                End With
            End If

            Return insertOrderDetailComm
        End Function

        Public Function InsertOrder(ByVal order As OrderData) As Int32
            Dim Command As SqlCommand = BuildInsertCommand()

            With order.Tables(OrderData.ORDERS_TABLE).Rows(0)

                Command.Parameters.Item("@OrderDate").Value = .Item(OrderData.ORDER_DATE)
                Command.Parameters.Item("@CustomerName").Value = .Item(OrderData.CUSTOMER_NAME)
                Command.Parameters.Item("@Address").Value = .Item(OrderData.ADDRESS)
                Command.Parameters.Item("@TotalPrice").Value = .Item(OrderData.TOTAL_PRICE)
                Command.Parameters.Item("@CreditCardNumber").Value = .Item(OrderData.CREDIT_CARD_NUMBER)
            End With
            Command.ExecuteNonQuery()
            Dim OrderID As Int32 = Command.Parameters.Item("@UID").Value

            Command = BuildInsertDetailCommand()
            Dim row As DataRow
            For Each row In order.Tables(OrderData.ORDER_BOOK_TABLE).Rows
                With row
                    Command.Parameters.Item("@OrderID").Value = OrderID
                    Command.Parameters.Item("@BookID").Value = .Item(OrderData.BOOK_ID)
                    Command.Parameters.Item("@Price").Value = .Item(OrderData.PRICE)
                    Command.Parameters.Item("@Quantity").Value = .Item(OrderData.QUANTITY)
                End With
                Command.ExecuteNonQuery()
            Next

        End Function
    End Class

End Namespace