Friday, September 12, 2008

Accessing DB2 Universal Database Tables with Delphi for .NET

Original Source click here


Level: Intermediate

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

18 Dec 2003

Bob Swart shows how Borland Delphi 8 for the Microsoft .NET Framework lets you build native .NET applications that connect to IBM DB2 UDB tables, display the data in a DataGrid, and edit, apply, or undo changes in the database tables.
Show developerWorks content related to my search: DelphiShow developerWorks content related to my search: Delphi


Introduction

In this article, I will demonstrate how you can use Borland® DelphiTM 8 for the Microsoft® .NET Framework (abbreviated to "Delphi for .NET" from now on) to build native .NET applications that connect to IBM® DB2® Universal DatabaseTM (UDB) tables, display the data in a DataGrid, and edit, apply, or undo changes in these database tables. As data access technology, I will use both dbExpress - a cross-platform data access framework - and the new Borland Data Providers for ADO.NET, both of which contain drivers for DB2 UDB.



Back to top


Connecting using dbExpress

Last time, I covered the migration of the Borland Database Engine's SQL Links data access layer to the new cross-platform dbExpress data-access framework of Borland (compatible with Kylix on Linux and Delphi on Windows). This time, I will demonstrate that the same dbExpress application will also compile and work with Delphi for .NET - as a native .NET application!

You can take an existing Delphi 7 dbExpress application that works with DB2, or build a new one (for example with Delphi 7) and migrate it to .NET using Delphi for .NET. Let's quickly build a new one using Delphi 7 Enterprise.

Start Delphi and create a new application. Drop a TSQLConnection component from the dbExpress tab of the Component Palette. Right-click on this component to start the Connections Editor and make sure to fill in the correct settings in order to connect to the DB2 UDB SAMPLE database tables (see Figure 1 for my settings).


Figure 1. dbExpress Connections Editor
dbExpress Connections Editor

Close the Connections Editor and set the LoginPrompt property of the TSQLConnection component to False.

Now, drop a TSQLDataSet component and point its SQLConnection property to the TSQLConnection component. Then, click on the ellipsis for the CommandText and use the Query Editor (also called CommandText Editor) to write the following SQL statement:

SELECT * FROM EMPLOYEE
Figure 2. dbExpress CommandText Editor
dbExpress CommandText Editor

Next, drop a TDataSetProvider component and point its DataSet property to the TSQLDataSet component. Drop a TClientDataSet component and point its ProviderName property to the TDataSetProvider component. Finally, drop a TDataSource component and point its DataSet property to the TClientDataSet component.

You can now drop data-aware controls, such as the TDBGrid component. Make sure its DataSource property is pointing to the TDataSource component. You can now get live data at design-time if you open the ClientDataSet (by setting the Active property to True):


Figure 3. dbExpress in Delphi 7 at design-time
dbExpress in Delphi 7 at design-timer

In order to make sure that the changes the end users can make in the DBGrid will be posted back to the DB2 UDB SAMPLE database tables, you need to write one line of code in the OnAfterPost and OnAfterDelete event handlers:

procedure TForm1.ClientDataSet1AfterPostOrDelete(DataSet: TDataSet);
begin
(DataSet as TClientDataSet).ApplyUpdates(0)
end;

Now, you can compile and run the application. It will work as expected.



Back to top


dbExpress and Delphi for .NET

Now it's time to open the project using Delphi for .NET (in my case on a different machine, not using the XP Desktop Theme to show the difference).

Open the project with Delphi for .NET, and press F9 in order to recompile it as a native .NET executable. You will not get any errors or warning, and the resulting project runs exactly as the Win32 original:


Figure 4. dbExpress compiled as native .NET Application
dbExpress compiled as native .NET Application

Note that the application retains the original Delphi 7 icon (in the upper-left corner of the form), although I've made sure that the caption now mentions Delphi for .NET by using the following code in the OnCreate event handler:

procedure TForm1.FormCreate(Sender: TObject);
begin
{$IFDEF VER150}
Caption := 'Delphi 7 using dbExpress to work with DB2 UDB'
{$ELSE}
Caption := 'Delphi for .NET using dbExpress to work with DB2 UDB'
{$ENDIF}
end;

Still, this example shows how you can migrate Delphi 7 dbExpress applications that connect to DB2 to Delphi for .NET, producing native .NET applications that use dbExpress (and VCL for .NET) to work with DB2 UDB on the .NET Framework.

Where dbExpress is a cross-platform solution (it's also available on Linux), let's now examine a .NET-only framework to connect to DB2 UDB: the Borland Data Providers for ADO.NET.



Back to top


Connecting using Borland Data Providers

Earlier this year, I described how the Borland Data Providers are an extension of the ADO.NET data access architecture (in the two articles that use C#Builder to connect to and work with DB2 databases: Working With DB2 Universal Database Tables Using C#Builder With ADO.NET and BDP for .NET and Connecting Borland C#Builder to DB2 UDB with Borland Data Providers for the Microsoft .NET Framework).

This time, I will use the Borland Data Providers for ADO.NET in combination with Delphi for .NET, to build a native WinForms application. Start Delphi for .NET and create a new project.


Figure 5. Delphi for .NET WinForms Application
Delphi for .NET WinForms Application

In the upper-right corner of the Delphi for .NET IDE, click on the Data Explorer tab, where you'll see the list of Borland Data Providers for ADO.NET (with drivers for DB2 and other database management systems). If you open up the DB2 node, you can right-click on the DB2Conn1 connection node and right-click to edit the connection properties.


Figure 6. BDP Connections Editor
BDP Connections Editor

In this case, for DB21Conn1 you need to specify SAMPLE as database, and the correct username and password to connect to this DB2 UDB database. You can click on the Test button to verify that the connection can be made. When the connection is correct, you can open up the DB2Conn1 node, and view the tables, views or (stored) procedures from the DB2 SAMPLE database, as can be seen in Figure 7.


Figure 7. Data Explorer showing DB2 SAMPLE tables
Data Explorer showing DB2 SAMPLE tables

Now, in order to create an application that works with the EMPLOYEE table, you only have to drag the ADMINISTRATOR.EMPLOYEE node from the Data Explorer to the WinForm designer area.

Delphi will then create two non-visual components (see also Figure 13 a bit later): a BdpConnection and a BdpDataAdapter component. These are part of the Borland Data Providers for ADO.NET. The BdpConnection component is responsible for connecting to the DB2 UDB SAMPLE database, while the BdpDataAdapter component will act as an "adapter" between a .NET DataSet and the database itself, using SELECT, UPDATE, DELETE, and INSERT commands (represented by BdpCommand classes, as you'll see in a moment).

The BdpConnection component is already configured, and doesn't require any further attention. However, you may want to examine the settings of the BdpDataAdapter component, so select that. In the lower part of the Object Inspector, you'll see two special verbs (links) appear, one for building Typed Datasets, and another one to Configure the DataAdapter.

Click on Configure the DataAdapter to start the Data Adapter Configuration dialog (see Figure 8 and further).


Figure 8. Data Adapter Configuration - Command
Data Adapter Configuration - Command

Like the dbExpress CommandText Editor, you can select the tables and fields here to build your own SELECT command. However, the Borland Data Providers also enable you to automatically generate your UPDATE, INSERT, and DELETE commands. If you change the table or fieldnames, you can click on the Generate SQL button to regenerate the correct SQL commands.

Note the Optimize option, which affects the WHERE clause of the UPDATE and DELETE commands, making sure that only the primary key fields are used in these WHERE clauses. This may result in faster UPDATE and DELETE statements (the selection is based on the primary key only), but this convenience comes with a potential cost: if someone has already changed a record that you want to update, and the changes were done in non-keyfields, then your UPDATE statement will not be able to identify the fact that this particular record was already changed by another user. This may be OK for you, but at least you need to be aware of the side effect, so use Optimize with care!

By the way, if you enable Optimize for the DB2 UDB SAMPLE table, and click on the Generate SQL button, then the following error message will appear: Automatic SQL statement generation requires a primary key (see Figure 9).


Figure 9. Generate SQL error message
Generate SQL error message

In order to fix this, you either have to restructure the EMPLOYEE table from the DB2 SAMPLE database, or uncheck the Optimize option, and regenerate the correct SQL. Note that this time, the Data Adapter Configuration dialog will explicitly list all fields (compare figure 10 with figure 8), instead of just SELECT * FROM ADMINISTRATOR.EMPLOYEE.


Figure 10. Data Adapter Configuration - New SQL
Data Adapter Configuration - New SQL

Once you're happy with the SELECT statement (and the other SQL commands as well), you can click on the DataSet tab (see Figure 11), to select or create the .NET DataSet that should hold the result of the SELECT statement, and will contain the data that you can update, insert, or delete from.


Figure 11. Data Adapter Configuration - DataSet
Data Adapter Configuration - DataSet

A .NET DataSet component can contain more than one table or result from a query, so you can select an existing DataSet (there isn't one at this time), or create a new one and give it a sensible name.

The next step involves going to the Preview Data tab and getting a preview of the result from the SELECT statement. By default, the output is limited to 100 records, but you can change this number, of course.


Figure 12. Data Adapter Configuration - Preview Data
Data Adapter Configuration - Preview Data

And now, close the Data Adapter Configuration dialog, and return to Delphi for .NET. There are now three components in the non-visual components area of the WebForm designer: BdpConnection, BdpDataAdapter, and DataSet.

When you activate the BdpDataAdapter component by setting its Active property to True, you can explicitly create a new table inside the .NET DataSet.



Back to top


Working with DB2 data

In order to display the contents of this table, you can drop a DataGrid component on the form. Set its DataSource property to DataSet, and its DataMember property to Table1. This will immediately show you live data at design-time, as can be seen in Figure 13.

What you may also notice in Figure 13 are three buttons (btnUndo, btnUpdate, and btnClose), that I gave the captions Undo, Update, and Close. The first two buttons are meant to give the end user the option to either undo the changes made in the DataGrid, or to apply them to the DB2 UDB SAMPLE database.


Figure 13. Delphi for .NET IDE with "live" data at design-time
Delphi for .NET IDE with 'live' data at design-time

The first Click event handler, for the Undo button, is easy, and simply calls the DataSet1.RejectChanges method. The second Click event handler, for the Update button, consists of a call to the BdpDataAdapter's AutoUpdate method. You can call AutoUpdate without arguments, or specify the dataset, tablename, update mode, and other options. In this case, I call the AutoUpdate method on DataSet1, with Table1 as tablename. As UpdateMode you can pass either BdpUpdateMode.Key or BdpUpdateMode.All, which should remind you a bit of the Optimize option in the BdpDataAdapter Configuration dialog. Indeed, using BdpUpdateMode.Key will generate an SQL update statement that uses the primary key of the table in the WHERE clause, while BdpUpdateMode.All will use all fields in the WHERE clause. Since the EMPLOYEE table from the DB2 UDB SAMPLE database has no primary key, you should use BdpUpdateMode.All (something I would probably prefer to do anyway). The source code for these event handlers is as follows:

procedure TWinForm.BtnClose_Click(sender: System.Object; e: System.EventArgs);
begin
Close
end;

procedure TWinForm.BtnUpdate_Click(sender: System.Object; e: System.EventArgs);
begin
if dataSet1.HasChanges then
BdpDataAdapter1.AutoUpdate(dataSet1, 'Table1', BdpUpdateMode.All, [], []);
BtnUpdate.Enabled := dataSet1.HasChanges;
BtnUndo.Enabled := dataSet1.HasChanges
end;

procedure TWinForm.BtnUndo_Click(sender: System.Object; e: System.EventArgs);
begin
if dataSet1.HasChanges then dataSet1.RejectChanges;
BtnUpdate.Enabled := dataSet1.HasChanges;
BtnUndo.Enabled := dataSet1.HasChanges
end;

Note that the BtnUpdate_Click and BtnUndo_Click event handlers also use the dataSet1.HasChanges property to enable or disable the BtnUpdate and BtnUndo buttons (if the RejectChanges or AutoUpdate worked correctly, then both buttons will be disabled at the end of the event handlers).

There's one final thing you need to do, and that's make sure that the two buttons are enabled when a change is made in the DataGrid. For that, you need to hook to the DataTable's RowChanged event handler, and write the following two lines of code:

procedure TWinForm.DataTable1_RowChanged(sender: System.Object;
e: System.Data.DataRowChangeEventArgs);
begin
BtnUpdate.Enabled := dataSet1.HasChanges;
BtnUndo.Enabled := dataSet1.HasChanges
end;

Now, save your work and compile the project. When you run the application, it will connect to the DB2 UDB SAMPLE database and show the EMPLOYEE table in the DataGrid.


Figure 14. Before submitting the change to the DataSet
Before submitting the change to the DataSet

You can make changes to the data in the DataGrid, and when you post that data in the grid (by moving from one row to another), then the RowChanged event handler is fired, which will enable the Undo and Update buttons, as can be seen in Figure 15.


Figure 15. The Change is made: Undo or Update?
The Change is made: Undo or Update?

Note that if you close the application without clicking on the Update button, then your changes are not saved in the actual DB2 SAMPLE database. For that reason, you could consider adding a final check for the DataSet1.HasChanges inside the Close event handler (but I'll leave that as an optional exercise for you).



Back to top


Deploying BDP applications

The Borland Data Provider consists of assemblies that are signed using a strong key and are recommended to be deployed in the Global Assembly Cache (GAC). Specifically, you need to deploy the Borland.Data.Provider.dll, Borland.Data.Common.dll, and Borland.Data.Db2.dll, and also need to make sure that the bdpdb2.dll Win32 DLL can be found as well, since that's the one that links with the DB2 client in db2cli.dll.

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



Back to top


Conclusion

In this article, I've shown that DB2 UDB is a powerful DBMS that can be used by Delphi for .NET in two different ways, using either the dbExpress cross-platform components (which are also compatible with Delphi 7 on Windows, and Kylix on Linux) or the native Borland Data Providers for ADO.NET.

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.

Next time, I will examine the use of DB2 UDB tables in an ASP.NET Web Forms application built with Delphi for .NET.


0 Comments: