by Ramesh Theivendran, Principal Architect

This article highlights the various features that were added to Borland Data Provider (BDP) 2.5 that ships with Borland Developer Studio 2006. It is assumed that you are already familiar with the ADO.NET data-access model and have worked with one of the standard ADO.NET data providers.

BDP 2.5 new features include support for connection pooling, tracing of SQL statements, automatic conflict resolution, extended metadata retrieval, customizable SQL type mapping for data migration, schema create enhancements to support quoting objects and a new MySQL 4.0.24 provider. Overall, most of the features are added to enhance runtime connectivity. Some of the features, such as the schema create enhancements, are added for better design-time and tooling support via the Data Explorer.

Establishing a new database connection can sometimes be very expensive as it involves allocating client and server resources, authenticating the user, etc. Applications can significantly improve performance by establishing a connection and reusing the same connection for subsequent requests. Since database connections are not always active while a client is locally processing data, it is possible for a single connection to be shared across multiple clients. So, a connection pool, which is a cache of database connections, can improve both the performance and scalability of an application especially in a multi-tier architecture.

In ADO.NET, a connection pool is identified by a unique connection string. When a new connection is opened, a new connection pool is created if the connection string is not an exact match to any existing pool. When a new connection pool is created, a minimum number of connection objects are created and added to the pool. If all the existing connections in the pool are being used then new connections are added to the pool up to the maximum pool size. The defaults for connection pooling parameters such as Min Pool Size, Max Pool Size can be overridden in the connection string.

BDP 2.5 now supports connection pooling. BdpPoolManager, BdpPool and ConnectionProperties are internal sealed classes that implement the BDP connection pooling subsystem. BdpPoolManager is a singleton for connection dispatching, and maintains a list of pools. Each pool is an instance of a BdpPool. BdpPool holds a stack of connections anywhere in between Min Pool size and Max Pool Size. Pools are created based on some of the ConnectionString properties. Pool properties include Database, UserName, Password, HostName, OsAuthentication, Pooling, Min Pool Size,

Max Pool Size, Connection Lifetime, GrowOnDemand. Currently, all these pooling properties are used to create a unique pool, and there is no option to customize the pooling properties for creating a connection pool. (In a future release, PoolKeys (currently internal) will be exposed so that users can control the pooling properties.) ConnectionProperties is an internal class for holding the Connection String name value pairs and identifying unique connection strings.

Connection pooling is enabled by default and the Pooling property can be set to false to disable connection pooling. Both pooled and unpooled connections go through the BdpPoolManager. An important advantage to note with the BdpPoolManager is that third party BDP drivers do not have to implement any new interface to take advantage of BDP connection pooling because the BDP core technology takes care of it for the driver. However, if you were to implement your own

ADO.NET provider, you will have to implement your own connection pooling.

Figure 1: Connection pooling

    Connection pooling properties

    New methods in BdpConnection

BDP 2.5 now allows SQL statement executed by provider to be traced. The SQL statements that can be traced include any application specific SQL that the application is executing, metadata retrieval SQL executed by the provider, and any SQL that is generated by the BDP resolver for resolving changes.

To enable tracing, set Tracing = True in the BdpConnection editor which in turn will set BdpConnection.ConnectionOptions. All the SQL statements will then be automatically traced into the Windows Event Viewer. You can redirect tracing by hooking the Tracing event in BdpConnection. Once that is done, any time a SQL is prepared or executed, this event will be triggered and BdpTracingEventArgs.Message will have the SQL.

The following code snippet will show a message box with the SQL being executed.

procedure TWinForm.BdpConnection1_Tracing(sender: System.Object; e: Borland.Data.Common.BdpTracingEventArgs);

begin

MessageBox.Show(e.Message);

end;

Hide image

Figure 2: BDP tracing showing a SQL in Windows event viewer

In BDP, DataSet changes can be resolved back to the database by either calling BdpDataAdapter.Update() or BdpDataAdapter.AutoUpdate(). BdpDataAdapter.Update() uses static SQL and doesn’t handle resolving NULL data correctly. It is mainly useful when you want to provide the SQL for resolving. For example: when you resolve changes by executing stored procedures for INSERTs, UPDATEs and DELETEs.

If you want BDP to automatically resolve single DataTable and master-detail DataSet changes BdpDataAdapter.AutoUpdate() is recommended instead.

During the resolving process, errors can occur because of constraint violations on the database. In a multi-user environment, the data on the server might have been modified by other users and as a result an optimistic locking would fail. When such errors happen in ADO.NET a concurrency violation exception will be thrown.

Application developers can handle conflict resolution by iterating through all the DataTables in the DataSet and look for errors by checking the DataTable.HasErrors property. Once a DataTable with errors is found you can call DataTable.GetErrors() to get an array of DataRows with errors. Then you can iterate through the DataRows and call DataRow.GetColumnsInError() to return an array of DataColumns with error. At this point, depending upon the error, you can either skip the errors by calling DataRow.ClearErrors() or attempt to resolve the conflict by retrieving the error row from the database and reconciling it with the changes in the DataSet.

BDP 2.5 now automatically handles error reconciliation when BdpDataAdapter.AutoUpdate() or DataHub.ApplyChanges() fails. All you need to do is hook the OnUpdateError event in BdpDataAdapter or DataHub and create a ReconcileErrorForm as shown in the following code snippet.

procedure TWinForm.BdpDataAdapter1_OnUpdateError(sender: System.Object; e: Borland.Data.Common.BdpUpdateErrorEventArgs);
var f : ReconcileErrorForm;
begin
    f := ReconcileErrorForm.Create(e);
    f.ShowDialog();
end;

For each error, the OnUpdateError event is triggered and the BdpUpdateErrorEventArgs has ErrorRow and ServerTable which provides the error row and the current data on the database to the ReconcileErrorForm. On the ReconcileErrorForm you have options to retry the update using primary key, skip the current error and continue resolving other changes or abort all the changes.

When working with DataHub and DataSync, error reconcile is not enabled by default. To enable error reconcile you need to set DataSync.Reconcile property to true. DataHub error reconcile will also work in a multi-tier distributed application when hosting a DataSync on a .NET Remoting server using RemoteServer and RemoteConnection components.

Hide image

Figure 3: BDP Error reconcile form

BDP allows migrating tables from one BDP data source to another easily. The data type mappings are built into the provider and can be persisted into an XML file by calling ISQLSchemaCreate.WriteSQLTypeMapping. Users can now change the mapping in the XML file

and use the new mapping for data migration after loading the mapping by calling ISQLSchemaCreate.ReadSQLTypeMapping. Currently, there is no UI for customizing the mapping, and future releases will make this process easier.

BdpCopyTable by default uses the built-in SQL type mapping and this can be changed by setting the SQLTypeMapping property to the modified mapping file as shown in the following code snippet.

procedure TWinForm3.Button1_Click(sender: System.Object; e: System.EventArgs);
var
 md: ISQLMetaData;
 sc: ISQLSchemaCreate;
begin
     if (bdpconnection1.State <> ConnectionState.Open) then
       bdpConnection1.Open
    md := bdpConnection1.GetMetaData;
    sc := md.GetSchemaCreate;
    sc.WriteSQLTypeMapping('c:\demos\bdpSQLMapping.xml',true);
  end;

procedure TWinForm3.Button2_Click(sender: System.Object; e: System.EventArgs);
begin
    bdpCopyTable1.SQLTypeMapping :='c:\demos\bdpSQLMapping.xml';
    bdpCopyTable1.Copy;   
end;