orderprocessor.vb

来自「Visual Basic.NET控件时尚编程百例」· VB 代码 · 共 119 行

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

Namespace Services

    Public Class OrderProcessor

         Private Connstring As String = "Data Source=localhost;Initial Catalog=store;user id=sa;pwd=sa"

        Public Function InsertOrders(ByVal strCustomerID As String) As String
            Dim sqlcmdAddItem As New SqlCommand("sp_Orders_INS")
            With sqlcmdAddItem
                .CommandType = CommandType.StoredProcedure
                With .Parameters
                    .Add("@strCustomerID", SqlDbType.Int, 4)
                    .Add("@dtShipDate", SqlDbType.DateTime, 8)
                    .Add("@intOrderID", SqlDbType.Int, 4)
                    .Item("@strCustomerID").Value = CInt(Val(strCustomerID))
                    .Item("@dtShipDate").Value = Now.Date.AddDays(4)
                    .Item("@intOrderID").Direction = ParameterDirection.Output
                End With
            End With
            Dim intOrderID As String
            Dim sqlconOrders As New SqlConnection(Connstring)
            sqlconOrders.Open()
            sqlcmdAddItem.Connection = sqlconOrders
            sqlcmdAddItem.ExecuteNonQuery()
            sqlconOrders.Close()
            intOrderID = sqlcmdAddItem.Parameters("@intOrderID").Value
            Return intOrderID.ToString
        End Function

        Public Function CustomerOrderDetails(ByVal strCustomerID As String) _
        As SqlDataReader
            Dim sqlcmdItemCount As New SqlCommand("sp_Orders_SEL_byCustomerID")
            With sqlcmdItemCount
                .CommandType = CommandType.StoredProcedure
                With .Parameters
                    .Add("@intCustomerID", SqlDbType.Int, 4)
                    .Item("@intCustomerID").Value = CInt(Val(strCustomerID))
                End With
            End With
            Dim Result As SqlDataReader
            Dim sqlconOrders As New SqlConnection(Connstring)
            sqlconOrders.Open()
            sqlcmdItemCount.Connection = sqlconOrders
            Result = sqlcmdItemCount.ExecuteReader
            Return Result
        End Function

        Public Function InsertOrderItem(ByVal OrderID As String, _
        ByVal ProductID As String, ByVal Quantity As Integer)
            Dim sqlcmdAddItem As New SqlCommand("sp_OrderItem_INS")
            With sqlcmdAddItem
                .CommandType = CommandType.StoredProcedure
                With .Parameters
                    .Add("@intOrderID", SqlDbType.Int, 4)
                    .Add("@ProductID", SqlDbType.NVarChar, 38)
                    .Add("@Quantity", SqlDbType.Int, 4)
                    .Item("@intOrderID").Value = CInt(Val(OrderID))
                    .Item("@ProductID").Value = ProductID
                    .Item("@Quantity").Value = Quantity
                End With
            End With
            Dim intOrderID As String
            Dim sqlconOrders As New SqlConnection(Connstring)
            sqlconOrders.Open()
            sqlcmdAddItem.Connection = sqlconOrders
            sqlcmdAddItem.ExecuteNonQuery()
            sqlconOrders.Close()
        End Function

        Public Function GetOrderDetails(ByVal OrderID As String) As OrderDetails
            Dim sqlcmdAddItem As New SqlCommand("sp_OrderDetails_SEL_byOrderID")
            With sqlcmdAddItem
                .CommandType = CommandType.StoredProcedure
                With .Parameters
                    .Add("@intOrderID", SqlDbType.Int, 4)
                    .Add("@OrderDate", SqlDbType.DateTime, 8)
                    .Add("@ShipDate", SqlDbType.DateTime, 8)
                    .Add("@OrderTotal", SqlDbType.Money, 8)
                    .Item("@intOrderID").Value = CInt(Val(OrderID))
                    .Item("@OrderDate").Direction = ParameterDirection.Output
                    .Item("@ShipDate").Direction = ParameterDirection.Output
                    .Item("@OrderTotal").Direction = ParameterDirection.Output
                End With
            End With
            Dim myOrderDetails As New DataSet()
            Dim sqlconOrders As New SqlConnection(Connstring)
            sqlcmdAddItem.Connection = sqlconOrders
            Dim sqladpCategories As New SqlDataAdapter(sqlcmdAddItem)
            sqladpCategories.Fill(myOrderDetails, "OrderDetails")
            Dim objOrderDetails As New OrderDetails()
            With objOrderDetails
                .OrderDate = CDate(sqlcmdAddItem.Parameters("@OrderDate").Value)
                .ShipDate = CDate(sqlcmdAddItem.Parameters("@ShipDate").Value)
                .SubTotal = Val(sqlcmdAddItem.Parameters("@OrderTotal").Value)
                .OrderItems = myOrderDetails
            End With
            Return objOrderDetails
        End Function

    End Class




    Public Class OrderDetails
        Public OrderDate As DateTime
        Public ShipDate As DateTime
        Public SubTotal As Double
        Public OrderItems As DataSet
    End Class

End Namespace