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 its usage is also not well-documented (if you ask me), so allow me to demonstrate the power of this little gem.
For some reason, the TADONETConnector component is not installed by default, and its usage is also not well-documented
Do File | New, create a new VCL Forms Application - Delphi for .NET project – and 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 an argument.
We can now navigate, browse, edit and do anything with the data (originating from an ADO.NET DataSource) with VCL for .NET components
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! I've done this in the past, using the IBM DB2 UDB SAMPLE database as server, offering connection and data access as well as update abilities (see http://www.drbob42.com/IBM for article #20 and #21).
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)
...
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.