Web Services & Relational Databases

WSTK bridges the gap between the relational and XML worlds by letting you mix and match technologies. It lets you build XML Views of relational data stored in SQL Server and query/update the relational data through these views. The data is, therefore, stored in the relational database in regular tables. Users can then request this data as XML, and WSTK retrieves relational rowsets from the database, converting them to XML hierarchies on-the-fly transparently to users. Users can thus view the relational database as if it was a huge XML file that can be efficiently queried using XML Query languages (http://www.w3.org/XML/Query/) and updated using XML updategrams. They can forget that they are working with a relational database and, instead, see it as an XML data source—querying with XML query languages, performing XML updates, and operating with a hierarchical data model.

WSTK is installed on the middle tier, together with an IIS server. Midtier administrators configure a set of virtual directories that accept queries through HTTP requests (including sending SQL statements directly on the URL) and return back data as XML. For example, the URL request http://srv1/myvr/myvn/my.xsd/Customer[@ID='1'] can be executed against WSTK. This translates into a request to WSTK that is installed on server srv1 and goes against a database configured for the /myvr/myvn virtual root on that server. The database is viewed through an XML view defined in my.xsd and the XPath query Customer[@ID='1'] retrieves a customer with ID='1'. WSTK translates this request into a SQL statement, executes it against the SQL Server database, and returns an XML result in the form <Customer Name="John" Balance="100" /> (the actual form of the result is defined by the XML View). Again, all translations are transparent to users—all they see are URL requests and XML results. Furthermore, databases can be accessed transparently from any platform because HTTP is a platform-independent protocol and XML a universal standardized format.

WSTK also supports XML templates, which in WSTK are analogous to database-stored procedures. They are XML files that can contain arbitrary XML mixed with XPath (http://www.w3.org/TR/xpath/) or SQL requests to the database. When executed, these requests return XML that is inserted in the template, and the template with the inserted XML is the result of the template execution. XML templates are stored on the middle tier and can be invoked through HTTP by name. The template in Example 1 takes state as parameter and executes SQL and XPath queries. The results of both queries are merged into the template output.

Programming Model Transparency

For application developers, WSTK provides a natural data access API. Visual Basic (VB) programmers, for example, have VB functions such as GetCustomer() for data retrieval (the same is true for other programming languages, but I use VB as an example here). These functions map to web methods, each of which translates to a database query that retrieves the data and returns it to users in a natural form. Programmers using web services do not need to know anything except the programming language to work with the database. They call functions and get data back. They don't see SQL statements, connection strings, or details of database protocols.

Example 2 is a VB function (using Visual Studio .NET) for accessing a SQL Server database, retrieving a Customer with Orders, and presenting it in a DataGrid on the screen. (This also assumes that a midtier administrator configured a myWebService web service and mapped a GetCustomer web method to a stored procedure that selects a customer with orders by customer ID from the SQL Server.) As you can see, the programming model is familiar and does not require client-side programmers to know about databases or database query languages.

Figure 1 illustrates the three-tier architecture with WSTK installed on the middle tier. WSTK performs all the transformations of data models, access protocols, and programming models. On the server side, all the database sees is SQL requests coming in and relational rowsets coming out. Administrators don't notice that their database is accessed through web services. On the client side, developers writing applications in familiar programming languages make function calls and get back data as XML or directly as values typed as their native data types.

Web Services Toolkit Design

There are two things that can be exposed as web services from SQL Server: database-stored procedures and XML templates. Any kind of stored procedure can be exposed; they do not need to be specially designed for WSTK, and legacy stored procedures can be exposed without any changes to them. User Defined Functions (UDFs) can be exposed the same way as stored procedures. Arbitrary XML templates containing database queries and updates can be turned into web methods. All the messaging is done according to SOAP 1.1 so that WSTK can be used from any client on any platform that supports this standard.

All results must be transformed into XML to be sent as part of SOAP messages. XML templates always return XML results, but for the stored procedures, the relational rowsets are converted into XML automatically by WSTK. The output parameters and error messages from the stored procedures are also converted to XML and returned in SOAP messages.

The server side must be running SQL Server 2000 DBMS managing a database to be exposed through web services. No changes are required to the server side, and no actions are required by database administrators.

The middle tier must be running Internet Information Services (IIS) and have WSTK installed. Middle tier administrators then need to configure web services by:

• Using a WSTK configuration tool to create a virtual directory on the IIS server with a virtual name of type SOAP, giving it a unique name and domain.
• Defining security settings on the virtual directory. Security can be configured so that users connecting to this web service use Windows integrated authentication or impersonate a given SQL Server account (most administrators use guest accounts).

• Browsing the list of stored procedures on the database and picking the ones to be exposed. For each of them, the administrator has to choose how results are converted into XML (RAW mode only changes the syntax of the rowset data to the XML, NESTED mode establishes hierarchies in the XML results), and how query results are typed. The result of a stored procedure execution can be typed as an array of XML objects, an array of DataSet objects, or as a single DataSet.

• Browsing the list of XML templates available on the middle tier and picking the ones to be exposed as web methods. The results of an XML template execution are always typed as an array of XML objects.

Administrators must choose how errors are handled, as SOAP errors or error objects inside the data.

Once administrators have configured the web service, a Web Service Definition (WSDL) file is created, describing all the web methods chosen to be exposed on this web service. This WSDL file can be retrieved using the URL request http://srv1/myvr/myvn?wsdl, where srv1 is the name of the WSTK server, /myvr/myvn is the virtual directory configured, and ?wsdl is the command to retrieve WSDL. Client-side tools that process web services requests include Microsoft SOAP Toolkit and Visual Studio .NET. Client-side developers execute the URL request in Example 2 to retrieve the WSDL file, and Visual Studio .NET automatically generates all the classes needed to access the web service.

Web methods can then be called as function calls with parameters in a programming language. The returned result can be interpreted according to how it has been typed in the web method definition. If the result is typed as an array of XML Objects, it can be parsed with an XMLReader. If the result is typed as a DataSet, it can be directly assigned to a variable of this type. In the case of DataSet ds = svc.MyStoredProc(param, retval), a stored procedure is called as a function with a parameter, the result is returned in a variable of a DataSet type, and a stored procedure output parameter is returned in the retval variable of Integer type. Visual Studio .NET treats these web methods as regular programming language functions.

When a web method is called on the client side, Visual Studio (or another SOAP tool) converts it into a SOAP request that is sent to the WSTK server through HTTP protocol. WSTK parses the SOAP request and identifies which stored procedure or XML template this request maps to. It also identifies the parameters to be passed through. If the request maps to a stored procedure, the stored procedure call is sent to the database server. Once the relational result is returned, it is converted to XML according to the conversion option chosen, wrapped with a SOAP header and returned to the client. For requests that map to the XML template, the template is executed by WSTK, possibly sending multiple SQL queries and updates to the server, and the resulting XML is wrapped with a SOAP header and also returned to the client.

If errors occur in the database query, the error message can be returned either as a SOAP error in the SOAP header or as a SqlError object in the data section of the SOAP message. It is up to administrators who configure the web service to choose how the errors are handled.

An Example Application

For purposes of illustration, assume you have a SQL Server running with a sample database called "Northwind" and the stored procedures GetCustomerInfo and GetCustomerInfoWithErrors (defined in Listing One). The first procedure takes CustomerID as input and returns information about the customer and orders in one result set. The second procedure has three SQL statements: The first statement retrieves the contact name of a customer, the second is invalid and produces an error (to demonstrate error handling), and the third retrieves this customer's orders.

WSTK is running on the middle tier. The XML template (Listing Two) that contains queries to retrieve customer and order detail information as one hierarchical XML result set is defined. You run the WSTK configuration tool to configure a web service named MySoap with the three web methods:

• GetCustomerInfo maps to the corresponding stored procedure, wrapping its relational result with the FOR XML NESTED keyword and declaring its result as a single DataSet type.

• GetCustomerInfoWithErrors maps to the corresponding stored procedure, but (in this case) you configure WSTK to wrap it with a FOR XML RAW keyword. Since this stored procedure contains multiple statements and returns multiple result sets, you declare the return type of this web method as an array of DataSet objects.

• paramtemplate maps to the XML-defined template and declares its result to be arbitrary XML.

Once this is done, the web service is ready to use.

As for the client application, in a Visual Studio .NET VB project you add a web reference pointing to the web service just configured, thereby automatically generating all the classes you need to call your web methods. You design a simple form that has a DataGrid to display data retrieved from the database, a ListBox to output messages such as error messages and return codes, a text box that lets users input parameters to the functions, and three buttons that call the three web methods.

In Listing Three (sample code for the first button), you create an instance of an autogenerated class MyServer.MySoap representing the MySoap web service running on MyServer, set credentials for it to use Windows integrated authentication when accessing the web service, and call the GetCustomerInfo web method, sending the returned dataset directly into the DataGrid to be displayed.

In Listing Four, the return values from the GetCustomerInfoWithErrors method need more extensive processing since this method returns an array of objects rather than a single dataset. You loop though the objects and process them differently based on the type of the object returned. It can be a DataSet with query result, a SqlMessage in case of an error message, or a scalar type representing a Stored Procedure return value.

In Listing Five, you call a web method declared as returning an arbitrary XML (database data processed by the XML template), so you can parse the output with an XmlNodeReader, then use the resulting XML data in an application or just use it to fill a DataSet that is easy to display with a DataGrid.

Conclusion

Developing client applications that access data from relational databases usually requires you to understand relational technologies, learn SQL and database-specific APIs, and deal with relational rowsets as query results. However, the SQL Server Web Services Toolkit lets developers access databases using programming models that are natural to familiar client-side programming languages, allowing databases to be easily converted into web services.

DDJ

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

Back to Article

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>

Back to Article

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

Back to Article

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

Back to Article

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

Back to Article