dcordersws.vb

wrox出版社的另一套经典的VB2005数据库编程学习书籍,收集了书中源码,郑重推荐,电子书,电子书下载

Option Explicit On
Option Strict On

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

<WebService(Namespace:="http://whatever.com/webservices/examples", _
Description:="The starting point for Chapter 9抯 examples.")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
Public Class DCOrdersWS
    Inherits System.Web.Services.WebService

    <WebMethod(Description:="Returns the TOP n Orders and Order Details records.")> _
     Public Function GetTopOrdersAndDetails(ByVal Number As Integer) As DataSet
        'Explicit column list is required because Orders table might be modified
        Dim strSQL1 As String = "SELECT TOP " + Number.ToString + _
         " OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, " + _
         "ShippedDate, ShipVia, Freight, ShipName, ShipAddress, " + _
         "ShipCity, ShipRegion, ShipPostalCode, ShipCountry " + _
         "FROM Orders ORDER BY OrderID DESC"
        Dim strSQL2 As String = "SELECT * FROM [Order Details] " + _
         "WHERE OrderID IN (SELECT TOP " + Number.ToString + _
         " OrderID FROM Orders ORDER BY OrderID DESC)"
        Return GetOrders(strSQL1, strSQL2)
    End Function

    Private Function GetOrders(ByVal strOrdersSql As String, _
     ByVal strDetailsSql As String) As DataSet
        'Create the typed DataSet from the schema
        Dim dsOrders As DataSet = New OrdersDataSet
        'Replace tempuri.org
        dsOrders.Namespace = _
          "http://whatever.com/webservices/northwind/orders/ordersdc"
        'Set up the connection
        Dim cnNwind As New SqlConnection
        Dim strConn As String = ConfigurationManager.ConnectionStrings( _
         "NorthwindConnectionString").ConnectionString

        cnNwind.ConnectionString = strConn

        'Define SqlCommands and SqlDataAdapters
        Dim cmOrders As SqlCommand = Nothing
        Dim cmDetails As SqlCommand = Nothing
        Dim daOrders As SqlDataAdapter = Nothing
        Dim daDetails As SqlDataAdapter = Nothing

        If strOrdersSql.Length > 0 Then
            'Create the Orders command and data adapter
            cmOrders = cnNwind.CreateCommand
            cmOrders.CommandText = strOrdersSql
            daOrders = New SqlDataAdapter
            daOrders.SelectCommand = cmOrders
        End If

        If strDetailsSql.Length > 0 Then
            'Create the Order Details command and data adapter
            cmDetails = cnNwind.CreateCommand
            cmDetails.CommandText = strDetailsSql
            daDetails = New SqlDataAdapter
            daDetails.SelectCommand = cmDetails
        End If

        Dim strDetailsTable As String = dsOrders.Tables(1).TableName
        'Name is "[Order Details]"
        Try
            'Open the connection and fill either or both tables
            cnNwind.Open()
            If daOrders IsNot Nothing Then
                daOrders.Fill(dsOrders, "Orders")
            End If
            If daDetails IsNot Nothing Then
                daDetails.Fill(dsOrders, strDetailsTable)
            End If
            cnNwind.Close()
            Return dsOrders
        Catch exc As Exception
            Dim excSoap As New _
             SoapException(exc.Message, SoapException.ClientFaultCode, _
             Context.Request.Url.AbsoluteUri)
            Throw excSoap
        Finally
            cnNwind.Close()
            cnNwind.Dispose()
            dsOrders.Dispose()
            If daOrders IsNot Nothing Then
                daOrders.Dispose()
            End If
            If daDetails IsNot Nothing Then
                daDetails.Dispose()
            End If
        End Try
    End Function

    <WebMethod(Description:="Updates the Orders and Order Details tables.")> _
    Public Function UpdateDataSet(ByVal dsUpdate As DataSet) As Integer
        Dim strDetailsTable As String = dsUpdate.Tables(1).TableName
        Dim strConn As String = ConfigurationManager.ConnectionStrings( _
         "NorthwindConnectionString").ConnectionString
        Dim cnNwind As New SqlConnection(strConn)
        Dim daOrders As New SqlDataAdapter("SELECT " + _
         "OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, " + _
         "ShippedDate, ShipVia, Freight, ShipName, ShipAddress, " + _
         "ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders", cnNwind)
        Dim cbOrders As New SqlCommandBuilder(daOrders)
        Dim daDetails As New SqlDataAdapter("SELECT * FROM [Order Details]", cnNwind)
        Dim cbDetails As New SqlCommandBuilder(daDetails)
        Try
            cnNwind.Open()
            'Invoke the Update method of both DataAdapters
            Dim intUpdates As Integer = daOrders.Update(dsUpdate, "Orders")
            intUpdates += daDetails.Update(dsUpdate, strDetailsTable)
            cnNwind.Close()
            Return intUpdates
        Catch excSql As SqlException
            Dim excSoap As New SoapException("SQLException: " + excSql.Message, _
             SoapException.ClientFaultCode, Context.Request.Url.AbsoluteUri)
            Throw excSoap
        Catch excSys As System.Exception
            Dim excSoap As New SoapException("SystemException: " + excSys.Message, _
             SoapException.ClientFaultCode, Context.Request.Url.AbsoluteUri)
            Throw excSoap
        Finally
            cnNwind.Close()
            cnNwind.Dispose()
            dsUpdate.Dispose()
            daOrders.Dispose()
            cbOrders.Dispose()
            daDetails.Dispose()
            cbDetails.Dispose()
        End Try
    End Function

    <WebMethod()> _
    Public Function HelloWorld() As String
        Return "Hello World"
    End Function

End Class