Create a TTable at runtime

Problem/Question/Abstract:

How to create a TTable at runtime

Answer:

Solve 1:

Delphi allows rapid addition and configuration of database elements to a Delphi project within the design environment, but there are situations where information needed to create and configure objects is not known at design time. For instance, you may want to add the ability to add columns of calculated values (using formulas of the users own creation) to an application at runtime. So without the benefit of the design environment, Object Inspector, and TFields editor, how do you create and configure TFields and other data related components programmatically?

The following example demonstrates dynamically creating a TTable, a database table based off the TTable, TFieldDefs, TFields, calculated fields, and attaches an event handler to the OnCalc event.

To begin, select New Application from the File menu. The entire project will be built on a blank form, with all other components created on-the-fly.

In the interface section of your forms unit, add an OnCalcFields\ event handler, and a TaxAmount field to the form declaration, as shown below. Later we will create a TTable and hook this handler to the TTable's OnCalcFields event so that each record read fires the OnCalcFields event and in turn executes our TaxAmountCalc procedure.

type
  TForm1 = class(TForm)
    procedure TaxAmountCalc(DataSet: TDataset);
  private
    TaxAmount: TFloatField;
  end;

in the implementation section add the OnCalc event handler as shown below.

procedure TForm1.TaxAmountCalc(DataSet: TDataset);
begin
  Dataset['TaxAmount'] := Dataset['ItemsTotal'] * (Dataset['TaxRate'] / 100);
end;

Create a OnCreate event handler for the form as shown below(for more information on working with event handlers see the Delphi Users Guide, Chapter 4 "Working with Code").

procedure TForm1.FormCreate(Sender: TObject);
var
  MyTable: TTable;
  MyDataSource: TDataSource;
  MyGrid: TDBGrid;
