Building Web Services for Relational Databases
by Alexander Vaschillo


Example 1:

<root xmlns:t="urn:schemas-microsoft-com:xml-sql">
<t:header>
    <t:param name="state">WA</t:param>
</t:header>
<t:query>
    SELECT CompanyName,ContactName,Phone
    FROM Customers AS Customer
    WHERE Region LIKE @state
    FOR XML NESTED
</t:query>
<t:xpath-query mapping-schema="nwind.xsd">
    /Customers[@Region=$state]
</t:xpath-query>
</root>

Example 2:

Dim svc As New srv1.myWebService()
DataGrid1.DataSource = svc.GetCustomer("1")


Listing One

Use Northwind
GO
CREATE PROCEDURE GetCustomerInfo@CustomerID nchar(5) 
AS  
SELECT *
FROM Customers
LEFT OUTER JOIN Orders 
ON Customers.CustomerID = Orders.CustomerID
WHERE Customers.CustomerID = @CustomerID
GO
CREATE  PROCEDURE GetCustomerInfoWithErrors@CustomerID nchar(5) 
AS  
SELECT CustomerID, ContactName 
FROM Customers 
WHERE CustomerID = @CustomerID
INSERT Customers (CustomerID) VALUES ('zzzzz')
SELECT OrderID, OrderDate FROM Orders
WHERE CustomerID = @CustomerID
GO

Listing Two

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:header>
       <sql:param name='CustomerID'>ALFKI</sql:param>
  </sql:header>
  <sql:query>
     SELECT  CustomerID,CompanyName 
     FROM    Customer
     WHERE   CustomerID=@CustomerID 
     FOR XML AUTO
  </sql:query>
  <sql:query>
     SELECT  top 5 [Order Details].OrderID, ProductID, UnitPrice, Quantity
     FROM    [Order Details], Orders
     WHERE   Orders.OrderID = [Order Details].OrderID
     AND     Orders.CustomerID = @CustomerID
     FOR XML AUTO
  </sql:query>
</ROOT>

Listing Three
Private Sub Button1_Click(ByVal sender As System.Object, 
                           ByVal e As System.EventArgs) Handles Button1.Click
    Dim i As Int16
    Dim svc As New MyServer.MySoap()
    svc.Credentials = System.Net.CredentialCache.DefaultCredentials
    DataGrid1.DataSource = svc.GetCustomerInfo(TextBox1.Text, i)
End Sub

Listing Four

Private Sub Button2_Click(ByVal sender As System.Object, 
                            ByVal e As System.EventArgs) Handles Button2.Click
    Dim svc As New MyServer.MySoap()
    Dim response() As Object
    Dim ds As New System.Data.DataSet()
    svc.Credentials = System.Net.CredentialCache.DefaultCredentials
    response = svc.GetCustomerInfoWithErrors(TextBox1.Text)
    Dim i As Int16
    For i = 0 To (response.Length - 1)
        If response(i).GetType.IsPrimitive() Then
            ListBox1.Items.Add("Return Value: " & response(i))
        Else
            Select Case response(i).GetType().ToString()
                Case "MyApp.MyServer.SqlMessage"
                    Dim message As MyApp.MyServer.SqlMessage
                    message = response(i)
                    ListBox1.Items.Add("Error Message: " & message.Message)
                    ListBox1.Items.Add("Error Source: " & message.Source)
                Case "System.Data.DataSet"
                    Dim result As New System.Data.DataSet()
                    result = response(i)
                    result.Tables(0).TableName=result.Tables(0).TableName & i
                    ds.Merge(result)
            End Select
        End If
    Next
    DataGrid1.DataSource = ds
End Sub


Listing Five
Private Sub Button3_Click(ByVal sender As System.Object, 
                      ByVal e As System.EventArgs) Handles Button3.Click
    Dim svc As New MyServer.MySoap()
    Dim response() As Object
    Dim ds As New System.Data.DataSet()
    svc.Credentials = System.Net.CredentialCache.DefaultCredentials
    response = svc.paramtemplate(TextBox1.Text)
    Dim i As Int16
    For i = 0 To (response.Length - 1)
        Dim xrdr As New System.Xml.XmlNodeReader(response(i))
        Dim result As New System.Data.DataSet()
        result.ReadXml(xrdr)
        ds.Merge(result)
    Next
    DataGrid1.DataSource = ds
End Sub






3


