nwordersws.vb

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

'*************************************************************************************
'This project was upgraded from a Visual Studio 1.1 ASMX Web service
'that runs on the OakLeaf Web site at http://www.oakleaf.ws/nwordersws/nwordersws.asmx
'Search for strReadConnect and change the two connection strings to suit your 
'SQL Server 2000, MSDE 2000, SQL Server 2005, or SQL Express development setup
'*************************************************************************************

Option Explicit On
Option Strict On

Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web

Namespace NWOrdersWS
    '**************************
    'NWOrdersWS XML Web service
    '**************************

    <WebService(Namespace:="http://oakleaf.ws/webservices/nwordersws", _
    Description:="This demonstration XML Web service connects to a local Northwind " + _
    "SQL Server database and has Web methods for retrieving and creating or updating " + _
    "Orders and Order Details records with stored procedures. It also has methods " + _
    "for creating Microsoft Office InfoPath 2003 secondary data sources to populate drop-down lists. " + _
     "Examples in ""Introducing Microsoft Office InfoPath 2003"" (Microsoft Press, ISBN " + _
    "0-7356-1952-2, published 6/9/2004) show you how to design InfoPath " + _
    "forms that consume NWOrdersWS's Web methods. You can read more about this Web service " + _
    "at http://www.oakleaf.ws/infopath/nworders.aspx. " + _
    "You can also use the MSDE 2000 NorthwindCS database or a Northwind sample database " + _
    "that runs under SQL Server 2005 or SQL Server Express.")> _
    <WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
     Public Class NWOrdersWS
        Inherits System.Web.Services.WebService
        'Change these connection strings to suit your SQL Server/MSDE setup
        Private strReadConnect As String = "Server=localhost;UID=sa;PWD=whidbey;Database=Northwind"
        Private strWriteConnect As String = "Server=localhost;UID=sa;PWD=whidbey;Database=Northwind"

        '******************************************
        'Stored procedure versions of the following
        'Methods: GetOrder and UpdateOrInsertOrder
        '******************************************

        <WebMethod(Description:="The GetOrderSP Web method accepts an integer OrderID value and returns " + _
        "a serialized Order object as its SOAP response message. Valid OrderID values " + _
        "range from 10248 to 11077 or higher, depending on how many orders you have added. " + _
        "If the OrderID is invalid, the method returns a SOAP exception.")> _
        Public Function GetOrderSP(ByVal intOrderID As Integer) As clsOrder
            'Get order data from server and return Order object
            Dim strSQL As String
            Dim intCol As Integer
            Dim intRow As Integer
            Dim objOrder As New clsOrder()
            Dim rdrOrder As SqlDataReader = Nothing
            Dim strSoapExc As String = "Can't connect to NorthwindCS database"

            Dim cnnNWind As New SqlConnection(strReadConnect)
            'Following commands return two result sets with stored procedure
            strSQL = "ipGetOrder"
            Dim cmdOrder As New SqlCommand(strSQL, cnnNWind)
            cmdOrder.CommandType = CommandType.StoredProcedure
            Dim prmOrderID As SqlParameter = cmdOrder.Parameters.Add("@OrderID", SqlDbType.Int)
            prmOrderID.Value = intOrderID
            Try
                cnnNWind.Open()
                rdrOrder = cmdOrder.ExecuteReader()
                With rdrOrder
                    'Read the Order resultset
                    .Read()
                    strSoapExc = "Invalid OrderID " + intOrderID.ToString
                    For intCol = 0 To .FieldCount - 1
                        Select Case intCol
                            Case 0
                                objOrder.OrderID = .GetInt32(intCol)
                            Case 1
                                objOrder.CustomerID = .GetString(intCol)
                            Case 2
                                objOrder.EmployeeID = .GetInt32(intCol)
                            Case 3
                                objOrder.OrderDate = .GetDateTime(intCol)
                            Case 4
                                If Not IsDBNull(.Item(intCol)) Then
                                    objOrder.RequiredDate = .GetDateTime(intCol)
                                End If
                            Case 5
                                If Not IsDBNull(.Item(intCol)) Then
                                    objOrder.ShippedDate = .GetDateTime(intCol)
                                End If
                            Case 6
                                objOrder.ShipVia = .GetInt32(intCol)
                            Case 7
                                objOrder.Freight = .GetDecimal(intCol)
                            Case 8
                                objOrder.ShipName = .GetString(intCol)
                            Case 9
                                objOrder.ShipAddress = .GetString(intCol)
                            Case 10
                                objOrder.ShipCity = .GetString(intCol)
                            Case 11
                                If IsDBNull(.Item(intCol)) Then
                                    objOrder.ShipRegion = ""
                                Else
                                    objOrder.ShipRegion = .GetString(intCol)
                                End If
                            Case 12
                                If IsDBNull(.Item(intCol)) Then
                                    objOrder.ShipPostalCode = ""
                                Else
                                    objOrder.ShipPostalCode = .GetString(intCol)
                                End If
                            Case 13
                                objOrder.ShipCountry = .GetString(intCol)
                        End Select
                    Next intCol
                    Dim strTest As String = ""
                    If .NextResult Then
                        'Read the OrderDetails resultset
                        While .Read
                            Dim Details As New OrderDetail()
                            For intCol = 0 To .FieldCount - 1
                                Select Case intCol
                                    Case 0
                                        Details.OrderID = .GetInt32(intCol)
                                    Case 1
                                        Details.ProductID = .GetInt32(intCol)
                                    Case 2
                                        Details.UnitPrice = .GetDecimal(intCol)
                                    Case 3
                                        Details.Quantity = .GetInt16(intCol)
                                    Case 4
                                        'Handles Real/Single or Decimal data types
                                        Details.Discount = CType(.GetValue(intCol), Decimal)
                                End Select
                            Next intCol
                            objOrder.OrderDetails(intRow) = Details
                            intRow += 1
                        End While
                    Else
                        'Error: No line items for objOrder
                        strSoapExc = "Order " + intOrderID.ToString + " has no Order Details records"
                    End If
                End With
                'Remove the nil entries
                ReDim Preserve objOrder.OrderDetails(intRow - 1)
                Return objOrder
            Catch excOrder As Exception
                Dim excSOAP As New SoapException(strSoapExc, SoapException.ClientFaultCode)
                Throw excSOAP
            Finally
                If Not rdrOrder Is Nothing Then
                    rdrOrder.Close()
                End If
                If Not cnnNWind Is Nothing Then
                    cnnNWind.Close()
                End If
            End Try
        End Function

        <WebMethod(Description:="The UpdateOrInsertOrderSP Web method accepts a serialized Orders " + _
        "object as its SOAP request message. If the OrderID value is 0, the method inserts a new order and returns " + _
        "the new OrderID value. If the OrderID exists, the method updates the order and returns the OrderID value. " + _
        "If the OrderID is invalid or contains no OrderDetail items, the method throws " + _
        "a SOAP exception.")> _
        Public Function UpdateOrInsertOrderSP(ByVal objOrder As clsOrder) As Int32
            Dim intCtr As Int32
            Dim blnNewOrder As Boolean
            Dim blnCommit As Boolean
            Dim intOrderID As Int32
            Dim intReturnValue As Int32
            Dim cnnNwind As SqlConnection = Nothing
            Dim xactOrder As SqlTransaction = Nothing
            Dim cmdOrder As New SqlCommand()
            Dim cmdDetail As New SqlCommand()
            Dim strSoapExc As String
            Dim blnIsDiscountDecimal As Boolean = False

            'Test for at least one OrderDetails element (business rule)
            If objOrder.OrderDetails.GetUpperBound(0) = -1 Then
                strSoapExc = "The Order object must have at least one OrderDetail element."
                Dim excSOAP As New SoapException(strSoapExc, SoapException.ClientFaultCode)
                Throw excSOAP
                Exit Function
            End If

            'Open the connection (prematurely due to tests)
            cnnNwind = New SqlConnection(strWriteConnect)
            cmdOrder.Connection = cnnNwind
            Try
                cnnNwind.Open()
            Catch excOpen As Exception
                Dim excSOAP As New SoapException(excOpen.Message, SoapException.ClientFaultCode)
                Throw excSOAP
                Exit Function
            End Try

            If objOrder.OrderID <> 0 Then
                'Test for existence of order
                cmdOrder.CommandType = CommandType.Text
                cmdOrder.CommandText = "SELECT COUNT(*) FROM Orders WHERE OrderID = " + objOrder.OrderID.ToString
                intOrderID = CType(cmdOrder.ExecuteScalar, Int32)
                If intOrderID = 0 Then
                    strSoapExc = "OrderID " + objOrder.OrderID.ToString + " is invalid"
                    Dim excSOAP As New SoapException(strSoapExc, SoapException.ClientFaultCode)
                    Throw excSOAP
                    Exit Function
                End If
            End If

            'Test for data type of Order Details.Discount and set blnIsDiscountDecimal flag
            'Get a valid OrderID for the test with an SqlRecord
            cmdOrder.CommandType = CommandType.Text
            cmdOrder.CommandText = "SELECT MIN(OrderID) FROM [Order Details]"
            intOrderID = CType(cmdOrder.ExecuteScalar, Int32)
            cmdOrder.CommandText = "SELECT * FROM [Order Details] WHERE OrderID = " + intOrderID.ToString
            'SqlRecord was removed from the beta program
            'Dim srRow As SqlRecord
            'srRow = cmdOrder.ExecuteRow
            Dim sdrRow As SqlDataReader = cmdOrder.ExecuteReader
            sdrRow.Read()
            If sdrRow.GetFieldType(4).ToString = "Decimal" Then
                blnIsDiscountDecimal = True
            Else
                blnIsDiscountDecimal = False
            End If
            sdrRow.Close()

            cmdOrder.CommandType = CommandType.StoredProcedure
            'Add the Order body parameters for ipUpdateOrder and ipInsertOrder
            Dim ordOrderID As SqlParameter = Nothing
            If objOrder.OrderID = 0 Then
                blnNewOrder = True
            Else
                ordOrderID = cmdOrder.Parameters.Add("@OrderID", SqlDbType.Int)
            End If
            Dim ordCustomerID As SqlParameter = cmdOrder.Parameters.Add("@CustomerID", SqlDbType.VarChar, 5)
            Dim ordEmployeeID As SqlParameter = cmdOrder.Parameters.Add("@EmployeeID", SqlDbType.Int)
            Dim ordOrderDate As SqlParameter = cmdOrder.Parameters.Add("@OrderDate", SqlDbType.DateTime)
            Dim ordRequiredDate As SqlParameter = cmdOrder.Parameters.Add("@RequiredDate", SqlDbType.DateTime)
            Dim ordShippedDate As SqlParameter = cmdOrder.Parameters.Add("@ShippedDate", SqlDbType.DateTime)
            Dim ordShipVia As SqlParameter = cmdOrder.Parameters.Add("@ShipVia", SqlDbType.Int)
            Dim ordFreight As SqlParameter = cmdOrder.Parameters.Add("@Freight", SqlDbType.Money)