begin
  {Create the TTable component - the underlying database table is created later}
  MyTable := TTable.Create(Self);
  with MyTable do
  begin
    {Specify an underlying database and table. Note: Test.DB doesn't exist yet}
    DatabaseName := 'DBDemos';
    TableName := 'Test.DB';
    {Assign TaxAmountCalc as the event handler to use when the OnCalcFields
                event fires for MyTable}
    OnCalcFields := TaxAmountCalc;
    {Create and add field definitions to the TTable's FieldDefs array, then create
                a TField using  the field definition information}
    with FieldDefs do
    begin
      Add('ItemsTotal', ftCurrency, 0, false);
      FieldDefs[0].CreateField(MyTable);
      Add('TaxRate', ftFloat, 0, false);
      FieldDefs[1].CreateField(MyTable);
      TFloatField(Fields[1]).DisplayFormat := '##.0%';
      {Create a calculated TField, assign properties, and add to MyTable's
                        field definitions array}
      TaxAmount := TFloatField.Create(MyTable);
      with TaxAmount do
      begin
        FieldName := 'TaxAmount';
        Calculated := True;
        Currency := True;
        DataSet := MyTable;
        Name := MyTable.Name + FieldName;
        MyTable.FieldDefs.Add(Name, ftFloat, 0, false);
      end;
    end;
    {Create the new database table using MyTable as a basis}
    MyTable.CreateTable;
  end;
  {Create a TDataSource component and assign to MyTable}
  MyDataSource := TDataSource.Create(Self);
  MyDataSource.DataSet := MyTable;
  {Create a data aware grid, display on the form, and assign MyDataSource to
        access MyTable's data}
  MyGrid := TDBGrid.Create(Self);
  with MyGrid do
  begin
    Parent := Self;
    Align := alClient;
    DataSource := MyDataSource;
  end;
  {Start your engines!}
  MyTable.Active := True;
  Caption := 'New table ' + MyTable.TableName;
end;

The following is the full source for the project.

unit gridcalc;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms,
  Dialogs, Grids, DBGrids, ExtCtrls, DBCtrls, DB, DBTables, StdCtrls;

type
  TForm1 = class(TForm)
    procedure FormCreate(Sender: TObject);
    procedure TaxAmountCalc(DataSet: TDataset);
  private
    TaxAmount: TFloatField;
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

procedure TForm1.TaxAmountCalc(DataSet: TDataset);
begin
  Dataset['TaxAmount'] := Dataset['ItemsTotal'] * (Dataset['TaxRate'] / 100);
end;

procedure TForm1.FormCreate(Sender: TObject);
var
  MyTable: TTable;
  MyDataSource: TDataSource;
  MyGrid: TDBGrid;
begin
  MyTable := TTable.Create(Self);
  with MyTable do
  begin
    DatabaseName := 'DBDemos';
    TableName := 'Test.DB';
    OnCalcFields := TaxAmountCalc;
    with FieldDefs do
    begin
      Add('ItemsTotal', ftCurrency, 0, false);
      FieldDefs[0].CreateField(MyTable);
      Add('TaxRate', ftFloat, 0, false);
      FieldDefs[1].CreateField(MyTable);
      TFloatField(Fields[1]).DisplayFormat := '##.0%';
      TaxAmount := TFloatField.Create(MyTable);
      with TaxAmount do
      begin
        FieldName := 'TaxAmount';
        Calculated := True;
        Currency := True;
        DataSet := MyTable;
        Name := MyTable.Name + FieldName;
        MyTable.FieldDefs.Add(Name, ftFloat, 0, false);
      end;
    end;
    MyTable.CreateTable;
  end;
  MyDataSource := TDataSource.Create(Self);
  MyDataSource.DataSet := MyTable;
  MyGrid := TDBGrid.Create(Self);
  with MyGrid do
  begin
    Parent := Self;
    Align := alClient;
    DataSource := MyDataSource;
  end;
  MyTable.Active := True;
  Caption := 'New table ' + MyTable.TableName;
end;

end.


Solve 2:

procedure TForm1.FormCreate(Sender: TObject);
begin
  MyTable := TTable.Create(Self);
  with MyTable do
  begin
    Active := False;
    DatabaseName := 'c:\temp';
    TableName := 'Test.DB';
    if not FileExists(DatabaseName + '\' + TableName) then
    begin
      with FieldDefs do
      begin
        Clear;
        Add('InputNr', ftAutoInc, 0, false);
        Add('SName', ftString, 35, false);
        Add('name', ftString, 35, false);
      end;
      with IndexDefs do
      begin
        Clear;
        Add('InputNr', 'InputNr', [ixPrimary]);
        Add('SName', 'SName', []);
      end;
      CreateTable;
    end;
  end;
  DataSource1.DataSet := MyTable;
  MyTable.Open;
  MyTable.FieldByName('SName').visible := false;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  with OKBottomDlg do
  begin
    Edit1.text := '';
    ShowModal;
    if ModalResult = mrOK then
    begin
      MyTable.Append;
      MyTable.SetFields([nil, AnsiUppercase(Edit1.text), Edit1.text]);
      MyTable.Post;
    end;
  end;
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
  MyTable.IndexFieldNames := 'sname';
end;

procedure TForm1.Button3Click(Sender: TObject);
begin
  MyTable.IndexFieldNames := 'InputNr';
end;

procedure TForm1.Button4Click(Sender: TObject);
begin
  with OKBottomDlg do
  begin
    Edit1.text := MyTable.FieldValues['name'];
    ShowModal;
    if ModalResult = mrOK then
    begin
      MyTable.Edit;
      MyTable.SetFields([nil, AnsiUppercase(Edit1.text), Edit1.text]);
      MyTable.Post;
    end;
  end;
end;


Solve 3:

It depends on the type of database you want to build. However, I can show you how to do it with a Paradox table. Conceivably, it stands to reason that since the TTable is database-independent and if you've got the right settings in the BDE, you should be able to create a table with the TTable component in any database. This is not necessarily true. SQL tables are normally created using the SQL call CREATE TABLE. And each server has its own conventions for creating tables and defining fields. So it's important to note this if you're working with a SQL database. The problem is that SQL databases support different data types that aren't necessarily available in the standard BDE set. For instance, MS SQL server's NUMERIC data format is not necessarily a FLOAT as it's defined in the BDE. So your best bet would probably be to create SQL tables using SQL calls.

What you have to do is declare a TTable variable, create an instance, then with the TTable's FieldDefs property, add field definitions. Finally, you'll make a call to CreateTable, and your table will be created. Here's some example code:

{ "Add" is the operative function here.
  Add(const Name: string; DataType: TFieldType; Size: Word; Required: Boolean);
}

procedure CreateATable(DBName, //Alias or path
  TblName: string); //Table Name to Create
var
  tbl: TTable;
begin
  tbl := TTable.Create(Application);
  with tbl do
  begin
    Active := False;
    DatabaseName := DBName;
    TableName := TblName;
    TableType := ttParadox;
    with FieldDefs do
    begin
      Clear;
      Add('LastName', ftString, 30, False);
      Add('FirstName', ftString, 30, False);
      Add('Address1', ftString, 40, False);
      Add('Address2', ftString, 40, False);
      Add('City', ftString, 30, False);
      Add('ST', ftString, 2, False);
      Add('Zip', ftString, 10, False);
    end;

    {Add a Primary Key to the table}
    with IndexDefs do
    begin
      Clear;
      Add('Field1Index', 'LastName;FirstName', [ixPrimary, ixUnique]);
    end;

    CreateTable; {Make the table}
  end;
end;

The procedure above makes a simple contact table, first by defining the fields to be included in the table, then creating a primary key. As you can see, it's a pretty straightforward procedure. One thing you can do is to change the TableType property setting to a variable that's passed as a parameter to the procedure so you can create DBase or even ASCII tables. Here's snippet of how you'd accomplish that:

procedure CreateATable(DBName, //Alias or path
  TblName: string); //Table Name to Create
TblType: TTableType); //ttDefault, ttParadox, ttDBase, ttASCII
var
  tbl: TTable;
begin
  tbl := TTable.Create(Application);
  with tbl do
  begin
    Active := False;
    DatabaseName := DBName;
    TableName := TblName;
    TableType := TblType;
    with FieldDefs do
    begin
      Clear;
      Add('LastName', ftString, 30, False);
      Add('FirstName', ftString, 30, False);
      Add('Address1', ftString, 40, False);
      Add('Address2', ftString, 40, False);
      Add('City', ftString, 30, False);
      Add('ST', ftString, 2, False);
      Add('Zip', ftString, 10, False);
    end;

    {Add a Primary Key to the table}
    with IndexDefs do
    begin
      Clear;
      Add('Field1Index', 'LastName;FirstName', [ixPrimary, ixUnique]);
    end;

    CreateTable; {Make the table}
  end;
end;

Pretty simple, right? One thing you should note is that the TableType property is only used for desktop databases. It doesn't apply to SQL tables.

Oh well, that's it in a nutshell. Have fun!