Friday, September 12, 2008

Moving DB2 database applications to ADO.NET with Delphi 8 for .NET

Original Source click here

Level: Intermediate

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

19 Feb 2004

Borland Delphi 8 for the Microsoft .NET Framework introduces the ADO.NET database architecture to Delphi applications for the first time. This article outlines what's different about ADO.NET and explains how to use it from existing Delphi VCL applications using .NET.
Show developerWorks content related to my search: DelphiShow developerWorks content related to my search: Delphi


Introduction

Borland® DelphiTM 8 for the Microsoft® .NET Framework (from now on referred to as Delphi 8 for .NET) introduces a new way to work with databases. If you've used Delphi or Kylix in the past, you'll be familiar with the Borland Database Engine or dbExpress and their components. However, the Microsoft® .NET Framework makes a different database engine available - the Microsoft ADO.NET library.

First I'll cover what ADO.NET gives Delphi developers, then I'll outline how the different components in ADO.NET work together. Finally, we'll explore how to use ADO.NET from existing Delphi VCL code.

There are other articles on IBM DeveloperWorks that illustrate how to use ADO.NET to access IBM® DB2® Universal DatabaseTM (UDB) data from Delphi 8. These include Accessing DB2 Universal Tables with Delphi for .NET, which uses .NET Windows Forms to work with ADO.NET data, and Using Delphi for .NET to work with DB2 Database Tables in ASP.NET Web Pages, that illustrates how to use Delphi to create ASP.NET Web applications that use DB2 through ADO.NET.



Back to top


The Delphi 8 for .NET architecture

Delphi 8 for .NET has two personalities. Both use the Pascal-derived Delphi language and share the same IDE. However, one creates applications using the Visual Component Library; the other personality creates Windows Forms (or WinForms) and ASP.NET applications that use the Microsoft .NET approach to development.

When using Windows Forms, it's possible to use the Delphi form designer to graphically connect together ADO.NET components in rather the same way as the VCL components for the BDE and dbExpress. (See Bob Swart's article, Accessing DB2 Universal Tables with Delphi for .NET, for examples of how this is done.)

However, a VCL Forms application can't use ADO.NET components visually. It's necessary to create the ADO.NET components through program code and work with them from there. In this article, I'll show you how to use Delphi program code to use ADO.NET.

As it happens, using program code is equally appropriate for both VCL and Windows Forms applications. For, like Borland C#Builder™, Delphi 8 for .NET Windows Forms applications have no data modules, so if you want to centralize your database retrieval you'll need to use program code.



Back to top


Why change?

The first question that should be asked is: Why use ADO.NET at all?

dbExpress continues to be supported by Borland under .NET, but it uses a non-managed DB2 driver. This may well reduce the performance of the application and could leave a system open to security issues.

ADO.NET is part of the .NET Framework and is 100% .NET managed code. It is the database driver technology used by Visual Studio .NET, so practically every database vendor supports ADO.NET: if the drivers work with Visual Studio .NET, they'll work with Delphi 8 for .NET.

This also means there is a vast (and growing) library of knowledge on ADO.NET and how best to use it. This knowledge is directly applicable to Delphi programs.

Finally, Borland has introduced a final advantage to ADO.NET. The Borland Data Provider (BDP) makes it possible to construct a .NET application that uses any of the principal database servers without the need to recompile or deploy extra drivers. This is especially useful for OEMs or VARs that wish to build a single version of their application for many clients.



Back to top


Using ADO.NET from VCL applications

As I mentioned, VCL applications cannot use .NET Framework components directly - they have to be created using program code. While this might seem to be restrictive, the program code isn't difficult and is quite easy to understand and maintain.

This means that you'll need to write some program code to create the ADO.NET components in your application. I'll outline the steps that you'll need to take to create a typical connection to a DB2 database and execute a simple query. You'll then use this query to populate a regular VCL DBGrid on a form.



Back to top


Using ADO.NET from code

Here's a simplified architectural view of ADO.NET as compared with the BDE.


Figure 1. Comparing ADO.NET with VCL components using the BDE
Figure 1

In summary, the DBConnection component links to the database, then the DBCommand and DBDataReader execute queries on the database. The DataSet component (which contains DataTables and DataColumns) uses the DBDataReader to populate a cached local data set, which is then displayed and modified through the Windows Forms components.

As supplied by Microsoft, DBConnection, DBCommand, and DBDataReader are abstract interfaces rather than actual concrete classes. Microsoft supplies managed database providers for different kinds of servers, such as SqlDBConnection, SqlDBCommand, and SqlDBDataReader for Microsoft SQL Server. IBM also supplies a managed provider with DB2Connection, DB2Command, and DB2DataReader.

Although this makes it possible for specialized features of each database to be made available in the data provider, the disadvantage of this approach is that your code is tied to that particular server.

Borland helps you get around this restriction through the BDP. This is a single set of classes - BdpConnection, BdpCommand, BdpDataReader, and so on - that can access many different database servers. The only change needed in code is the 'connection string' that is passed to the BdpConnection component when it is first instantiated.

For more details on the inside workings of ADO.NET from the perspective of C# and Borland C#Builder, see Moving from the BDE and dbExpress to ADO.NET. This paper also goes into a little more detail on the inner workings of the BDP than I do here.



Back to top


Setting up a Delphi application to use ADO.NET

From a Windows Forms application, it's possible to use the Tool Palette in the form designer to add ADO.NET components to a Delphi 8 for .NET application. As you'd expect, Delphi automatically adds the relevant references to the uses clause in your program .

If you're using ADO.NET from an application through code, you'll need to tell your program to use some additional .NET assemblies. These are where the database drivers and ADO.NET components are located.

First, create a new, blank VCL Forms application, compile it, and save it.

The next step is to add the BDP assemblies to your program. In the Project Manager, expand the References tab for the project to see the current list. For a simple VCL application it will most likely look something like this:


Figure 2. The default references from a blank VCL application
Figure 2

Right-click on the References leaf, and choose Add Reference. Your system will then pause as it looks through all of the assemblies in the Global Assembly Cache.

The two references that you'll want will be Borland.Data.Provider and Borland.Data.Common. Select each of these in turn and add them to the bottom list.

When you've added each of the assemblies, dismiss the dialog. You can now refer to the assemblies just as if you were working with a regular Delphi unit - just add the full assembly name to the uses clause of the application. For example, to use BDP managed provider, add

uses Borland.Data.Provider, Borland.Data.Common;

to your application.



Back to top


Setting up Delphi to bridge between ADO.NET and VCL

There's one final setup step that you'll need to look at if you want to use ADO.NET from a VCL Forms application, and that's to load the TADONETConnector component into the tool palette. That needs a design-time package, not the run-time packages that we've included in our project.

Choose Components | Installed .NET Components. The .NET VCL Components tab of the dialog displays the design-time .NET assemblies that contain the VCL components that you're using in Delphi.

On my PC, by default the ADO.NET Connector isn't included in this list, because it's contained in its own design-time assembly. Choose Add, and browse for

C:\Program Files\Borland\BDS\2.0\Bin\Borland.Vcl.Design.AdoNet.dll

(Don't add Borland.VclAdoNet.dll from the shared assemblies - that's a run-time only package.)

Dismiss the dialog, and you should now see the component appear in the Tool Palette in the Data Access category when you're in form design mode.



Back to top


Connecting with DBConnection

You should be all set up and ready to create ADO.NET components in your application now.

I suggest that you start by creating a DataModule and place the ADO.NET code there. This centralizes the database access logic. So, select File | New | Data Module, and switch to the code unit.

Your first step will be to create a small function that connects to the DB2 database and runs a query. We'll then make this data available to VCL data-aware components on the application's main form.

First, a little more housekeeping so you can use the ADO.NET components in this unit. Add Borland.Data.Provider, Borland.Data.Common, and System.Data to the uses clause at the top of the unit.

Then you'll create the BdpConnection object. This is used as a 'handle' for all the other BDP objects that actually retrieve data from the database. It looks after authentication and session management, somewhat like the TDatabase and TSession components from the BDE.

Add a private declaration

myConn: BdpConnection;

to the class declaration (under the comment {Private declarations}, then add a public forward procedure declaration

procedure Connect;

under the comment {Public declarations} because you'll want to call this procedure from the main form.

Then you can add the body of the procedure itself in the implementation block. You can start with the simple

procedure TDataModule2.Connect;

begin

// Open the connection
myConn:=BdpConnection.Create('database=SAMPLE;assembly=Borl
and.Data.Db2,Version=1.5.0.0,Culture=neutral,'+
'PublicKeyToken=91d62ebb5b0d1b1b;vendorclient=db2cli.dll;provider=DB2;'+
'username=<uname>;password=<pwd>');

end;

You'll need to replace <uname> and <pwd> with your DB2 instance username and password.

This doesn't do anything beyond create a BdpConnection object and use the connection string to connect to the standard DB2 SAMPLE database. But, unlike other ADO data providers, should you need to later change the connection to point to another database, this connection is all that you'd need to change.

This BdpConnection class corresponds roughly to the BDE TDatabase or dbExpress TSQLConnection components. Like these components, BdpConnection is the point where transactions are managed. The approach to transaction management is quite different, however: the BeginTransaction method for the BdpConnection classes returns a transaction object, which can be used as a property for later SQL commands.



Back to top


Retrieving data

With the connection made, we can set about retrieving data from the DB2 database.

ADO.NET uses a DataSet component to act as a local data store. This is a little like a Delphi TClientDataSet, in that it holds a cached copy of the data from the database and makes it available to data-aware controls.

Unlike the TClientDataSet, which just manages a single table, the DataSet component can cache many tables simultaneously. These tables, in turn, consist of several columns. ADO.NET defines DataTable objects for these tables, and each DataTable contains one or more DataColumns.

To populate the DataSet, you'll use a DBCommand object from the data provider - in this case, a BdpCommand object. This runs a SQL query on the server.

The results from this query can be connected to the DataSet through the BdpDataAdpater component. This automatically executes the query in the BdpCommand and puts the data into the DataTable in the DataSet.

This is probably easier to explain in the program code. Add the following object names to the {Private declarations} section of the DataModule unit:

dsStaff: DataSet;
tabStaff: DataTable;
colID, colName, colYears: DataColumn;
commSel: BdpCommand;
daStaff: BdpDataAdapter;

Then add the following to the Connect procedure:

    // Create a new dataset
dsStaff := DataSet.Create('DSStaff');

// Set up the columns for the table for the dataset
colID := DataColumn.Create('ID');
colName := DataColumn.Create('NAME');
colYears := DataColumn.Create('YEARS');

// Set up the table for the dataset and add the columns
tabStaff := DataTable.Create('TabStaff');
tabStaff.Columns.AddRange([colID, colName, colYears]);

// Add the table to the dataset
dsStaff.Tables.AddRange([tabStaff]);

// Create a command object to retrieve data
commSel := BdpCommand.Create('SELECT ID, NAME, YEARS FROM STAFF',myConn);

// Create the data adapter
daStaff := BdpDataAdapter.Create;

// Point the data adapter to the command object to get data
daStaff.SelectCommand := commSel;

// Point the data adapter to the DataSet and the table within it
daStaff.DataSet:=dsStaff;
daStaff.DataTable:=tabStaff;
// Tell the DataAdapter to automatically populate the table
daStaff.Active:=True;

Note the syntax that I'm using for the AddRange methods. The equivalent syntax in C# is, for example,

tabStaff.Columns.AddRange(new DataColumn[] {colID, colName, colYears});

Delphi infers the array type from the array elements.



Back to top


Connecting to data-aware VCL components

Now you have the ADO.NET DataSet configured and running. The final step is to connect it to a grid on the application's main form.

The component that does this is the TADONETConnector. In the DataModule, switch to design mode and add a TADONETConnector from the Tool Palette - you'll find it in the Data Access section. Drop it on the form.

TADONETConnector works a little like a TTable that gets data from a DataTable in a DataSet. Once it has been placed on a form, a regular TDataSource can be used to retrieve its data.

So drop a TDataSource on the form, and connect its DataSet property to ADONETConnector1. Then switch to the project's main unit, select Design view, and add a TDBGrid to the form. Hook up the DataSource: from the File menu, choose Use Unit and select Unit2, then select the DBGrid and set its DataSource property to be DataModule1.DataSource1.

You'll then need to add the code to retrieve data for ADONETConnector1 from the DataSet. Switch back to the code for the data module, and add the following lines to the Connect procedure:

    // Hook up the ADONetConnector
ADONetConnector1.DataTable:=tabStaff;
ADONetConnector1.Active:=True;

The final step is to run the Connect procedure to connect to the database, load the DataSet, and populate the grid. Switch back to the main form, add a TButton, set the Caption to Connect, and in the Click event handler add the line

DataModule2.Connect;

That's it! Compile the application and run it. (If you get compilation problems, make sure that you've remembered to copy over the field definitions for the DataSet, DataTable, and other components. Also, remember you'll need the File | Use Unit between the main form and the DataModule.)

When the application runs, you will at first see a blank grid. Press the button to connect, and you'll see the data appear in the grid.


Figure 3. The completed application
Figure 1


Back to top


Moving from the BDE to ADO.NET and BDP

Here's a quick summary of the components that you'll want to look at if you're used to the BDE. This isn't exhaustive, and is naturally simplified, but it should help you see where to start.

BDE componentWhat it doesADO.NET componentWhat it does
TSession alongside TDataBase Establishes connection to database and manages transactions.DB2Connection, BdpConnectionEstablishes connection to database and manages transactions.
TtableManages a local cache of a table.No direct equivalent. Use either a DataSet with DbDataAdapter or a DbCommand with a DataReader.DataSet creates a local cache of one or more tables based on queries.
TqueryManages a local cache of a query.No direct equivalent. Use either a DataSet / DbDataAdapter or a DbCommand / DataReader.DbCommand / DataReader manage a forward-only cursor into results of a query.
TstoredProcExecutes a stored procedure.DbCommandCan execute a stored procedure, optionally returning a result set.


Back to top


Conclusion

I've shown that replacing the Borland Database Engine in a VCL application with ADO.NET isn't all that hard. In essence, you'll need to add code to instantiate one or more DataSets and populate them through BdpConnection and BdpCommand objects. These DataSets can then be used through TADONETConnector objects to replace existing TTable and TQuery objects.

ADO.NET is a rich, sophisticated database access library. While ADO.NET is quite different from the Borland Database Engine, the streamlined features of the Borland Data Provider (BDP) make it a little easier to use. The BDP gives Windows developers that need to access DB2 an efficient and scalable way to create Windows client and Web applications.



0 Comments: