Important: Read the disclaimer before reading this article.

© 2003 International Business Machines Corporation. All rights reserved.

Introduction

In this article, you'll learn the basic concepts behind building an n-tier data application using Borland® C#Builder^TM to connect to your IBM® DB2® Universal Database^TM (UDB). You'll understand how to create a Web service to retrieve and update data. You'll also learn how to create references to Web services within your applications. You'll need C#Builder Enterprise or Architect, DB2 Universal Database, and a Microsoft® ASP.NET-compatible Web server.

Back to top

Cooking with Web services

Can you remember the first time you developed an application that had to call another procedure or function located on another machine? Chances are that you either ended up with some proprietary format that required significant testing, or you had to communicate with someone else's proprietary format. Developing and testing distributed applications took what seemed like forever.

But we developers have come a long way since then-rather than develop our own protocols and systems, we learned to communicate across machine boundaries using standards such as RPC, COM, or CORBA. However, these solutions presented their own problems-some of these technologies had differences between implementations, others were limited to certain platforms, and most were difficult to use over a network because the protocols would use a variety of network ports that network administrators didn't want to use.

A Web service, on the other hand, is a Remote Procedure Call mechanism that uses open standards such as XML and HTTP to make calls to services on other machines. XML works to pass the request to the service and retrieve the parameters from the client, and HTTP serves as the calling mechanism. For more information on Web services, see DB2 Web Services: The Big Picture.

Back to top

Your first Web service

The .NET platform handles the underlying Web services technologies for you automatically. Instead of coding against XML and HTTP directly, you simply create a class that is defined as a Web service, and you choose methods of that class that you want to expose.

To create a Web service within C#Builder, you first need to have the service reside in either an ASP.NET Application or an ASP.NET Web Service Application (see Figure 1). The only difference between the two is that creating an ASP.NET Application will create an ASP.NET page by default, and creating an ASP.NET Web Service Application will create an ASP.NET Web service by default.

After you create the Web service application you should have an ASP.NET Web service available to you, named something like WebService1.asmx.

The class that is created for you inherits from System.Web.Services.WebService, which defines your new class as a Web service. .NET handles all the underlying technology; so this is all you need to write for the method that you want to expose.

Now switch to the Code View of the Web service. You'll see a class that derives from System.Web.Services.WebService created in your ASP.NET Web application. Expose some functionality by writing a method and using the [WebMethod] attribute before the method declaration. So for a simple Web service that calculates compound interest, you might have something like this:

 
[WebMethod] 
public double GetTotalRepaymentAmount(double initialAmount, 
	double interestRate, int numberOfPeriods) 
{ 
return initialAmount * Math.Pow(1 + interestRate, numberOfPeriods); 
}

From this point, you can test your Web service by displaying the service in a Web browser (see Figure 2). To view the service, you either type in the location of the service into your browser, or select Run | Run from the C#Builder main menu. When you select the Run | Run option in a Web Services application (or an ASP.NET Application), C#Builder will start a new Web Page and then open the Web Services URL for you. If you have more than one Web service in your application, you can change the project's start page by right-clicking on an ASP.NET Web Services page and selecting Set as Start Page.

Once you have the Web Services displayed (see Figure 2 again), you can select the method of the service that you want to call and fill in the parameter values to test the service. After you have done that, you can invoke the service to see the XML result.

Now that you see how easy it is to expose functionality from a Web service, you can move on to the meatier stuff-exposing your DB2 UDB data from a Web service.

Back to top

Expose DB2 UDB data with Web services

For our Web service, we will be dealing primarily with exposing some form of database functionality. In a data-driven Web service, you will either be returning data or updating data. In my last article, on using ASP.NET with C#Builder and DB2 UDB, you learned about retrieving data through the use of BdpDataAdapters. Even though now you are programming Web services instead of ASP.NET pages, the rules for accessing data from your DB2 UDB stay the same.

To connect through to your data, you need a BdpConnection component and a BdpDataAdapter component (also see my last article for how to create a BdpConnection and BdpDataAdapter). In the last article, I added a DataSet component to the Designer, but you will not need a DataSet component on the Designer because the DataSets will be created dynamically in the code. You may need to add a new connection in the Data Explorer, so you can use that data connection to access your database. If you already have a connection set up in Data Explorer, a quick way to get a BdpConnection component and a BdpDataAdapter component is to select a table from the Data Explorer and drag it to the Designer (see Figure 3).

At that point, you should have a BdpConnection component that represents your connection to the database, and a BdpDataAdapter component that is set up to access the data.

Now you are ready to expose some data through your Web service. Very little code is required to expose a DataSet from the service. Here's how to create a DataSet dynamically and use the BpdDataAdapter to fill the DataSet with data:

 
[WebMethod] 
public DataSet GetProducts() 
{ 
	DataSet ds = new DataSet(); 
	daProducts.Fill(ds, "Products"); 
	return ds; 
}

Because you used the [WebMethod] attribute, this method is now exposed from the service, and you can test it by running the service in a Web browser. When you run the application, the GetProducts function should be available to you, and you will see XML data that represents the returning DataSet.

Back to top

Updating data in the database

Exposing data is easy to do by using the drag-and-drop method and writing some simple code, as we learned in the previous section. Updating data using the BdpDataAdapter is a little trickier than just retrieving it, though. You need to use the InsertCommand, UpdateCommand, and DeleteCommand objects within your database.

Right-click on BdpDataAdapter in the C#Builder Designer and select Configure Data Adapter . You will then see a dialog like the one in Figure 4.

You need to modify the BdpDataAdapter component to allow inserts, updates, and deletes. When you select Configure Data Adapter, you can select the table and the columns that you use. If you click on the Generate SQL button, you will see in the tab pages that the Select, Insert, Update, and Delete commands have been generated for you, and each parameter that will be passed to the command is represented by a question mark (?).

After the SQL for the Data Adapter is generated, you must define how the parameters relate to the columns that are passed back to the DataSet. For this SQL, there are seven parameters:

 
UPDATE 
  GLENN.PRODUCTLIST 
SET 
  PRODUCTCODE = ?, 
  PRODUCTNAME = ?, 
  DESCRIPTION = ?, 
  UNITPRICE = ?, 
  CATEGORYCODE = ?, 
  IMAGEURL = ? 
WHERE 
  PRODUCTCODE = ? 

Each parameter for the query must be populated from the contents of the DataSet when it gets updated. You need to populate the parameters collection for the Update query in order to do this. There are seven parameters, so you need to create seven BdpParameter objects for the command.

The first six parameters in the query will be set to the new field values of the DataSet. The last parameter, though, would be set to the original PRODUCTCODE field. As a result you need to reflect these issues in the parameters collection (see Figure 5).

Figure 5 highlights the parameter that represents the PRODUCTNAME field. The important properties to set in each collection are the BdpType property, the SourceColumn property, the SourceVersion property, and the Size property. The BdpType property tells the BdpCommand what data type the parameter is. If you have a String bdpType you should also set the size for the field. Similarly, you should also set the Precision and Scale properties if you are using decimals.

The SourceColumn tells the BdpCommand that when the value is being updated from a database, it should retrieve the value from this column. The SourceVersion property's role is to say which version of the SourceColumn to retrieve. The DataSet will hold not only the new changes to a DataSet Table, but also the original values for each field. When you want to use the current values for the DataSet table, you choose 'Current' for the value. When you are updating, though, you will most likely want to remove the record based on the original value of the key-so for the last parameter of the seven, or for when you are removing records, set the SourceVersion property to 'Original'.

When you are defining the parameters for the commands, keep in mind that for new parameters (such as those used for insert SQL statements and the set portion of update SQL statements), you should always set the SourceVersion to Current. Parameters used for the Key should usually be set to Original.

Now that the parameters are defined, you can allow your database to be updated. To update the DataSet based on the changes, call the Update method of the BdpDataAdapter. The BdpDataAdapter will use its InsertCommand, UpdateCommand, and DeleteCommand objects to update individual rows of the DataSet. In addition, you should also pass in which particular DataTable should be updated from the DataSet:

 
[WebMethod] 
public void UpdateProducts(DataSet ds) 
{ 
	//Update the list of Products 
	daProducts.Update(ds, "Products"); 
}

You now have a Web service that can retrieve and update data from the database. But you can't test it-you can only test Web services that take simple parameters, and the UpdateProducts method takes a DataSet as a parameter. So you need to build a client application that talks to the Web service to ensure it works properly.

Back to top

Create the Web service client application

Not only does C#Builder have excellent support for creating Web services, it also makes it easy to call external Web services from applications. Start out by creating a simple WinForms application (see Figure 6): choose File | New | C# Application from the Main Menu. Place a DataGrid and two buttons onto your form. The DataGrid will be used to edit the data, the first button will be used to retrieve the data, and the second button will be used to send the update to the Web service.

Back to top

Adding a Web reference

You create a reference to your application using the file or URL that contains the Web Services Description Language (WSDL). Select Project | Add Web Reference from the main menu to pop up the dialog box in Figure 7.

Enter the location of your Web service's WSDL in the dialog. This is usually the same location as the .asmx file, but append ?WSDL to the URL (see Figure 7, above). In the Project Manager pane you will now have a reference to the Web service. C#Builder then imports the WSDL and creates a class file, after which it's up to you to create an instance of that class and call the methods. It's as simple as that.

DB2 UDB Note: It is worth noting that DB2 UDB also has specialized support for Web services known as Web Services Object Runtime Framework (WORF), which provides a powerful way of generating Web services that expose functionality based on queries. Not only does it create these Web services, but it also generates the WSDL required for the Web service. For more information, see Web Services Object Runtime Framework for DB2.

Back to top

Get the data

Now that you have access to the Web service, you just need to have a DataSet somewhere within your forms declaration. After that you just need to call your WebMethod. Here's how to create an instance of the Web service class and then retrieve the DataSet by calling the Web service's GetProducts() method:

 
DataSet dsProducts; 
 
private void btnGetProducts_Click(object sender, System.EventArgs e) 
{ 
	//Get the WebService 
ProductService ps = new ProductService(); 
	dsProducts = ps.GetProducts(); 
 
	//Bind to the DataGrid 
	dataGrid1.DataSource = dsProducts; 
	dataGrid1.DataMember = "Products"; 
}

After retrieving the data, you need to bind the DataSet returned from the Web service to the DataGrid. You can do this easily by using the DataGrid's DataSource and DataMember properties. Run the client application and select the first button, and you should have your DataGrid populated with the products data (see Figure 6).

Back to top

Send updates to the Web service

In this Web service, you have the ability to accept changes to a DataSet and have those updates applied to the database. To apply the updates, all you need to do is call the UpdateProducts method and pass it the DataSet that contains the updates:

 
private void btnUpdateProducts_Click(object sender, System.EventArgs e) 
{ 
	//Get the changes to the Products DataSet 
	ProductService ps = new ProductService(); 
 
	//Update the Data via the Web Service 
	ps.UpdateProducts(dsProducts); 
}

That's how simple it is to pass the DataSet to the Web service, but there is one problem with this method-if you use it, the entire DataSet will be passed across to the Web service. If you have 500 records and only three changes, you can see that a lot of data is being sent unnecessarily. To combat this, the DataSet has a method called GetChanges that returns a DataSet consisting only of changes to the DataSet. Passing only the changes to the Web service can improve your application performance significantly if you have a lot of data:

 
private void btnUpdateProducts_Click(object sender, System.EventArgs e) 
{ 
	//Get the changes to the Products DataSet 
	ProductService ps = new ProductService(); 
 
	//Update the Data via the Web Service 
	ps.UpdateProducts(dsProducts.GetChanges()); 
}

Back to top

Conclusion

In this article, I've shown you the fun and ease of creating Web services with C#Builder. You can see how simple it is not only to create them, but also to use them. When you add the power of the DB2 database, you can easily retrieve and update data using Web services faster than ever before.

Back to top

Disclaimer

This article contains sample code. IBM grants you ("Licensee") a non-exclusive, royalty free, license to use this sample code. However, the sample code is provided as-is and without any warranties, whether EXPRESS OR IMPLIED, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT. IBM AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE THAT RESULT FROM YOUR USE OF THE SOFTWARE. IN NO EVENT WILL IBM OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE SOFTWARE, EVEN IF IBM HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

Back to top

Download

Name	Size	Download method
SourceCode.zip	48 KB	FTP | HTTP

Information about download methods

About the author

Comments

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Cooking with Web services
• Your first Web service
• Expose DB2 UDB data with Web services
• Updating data in the database
• Create the Web service client application
• Adding a Web reference
• Get the data
• Send updates to the Web service
• Conclusion
• Disclaimer
• Download
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers