Delphi Clinic C++Builder Gate Training & Consultancy Delphi Notes Weblog Dr.Bob's Webshop
Bob Swart (aka Dr.Bob) - Medical Officer TurboDB.NET
 DataWeb's TurboDB.NET
See Also: more tool reviews for Delphi, C++Builder and JBuilder

You may have noticed that I've been playing a lot with the Delphi for .NET preview command-line compiler these past few months. And while we're all waiting for a fully integrated .NET development environment from Borland, there's something else that I'm missing right now... easy-to-use data access components that I can use with Delphi for .NET.
Obviously, the subject of this review (TurboDB.NET) covers one of the possible data access solutions available for Delphi developers. And it's even cross-platform, so also good news for Delphi and Kylix developers.

TurboDB.NET
TurboDB.NET is made by dataWeb, a German company. Originally, TurboDB was offered as a BDE replacement for Delphi and C++Builder, and later also for Kylix, and now also for .NET. The latter obviously means that it can be used by C# as well as Delphi for .NET and other .NET development tools. Currently, the demos that ship with TurboDB.NET are for C# only, so I decided to write a few little demos of my own using the Delphi for .NET preview command-line compiler. Like I said in the introduction, I was looking for data access solutions on .NET anyway, so TurboDB.NET came in handy.
TurboDB is a small multi-user database engine for Windows, Linux that can be linked with your executable. For Windows and Linux, this results in stand-alone executables that are easy to deploy (no more BDE to install for example). For the .NET version, there are two small assemblies that need to be deployed - for best results you can register them and use gacutil to put them in the .NET Global Assembly Cache (GAC).
TurboDB stores a database in a single file or as multiple files, and has the capability to encrypt tables inside a database. It uses TurboSQL, a subset of ANSI SQL 92 (and a superset of the ODBC SQL dialect).

Installation and first use
You can download 30-day trial versions of TurboDB from the dataWeb website. These trial versions are fully functional. Installation of TurboDB.NET is done with a single .msi file (the one that you can download). After that, Visual Studio .NET users can add the TurboDB data provider to the Visual Studio .NET toolbox, and the TurboDB viewer to the Tools menu.
Since I'm not using an IDE but a command-line compiler, I have to use the TurboDB.NET tools from the command-line. They can be found in the C:\Program Files\dataWeb\TurboDB.NET\tools directory and are called cfexplorer.exe for the dataWeb Compound File Explorer (to show which files are part of a .tdbd file), and TurboDBViewer.exe for the TurboDB Viewer to actually see the records inside the TurboDB databases, or create/modify tables. We can think of it as the Database Desktop of the BDE.

TurboDB.NET and DCCIL
Now that we've seen that TurboDB.NET consists of two assemblies, and comes with C# samples, it's time to see if we can also use it with the Delphi for .NET preview command-line compiler (I'm using the Delphi for .NET Update 1 in this article, which can be downloaded from the Registered Delphi 7 Users page on the Borland website). If you take a look at the two TurboDB.NET assemblies, you see that one is called DataWeb.TurboDB.dll and the other is DataWeb.TurboDB.Design.dll. I think it's safe to assume that we only need to use classes from the former at run-time. This means that we need add the DataWeb.TurboDB unit to the uses clause of our Delphi for .NET applications, and that we have to compile our Delphi for .NET applications with the -LU flag, specifying the location of the external DataWeb.TurboDB.dll assembly. For this, I've written a little batch file dw.bat as follows:

  dccil -LU"C:\Program Files\dataWeb\TurboDB.NET\bin\DataWeb.TurboDB.dll" %1
The %1 will expand to the argument that I pass the dw.bat file. Note that you may get some errors like "Item redeclared", but these will be ignored, and you still get a native .NET executable (unless you have some real syntax errors, of course).

Creating a Database
As first example, let's see if we can create a new TurboDB.NET database with a table to hold customer records. TurboDB.NET databases can be stored in a single file with the tdbd extension (just like .mdb files for example) or in separate files (where the table and index each have their own file on disk). In order to work with the TurboDB database, we need a TurboDBConnection component. After we set the DataSource (which points to the location of the database), we can call CreateDatabase to create a physical database file on disk. Note that an exception will be raised if the database file already exists, so we should place the CreateDatabase in a try-except block (if an exception "escapes" a .NET application you get a dialog from the .NET Framework that asks you if you want to use a debugger to debug your .NET application. Not a nice dialog, especially not one to present to your clients).
After the database is created (or an exception was handled in case the database already existed), we can open the database, and close it again. All this is shown in the following console application, which creates and opens an empty TurboDB.NET database in g:\usr\uk-bug\database.tdbd (obviously you should specify your own location here if you want to play along).

  program TurboDBDemo42;
  {$APPTYPE CONSOLE}
  uses
    DataWeb.TurboDB;

  var
    TurboDBC: TurboDBConnection;

  begin
    TurboDBC := TurboDBConnection.Create;
    try
      TurboDBC.DataSource := 'g:\usr\uk-bug\database.tdbd';
      try
        TurboDBC.CreateDatabase;
        writeln('Database ' + TurboDBC.DataSource + ' created.');
      except
        on E: Exception do
          writeln(E.ClassName,' Error: ', E.Message,' (', TurboDBC.DataSource, ')')
      end;

      try
        TurboDBC.Open;
        writeln('Database opened.')
      finally
        TurboDBC.Close;
        writeln('Database closed.')
      end
    finally
      TurboDBC.Free
    end;
    readln
  end.
Using the dw.bat file I can compile the above project to a TurboDBDemo42.exe of 12,800 bytes that will create a new, empty database.tdbd of 4,205 bytes. Ready for the next step.

Creating Tables
You can open existing databases in read-only or exclusive mode, which can be specified with the ReadOnly and Exclusive properties. If you want to perform maintenance operations (like creating tables),then it may be a good idea to set Exclusive to True so nobody else can access the database. It's also possible to lock individual tables, which is handy when updating data inside a table in multiple steps that could be seen as a single transaction (note that TurboDB.NET has no explicit support for transactions, and the TurboDBTransaction class is used to lock a database table to simulate the transactions).
Continuing with our example, once we have a database (and an open database connection), we can programmatically create new tables. Of course, you can use the TurboDB Viewer and do it visually, but since it doesn't hurt to get more feeling (and experience) with the Delphi for .NET preview command-line compiler, I'd like to continue doing things non-visually. The DataWeb.TurboDB assembly contains a component called TurboDBCommand for this purpose. When we create a TurboDBCommand instance, we can pass the SQL command string as well as the TurboDBConnection we want to send the command string to. If the SQL command is query, returning a dataset, we can execute the TurboDBCommand using the ExecuteReader method, putting the result in a TurboDBDataReader instance. However, we need to create a table first, so the first SQL statement won't return anything, and can be executed with the ExecuteNoQuery method. For our example, I want to create a customer table with three fields: CustNo (of type integer), Name (char string of length 24) and company (another char string of length 42). The modified example program below creates the customer table inside the database.tdbd database:

  program TurboDBDemo42;
  {$APPTYPE CONSOLE}
  uses
    DataWeb.TurboDB;

  var
    TurboDBC: TurboDBConnection;
    TurboCOM: TurboDBCommand;

  begin
    TurboDBC := TurboDBConnection.Create;
    try
      TurboDBC.DataSource := 'g:\usr\uk-bug\database.tdbd';
      try
        TurboDBC.CreateDatabase;
        writeln('Database ' + TurboDBC.DataSource + ' created.');
      except
        on E: Exception do
          writeln(E.ClassName,' Error: ', E.Message,' (', TurboDBC.DataSource, ')')
      end;
      try
        TurboDBC.Exclusive := True; // exclusive access!!
        TurboDBC.Open;
        writeln('Database opened.');
        TurboCOM := TurboDBCommand.Create('CREATE TABLE customer ' +
          '(CustNo INTEGER NOT NULL, Name CHAR(24), Company CHAR(42))', TurboDBC);
        try
          try
            TurboCOM.ExecuteNonQuery;
            writeln('New (empty) table customer created.');
          except
            on E: Exception do
              writeln(E.ClassName,' Error: ', E.Message,' (', TurboDBC.DataSource, ')')
          end
        finally
          TurboCOM.Free
        end;
      finally
        TurboDBC.Close;
        writeln('Database closed.')
      end
    finally
      TurboDBC.Free
    end;
    readln
  end.
The example application will produce a database with an empty customer table inside database.tdbd of 24,576 bytes. Based on this example code, you can now create all required tables with fields (for more information, there's a detailed PDF file on the dataWeb website that documents the capabilities of TurboDB.NET - it also shows how to create indices on tables using CREATE INDEX, etc.).

Inserting Records
The next step consists of adding records to the customer table. For this we can construct an SQL INSERT INTO statement, using a TurboDBCommand component again, reading the new values of CustNo, Name and Company from the console. Note that I'd rather use write statements to be able to enter the new values directly after the prompt, but unfortunately, the Delphi for .NET preview command-line compiler Update 1 gives me an errormessage "Fatal: System unit out of date or corrupted: missing '@flush'", so I'm afraid I have to use writelns at this time.

  program TurboDBDemo42;
  {$APPTYPE CONSOLE}
  uses
    DataWeb.TurboDB;

  var
    TurboDBC: TurboDBConnection;
    TurboCOM: TurboDBCommand;
    CustNo: Integer;
    Name, Company: String;

  begin
    TurboDBC := TurboDBConnection.Create;
    try
      TurboDBC.DataSource := 'g:\usr\uk-bug\database.tdbd';
      try
        TurboDBC.Open;
        writeln('Database opened.');

        write('New CustNo: ');
        readln(CustNo);
        write('Customer Name: ');
        readln(Name);
        write('Company Name: ');
        readln(Company);

        TurboCOM := TurboDBCommand.Create('INSERT INTO customer ' +
                 ' (CustNo, Name, Company) VALUES (' + IntToStr(CustNo) +
                 ', "' + Name + '", "' + Company + '")', TurboDBC);
        try
          try
            TurboCOM.ExecuteNonQuery;
            writeln('New customer record inserted.');
          except
            on E: Exception do
              writeln(E.ClassName,' Error: ', E.Message,' (', TurboDBC.DataSource, ')')
          end
        finally
          TurboCOM.Free
        end;

      finally
        TurboDBC.Close;
        writeln('Database closed.')
      end
    finally
      TurboDBC.Free
    end;
    readln
  end.
Obviously, this is not a very pleasant way to enter new records, but once you use the code from this third example in an ASP.NET Web Form, you'll suddenly see a web server application that can receive input from a nice-looking web page and insert it into a TurboDB.NET database (on the web server for example). That's a topic for another time.

Performing Queries
The first three small console programs that I've presented here were used to create the TurboDB.NET database, create a new customer table with three fields, and finally to insert new records inside this table. Now it's time to use the data, and in the final console application, I want to show you a bare-bones way to perform a query and walk through the resultset.

  program TurboDBDemo42;
  {$APPTYPE CONSOLE}
  uses
    DataWeb.TurboDB;

  var
    TurboDBC: TurboDBConnection;
    TurboCOM: TurboDBCommand;
    DataReader: TurboDBDataReader;
    Name: String;

  begin
    TurboDBC := TurboDBConnection.Create;
    try
      TurboDBC.DataSource := 'g:\usr\uk-bug\database.tdbd';
      TurboDBC.Open;
      writeln('Database opened.');

      write('Search for Customer Name: ');
      readln(Name);

      TurboCOM := TurboDBCommand.Create('SELECT * FROM customer WHERE (Name = "' +
                  Name + '")', TurboDBC);
      try
        try
          DataReader := TurboCOM.ExecuteReader;
          while DataReader.Read do
          begin
            writeln('CustNo: ', DataReader.GetInt16(0));
            writeln('Name: ', DataReader.GetString(1));
            writeln('Company: ', DataReader.GetString(2));
          end;
          writeln;
        except
          on E: Exception do
            writeln(E.ClassName,' Error: ', E.Message,' (', TurboDBC.DataSource, ')')
        end
      finally
        TurboCOM.Free
      end;

      TurboDBC.Close;
      writeln('Database closed.')
    finally
      TurboDBC.Free
    end;
    readln
  end.
As you can see, when we perform a query, we need to call the ExecuteReader which returns a DataReader instance that we can use to walk through the results of the query. Again, this is but a simple non-GUI example, but next time I will combine the current data-access code we've written this time with some user interface screens on .NET.

Conclusion
DataWeb's TurboDB is a BDE replacement that can be used on Windows, Linux and .NET. The TurboDB.NET can be used with Visual Studio .NET, but also with the Delphi for .NET preview command-line compiler as I've tried to show in this article. Obviously, by the time the full Delphi for .NET IDE is available, we will have real design-time support for TurboDB.NET, but until that time, I can use it to produce useful database applications for .NET.
TurboDB.NET can be purchased from dataWeb for US$ 499 (per developer), and a 30-day trial version is available for download from the dataWeb website. For this article, I used this trial version, and encountered no problems (although I only used the DataWeb.TurboDB.dll assembly).
DataWeb told me that they are currently working on the SQL support for full-text search. Full-text search is a feature of TurboDB since the beginning but currently only available when accessing the tables directly (with a TTdbTable component in Delphi). As soon as it is available in TurboSQL it will also be accessible from TurboDB.NET.

(Bob Swart)

This review was first published in the March/April 2003 issue of the UK-BUG Developer's Magazine
This webpage © 2002-2009 by Bob Swart (aka Dr.Bob - www.drbob42.com). All Rights Reserved.