Delphi map database table as class

Question

A friend of mine asked me how he can at runtime to create a class to 'map' a database table. He is using ADO to connect to the database.

My answer was that he can fill an ADOQuery with a 'select first row from table_name', set the connection to database, open the query, and after that by using a cycle on the ADOQuery.Fields he can get FieldName and FieldType of all the fields from the table. In this way he can have all the fields from the table and their type as members of the class.

There are other solutions to his problem?

Answer 1

@RBA, one way is to define the properties of the class you want to map as "published", then use RTTI to cycle through properties and assign the dataset rows to each property.

Example:

TMyClass = class
private
  FName: string;
  FAge: Integer;
published
  property Name: string read FName write FName;
  property Age: Integer read FAge write FAge;
end;

Now, do a query:

myQuery.Sql.Text := 'select * from customers';
myQuery.Open;
while not myQuery.Eof do
begin
  myInstance := TMyClass.create;
  for I := 0 to myQuery.Fields.Count - 1 do
    SetPropValue(myInstance, myQuery.Fields[I].FieldName, myQuery.Fields[I].Value);
  // now add myInstance to a TObjectList, for example
  myObjectList.Add(myInstance);  
  Next;
end;

This simple example only works if all fields returned by the query have an exact match in the class.

A more polished example (up to you) should first get a list of properties in the class, then check if the returned field exists in the class.

Hope this helps, Leonardo.