Delphi Clinic C++Builder Gate Training & Consultancy Delphi Notes Weblog Dr.Bob's Webshop
Dr.Bob's Delphi Notes Dr.Bob's Delphi Clinics Dr.Bob's Delphi Courseware Manuals
 Dr.Bob Examines... #62
See Also: other Dr.Bob Examines columns or Delphi articles

This article will also appear in the UK Developers Group Magazine.

TADONETConnector: using ADO.NET in VCL for .NET
In this paper, I will demonstrate how to use the hardly documented TADONETConnector component to feed (ADO).NET DataSets to VCL for .NET clients, where you can show and work with the contents in regular data-aware controls from the VCL (for .NET).

Delphi VCL applications use VCL TDataSets - and so do Delphi VCL for .NET applications (they use VCL TDataSets). VCL Data Access technologies include the Borland Database Engine (BDE), dbExpress, dbGo for ADO and InterBase Express (IBX) among others.
The .NET Framework offers a different data access technology, called ADO.NET, with a .NET DataSet. The .NET DataSet is a bit similar to a VCL TClientDataSet in that it is disconnected and maintained in memory, but can be streamed to an XML file, and used to hold data resulting from an SQL Command on a database table. One difference is that the .NET DataSet can hold multiple DataTables, where the TClientDataSet can hold only one table (with the exception of nested tables, but these are all related to each other). Unfortunately, that's not the only difference, as their (internal) formats are also quite different: an XML file saved by a VCL TClientDataSet is not understood by a .NET DataSet - and vice versa. In fact, it's very hard to get a .NET DataSet and a VCL TDataSet working together. That's why I'm so surprised that the TADONETConnector of Delphi 2005 (and Delphi 8 for .NET) is so well-hidden...

TADONETConnector
TADONETConnector allows .NET DataTables to supply the content of VCL DataSets. TADONETConnector is a VCL TDataSet descendant, so VCL TDataSources can connect to it. TADONETConnector has a DataTable property, which can be assigned to a .NET DataTable. Brilliant, isn't it?
For some reason, the TADONETConnector component is not installed by default, and it's usage is also not well-documented (if you ask me), so allow me to demonstrate the power of this little gem.

Do File | New and create a new VCL Forms Application - Delphi for .NET project - save it. First, we have to make sure we can use the TADONETConnector component. The design-time registration can be found in the Borland.Vcl.Design.AdoNet.dll assembly, that we manually need to install using the Component | Install .NET Components... dialog. On the .NET VCL Components tab, you can click on the Add button and locate the Borland.Vcl.Design.AdoNet.dll assembly in the Borland\BDS\3.0\Bin directory.

Using Delphi 8 for .NET, this results in a TADONETConnector component being added to the Data Access category, but with Delphi 2005 I found a TListConnector component (and no TADONETConnector component).

It doesn’t really matter, as you can always create the component in source code, of course, but it's a bit strange that the TADONETConnector is becoming even more "hidden" in Delphi 2005 than it was in Delphi 8 for .NET.

Anyway, let's start using TADONETConnector now. According to the Delphi 2005 on-line help, you should use the CommandText property, but this property doesn't exist! Instead of the CommandText property, my experience tells me that we should simply assign a .NET DataTable to the DataTable property of the TADONETConnector.
How do we get a .NET DataTable? Well, like the TADONETConnector, we have to create the .NET DataSet, xxxConnection and xxxDataAdapter components by hand: in code.

In a VCL for .NET unit, you need to add a number of units to the uses clause, namely the System.Data (for DataSet), System.Data.SqlClient (for the sqlConnection etc.), as well as ADONETDb (for the TADONETConnector).

  unit Unit1;
  interface
  uses
    Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
    Dialogs, System.ComponentModel, Borland.Vcl.StdCtrls, Borland.Vcl.Db,
    Borland.Vcl.Grids, Borland.Vcl.DBGrids, Borland.Vcl.ExtCtrls,
    Borland.Vcl.DBCtrls,
    System.Data, // DataSet
    System.Data.SqlClient, // sqlXXX
    ADONETDb; // TADONETConnector

I place two TButtons on the Form: btnConnect and btnUpdate, as well as a TDBNavgiator, TDBGrid and TDataSource. The TDBNavigator and TDBGrid can be connected to the TDataSource already, but the actual data access stuff needs to be done in code, as mentioned before.

Note that in the class declaration below, I've included the connection string to the Northwind database on my NX03\NX3 instance of SQL Server.

  type
    TForm1 = class(TForm)
      btnConnect: TButton;
      btnUpdate: TButton;
      DBNavigator1: TDBNavigator;
      DBGrid1: TDBGrid;
      DataSource1: TDataSource;
      procedure FormCreate(Sender: TObject);
      procedure btnConnectClick(Sender: TObject);
      procedure btnUpdateClick(Sender: TObject);
    const
      ConnectionString = 'user id=sa;data source="NX03\NX3";'+
        'persist security info=True;initial catalog=Northwind;password=********';
    private
      { Private declarations }
      ADODataSet: TADONETConnector;
      SQLConn: sqlConnection;
      SQLAdap: sqlDataAdapter;
      Data: DataSet;
    end;

And as you can see, I've also declared a TADONETCOnnector, sqlConnection, sqlDataAdapter and .NET DataSet in the private section of my VCL Form.

Time to write some code. In the FormCreate method, I'll create instances for the four private components, without actually connecting to the database or retrieving any data, yet.

  procedure TForm1.FormCreate(Sender: TObject);
  begin
    SQLConn := sqlConnection.Create(ConnectionString);
    SQLAdap := sqlDataAdapter.Create('select * from Employees', SQLConn);
    Data := DataSet.Create;
    ADODataSet := TADONETConnector.Create(self)
  end;

A click on btnConnect will execute the Fill method of the sqlDataAdapter, and assign the resulting DataTable from the DataSet to the ADONETConnector's DataTable property. The TDataSource then also needs to be connected to the TADONETConnector component, and then we only need to activate the latter to get the data!

Note that we can now navigate, browse, edit and do anything with the data (originating from an ADO.NET DataSource) with VCL for .NET components. Not VCL for Win32, by the way, since TADONETConnector is a VCL for .NET component only!

  procedure TForm1.btnConnectClick(Sender: TObject);
  begin
    SQLConn.Open;
    try
      SQLAdap.Fill(Data, 'Employees');
      ADODataSet.DataTable := Data.Tables['Employees'];
      DataSource1.DataSet := ADODataSet;
      ADODataSet.Active := True
    finally
      SQLConn.Close
    end
  end;

Sending updates back to the ADO.NET database is a bit more complicated, but not much. We need to use a sqlCommandBuilder to generate Update, Insert and Delete statements for the sqlDataAdapter, and then call the TADONETConnector's ApplyUpdates method, passing the sqlDataAdapter as argument.

  procedure TForm1.btnUpdateClick(Sender: TObject);
  var
    SQLCB: sqlCommandBuilder;
  begin
    SQLCB := sqlCommandBuilder.Create(SQLAdap);
    SQLAdap.UpdateCommand := SQLCB.GetUpdateCommand;
    SQLAdap.InsertCommand := SQLCB.GetInsertCommand;
    SQLAdap.DeleteCommand := SQLCB.GetDeleteCommand;
    ADODataSet.ApplyUpdates(SQLAdap);
  end;

ASP.NET Web Service
Apart from building the connection yourself, this opens up an opportunity of connecting to an ASP.NET Web Service that returns ADO.NET DataSets (as described in Dr.Bob Examines #65 on Multi-tier/Distributed Database Applications in .NET). I've also done this in the past using the IBM DB2 UDB SAMPLE database as server, offering connection and data access as well as update abilities.
This time, I want to use the SQL Server Northwind database. So, start a new project, this time an ASP.NET Web Service Application - Delphi for .NET.

As the name for the new project, I've specified NorthwindWS, which results in a new virtual directory c:\inetpub\wwwroot\NorthwindWS on my machine.

I want to rename the WebService1.asmx file to Database.asmx, so I can reach the Web Service as http://localhost/NorthwindWS/Database.asmx.

Inside the Database.pas file, I now want to rename TWebService1 to a more descriptive name, such as TNorthwindWebService, so let's use Refactoring to do that!

When this is done, I always start by adding a namespace to the web service class definition, using the WebService attribute. I only want to work with the Employees table from the Northwind database, so there are only two methods that I need: GetEmployees and SetEmployees. Note the ConnectionString again, as const embedded in the class definition itself.

  type
    [WebService(Namespace='http://eBob42.org')]
    TNorthwindWebService = class(System.Web.Services.WebService)
      ...
    const
      ConnectionString = 'user id=sa;data source="NX03\NX3";'+
        'persist security info=True;initial catalog=Northwind;password=********';
      ...
    public
      constructor Create;

      [WebMethod]
      function GetEmployees: DataSet;
      [WebMethod]
      procedure SetEmployees(Changes: DataSet);
    end;

The implementation of the two web methods is relatively straightforward:

  function TNorthwindWebService.GetEmployees: DataSet;
  var
    SqlConn: sqlConnection;
    SqlAdap: sqlDataAdapter;
  begin
    SqlConn := sqlConnection.Create(ConnectionString);
    SqlConn.Open;
    try
      SqlAdap := sqlDataAdapter.Create('SELECT * FROM Employees',SqlConn);
      Result := DataSet.Create;
      SqlAdap.Fill(Result)
    finally
      SqlConn.Close
    end
  end;

  procedure TNorthwindWebService.SetEmployees(Changes: DataSet);
  var
    SqlConn: sqlConnection;
    SqlAdap: sqlDataAdapter;
    SqlCB: sqlCommandBuilder;
  begin
    SqlConn := sqlConnection.Create(ConnectionString);
    SqlConn.Open;
    try
      SqlAdap := sqlDataAdapter.Create('SELECT * FROM Employees',SqlConn);
      SqlCB := sqlCommandBuilder.Create(SqlAdap);
      SqlAdap.UpdateCommand := SqlCB.GetUpdateCommand;
      SqlAdap.InsertCommand := SqlCB.GetInsertCommand;
      SqlAdap.DeleteCommand := SqlCB.GetDeleteCommand;
      SqlAdap.Update(Changes)
    finally
      SqlConn.Close
    end
  end;

You can now compile and deploy the Web Service. Any application that needs to use this Web Service must add a Web Reference to this Web Service (the WSDL file or location).
Then we can modify the client application to connect to the Web Service instead of the local SQL Server database. Note that I've added some IFDEFs to make sure we can switch from local to Web Service database now.

  unit Unit1;
  {.$DEFINE WS}
  interface
  uses
    Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
    Dialogs, System.ComponentModel, Borland.Vcl.StdCtrls, Borland.Vcl.Db,
    Borland.Vcl.Grids, Borland.Vcl.DBGrids, Borland.Vcl.ExtCtrls,
    Borland.Vcl.DBCtrls,
    System.Data, // DataSet
    System.Data.SqlClient, // sqlXXX
    localhost.Database, // TNorthwindWebService
    ADONETDb; // TADONETConnector

  type
    TForm1 = class(TForm)
      btnConnect: TButton;
      btnUpdate: TButton;
      DBNavigator1: TDBNavigator;
      DBGrid1: TDBGrid;
      DataSource1: TDataSource;
      procedure FormCreate(Sender: TObject);
      procedure btnConnectClick(Sender: TObject);
      procedure btnUpdateClick(Sender: TObject);
    const
      ConnectionString = 'user id=sa;data source="NX03\NX3";'+
        'persist security info=True;initial catalog=Northwind;password=********';
    private
      { Private declarations }
      ADODataSet: TADONETConnector;
    {$IFDEF WS}
      WS: TNorthwindWebService;
    {$ELSE}
      SQLConn: sqlConnection;
      SQLAdap: sqlDataAdapter;
    {$ENDIF}
      Data: DataSet;
    end;

  var
    Form1: TForm1;

  implementation

  {$R *.nfm}

  procedure TForm1.FormCreate(Sender: TObject);
  begin
  {$IFDEF WS}
    WS := TNorthwindWebService.Create;
  {$ELSE}
    SQLConn := sqlConnection.Create(ConnectionString);
    SQLAdap := sqlDataAdapter.Create('SELECT * FROM Employees', SQLConn);
  {$ENDIF}
    Data := DataSet.Create;
    ADODataSet := TADONETConnector.Create(self)
  end;

  procedure TForm1.btnConnectClick(Sender: TObject);
  begin
  {$IFDEF WS}
    Data := WS.GetEmployees;
  {$ELSE}
    SQLConn.Open;
    try
      SQLAdap.Fill(Data);
  {$ENDIF}
      ADODataSet.DataTable := Data.Tables[0];
      DataSource1.DataSet := ADODataSet;
      ADODataSet.Active := True
  {$IFNDEF WS}
    finally
      SQLConn.Close
    end
  {$ENDIF}
  end;

  procedure TForm1.btnUpdateClick(Sender: TObject);
  {$IFNEF WS}
  var
    SQLCB: sqlCommandBuilder;
  {$ENDIF}
  begin
  {$IFDEF WS}
    WS.SetEmployees(ADODataSet.DataTable.DataSet.GetChanges);
    ADODataSet.MergeChangeLog; // accept changes;
  {$ELSE}
    SQLCB := sqlCommandBuilder.Create(SQLAdap);
    SQLAdap.UpdateCommand := SQLCB.GetUpdateCommand;
    SQLAdap.InsertCommand := SQLCB.GetInsertCommand;
    SQLAdap.DeleteCommand := SQLCB.GetDeleteCommand;
    ADODataSet.ApplyUpdates(SQLAdap)
  {$ENDIF}
  end;

  end.
Note that we now have to call the ADODataSet.MergeChangeLog if the call to WS.SetEmployees was successful, since that won't accept the changes in the DataTable itself.

Summary
In this paper, I have shown how to use the TADONETConnector component to feed (ADO).NET DataSets to VCL for .NET clients, where we can work with the contents in regular data-aware controls from the VCL (for .NET). I've even shown that this can be used in multi-tier applications, where the DataSet is coming from an ASP.NET Web Service, for example. Obviously, .NET Remoting is another possibility.
See my Delphi Training Days for more information about ASP.NET Web Services and .NET Remoting with Delphi for .NET, or read Dr.Bob Examines #65 on Multi-tier/Distributed Database Applications in .NET.

Update: I just found out that Fabricio (Delphian) has posted an article based on this work that shows how to use a TClientDataSet (of VCL for .NET) in an ASP.NET Web Service.


This webpage © 2005-2010 by Bob Swart (aka Dr.Bob - www.drbob42.com). All Rights Reserved.