Creating database tables programmatically with Delphi DBX4 metadata classes
Inspired by Steve Shaughnessy's post on "The New dbExpress 4.0 MetaData" I've decided to give this approach a try and created a sample Delphi 2007 for .NET VCL Forms application that I'm using during current serie of CodeGear seminars to demonstrate the incredible amount of innovation in DBX4 architecture. Many Delphi programmers asked me to blog about it, so here we are:-)
With the new DBX4 metadata classes it is possible to programmatically create database tables and indices in code against any database connection configured in the CodeGear RAD Studio 2007 Data Explorer.
One possible approach, as demonstrated by Dr. Bob during 2007 December SDE conference on "Migratie: van BDE naar BlackfishSQL via dbExpress" session, is to programmatically construct a SQL statement with appropriate "CREATE TABLE ..." SQL code and pass it to "TSQLConnection.ExecuteDirect" method for immediate execution. One problem with this approach is the fact that there are subtle differences in SQL syntax accross different database products, so it is very difficult to create one SQL "create table" string good for all databases.
With Delphi DBX4 Metadata classes it is possible to create database tables and indices on the higher level of abstraction. You can now do it in Pascal code instead of low-level SQL statements.
Steps:
1. Start CodeGear RAD Studio 2007
2. In Data Explorer add a connection to a database where you want to programmatically create a new database table. I'm using sample CodeGear InterBase 2007 "Employee.gdb" database located in default installation under: "C:\Program Files\Common Files\CodeGear Shared\Data". Make sure that the name of the database in Data Explorer is "EMPLOYEE".
3. In the "New Items" dialog select "VCL Forms Application - Delphi for .NET". Save All.
4. Select "Add Reference" from "Project" menu. In the "Add Reference" dialog select "Borland.Data.DbxCommonDriver.dll", "Borland.Data.DbxReadOnlyMetaData.dll" and "Borland.Data.AdoDbxClient" assemblies. Click "Add Reference" button, and then OK.
5. Add an empty Delphi unit to project and save it as "dbxutils".
6. Replace new unit's empty body with the following code and save.
unit dbxutils; interface uses System.Data.Common, Borland.Data.DBXMetaDataProvider, Borland.Data.AdoMetaDataProvider, // database specific metadata writers Borland.Data.DBXInterbaseMetaData, Borland.Data.DBXDataStoreMetaData; type TDBXUtils = class public class function GetAdoConnection(aConnectionName: string): DBConnection; class function GetAdoMetaDataProvider(aConnection: DBConnection): TAdoMetaDataProvider; class function GetMetaDataProvider(aConnectionName: string): TDBXMetaDataProvider; end; implementation { TDBXUtils } class function TDBXUtils.GetAdoConnection( aConnectionName: string): DBConnection; var Factory: System.Data.Common.DbProviderFactory; Connection: System.Data.Common.DbConnection; begin Factory := System.Data.Common.DbProviderFactories.GetFactory( 'Borland.Data.AdoDbxClient'); Connection := Factory.CreateConnection(); Connection.ConnectionString := 'ConnectionName=' + aConnectionName; Connection.Open; Result := Connection; end; class function TDBXUtils.GetAdoMetaDataProvider( aConnection: DBConnection): TAdoMetaDataProvider; begin if aConnection = nil then raise Exception.Create('Connection is nil'); Result := TAdoMetadataProvider.Create; Result.Connection := aConnection; Result.Open; end; class function TDBXUtils.GetMetaDataProvider( aConnectionName: string): TDBXMetaDataProvider; begin Result := GetAdoMetaDataProvider(GetAdoConnection(aConnectionName)); end; end.
7. Add two "TEdit" and one "TButton" components to the main form of your application. In the Object Inspector change "Name" property of TEdit components to respectively "EditConnectionName" and "EditNewTableName".
8. Add "dbxutils" and "Borland.Data.DBXMetaDataProvider" to the form's unit "uses" clause.
9. Add the following code in the button's "OnClick" event:
procedure TFormMain.Button1Click(Sender: TObject); var AMetaDataTable: TDBXMetaDataTable; AMetaDataProvider: TDBXMetaDataProvider; begin AMetaDataTable := TDBXMetaDataTable.Create; AMetaDataTable.TableName := EditNewTableName.Text; AMetaDataTable.AddColumn(TDBXInt32Column.Create('C1')); AMetaDataTable.AddColumn(TDBXDecimalColumn.Create('C2', 10, 2)); AMetaDataTable.AddColumn(TDBXUnicodeCharColumn.Create('C3', 32)); AMetaDataProvider := TDBXUtils.GetMetaDataProvider(EditConnectionName.Text); AMetaDataProvider.CreateTable(AMetaDataTable); end;
10. Run the application, and click on the button to execute database table creation code. Close the application.
11. In the Data Explorer right-click on "EMPLOYEE" database node and select "Refresh". When you expand "Tables" node you should see newly created database table. If you right-click on the new table and select "Alter Table" you should see the metadata information about the new table.
CONGRATULATIONS! You have just created the first database table in code with the new RAD Studio 2007 DBX4 Metadata classes. The complete project source code is available for download from CodeGear Developer Network CodeCentral.
One of the nicest things about Delphi since its first release is availability of Delphi VCL source code. DBX4 is not different and it comes with the source code. DBX4 is written in very elegant pure Delphi code, with very useful embedded XMLDocumentation Comments, and most interestingly it is single sourced, which means that the same Delphi source was compiled with either the native dcc32 or managed dccil compilers.In order to be able to step into the VCL DBX4 source code from within our project we need to add "C:\Program Files\CodeGear\RAD Studio\5.0\source\database\src\pas\dbx\metadata" directory to search path of the project in "Project Options".
If you now press "Ctrl" button and move mouse over "DBXMetaDataProvider" in "uses" clause
you will be able to step into the VCL DBX source code where types used in "dbxutils" unit are defined.
The "DBXMetaDataProvider" unit defines a number of interesting classes
that can be used to programmatically created tables with indices and foreign keys in code. There is also a number of different convenience classes derived from "TDBXMetaDataColumn" that makes it easy to define columns of certain type. They contain a constructor only, so in reality you will be probably using properties of the base class to define different aspects of the database table column, like for example "AutoIncrement" or "Nullable".
Summary
In this example I've been using Delphi 2007 for .NET, but since RAD Studio 2007 December Update it is also possibly to write a similar application in Delphi 2007 for Win32.
The complete project source code is available for download from CodeGear Developer Network CodeCentral.
With the new DBX4 metadata classes it is possible to programmatically create database tables and indices in code against any database connection configured in the CodeGear RAD Studio 2007 Data Explorer.
One possible approach, as demonstrated by Dr. Bob during 2007 December SDE conference on "Migratie: van BDE naar BlackfishSQL via dbExpress" session, is to programmatically construct a SQL statement with appropriate "CREATE TABLE ..." SQL code and pass it to "TSQLConnection.ExecuteDirect" method for immediate execution. One problem with this approach is the fact that there are subtle differences in SQL syntax accross different database products, so it is very difficult to create one SQL "create table" string good for all databases.
With Delphi DBX4 Metadata classes it is possible to create database tables and indices on the higher level of abstraction. You can now do it in Pascal code instead of low-level SQL statements.
Steps:
1. Start CodeGear RAD Studio 2007
2. In Data Explorer add a connection to a database where you want to programmatically create a new database table. I'm using sample CodeGear InterBase 2007 "Employee.gdb" database located in default installation under: "C:\Program Files\Common Files\CodeGear Shared\Data". Make sure that the name of the database in Data Explorer is "EMPLOYEE".
3. In the "New Items" dialog select "VCL Forms Application - Delphi for .NET". Save All.
4. Select "Add Reference" from "Project" menu. In the "Add Reference" dialog select "Borland.Data.DbxCommonDriver.dll", "Borland.Data.DbxReadOnlyMetaData.dll" and "Borland.Data.AdoDbxClient" assemblies. Click "Add Reference" button, and then OK.
5. Add an empty Delphi unit to project and save it as "dbxutils".
6. Replace new unit's empty body with the following code and save.
unit dbxutils; interface uses System.Data.Common, Borland.Data.DBXMetaDataProvider, Borland.Data.AdoMetaDataProvider, // database specific metadata writers Borland.Data.DBXInterbaseMetaData, Borland.Data.DBXDataStoreMetaData; type TDBXUtils = class public class function GetAdoConnection(aConnectionName: string): DBConnection; class function GetAdoMetaDataProvider(aConnection: DBConnection): TAdoMetaDataProvider; class function GetMetaDataProvider(aConnectionName: string): TDBXMetaDataProvider; end; implementation { TDBXUtils } class function TDBXUtils.GetAdoConnection( aConnectionName: string): DBConnection; var Factory: System.Data.Common.DbProviderFactory; Connection: System.Data.Common.DbConnection; begin Factory := System.Data.Common.DbProviderFactories.GetFactory( 'Borland.Data.AdoDbxClient'); Connection := Factory.CreateConnection(); Connection.ConnectionString := 'ConnectionName=' + aConnectionName; Connection.Open; Result := Connection; end; class function TDBXUtils.GetAdoMetaDataProvider( aConnection: DBConnection): TAdoMetaDataProvider; begin if aConnection = nil then raise Exception.Create('Connection is nil'); Result := TAdoMetadataProvider.Create; Result.Connection := aConnection; Result.Open; end; class function TDBXUtils.GetMetaDataProvider( aConnectionName: string): TDBXMetaDataProvider; begin Result := GetAdoMetaDataProvider(GetAdoConnection(aConnectionName)); end; end.
7. Add two "TEdit" and one "TButton" components to the main form of your application. In the Object Inspector change "Name" property of TEdit components to respectively "EditConnectionName" and "EditNewTableName".
8. Add "dbxutils" and "Borland.Data.DBXMetaDataProvider" to the form's unit "uses" clause.
9. Add the following code in the button's "OnClick" event:
procedure TFormMain.Button1Click(Sender: TObject); var AMetaDataTable: TDBXMetaDataTable; AMetaDataProvider: TDBXMetaDataProvider; begin AMetaDataTable := TDBXMetaDataTable.Create; AMetaDataTable.TableName := EditNewTableName.Text; AMetaDataTable.AddColumn(TDBXInt32Column.Create('C1')); AMetaDataTable.AddColumn(TDBXDecimalColumn.Create('C2', 10, 2)); AMetaDataTable.AddColumn(TDBXUnicodeCharColumn.Create('C3', 32)); AMetaDataProvider := TDBXUtils.GetMetaDataProvider(EditConnectionName.Text); AMetaDataProvider.CreateTable(AMetaDataTable); end;
10. Run the application, and click on the button to execute database table creation code. Close the application.
11. In the Data Explorer right-click on "EMPLOYEE" database node and select "Refresh". When you expand "Tables" node you should see newly created database table. If you right-click on the new table and select "Alter Table" you should see the metadata information about the new table.
CONGRATULATIONS! You have just created the first database table in code with the new RAD Studio 2007 DBX4 Metadata classes. The complete project source code is available for download from CodeGear Developer Network CodeCentral.
One of the nicest things about Delphi since its first release is availability of Delphi VCL source code. DBX4 is not different and it comes with the source code. DBX4 is written in very elegant pure Delphi code, with very useful embedded XMLDocumentation Comments, and most interestingly it is single sourced, which means that the same Delphi source was compiled with either the native dcc32 or managed dccil compilers.In order to be able to step into the VCL DBX4 source code from within our project we need to add "C:\Program Files\CodeGear\RAD Studio\5.0\source\database\src\pas\dbx\metadata" directory to search path of the project in "Project Options".
If you now press "Ctrl" button and move mouse over "DBXMetaDataProvider" in "uses" clause
you will be able to step into the VCL DBX source code where types used in "dbxutils" unit are defined.
The "DBXMetaDataProvider" unit defines a number of interesting classes
- TDBXMetaDataProvider
- TDBXMetaDataTable
- TDBXMetaDataColumn
- TDBXMetaDataIndex
- TDBXMetaDataForeignKey
that can be used to programmatically created tables with indices and foreign keys in code. There is also a number of different convenience classes derived from "TDBXMetaDataColumn" that makes it easy to define columns of certain type. They contain a constructor only, so in reality you will be probably using properties of the base class to define different aspects of the database table column, like for example "AutoIncrement" or "Nullable".
- TDBXInt8Column
- TDBXInt64Column
- TDBXInt32Column
- TDBXInt16Column
- TDBXDoubleColumn
- TDBXDecimalColumn
- TDBXDateColumn
- TDBXBooleanColumn
- TDBXBinaryLongColumn
- TDBXBinaryColumn
- TDBXAnsiVarCharColumn
- TDBXAnsiLongColumn
- TDBXAnsiCharColumn
- TDBXObjectColumn
- TDBXSingleColumn
- TDBXTimeColumn
- TDBXTimestampColumn
- TDBXUInt16Column
- TDBXUInt32Column
- TDBXUInt64Column
- TDBXUInt8Column
- TDBXUnicodeCharColumn
- TDBXUnicodeLongColumn
- TDBXVarBinaryColumn
- TDBXWideVarCharColumn
Summary
In this example I've been using Delphi 2007 for .NET, but since RAD Studio 2007 December Update it is also possibly to write a similar application in Delphi 2007 for Win32.
The complete project source code is available for download from CodeGear Developer Network CodeCentral.
Tags:
.NET
DBX
DBX
DBX4
Delphi
Delphi 2007
Delphi for .NET
Delphi for .NET
Delphi Programming
source-code
source-code
good information thank you