nwordersws.vb

来自「wrox出版社的另一套经典的VB2005数据库编程学习书籍,收集了书中源码,郑重」· VB 代码 · 共 724 行 · 第 1/3 页

            Dim cmdShips As New SqlCommand(strSQL, cnnNWind)
            cmdShips.CommandType = CommandType.Text
            Dim rdrShips As SqlDataReader
            Try
                cnnNWind.Open()
                rdrShips = cmdShips.ExecuteReader()
                intRow = 0
                With rdrShips
                    strSoapExc = "Error reading Shippers table"
                    While .Read
                        Dim objShip As New shipper()
                        objShip.shipID = .GetInt32(0)
                        objShip.shipName = .GetString(1)
                        objShips.shippers(intRow) = objShip
                        intRow += 1
                    End While
                End With
                ReDim Preserve objShips.shippers(intRow - 1)
                Return objShips
            Catch ex As Exception
                Dim excSOAP As New SoapException(strSoapExc, SoapException.ClientFaultCode)
                Throw excSOAP
            Finally
                If cnnNWind.State = ConnectionState.Open Then
                    cnnNWind.Close()
                End If
            End Try
        End Function

        <WebMethod(Description:="The GetProducts Web method returns " + _
        "a prodLookup object with IDs and product names of Northwind products. " + _
        "This method is intended for filling an InfoPath drop-down list from " + _
        "a secondary data source to select valid product ID values.")> _
        Public Function GetProducts() As prodLookup
            Dim strSQL As String
            Dim intRow As Integer
            Dim strSoapExc As String = "Can't connect to NorthwindCS database"
            Dim objProds As New prodLookup()

            Dim cnnNWind As New SqlConnection(strReadConnect)
            strSQL = "SELECT ProductID, ProductName FROM Products ORDER BY ProductName"
            Dim cmdProds As New SqlCommand(strSQL, cnnNWind)
            cmdProds.CommandType = CommandType.Text
            Dim rdrProds As SqlDataReader
            Try
                cnnNWind.Open()
                rdrProds = cmdProds.ExecuteReader()
                intRow = 0
                With rdrProds
                    strSoapExc = "Error reading Products table"
                    While .Read
                        Dim objProd As New product()
                        objProd.prodID = .GetInt32(0)
                        objProd.prodName = .GetString(1)
                        objProds.products(intRow) = objProd
                        intRow += 1
                    End While
                End With
                ReDim Preserve objProds.products(intRow - 1)
                Return objProds
            Catch ex As Exception
                Dim excSOAP As New SoapException(strSoapExc, SoapException.ClientFaultCode)
                Throw excSOAP
            Finally
                If cnnNWind.State = ConnectionState.Open Then
                    cnnNWind.Close()
                End If
            End Try
        End Function

        'Following added 12/15/03 to support Chapter 17
        <WebMethod(Description:="The GetProductsDetailed Web method returns " + _
        "a prodLookupDtl object with IDs, product names, SKU (QuantityPerUnit), " + _
        "and prices of Northwind products. " + _
        "This method is intended for filling an InfoPath drop-down list from " + _
        "a secondary data source to select valid product ID values and provide prices.")> _
        Public Function GetProductsDetailed() As prodLookupDtl
            Dim strSQL As String
            Dim intRow As Integer
            Dim strSoapExc As String = "Can't connect to NorthwindCS database"
            Dim objProds As New prodLookupDtl()

            Dim cnnNWind As New SqlConnection(strReadConnect)
            strSQL = "SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice FROM Products ORDER BY ProductName"
            Dim cmdProds As New SqlCommand(strSQL, cnnNWind)
            cmdProds.CommandType = CommandType.Text
            Dim rdrProds As SqlDataReader
            Try
                cnnNWind.Open()
                rdrProds = cmdProds.ExecuteReader()
                intRow = 0
                With rdrProds
                    strSoapExc = "Error reading Products table"
                    While .Read
                        Dim objProd As New productDtl()
                        objProd.prodID = .GetInt32(0)
                        objProd.prodName = .GetString(1)
                        objProd.prodSKU = .GetString(2)
                        objProd.prodPrice = .GetSqlMoney(3).ToDouble
                        objProds.productsDtl(intRow) = objProd
                        intRow += 1
                    End While
                End With
                ReDim Preserve objProds.productsDtl(intRow - 1)
                Return objProds
            Catch ex As Exception
                Dim excSOAP As New SoapException(strSoapExc, SoapException.ClientFaultCode)
                Throw excSOAP
            Finally
                If cnnNWind.State = ConnectionState.Open Then
                    cnnNWind.Close()
                End If
            End Try
        End Function

        <WebMethod(Description:="The GetCustomers Web method returns " + _
        "a custLookup object with IDs and company names of Northwind customers. " + _
        "This method is intended for filling an InfoPath drop-down list from " + _
        "a secondary data source to select valid customer ID values.")> _
        Public Function GetCustomers() As custLookup
            Dim strSQL As String
            Dim intRow As Integer
            Dim strSoapExc As String = "Can't connect to NorthwindCS database"
            Dim objCusts As New custLookup()

            Dim cnnNWind As New SqlConnection(strReadConnect)
            strSQL = "SELECT CustomerID, CompanyName FROM Customers"
            Dim cmdCusts As New SqlCommand(strSQL, cnnNWind)
            cmdCusts.CommandType = CommandType.Text
            Dim rdrCusts As SqlDataReader
            Try
                cnnNWind.Open()
                rdrCusts = cmdCusts.ExecuteReader()
                intRow = 0
                With rdrCusts
                    strSoapExc = "Error reading Products table"
                    While .Read
                        Dim objCust As New customer()
                        objCust.custID = .GetString(0)
                        objCust.custName = .GetString(1)
                        objCusts.customers(intRow) = objCust
                        intRow += 1
                    End While
                End With
                ReDim Preserve objCusts.customers(intRow - 1)
                Return objCusts
            Catch ex As Exception
                Dim excSOAP As New SoapException(strSoapExc, SoapException.ClientFaultCode)
                Throw excSOAP
            Finally
                If cnnNWind.State = ConnectionState.Open Then
                    cnnNWind.Close()
                End If
            End Try
        End Function
    End Class

    '*******************************
    'Orders and OrderDetails classes
    'Maximum of 25 line items/order
    '*******************************

    Public Class clsOrder
        Public OrderID As Int32
        Public CustomerID As String
        Public EmployeeID As Int32
        Public OrderDate As Date
        Public RequiredDate As Date
        Public ShippedDate As Date
        Public ShipVia As Int32
        Public Freight As Decimal
        Public ShipName As String
        Public ShipAddress As String
        Public ShipCity As String
        Public ShipRegion As String
        Public ShipPostalCode As String
        Public ShipCountry As String
        Public OrderDetails(24) As OrderDetail
    End Class

    Public Class OrderDetail
        Public OrderID As Int32
        Public ProductID As Int32
        Public UnitPrice As Decimal
        Public Quantity As Int16
        Public Discount As Decimal
    End Class

    '***********************************
    'Classes for lookup services
    'Each has an allowance for additions
    '***********************************

    Public Class empLookup
        Public employees(20) As employee
    End Class

    Public Class employee
        Public emplID As Int32
        Public emplName As String
    End Class

    Public Class shipLookup
        Public shippers(10) As shipper
    End Class

    Public Class shipper
        Public shipID As Int32
        Public shipName As String
    End Class

    Public Class prodLookup
        Public products(100) As product
    End Class

    Public Class product
        Public prodID As Int32
        Public prodName As String
    End Class

    Public Class prodLookupDtl
        Public productsDtl(100) As productDtl
    End Class

    Public Class productDtl
        Public prodID As Int32
        Public prodName As String
        Public prodSKU As String
        Public prodPrice As Double
    End Class

    Public Class custLookup
        Public customers(100) As customer
    End Class

    Public Class customer
        Public custID As String
        Public custName As String
    End Class
End Namespace