Friday, September 12, 2008

Choose a database access to IBM DB2 UDB from Delphi 8

Original Source click here

Level: Intermediate

Jeremy McGee (jeremy@mcgee.demon.co.uk), Consultant

20 May 2004

This article shows you how to determine the best of three ways to connect your app to DB2.
Show developerWorks content related to my search: DelphiShow developerWorks content related to my search: Delphi


Introduction

There are three principal ways to connect to IBM® DB2® Universal Database™ (UDB) from Borland® Delphi™ 8 for the Microsoft .NET Framework: through the old Borland Database Engine (BDE), the newer dbExpress, or the latest ADO.NET technologies. In this article, I'll help you choose the right one for your application. I'll also give you tips on how to use each of the database engines to get the best performance from your systems.



Back to top


New application - or an upgrade?

The main question that you'll need to look at immediately is whether your application will use the 'traditional' Visual Component Library (VCL) or the native .NET Framework libraries.

Table 1 summarizes the compatibility between the different database libraries and the two frameworks. You'll see that using the BDE or dbExpress components means that you'll have to use the Delphi VCL. On the other hand, the ADO.NET library can be used (from code) by any Delphi 8 application.

Another important point is performance. As we'll see, while the ADO.NET library is perfectly adequate for most interactive tasks, for systems that need top performance the Borland dbExpress drivers for .NET are faster.

Table 1: Summary of support for database technologies.



BDEdbExpressADO.NET
Delphi 7VCLDeprecatedSupportedNot available
Delphi 8VCL.NETDeprecatedSupportedAvailable through code
Delphi 8Windows Forms
and Web Form
s
Not availableNot availableSupported


Back to top


ADO.NET

For Delphi developers, ADO.NET is the 'new' way of linking to databases. ADO.NET uses 100% managed code drivers, so there is no Win32 code between your application and the DB2 database driver library. Because ADO.NET uses native .NET assemblies, it can be installed alongside your application by simply copying some DLLs.


Figure 1. The two approaches to data connections using ADO.NET and DB2.
Figure 1

There are two distinct ways of working with ADO.NET: by direct manipulation of the server through DB2Command components, or through a disconnected DataSet.



Back to top


Direct manipulation through code

The DB2Command components give the best performance, but they have to be used through program code. As well as simple select statements, DB2Command can perform updates, inserts, and deletions, and can also execute stored procedures.

For example, these commands are especially useful if you're creating a generic database storage layer that maps from objects to databases.

For DB2Command components that are used with a SELECT statement, the DataReader object gives an easy way to work with the returned data. The DataReader is read-only and only allows forward traversal of the resultset: there is a Next method but no Prior method. But because this component is so fast and requires little processing on the server, it is the fastest way to do database reporting.



Back to top


Interactive manipulation through components

Although the DB2Command and DataReader components are fast, they have to be used from program code and can only be used to read data forwards. To work with data on a grid, where the user might want to move backwards, some kind of data cache is necessary.

For this, ADO.NET provides the DataSet component. Internally, the DataSet stores data from one or many tables as collections of objects in an XML data store. The Windows Forms and Web Forms components in the .NET Framework can be bound directly to these objects.

Because the DataSet caches the data locally, it is not particularly quick: your application will need to populate the cache with enough data rows to display in the user interface, which might take a while on a slow link. If an application will frequently page through the data, it might be better to use the DataReader component or some kind of stored procedure on the server.

The Windows Forms and Web Forms data binding mechanism is much broader than the approach used by the VCL. Through program code, almost any property that can be set at run-time can be bound to any collection class. While this is extremely flexible, the different approaches used mean that programs that rely on VCL components to bind to DB2 data will probably need extensive rework.



Back to top


Using ADO.NET in existing applications

Although ADO.NET is part of the .NET Framework, the interactive components, such as the DataSet, can't be placed on VCL.NET forms.

However, all of the ADO.NET components can be used through program code, and a special new component can be used to connect the data-aware VCL.NET components to ADO.NET data sources. The TADONETConnector isn't installed in Delphi 8 by default, but it's a very useful component that bridges between the VCL and ADO.NET.


Figure 2. The TADONETConnector bridges between ADO.NET, on the left, and VCL user interface components, on the right.
Figure 2

To install the TADONETConnector, choose from the Delphi 8 menu's Component | Installed .NET Components , then at the dialog choose the .NET VCL Components page. Choose Add and browse to the Borland\BDS\2.0\Bin directory of your Program Files directory. Select Borland.Vcl.Design.AdoNet.dll to make the TADONETConnector component available in the tool palette.

Through the TADONETConnector, it's possible to keep the existing user interface of a Delphi program but add code in (for instance) the DataModules, for the program to use ADO.NET to work with DB2 data rather than the BDE.

Note that the TADONETConnector component is a thin wrapper around a DataSet, the component in ADO.NET that caches data locally on the workstation. This means that while you can move the record pointer bi-directionally, this will not be the fastest way of working with batch data. If performance is important then you may find it best to rework the application to use a .NET DataReader to access DB2 directly.



Back to top


The different flavors of ADO.NET

As I discussed in a previous article, the generic implementation of ADO.NET has a series of database-specific components that are tailored for each server database. For DB2, these are part of the DB2 client library for the .NET Framework and include components such as the DB2DataReader.

While this means that the data access components expose features that are specific for DB2, this also means that an application has to be reworked if a different database needs to be used.

Borland has a more generic approach to ADO.NET. The Borland Data Provider (BDP) is a library that can be used to work with many different server databases.


Figure 3. The Borland Data Provider is a generic set of components for ADO.NET that is not tied to any particular database.
Figure 3. The Borland Data Provider is a generic set of components for ADO.NET that is not tied to any particular database

To change an application that uses the BDP from, say, Microsoft SQL Server to IBM DB2 UDB, would mean merely changing the connection string in the application's configuration. Given that the application doesn't then rely on server-specific features such as advanced stored procedures, the application should run unchanged.

Using the BDP gives the most flexibility. It means that an application can be upgraded to work with .NET separately from any later decision to upgrade the database to DB2.



Back to top


dbExpress

The fastest way to work with DB2 data in Delphi is to use dbExpress. Like the SQLCommand components in ADO.NET, data retrieval using dbExpress provides a read-only, forward-only resultset.


Figure 4. The TSimpleDataSet in VCL.NET is the simplest component to use for two-tier data access to DB2. It's also very fast.
Figure 4. The TSimpleDataSet in VCL.NET is the simplest component to use for two-tier data access to DB2. It's also very fast.

If you want to browse through and update DB2 data with dbExpress, then it's necessary to cache the data locally with a ClientDataSet component. The easiest way to do this in Delphi 7 is through the TSimpleDataSet component, which includes all the necessary provider components to both retrieve and update data interactively. Using a TSimpleDataSet is even simpler than using the ADO.NET components.

Because dbExpress for .NET is a set of VCL components, it's only possible to use it on a VCL form or data module. It can't be used directly from Windows Forms or Web Forms. But for applications that need top performance, especially for tasks where read-only, forward-only functions are all that's necessary, then dbExpress remains the fastest option.



Back to top


Borland Database Engine (BDE)

The venerable Borland Database Engine dates back to before Delphi. The first release was as the IDAPI for Quattro Pro for Windows in 1992. Since then there have been many updates, not least for 32-bit Windows in 1995.

Borland has released a managed .NET version of BDE along with Delphi 8, allowing native .NET applications to use the older Paradox and dBASE format data files. This version of the BDE also works with generic ODBC drivers and with the older Win32 version of the DB2 SQL Links driver, but this is now deprecated and will most likely not be supported in future versions of Delphi.

Many systems have significant amounts of code that use the BDE components TTable, Tquery, and TStoredProcedure. In these situations, the ability to keep the BDE data access option is useful, as it means that it's not essential to rework an application to use dbExpress or ADO.NET.

As we've already seen, the Delphi 8 TADONETConnector component makes it possible to replace the BDE with ADO.NET components. You'll need to create these ADO.NET components through code rather than through the visual designer, but this generally needs only one or two lines of code for each component.

The main advantage of moving to ADO.NET is the removal of the need to supply and maintain the BDE and its configuration file on each workstation. Because ADO.NET is a native .NET assembly, it can be deployed using a simple file copy.



Back to top


Summary

While there might seem to be a bewildering range of choices for data access to DB2 from Delphi 8, the selection depends on what's already been written and what the application does.

For new applications that use Windows Forms or Web Forms: ADO.NET is the best approach. Although not as fast as dbExpress, it includes both interactive caching and forward-only direct manipulation techniques. The data binding capabilities are especially strong, so your application can have a clear separation between data retrieval, business logic, and the user interface.

For new applications that use the VCL: You have a broad range of options. If you are not using data-bound VCL controls, then the .NET version of dbExpress is the fastest way to get data to and from DB2. If you do need to use data binding, then using the ADO.NET library with the TADONETConnector component is a good all-round choice.

For existing VCL applications: The existing dbExpress or BDE connections to DB2 can be used with Delphi 8 with few - if any - changes. However, if you're still using the BDE, then bear in mind that your application still needs to have the Win32 BDE installed and configured. It may be worth considering moving the database access layer to ADO.NET (if data binding is used) through the TADONETConnector component.



0 Comments: