Connecting Borland C#Builder to DB2 UDB with Borland Data Providers for the Microsoft .NET Framework

This article shows how to use Borland C#Builder Enterprise and the Borland Data Providers (BDP) for .NET to connect to the DB2 SAMPLE database. It shows how three of the four BDP for .NET components are used at design-time in a Rapid Application Development (RAD) way.

Bob Swart (drbob@chello.nl), Author, Trainer, Consultant and Webmaster, Bob Swart Training and Consultancy

Bob Swart (aka Dr.Bob - www.drbob42.com) is an author, trainer, consultant and Webmaster working for his own company called Bob Swart Training & Consultancy (eBob42) in Helmond, The Netherlands. Bob, who writes his own Delphi training material, has spoken at Delphi and Borland Developer Conferences since 1993. Bob has written hundreds of articles, and 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.



22 May 2003

Introduction

In this article I'll make a move to the Microsoft® .NET world, using Borland® C#BuilderTM Enterprise and the Borland Data Providers (BDP) for .NET to connect to the DB2 SAMPLE database. Specifically, I'll show how three of the four BDP for .NET components (the BdpConnection, BdpDataAdapter and BdpCommand) are used at design-time in a Rapid Application Development (RAD) way. The BDP for .NET of C#Builder Enterprise can connect to both IBM DB2® Universal DatabaseTM v7.x and DB2 v8.1. I'm using the latest version of DB2 Universal Database v8.1 Personal Edition for this article.


Developing C#Builder applications

Borland C#Builder can generate C# applications that run on the .NET Framework version 1.1. Although C#Builder supports ADO.NET, for high-performance database access use the BDP which implement the .NET Provider interface.

Start C#Builder and do File > New > C# Application to start a new C# project. This will give you a dialog where you can specify the name of the new application as well as the location where the project files should be placed. By default this is the C:\Documents and Settings\<user>\My Documents\Borland Studio Projects\<projectname> directory. I've specified DB2BDS as project name, which automatically suggests that the project files should be placed in my personal DB2BDS project directory.

Using the Borland data providers for .NET

Once the project is created, go to the Tool Palette, open up the Categories combobox and select the Borland Data Provider category (see Figure 1, below). This will show the four BDP components: BdpConnection, BdpCommand, BdpDataAdapter, and BdpCommandBuilder. BdpConnection is used to build a connection to a DBMS, BdpDataAdapter can be used to automatically build a connection and commands, where the BdpCommand is used to run an SQL command on the database, and the BdpCommandBuilder can be used to construct the update, insert, and delete commands. All BDP components have special designers that help the C#Builder developer to build connections, commands and more at design-time.

In this article, I'll show you the easy way to use these components to connect to the DB2 v8.1 SAMPLE database and present some data from the tables, and in the next article I'll go a bit more in-depth and show you what's happening behind the scenes (including ADO.NET details) and how you can make best use of the BDP (including deployment details).


Connecting to DB2 UDB using the BdpConnection

To build a connection to the DB2 v8.1 SAMPLE database, you need to start with a BdpConnection component. Note that when you drop the BdpConnection component (called bdpConnection1) on the WinForm, it will not be shown on the actual WinForm itself, but will automatically be placed on the bottom part of the designer in a section especially reserved for non-visual components (see Figure 1). In fact, all components from the BDP (and other non-visual components) will end up in that special section of the designer, as you'll find out. The benefit of this design feature is that the WinForm itself will never get cluttered with non-visual (data access) components on top or below the visual components.

Figure 1. C#Builder IDE with new BdpConnection component
C#Builder IDE with new BdpConnection

When you click on bdpConnection1 after you've dropped it, a link appears in the bottom part of the Object Inspector (in the lower left corner of Figure 1 , above) that can be used to start the Connection Editor of Figure 2:

Figure 2. Connection Editor
Connection Editor

The BDP: Connections Editor shows the four already prepared example connections: for Borland InterBase® (IBConn1) , Oracle 9i (ORAConn1), DB2 (DB2Conn1) and Microsoft SQL ServerTM 2000 (MSSConn1). The specific properties values for these four example connections can be edited using this dialog, and they will be saved in the external file BDS\1.0\bin\bdpConnections.xml. This file contains definitions for the four different providers (InterBase, Oracle, DB2 and MSSQL) as well as the different connections.

In this case, only the DB2 connection is relevant. You can edit the DB2Conn1 example connection and enter your own values for Database, UserName and Password. However, since you may be working with more than one DB2 database, I usually create a new connection for each and every database that I plan to connect to (which will also be saved in the bdpConnections.xml file). In order to create a new connection for the DB2 SAMPLE database, click on the Add button.

Figure 3. Add new connection
Add New Connection

This will give you a little dialog (see Figure 3) in which you can select the Provider Name (also taken from the bdpConnections.xml file). C#Builder Professional has the choice between InterBase and MS SQL Server (MSDE) while C#Builder Enterprise (or higher) adds Oracle and DB2 to this list. In this case, you have to select DB2 as Provider Name, and can specify a new name for the Connection, such as DB2SAMPLE to indicate that this is the connection to the DB2 SAMPLE database.

You have to specify SAMPLE as name for the Database, the DB2 user as UserName, and the corresponding password as the value for Password. Note from Figure 4 that the Assembly points to Borland.Data.Db2.dll.

Figure 4. Connections editor (again)
Connection Editor

After you have specified all connection property values, you can click on the Text button to test the connection to the DB2 SAMPLE database. If you entered everything correctly, then a BDP dialog will appear with the message "Connection Successful".

Figure 5. Connection successful
Figure 5. Connection Successful

In case you wonder if you need to deploy the bdpConnections.xml file with your application (since that file contains the DB2SAMPLE connection property values), the answer is "No." You don't have to do that because the Connection Name is only a shortcut to help you when you build a new application against an existing database (for which you already have a connection). And the connection properties are assigned to the BdpConnection component in the InitializeComponent() method of the WinForm, as can be seen below:

private void InitializeComponent() 
{ 
  this.bdpConnection1 = new Borland.Data.Provider.BdpConnection(); 
  // 
  // bdpConnection1 
  // 
  this.bdpConnection1.ConnectionOptions = ""; 
  this.bdpConnection1.ConnectionString = "Provider=DB2;" + 
    "VendorClient=db2cli.dll;Database=SAMPLE;" + 
    "UserName=myuser;Password=********;" + 
    "Assembly=Borland.Data.Db2.dll;";

Explore databases with the Data Explorer

Now that you have defined the DB2SAMPLE connection, you can add a BdpDataAdapter component to the WinForm (or rather to the non-visual design section under it) and use the Data Adapter Configuration dialog to generate BdpCommands and DataSets. This is something I'll show in detail next time — when I also look at what's going on behind the scenes. This time, however, you'll use an even easier way to do the work for you: the Data Explorer. This powerful little database browser can be started by View > Data Explorer (see Figure 6).

Figure 6. Data Explorer
Data Explorer

Note the DB2SAMPLE connection under the DB2 node (this is the new DB2 connection we just made), which is the only valid connection that can be opened to view the tables, views, and stored procedures from the DB2 SAMPLE database (the others still contain the sample connection property values).

If you want to use the EMPLOYEE table in the WinForms application, then the quickest way to build this is to drag the EMPLOYEE table from the Data Explorer and drop it on the WinForm. This will automatically cause the C#Builder designer to create the BDP components you need:. Specifically, the drag-and-drop action will result in a BdpDataAdapter and a BdpCommand component being added to the non-visual designer section of the WinForm.

You don't see the data itself yet, because there are still no visual components on the WinForm. In a moment, you'll drop a DataGrid component to view the data from the EMPLOYEE table. But first I want to explore the BdpDataAdapter in a bit more detail.


Configure the BdpDataAdapter

Select bdpDataAdapter1. Under the Object Inspector, you see two links: Configure Data Adapter, and Generate Typed Dataset. If you hadn't dragged the EMPLOYEE table to the WinForms, then this would be the way to configure the data adapter and use the BdpConnection to connect to the DB2 v8.1 SAMPLE database. But in this case, you can click on the Configure Data Adapter link and view the settings of the Data Adapter Configuration dialog (see Figure 7).

The Connection value is set to the bdpConnection1 component that you dropped yourself. On the left side, you can see all tables that can be accessed through this connection to the DB2 SAMPLE database. Note that in my situations the tables get the prefix ADMINISTRATOR (due to the fact that I installed both DB2 and C#Builder as administrator, I'm afraid). The columns listbox shows all fields from the selected table, and in the Select memo field you'll already see the select query that is used by the BdpCommand component, which was also automatically created when we dragged the EMPLOYEE table from the Data Explorer to the WinForm.

Figure 7. Data Adapter configuration - Command
Data Adapter Configuration - Command

The second tab of the Data Adapter configuration dialog can be used to see a preview of the data. But before we can see the data, we need a generic ADO.NET DataSet component. This wasn't generated, yet, but we can do that with the DataSet tab of the Data Adapter Configuration dialog. See Figure 8:

Figure 8. Data Adapter configuration - DataSet
Data Adapter Configuration - DataSet

Because there is no DataSet component on the WinForm, you can now only choose to create a new DataSet and use it for the result of the Select statement in the BdpCommand component. After you've specified that you want to use a new DataSet component, you can go to the Preview Data tab and click on the refresh button to view a preview of the data (as shown in Figure 9).

Figure 9. Data Adapter configuration - previewing data
Data Adapter Configuration - Preview Data

Once you've seen the preview data, it's time to close the Data Adapter Configuration dialog again and return to the WinForm.


View live data in the DataGrid

Now, select the Data Controls category of the Tool Palette and drop a DataGrid component on the WinForm. The DataGrid will be empty, and before you can connect it, you must first activate the DataAdapter. So, select the BdpDataAdapter component and in the Live Data category set the Active property to True.

Now, select the DataGrid component and in the Data category set the DataSource property to dataSet1 (the one that you created using the Data Adapter Configuration dialog), and then set the DataMember property to Table1.

This will show the records from the EMPLOYEE table in the DB2 SAMPLE database at design-time, as shown in Figure 10:

Figure 10. WinForm and DataGrid at design-time
WinForm and DataGrid at design-time

If you take a look at the source code of the project, then the most interesting snippet is the InitializeComponent method where the BdpConnection, BdpCommand, BdpDataAdapter, DataSet and DataGrid component are created and initialized. (I'll examine this code in detail next time, as well as the ways to extend it.)

Figure 11 shows the application after being compiled, running as a native .NET application.

Figure 11. WinForm DataGrid at run time
WinForm DataGrid at RunTime

Conclusion

In this article I've shown how C#Builder contains the Borland Data Providers for .NET so you can quickly and easily build a connection to DB2 UDB databases and thus take advantage of DB2 UDB for your .NET applications. I've shown how to use the BdpConnection component to build a connection to the DB2 database, as well as how to use the Data Explorer to automatically drag-and-drop and create BdpDataAdapter and BdpCommand components that use the BdpConnection to send SQL commands to the DB2 database and put the resulting data in a local DataSet. The last step involved binding the DataSet to a DataGrid for the visual effect.

Next time, I'll examine BDP for .NET and the specific design-time editors, and see how the Borland Data Providers are related to ADO.NET in a bit more detail, and also cover specific deployment steps for the resulting C#Builder project as well as the BDP assemblies.

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=13233
ArticleTitle=Connecting Borland C#Builder to DB2 UDB with Borland Data Providers for the Microsoft .NET Framework
publish-date=05222003