Working With DB2 Universal Database Tables Using C#Builder With ADO.NET and BDP for .NET

Share:

Bob Swart (b.swart@chello.nl), Developer, Bob Swart Training & Consultancy

Bob SwartBob Swart (a.k.a. Dr.Bob - www.drbob42.com) is an author, trainer, consultant, and Webmaster for Bob Swart Training & Consultancy (eBob42) in The Netherlands. He has spoken at Delphi and Borland Developer Conferences since 1993 and blogs in his model-driven weblog at http://www.drbob42.com/blog. Bob has written hundreds of articles about Borland Delphi and several dozen on developing database applications using Borland Delphi and IBM DB2. Bob is co-author of the Revolutionary Guide to Delphi 2, Delphi 4 Unleashed, C++Builder 4 Unleashed, C++Builder 5 Developer's Guide, Kylix Developer's Guide, Delphi 6 Developer's Guide, and the C++Builder 6 Developer's Guide and has written the Delphi for .NET and ASP.NET courseware.



19 June 2003

Introduction

In my first article on using IBM DB2® Universal Database TM (UDB) with Borland® C#Builder TM , I showed how the Borland Data Providers for .NET could be used to build a connection to DB2 UDB v8.1 and to use the DB2 SAMPLE tables. This time, I will examine some individual Borland Data Provider classes in a bit more detail, especially the design-time editors, and compare them to generic ADO.NET.

Also, at the end of this article, I will cover some important deployment details for the resulting C#Builder project and the BDP assemblies.


Extending ADO.NET with Borland Data Providers

Let's start with the generic ADO.NET connectivity, which consists of five classes that can be found in the Data Components category of the Tool Palette: SqlDataAdapter, SqlConnection, DataView, DataSet, and SqlCommand. Last time, I demonstrated the BDP editions of the three SQL classes: BdpDataAdapter, BdpConnection, and BdpCommand. Each of the BDP classes is derived from its SQL counterpart, and extends it with additional design-time support.

The main purpose of ADO.NET is to provide a common type system, naming convention and design pattern to work with databases in the Microsoft® .NET Framework. As a result, ADO.NET is the native way of connecting to DB2 UDB on the .NET Framework.

An ADO.NET Data Provider is the name for a collection of classes that implement the connection to a specific data source—in our case the DB2 UDB, of course. The Data Provider can also execute commands against the data source, which—for a developer—work in a similar way regardless of the data source. This means that if you currently have an application using ADO.NET to connect to a small database that doesn't scale well (which is especially an issue for multi-user Web applications), then you can switch to using DB2 UDB as your database engine with little effort. The classes and use of the classes inside ADO.NET will be virtually the same.

The SqlConnection class is responsible for the connection to the DBMS, and the SqlCommand is responsible for executing SQL statements that are then used to fill the DataSet class. The SqlDataAdapter class functions as the adapter between the SqlConnection and SqlCommand on one hand, and the DataSet on the other hand, allowing the DataSet to become disconnected from the actual DBMS (where you only need to connect back if you want to retrieve more data or apply updates). The DataView, finally, is a special class that allows the data from the DataSet to be viewed in different ways (sorted, filtered, etc).

The Borland Data Providers implement and extend the ADO.NET data provider classes (specifically the SqlConnection, SqlDataAdapter and SqlCommand classes). In fact, there are a number of different Data Providers available in C#Builder, including the one optimized for DB2 UDBv7.1 and v8.1. This particular Borland Data Provider (BDP) will be the most effective ADO.NET Data Provider to use in order to connect to and work with DB2 UDB tables.

In this article, I will show you some more of the design-time support of the BDP for DB2, as well as some practical issues when working with DB2 data, and finally some deployment information.


Specifying a new DB2 connection

Start a new C#Builder project (a regular C# application) and then go to the Data Explorer (in the upper left corner). This shows the four possible Providers: Borland InterBase®, Oracle®, DB2, and Microsoft® SQL Server TM . Right-click on DB2 and add a new connection. Call it SAMPLE, since you're talking to the DB2 UDB SAMPLE tables again. Right-click on the SAMPLE connection to modify the connection.

Figure 1. Connections Editor
Figure 1. Connections Editor

Specify SAMPLE for Database, and the correct user name and password to connect to the DB2 database. Alternately, you can set LoginPrompt to true, which will enable the end users to specify the username and password by themselves. Note that setting this property to true will actually force the Login Prompt dialog to be shown even if the specified user name and password were correct.

Note that VendorClient points to the db2cli.dll —the DB2 UDB client DLL. This DLL must be installed on the machine where the application runs, and can be found in the C:\Program Files\IBM\SQLLIB\BIN directory, which will be included in your PATH after a successful installation of DB2 UDB (version 8.1.0.36 is installed with DB2 UDB v8.1 on my machine).

The Assembly specified is the Borland.Data.Db2.dll that contains the DB2-specific ADO.NET Data Provider. Note the version number and the fact that it is signed (using a public key) so you can deploy this assembly in the Global Assembly Cache (GAC). (I'll get back to that topic at the end of this article.)

You can set Transaction Isolation to ReadCommitted (the default value), RepeatableRead (which locks all data that is used in a query to avoid non-repeatable reads), or DirtyRead (no shared locks are used or exclusive locks honored). ReadCommitted means that the data is locked while being read, to avoid dirty reads, but the data can change before the end of the transaction resulting in non-repeatable reads or phantom data.

After you're done specifying the connection properties, close the dialog. You can now drag the SAMPLE connection to your Form, which will result in a BdpConnection class being placed in the non-visual components section of the designer.


Adapting the data using BDP for .NET

Next, select the Borland Data Provider category of the Tool Palette and drop a BdpDataAdapter component on the form. This is a special version of the ADO.NET SqlDataAdapter, and can be used to generate SQL statements (for BdpCommand classes) to fill a DataSet as well as update the DB2 tables at a later point. In order to configure the BdpDataAdapter, click on the Configure Data Adapter link in the lower-left corner of the designer (right under the Object Inspector).

If you've set the LoginPrompt property to true, then you will now first see the login dialog. Make sure to specify a valid DB2 user name and password here to connect to the DB2 UDB SAMPLE tables.

Figure 2. Login Dialog
Figure 2. Login Dialog

After a successful login to the DB2 SAMPLE database, the Data Adapter Configuration dialog appears. Obviously, the BdpConnection is already assigned as the Connection class, and you can now select the EMPLOYEE table in the upper left corner and the fields you want to work with (EMPNO, FIRSTNME, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, and BIRTHDATE). You don't have to write any SQL, but can click on the Generate SQL button now to produce the SQL for the selected SQL Commands that you need (by default, the Select, Insert, Update, and Delete options have been checked, but if you only want to view data, or modify only existing records but not insert new records, then you should uncheck the options that are not needed).

Note the Optimize option, which can be used to create more efficient SQL for the Update and Delete statements (optimized to make use of the primary key in the WHERE clause for these SQL commands).

Figure 3. Data Adapter Configuration Dialog
Figure 3. Data Adapter Configuration Dialog

For our example, select the EMPLOYEE table, the * for all columns, pick only the Select SQL Command, check on the Optimize checkbox, and click on the Generate SQL button to generate the Select query shown in Figure 2.

Note that you cannot generate optimized SQL commands for the Update and Delete statements without a primary key in the table (in that case, you have to generate and use non-optimized SQL commands). And guess what? In our example, the DB2 SAMPLE EMPLOYEE table does not have a primary key, although you can assume that the EMPNO field is a unique value. If you want, you can use the DB2 Control Center to alter the table and add the EMPNO field as key field, although you can work around this issue as I'll show in a moment.


Displaying DB2 data

After you've used the Data Adapter Configuration dialog to generate an SQL command, you can click on the DataSet tab to specify which DataSet class to use (or to create a new one), and then the Preview Data tab to get a preview of the data, in order to verify that you get what you want.

The BdsDataAdapter class will use a special BdsCommand class that fills the dataSet with the data from the select query. An end user of the application can view this data, by connecting it to visual components like the DataGrid (as demonstrated last time) or another list-supporting class like a ListBox. For the DataGrid, you need to set DataSource to this.dataSet1 (the DataSet specified in the Data Adapter Configuration dialog), and DataMember to Table1. For the ListBox to display a list of EMPNO values, you have to perform the same steps, but this time connect it to the Table1.EMPNO field.

If you set the Active property of the bdpDataAdapter to true, then you'll see the live data at design-time (see also Figure 4 ).


Working with DB2 data

Although it's nice to see live data at design-time, most people do not want to be limited to viewing data but also want to change the data, modify some of the fields that are displayed in the grid, perhaps insert a new record, or even delete one or more existing records.

When working with DB2 UDB tables using ADO.NET (or the BDP for .NET), you must understand that you are not directly writing your changes to the DB2 SAMPLE table, but instead are working with the local DataSet that will hold the changes (updates, inserts, and deletes) for you, ready to be sent as a set of update, insert, and delete SQL commands to the DB2 UDB.

In other words, until the moment that you send all updates to the DB2 SAMPLE table, the changes are only visible at your client side and not by anyone else. This also means that you're using optimistic locking by default, since you're not locking the database when the client starts to make an update (at the client side), but only when the changes are sent back to the DB2 SAMPLE database. And at that time, someone else may have made changes that conflict with your changes, and instead of a successful update, you'll get an exception back to inform you that the update failed. But let's not get ahead of ourselves, let's first handle the update itself.

Remember the Data Adapter Configuration dialog in Figure 3? If you had used this dialog to create (non-optimized) SQL commands for the Update and Delete SQL commands, then these would have contained many parameters. Using the ADO.NET way, you would have had to create and connect these parameters in order to be able to call the Update method. Fortunately, this was not really needed, since the Borland Data Adapter is smart enough to do all the work behind the scenes— creating the SQL command, as well as creating and filling the parameters—using the AutoUpdate method.

In short, to send the changes (updates, inserts and/or deletes) of the DataSet back to the DB2 SAMPLE table, you need to write the following code:

private void button1_Click(object sender, System.EventArgs e)
{
  if (this.dataSet1.HasChanges())
    this.bdpDataAdapter1.AutoUpdate();
}

The line with the this.dataSet1.HasChanges() is just added to demonstrate the fact that you can actually check for any pending changes (a good thing to do when the application is closed, offering the user a last chance to send all unsaved changes to the DB2 UDB table).

To show in a few more and smaller steps what's going on behind the scenes, consider the following method implementation that does exactly the same thing, but this time explicitly uses a BdpCommandBuilder class to dynamically build the SQL update, insert, and delete commands.

private void button2_Click(object sender, System.EventArgs e)
{
  if (this.dataSet1.HasChanges())
  {
    Borland.Data.Provider.BdpCommandBuilder cb =
      new Borland.Data.Provider.BdpCommandBuilder(bdpDataAdapter1);

    try
    {
      cb.UpdateMode = Borland.Data.Common.BdpUpdateMode.All; // Key
      bdpDataAdapter1.Update(dataSet1, "Table1");
    }
    catch
    {
      // Handle the error...
    }
  }
}

Note that UpdateMode property of the BdpCommandBuilder class that I have to set to BdpUpdateMode.All since the EMPLOYEE table has no primary key (otherwise you can use the BdpUpdateMode.Key to allow the BdpCommandBuilder to generate the optimized SQL commands).

Also note the use of a try-catch block where I respond to any exceptions that you can receive from the server (or from calling the Update command in case you specify BdpUpdateMode.Key and there is no primary key). At this time, I have left the actual error handling in your hands—either display a message, or leave the changes in the DataSet so you can edit them and try again.

If, for some reason, you want to explicitly skip the changes that you've made locally (that is, before you send them to the DB2 SAMPLE database), then you can call the RejectChanges method, as shown below:

private void button3_Click(object sender, System.EventArgs e)
{
  dataSet1.RejectChanges();
}

The three buttons—two with a different update mechanism, and one with the reject changes—are shown together with live data in Figure 4:

Figure 4. The three buttons with live data
Figure 4. The three buttons with live data

And the last screenshot shows the application at run-time after I've made a small change to one of the records:

Figure 5. The application at run-time
Figure 5. The application at run-time

This shows how you can display but also update, insert, or delete records from the DB2 SAMPLE database tables using a C#Builder application and the BDP to produce dynamic SQL. But how easy is it to deploy or install such an application that uses the BDP for .NET to talk to the DB2 UDB?


Deploying your application

As I explained in the previous section, the BDP assemblies are signed using a strong key. This means they can be deployed in the Global Assembly Cache (GAC). In effect, you have two ways to deploy your C#Builder executable that uses the BDP. The first one is always available, and is often called the XCOPY deployment. This means that you simply have to place all required files (executables and assemblies) in a single directory and can use XCOPY to deploy it to another machine. For a C#Builder executable, that other machine must be a machine with the .NET Framework version 1.1. In order to connect to DB2 UDB tables, you must have DB2 UDB v7.1 or v8.1 installed on that machine as well (for example, DB2 UDB v8.1 Personal Edition) or at least the DB2 client.

You also need to deploy four files from the C#Builder product (that are freely distributable), namely:

  • Borland.Data.Provider.dll —the Borland Data Providers for .NET—both Borland.Data.Design and Borland.Data.Provider with BdpConnection, BdpAdapter, etc.
  • Borland.Data.Common.dll —common BDP stuff with Borland.Data.Common (BdpError, BdpException, etc) and Borland.Data.Schema
  • Borland.Data.Db2.dll —the DB2 UDB specific classes in Borland.Data.Db2 (like BdpDB2, DB2Command, etc.)
  • bdpdb2.dll —the Win32 DLL that links to the db2cli.dll

In case you wonder why the Borland.Data.Design namespace is included in the Borland.Data.Provider.dll assembly, then the only reason I can give you is the Login Dialog. Note that this may all change in future versions of C#Builder, but at this time, these four files, in combination with your C#Builder project executable and the DB2 UDB, are all that's required for deployment.

The three Borland.Data assemblies can either be placed in the same directory as the client executable (so only our client application can see them and use them), or you can deploy them in the Global Assembly Cache using gacutil. In the latter case, they will be shared on your .NET machine and can be used by other .NET applications as well.


Conclusion

In this article, I've shown that DB2 UDB is a powerful DBMS on the .NET Framework that can be accessed using the Borland Data Providers for .NET, enhancing the ADO.NET experience with lots of design-time and run-time support, including automatic (optimized) SQL command generation.

Deployment of C#Builder applications that work with the DB2 UDB require only four additional files, and can be done with XCOPY, or by placing the assemblies in the Global Assembly Cache (to avoid DLL Hell problems).

Borland RAD tools and DB2 UDB can connect on .NET, native Win32 as well as Linux, and in all cases the Borland tools offer a direct and optimized "connection" to work with the DB2 tables.

Acknowledgements

I would like to thank Dan Miser and Ramesh Theivendran from Borland for their helpful comments while I was writing this article.


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.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=13184
ArticleTitle=Working With DB2 Universal Database Tables Using C#Builder With ADO.NET and BDP for .NET
publish-date=06192003