jinfeng_wang

By default .Text uses SQL Server for its database back end. However, .Text was designed with a provider model to support different database back ends. In 2003, John Kaster presented Delphi 8 to our local users group. Afterwards he issued a challenge, and I accepted. The challenge was to support InterBase with .Text. The result is blogs.borland.com and blogs.teamb.com.

.Text provides two interfaces to make replaceable database backends possible.

• Dottext.Framework.Data.IDbProvider
• Dottext.Framework.Data.IDTOProvider

Dottext.Framework.Data.IDbProvider

This interface defines approximately 65 methods needed to select, insert, update, and delete records for various tables in the database. Data is retrieved from select statements using standard ADO.NET Interfaces and classes. The IDataReader interface is returned for many of the select statements. The rest are returned in a DataSet.

Dottext.Framework.Data.IDTOProvider

For the most part .Text does not directly access data that is returned from a class that implements Dottext.Framework.Data.IDbProvider. Instead it calls a class that implements Dottext.Framework.Data.IDTOProvider, .Text provides a default implementation of this class which it uses for SQL Server called Dottext.Framework.Data.DataDTOProvider. It is responsible for taking the raw data and mapping it into objects that .Text has defined for each type of data.

Implementing the Interfaces

After converting the database schema to InterBase, (Minus the 60+ stored procedures that .Text has for its MS SQL Server implementation) I started by implementing a custom IDbProvider in Delphi for .NET. This provider Implemented all of the database calls with SQL statements instead of using stored procedures.

One problem I ran into was the way .Text used several procedures that returned multiple cursors. The only way to duplicate this behavior for InterBase was to fill a DataSet using Multiple SQL Statements.

var
   Cmd : BDPCommand;
   DA   : BDPDataAdapter;
   ResultData : DataSet;
begin
   ResultData := DataSet.Create;
...
   Cmd.CommandText := 'SELECT * FROM TABLEA';
...
   DA := BdpDataAdapter.Create(Cmd);
   DA.Fill(ResultData);
   DA.Free;
...
   Cmd.CommandText := 'SELECT * FROM TABLEB';
...
   DA := BdpDataAdapter.Create(Cmd);
   DA.Fill(ResultData.Tables.Add);
   DA.Free;
...
end;

I also created a new class that implemented the Dottext.Framework.Data.IDTOProvider using C#, to address data transformation issues. I used C# for this class as it was almost the same as the original one provided by .Text so I able to copy, rename, modify instead of writing it from scratch.

Database Connections

BDP does not currently implement connection pooling, so I needed a custom solution. For performance reasons I could not have each page request connecting to the database. Instead I opted to create a unique Database Connection per thread. With Delphi this was quite easy to do, we placed our connection variable in a threadvar section, then we always accessed our database connection through a function that would check to see if the database was created if it was not created it would create it.

function BdpDataProvider.GetDbConnection: BdpConnection;
begin
  if Not Assigned(FConnection) then
     FConnection := BdpConnection.Create(ConnectionString);
end;

The number of possible page request threads your ASP.NET application can have is partially controlled by how you have configured your system, and also is further controlled by how you have configured you application. The following article explains many of the details on how threading works in ASP.NET.

Closing the BdpCommand

When working BDP you must remember to always call close on the BdpCommand. SqlClient does not seem to have this requirement most of the original .Text DTO provider code never called close on the Command, but I have heard from other users of .Text that their servers might run more reliably if the commands were closed in the .Text code. During during the testing, I found myself on a bug hunt, making sure every command was closed.

Specifically remember to call close in the correct order.

BdpReader.Close;
BdpCommand.Close;
BdpConnection.Close;

Building the Code

.Text was written with C# in Visual Studio 2003. My IDBProvider was written in Delphi for .NET. Initially, to compile the application, I had to first compile the .Text Solution in Visual Studio. Then I used Delphi 8 for .Net to compile the Provider. .Text does not know about the Delphi Assembly at compile time: it dynamically loads the provider using a value stored in the web.config file. Initially, I was unsure of what to expect when debugging my Delphi Assembly when a Visual Studio application was the main project. I found out that as long as I include debug information, it is possible to step through your Delphi code in Visual Studio.

Now that Delphi 2005 that combines both Delphi and C#, we were able to use the Visual Studio project Import wizard to import the existing projects. After setting up all the projects into a single Project Group and I am able to compile the entire blogging application inside Delphi, with no need to use Visual Studio at all on the project.

In addition to writing a custom provider, I modified the security system in .Text so that Borland Employees can use their BDN account to administer their .Text blogs, instead of having another user id/password to maintain. Currently this is done by modifying security.cs from the .Text code base, but I hope to change this to be a provider-based model similar to the database provider model in use now.

Feedback intelligence

If you are logged into BDN when you submit feedback, your name will auto-populate with your BDN account information. In the future, the feedback may be changed to require that you log into your BDN account to comment, if comment spam becomes a problem as it has for some other popular blog servers.

Complete interoperability

During this exercise, I was able to prove to myself that Delphi is a first class citizen in the world of the .Net framework. It was able to work in a mixed language environment with out any problems. I also found that BDP is a good database solution that implements the ADO.NET interfaces correctly.

Robert Love - http://peakxml.com (My personal blog)

Side Note: If you are Delphi user interested in creating a technical blog for yourself, visit blogs.slcdug.org. we still have enough bandwidth for quite a few more active bloggers.

---

NOTE: The views and information expressed in this document represent those of its author(s) who are solely responsible for its content. Borland does not make or give any representation or warranty with respect to such content.