By: Robert Love
Abstract: A case study on the porting of .Text from SQL Server to InterBase using Delphi for .NET
Whether you love or loathe blogging, it has grown extremely popular.
Many sites and applications that have been created for blogging in an attempt
to fulfill this new demand. One such application is .Text, written by Scott Watermasysk.
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
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
.Text provides two interfaces to make replaceable database backends
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.
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.
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
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.
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.
Specifically remember to call close in the correct order.
.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.
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.
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
Robert Love - http://peakxml.com (My personal
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.
Download Delphi XE8 now!
Webinars on demand!
More social media choices:
Delphi on Google+
@RADTools on Twitter
Server Response from: ETNASC01