Build a Database Web Service
Extend your company's product database to any device anywhere.
by Andrew M. Barfield
Web Services in the Enterprise 2002
Technology Toolbox: C#, SQL Server 2000, ASP.NET
Arguably, the single most obvious use for XML Web services is universal data access. You can make your company database accessible by multiple Internet clients, get it imported into third-party Web sites on the fly, and even let your partner's Web services query it. I'll show you how to set up a simple Web service to deliver your database content to Internet Explorer, third-party Web services, and custom C# and VB.NET clients.
Partners, clients, and employees have a much richer experience when using data designed for multiple clients and devices anywhere. No matter how your legacy database is formatted, Web services return XML-formatted data to a requesting client, ensuring global compatibility. For instance, imagine that a delivery company is about to deliver your new widget to a client. Just as the driver arrives at the client's doorstep, her PDA audibly signals a change in delivery. She stops and delivers it elsewhere, because your client changed their address in your database, which was then updated automatically in your partner's system.
I'll show you how to write your own ASP.NET database Web service. Start by examining your database. Is it in a format you can expose easily as XML? Can ADO.NET read it and convert it on the fly? In some situations, you might need to convert your legacy database to another format to expose it in these ways. I recommend doing so in any situation where your access code becomes so involved that it affects scaling.
For simplicity, I'll assume the database in question has a single "Products" table and nothing more. Of course, your database will have multiple tables and your service might access more than one database.
Now you can begin writing code. First, examine the DataService Visual Studio project itself, which contains all the methods you need for this simple Web service. Open a new instance of Visual Studio .NET and create a new C# ASP.NET project in a folder named DataService. You'll need access to an ASP.NET-enabled server. If you don't have the Web space already, look in the Visual Studio documentation for free 30-day services.
Once you create your basic project, right-click on the Service1.aspx file and rename it DataService.aspx. This file will hold the WebMethods used to retrieve data from various databases. Now, switch to code view and rename the DataService class and the constructor's name. Renaming these objects gives the Web service a needed consistency: The DataService file and class contain WebMethods for accessing your data.
The first method in your service accesses SQL databases. You can enter the code after the sample "Helloworld" method in your project (see Listing 1). The SQLDB WebMethod handles a client request for data from a SQL database based on a SQL query. The query is passed as a parameter on the browser URL line. All the code for this WebMethod is contained in "try/catch" statements, allowing the Web service to fail gracefully in the event of a malformed query or other exception. If the WebMethod encounters any exception at run time, the catch statement generates a data set containing an "Error" table with the error message.
The method first creates and opens a SQL connection to the database based on the connection string. The connection string you use will be unique to your server and database (I used the Microsoft Northwind sample database for my online DataService). The method then creates a SQL data adapter, using the client's Query parameter and the new SQL connection. The query determines what records you pull from the database. Finally, the code fills a data set with any available data and passes it back to the client as XML. Name the XML root "Results."
The code to develop a Web service for Access databases is only slightly more complex (see Listing 2). Like the SQLDB WebMethod, this method accepts a query parameter on the URL line and returns an XML error message gracefully for all exceptions.
This code also allows a SQL query to access the database. The code establishes a connection to the database, using the provided connection string. Again, this connection string will be unique to you. Once the connection is made, a new OleDbCommand class is created based on the Query parameter and the Connection object. An OleDbDataAdapter is also created to fill the dataset later. The data adapter opens the database, then fills the data set based on the command query. Again, the root XML node is "Results." The code then closes the database connection and returns the data set to the client as XML. Although this Access code is slightly more complex, it might be more commonly used because you can edit the database—you don't need special servers.
Finally, believe it or not, you'll still find many applications for comma-delimited and tab-delimited data. My last example of the database Web service reads both of these types of data and returns them as a data set (see Listing 3).
This method creates and opens a connection to the database by initializing a new ADOConnection class. The method then creates a command, using the Query parameter and the new connection. Lastly, it fills a data set from the text file and returns that data to the client as XML.
Deploy the Web Service
Before you can actually use the DataService, you need data to access. You can move an Access or text-based database to the server either by adding it to your project so VS.NET uploads it for you, or by using an FTP client such as WSFTP.
Rebuild and test your Web service once you complete it. Click on the Start button on the Visual Studio toolbar. VS.NET launches Internet Explorer after rebuilding and uploading the service. Use the loaded page to select and play with your service's WebMethods.
The page displays a list of the methods you created in the code editor. Click on the AccessDB method and enter "select * from Suppliers" in the Query value's parameter box. Click on Invoke. If the service functions correctly, you'll see the results of your query in a new browser window. The result should look like this:
<Results diffgr:id="Results1" msdata:rowOrder="0">
<SupplierID>1</SupplierID>
<CompanyName>Exotic Liquids</CompanyName>
<ContactName>Charlotte Cooper</ContactName>
<ContactTitle>Purchasing Manager</ContactTitle>
<Address>49 Gilbert St.</Address>
<City>London</City>
<PostalCode>EC1 4SD</PostalCode>
<Country>UK</Country>
<Phone>(171) 555-2222</Phone>
</Results>
The SQL queries are not limited in any way—they can be as complex as necessary. For example, enter "select * from Products where UnitsInStock = 0" to list the out-of-stock products.
Now consider some server options available to you that affect the behavior of your DataService. For example, the ASP.NET config file is an XML configuration file—a set of XML elements representing the configuration options for a specific feature of the .NET Framework. The Web services element of a configuration file encapsulates the configuration options.
This is where the database Web service shines. Internet Explorer can view raw XML data natively, and incorporating an XSL stylesheet enables you to form an interface for the data. You can enter a URL such as http://www.xcalibre-technologies.com/WebServices/DataService/DataService.asmx/AccessDB?Query=select+*+from+products into the URL line or link to the Web service from an HTML page. Other Web services can access data from your database. For example, resellers can import and merge your data with their databases, giving their own clients the appearance of truly branded reseller solutions.
Write a Web Service Client
The third type of client is one written especially to consume a Web service (see Figure 1). You can write this client in any language designed to target the .NET Common Language Runtime (CLR). You need a proxy class to communicate with the Web service. This class acts as a middleman between your client and service. The proxy class uses SOAP to map parameters to XML elements. Create a proxy class for your client using VS.NET to generate client code for a target Web service. Create a new C# Windows application and name it DBClient. Right-click on References in Solution Explorer and select Add Web Reference. When the dialog appears, enter the complete address of the DataService Web service.
Enter the URL in the Address box and click on Add Reference. VS.NET will work for a while and add the necessary files to your project. You can view this Web reference from the object browser by pressing Ctrl+Alt+J. To use the online Web Reference, create an instance of the Web Service and call its methods:
private void menuItem2_Click(object sender,
System.EventArgs e)
{
DBClient.com.xcalibre_technologies.www.
DataService Database = new DBClient.com.
xcalibre_technologies.www.DataService();
DataSet ds = Database.AccessDB("select *
from Customers");
dataGrid1.DataSource = ds.Tables[0];
}
This Windows form method creates a new instance of the DataService proxy class and calls the AccessDB method, passing to it a SQL query to get a list of all the Northwind customers. When the data is returned, the one and only table is assigned directly to the data grid. The result is a list of customers and their personal data.
This simple code connects to the Web service using the Internet, returning all the records in the Customers table. Simply create an instance of the proxy class and call the Web methods as you would any other class.
The data set return value of the Web service feeds directly to the data set in your client. Microsoft created the data set type with a disconnected design in part to assist data transport over the Internet. You can serialize the type so anyone can specify it as an input to or output from Web services. This way, you don't need additional coding to stream its contents between a Web service and a client. Your existing code converts the data set to an XML stream sent over the network and reconstructed on the receiving end. Now go and put a catalog of your own online as a Web service.
About the Author
Andrew Barfield is the CEO of Xcalibre Technologies. in Lumberton, N.C Andrew codes in C# and handles tech support for Internet access. Reach him at .
|