datasetws.vb

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

Option Explicit On
Option Strict On

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

<WebService(Description:="Demonstration Web service with Web methods that return untyped DataSets. Errors throw SOAP faults.", Namespace:="http://oakleaf.ws/webservices/datasetws/northwind")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1, EmitConformanceClaims:=True)> _
Public Class DataSetWS
    Inherits System.Web.Services.WebService

    'Fix the connection string to correspond to your SQL Server configuration
    Private strConn As String = "Server=localhost;UID=sa;PWD=whidbey;Database=Northwind"

    'Web method descriptions
    Private Const strGetCustomers As String = "A simple Web method that returns an untyped Customers DataSet"
    Private Const strGetOrdersByCustomer As String = "A parameterized Web method that returns an untyped Orders DataSet for a specified CustomerID in descending OrderDate sequence. Specify RATTC to return 18 orders."
    Private Const strUpdateCustomersDataSet As String = "A parameterized Web method for updating the Customers DataSet. Accepts a DataSet of Customers table changes and returns True if updates succeed."
    Private Const strUpdateOrdersDataSet As String = "A parameterized Web method for updating the Orders DataSet. Accepts a DataSet of Orders table changes and returns True if updates succeed."

    Private Const strOrdersSelect As String = "SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, " + _
     "ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, " + _
     "ShipPostalCode, ShipCountry FROM dbo.Orders "
    Private Const strOrdersSort As String = "ORDER BY OrderID DESC;"

    <WebMethod(Description:=strGetCustomers)> _
     Public Function GetAllCustomers() As DataSet
        Dim dsNwind As New DataSet
        Dim daCusts As SqlDataAdapter = Nothing
        Try
            'Create an SqlDataAdapter and fill the DataSet
            daCusts = New SqlDataAdapter("SELECT * FROM Customers", strConn)
            daCusts.Fill(dsNwind)

            'Replace "NewDataSet" with "Customers" and assign namespace
            dsNwind.DataSetName = "Northwind"
            dsNwind.Namespace = "http://oakleaf.ws/webservices/datasetws/northwind"
            With dsNwind.Tables(0)
                'Assign the table name
                .TableName = "Customers"
                'Assigning a table namespace breaks the published Web service
                '.Namespace = "http://oakleaf.ws/webservices/datasetws/northwind/customers"
                'Specify the primary key
                .PrimaryKey = New DataColumn() {.Columns(0)}
                'Require a CompanyName value (table constraint)
                .Columns(1).AllowDBNull = False
            End With
            Return dsNwind
        Catch excSys As Exception
            Dim excSoap As New SoapException(excSys.Message, _
            SoapException.ClientFaultCode, Context.Request.Url.AbsoluteUri)
            Throw excSoap
        Finally
            dsNwind.Dispose()
            daCusts.Dispose()
        End Try
    End Function

    <WebMethod(Description:=strGetOrdersByCustomer)> _
     Public Function GetOrdersByCustomerID(ByVal CustomerID As String) As DataSet
        Dim dsNwind As New DataSet
        Dim daOrders As SqlDataAdapter = Nothing
        Try
            'Create an SqlDataAdapter and fill the DataSet
            'daOrders = New SqlDataAdapter("SELECT * FROM Orders WHERE CustomerID = '" _
            '+ CustomerID + "' ORDER BY OrderDate DESC", strConn)
            'Explicit column list is required due to added timestamp column
            Dim strSQL As String = strOrdersSelect + " WHERE CustomerID = '" + _
             CustomerID + "' " + strOrdersSort
            daOrders = New SqlDataAdapter(strSQL, strConn)
            daOrders.Fill(dsNwind)

            'Replace "NewDataSet" with a meaningful name and assign namespace
            dsNwind.DataSetName = "Northwind"
            dsNwind.Namespace = "http://oakleaf.ws/webservices/datasetws/northwind"
            With dsNwind.Tables(0)
                'Replace "Table" with "Orders"
                .TableName = "Orders"
                'Assigning a table namespace breaks the published Web service
                '.Namespace = "http://oakleaf.ws/webservices/datasetws/northwind/orders"
                'Specify the primary key
                .PrimaryKey = New DataColumn() {.Columns(0)}
                'Specify as required fields with foreign key constraints
                'The default is AllowDBNull = True
                .Columns("CustomerID").AllowDBNull = False
                .Columns("EmployeeID").AllowDBNull = False
                .Columns("ShipVia").AllowDBNull = False
                'These values would be required in production
                .Columns("OrderDate").AllowDBNull = False
                '.Columns("ShipName").AllowDBNull = False
                '.Columns("ShipAddress").AllowDBNull = False
                '.Columns("ShipCity").AllowDBNull = False
                'Region (State/Province) is required for USA and Canada
                'PostalCode is required except Ireland (outside Dublin)
                '.Columns("ShipCountry").AllowDBNull = False
            End With
            Return dsNwind
        Catch excSys As Exception
            Dim excSoap As New SoapException(excSys.Message, _
            SoapException.ClientFaultCode, Context.Request.Url.AbsoluteUri)
            Throw excSoap
        Finally
            dsNwind.Dispose()
            daOrders.Dispose()
        End Try
    End Function

    <WebMethod(Description:=strUpdateCustomersDataSet)> _
     Public Function UpdateCustomersDataSet(ByVal dsNwind As DataSet) As Boolean
        Dim cnNwind As New SqlConnection(strConn)
        Dim daCusts As SqlDataAdapter = Nothing
        Dim cbCusts As SqlCommandBuilder = Nothing
        Try
            daCusts = New SqlDataAdapter("SELECT * FROM Customers", cnNwind)
            cbCusts = New SqlCommandBuilder(daCusts)
            cbCusts.ConflictOption = ConflictOption.CompareAllSearchableValues
            daCusts.Update(dsNwind, "Customers")
            Return True
        Catch excSys As Exception
            Dim excSoap As New SoapException(excSys.Message, _
            SoapException.ClientFaultCode, Context.Request.Url.AbsoluteUri)
            Throw excSoap
            Return False
        Finally
            cbCusts.Dispose()
            daCusts.Dispose()
            dsNwind.Dispose()
        End Try
    End Function

    <WebMethod(Description:=strUpdateOrdersDataSet)> _
     Public Function UpdateOrdersDataSet(ByVal dsNwind As DataSet) As Boolean
        Dim cnNwind As New SqlConnection(strConn)
        Dim daOrders As SqlDataAdapter = Nothing
        Dim cbOrders As SqlCommandBuilder = Nothing
        Try
            'daOrders = New SqlDataAdapter("SELECT * FROM Orders", cnNwind)
            'To accommodate timestamp column
            daOrders = New SqlDataAdapter(strOrdersSelect, cnNwind)
            cbOrders = New SqlCommandBuilder(daOrders)
            cbOrders.ConflictOption = ConflictOption.CompareAllSearchableValues
            daOrders.Update(dsNwind, "Orders")
            Return True
        Catch excSys As Exception
            Dim excSoap As New SoapException(excSys.Message, _
            SoapException.ClientFaultCode, Context.Request.Url.AbsoluteUri)
            Throw excSoap
            Return False
        Finally
            cbOrders.Dispose()
            daOrders.Dispose()
            dsNwind.Dispose()
        End Try
    End Function
End Class