Analog. Net Entity Framework in Delphi through RTTI. Part one, introductory
After Delhi was revived in Embarcadero, I returned from developing C # to a more familiar tool. I was seriously pleased that most of the syntactic features, classes and various "ryushechek" magically moved from Sharp. Unfortunately, such a pleasant opportunity as mapping a selection from a database onto a collection of classes was left out of the brackets.
In our projects, we often encounter the need for algorithmic processing of various samples, the implementation of which is impossible by means of SQL. For each sample, a class was created, and every time you need to create a new sample, you had to perform exactly the same movements, with the difference that you had to fill in the class fields with pens.
Having stretched our brains and evaluating the possibilities of RTTI, labor costs and the available supply of tambourines, we have a list of “hoteles” for working with databases that are missing in our boring life:
')
And with proper implementation and accurate work in the distance, the possibility of cross-platform work between different types of SQL servers begins to loom.
Let's check the ability to display data from DataSets on classes. The updated RTTI allows you to enumerate the property names of a class, as well as get and set property values.
Let's create an example of sampling from a simple table and filling in the list of classes containing public properties that match the name of the table fields. We will work MS SQL server.
Let's create a DB, in it the table with physical. persons and a couple of entries:
Handles in the UsersPersonsEntity.pas module, create a class TUsersPersonsEntity and, running ahead, declare its list and create for it the type of reader class:
In the current situation, we don’t even need a class constructor. Now the most fun thing is to map a string from the DataSet to an instance of the class. All the reading code is in a separate module and occupies almost one hundred and fifty lines.
For the convenience of operating generic classes, it is desirable to create a base class of table row entities with the virtual designer TBaseDataRecord and generate real entities of table rows from it (see the TUsersPersonsEntity declaration). In addition to the base class, in the module there is a generic class "reader". Its task is to go over the DataSet, create instances of strings, and slip the current selection string into the created instance of the TBaseDataRecord inheritor and store it in the resulting list.
The function of displaying data from a sample for a class is in the TBaseDataRecord. When the class properties are searched, the DataSet will search for fields with the same name. If the field is found, then after a light shamanism with variant types and TValue, the property has the required value.
Unfortunately, "not so simple." In the SetPropertyValueByField method, you have to check that the current property is of type TGUID. MSSQL gives the GUID as a string and a direct assignment gives an error. You have to explicitly convert the string to a GUID. Moreover, further use has shown the need for additional squats for:
The list is constantly updated as it is discovered. But the main thing is that it works. And it works as follows (the actual text of the program):
The main thing in the code is the string UsersPersons: = Reader.Read (Query); . And that's all. Compact, however. And here is the output of the application:
This is just an opportunity check. Although for “flat” simple queries, the above mechanism is quite efficient.
And then - the automatic creation of a database contract and table entities, the creation of a reference database schema, linking lists of entities, updating data, serialization of lists and cross-platform reading.
In our projects, we often encounter the need for algorithmic processing of various samples, the implementation of which is impossible by means of SQL. For each sample, a class was created, and every time you need to create a new sample, you had to perform exactly the same movements, with the difference that you had to fill in the class fields with pens.
Having stretched our brains and evaluating the possibilities of RTTI, labor costs and the available supply of tambourines, we have a list of “hoteles” for working with databases that are missing in our boring life:
')
- Automatic generation of classes according to the structure of the tables of the database being developed.
- Populating class lists with data from tables.
- To implement the creation of classes it will not be superfluous to read the structure of the database tables.
- With the hands on the structure of the database, you can automate:
- Comparison of the structure of the existing database with the reference one to prevent errors when updating the developed software from the end user;
- The formation of a "database contract" containing the constants of the names of tables, fields, stored procedures and functions;
- Creating classes from nn. 1. taking into account the relationships between the tables.
- Creating "wrappers" to call stored procedures and functions.
And with proper implementation and accurate work in the distance, the possibility of cross-platform work between different types of SQL servers begins to loom.
Start simple
Let's check the ability to display data from DataSets on classes. The updated RTTI allows you to enumerate the property names of a class, as well as get and set property values.
Let's create an example of sampling from a simple table and filling in the list of classes containing public properties that match the name of the table fields. We will work MS SQL server.
Let's create a DB, in it the table with physical. persons and a couple of entries:
USE [master] GO CREATE DATABASE [TestRtti] GO USE [TestRtti] GO CREATE TABLE [dbo].[Users_Persons]( [Guid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_Users_Persons_Guid] DEFAULT (newid()), [Created] [datetime2](7) NOT NULL CONSTRAINT [DF_Users_Persons_Created] DEFAULT (getutcdate()), [Written] [datetime2](7) NOT NULL CONSTRAINT [DF_Users_Persons_Written] DEFAULT (getutcdate()), [First_Name] [nvarchar](30) NOT NULL, [Middle_Name] [nvarchar](30) NOT NULL, [Last_Name] [nvarchar](30) NOT NULL, [Sex] [bit] NOT NULL, [Born] [date] NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Users_Persons] ADD CONSTRAINT [PK_Users_Persons] PRIMARY KEY NONCLUSTERED ( [Guid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO INSERT [dbo].[Users_Persons] ([Guid], [Created], [Written], [First_Name], [Middle_Name], [Last_Name], [Sex], [Born]) VALUES (N'291fefb5-2d4e-4ccf-8ca0-25e97fabefff', CAST(N'2016-07-21 10:56:16.6630000' AS DateTime2), CAST(N'2016-12-09 16:22:01.8670000' AS DateTime2), N'', N'', N'', 1, CAST(N'1970-01-01' AS Date)) GO INSERT [dbo].[Users_Persons] ([Guid], [Created], [Written], [First_Name], [Middle_Name], [Last_Name], [Sex], [Born]) VALUES (N'11ad8670-158c-4777-a099-172acd61cbd3', CAST(N'2016-07-21 10:59:02.2030000' AS DateTime2), CAST(N'2016-12-09 16:22:10.4730000' AS DateTime2), N'', N'', N'', 1, CAST(N'1970-01-01' AS Date)) GO Handles in the UsersPersonsEntity.pas module, create a class TUsersPersonsEntity and, running ahead, declare its list and create for it the type of reader class:
unit UsersPersonsEntity; interface uses Generics.Collections, DataSetReader; type TUsersPersonsEntity = class(TBaseDataRecord) private FGuid: TGUID; FCreated: TDateTime; FWritten: TDateTime; FFirstName: String; FMiddleName: String; FLastName: String; FSex: Boolean; FBorn: TDate; public property Guid: TGUID read FGuid write FGuid; property Created: TDateTime read FCreated write FCreated; property Written: TDateTime read FWritten write FWritten; property First_Name: String read FFirstName write FFirstName; property Middle_Name: String read FMiddleName write FMiddleName; property Last_Name: String read FLastName write FLastName; property Sex: Boolean read FSex write FSex; property Born: TDate read FBorn write FBorn; end; TUsersPersonsList = TDataRecordsList<TUsersPersonsEntity>; TUsersPersonsReader = TDataReader<TUsersPersonsEntity>; implementation end. In the current situation, we don’t even need a class constructor. Now the most fun thing is to map a string from the DataSet to an instance of the class. All the reading code is in a separate module and occupies almost one hundred and fifty lines.
unit DataSetReader; interface uses System.TypInfo, System.Rtti, SysUtils, DB, Generics.Collections, Generics.Defaults; type TBaseDataRecord = class public constructor Create; overload; virtual; procedure SetPropertyValueByField(ClassProperty: TRttiProperty; Field: TField; FieldValue: Variant); procedure SetRowValuesByFieldName(DataSet: TDataSet); procedure AfterRead; virtual; end; TBaseDataRecordClass = class of TBaseDataRecord; TDataRecordsList<T: TBaseDataRecord> = class(TObjectList<T>); TDataReader<T: TBaseDataRecord, constructor> = class public function Read(DataSet: TDataSet; ListInstance: TDataRecordsList<T> = nil; EntityClass: TBaseDataRecordClass = nil): TDataRecordsList<T>; end; implementation var Context: TRttiContext; { TBaseDataRecord } constructor TBaseDataRecord.Create; begin end; procedure TBaseDataRecord.AfterRead; begin end; procedure TBaseDataRecord.SetPropertyValueByField(ClassProperty: TRttiProperty; Field: TField; FieldValue: Variant); function GetValueGuidFromMsSql: TValue; var Guid: TGUID; begin if Field.IsNull then Guid := TGUID.Empty else Guid := StringToGUID(Field.AsString); Result := TValue.From(Guid); end; var Value: TValue; GuidTypeInfo: PTypeInfo; begin if Field = nil then Exit; GuidTypeInfo := TypeInfo(TGUID); Value := ClassProperty.GetValue(Self); case Field.DataType of ftGuid: begin if Value.TypeInfo = GuidTypeInfo then ClassProperty.SetValue(Self, GetValueGuidFromMsSql) else ClassProperty.SetValue(Self, TValue.FromVariant(FieldValue)); end; else ClassProperty.SetValue(Self, TValue.FromVariant(FieldValue)); end; end; procedure TBaseDataRecord.SetRowValuesByFieldName(DataSet: TDataSet); var Field: TField; FieldName: String; FieldValue: Variant; ClassName: String; ClassType: TRttiType; ClassProperty: TRttiProperty; begin ClassName := Self.ClassName; ClassType := Context.GetType(Self.ClassType.ClassInfo); for ClassProperty in ClassType.GetProperties do begin Field := DataSet.FindField(ClassProperty.Name); if Field <> nil then begin FieldName := Field.FieldName; FieldValue := Field.Value; SetPropertyValueByField(ClassProperty, Field, FieldValue); end; end; end; { TDataReader<T> } function TDataReader<T>.Read(DataSet: TDataSet; ListInstance: TDataRecordsList<T>; EntityClass: TBaseDataRecordClass): TDataRecordsList<T>; var Row: T; begin if ListInstance = nil then Result := TDataRecordsList<T>.Create else begin Result := ListInstance; Result.OwnsObjects := True; Result.Clear; end; DataSet.DisableControls; Result.Capacity := DataSet.RecordCount; while not DataSet.Eof do begin if EntityClass = nil then Row := T.Create() else Row := EntityClass.Create() as T; Row.SetRowValuesByFieldName(DataSet); Row.AfterRead; Result.Add(Row); DataSet.Next; end; end; initialization Context := TRttiContext.Create; end. For the convenience of operating generic classes, it is desirable to create a base class of table row entities with the virtual designer TBaseDataRecord and generate real entities of table rows from it (see the TUsersPersonsEntity declaration). In addition to the base class, in the module there is a generic class "reader". Its task is to go over the DataSet, create instances of strings, and slip the current selection string into the created instance of the TBaseDataRecord inheritor and store it in the resulting list.
The function of displaying data from a sample for a class is in the TBaseDataRecord. When the class properties are searched, the DataSet will search for fields with the same name. If the field is found, then after a light shamanism with variant types and TValue, the property has the required value.
Unfortunately, "not so simple." In the SetPropertyValueByField method, you have to check that the current property is of type TGUID. MSSQL gives the GUID as a string and a direct assignment gives an error. You have to explicitly convert the string to a GUID. Moreover, further use has shown the need for additional squats for:
- MSSQL, OLEDB and DATE, DATETIME
- Processing BLOBs
- Firebird and GUID when stored in CHAR (16) CHARACTER SET OCTETS
- Firebird and TIMESTAMP
The list is constantly updated as it is discovered. But the main thing is that it works. And it works as follows (the actual text of the program):
program TestRtti; {$APPTYPE CONSOLE} {$R *.res} uses DB, ADODB, System.SysUtils, ActiveX, DataSetReader in 'DataSetReader.pas', UsersPersonsEntity in 'UsersPersonsEntity.pas'; var Connection: TADOConnection; Query: TADOQuery; UsersPersons: TUsersPersonsList; UserPerson: TUsersPersonsEntity; Reader: TUsersPersonsReader; i: Integer; begin ReportMemoryLeaksOnShutdown := True; UsersPersons := nil; try CoInitialize(nil); Connection := TADOConnection.Create(nil); try Connection.ConnectionString := 'Provider=SQLNCLI11.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";' + 'Initial Catalog="TestRtti";Data Source=localhost;Initial File Name="";Server SPN=""'; Connection.Connected := True; Query := TADOQuery.Create(nil); Reader := TUsersPersonsReader.Create; try Query.Connection := Connection; Query.SQL.Text := 'SELECT * FROM Users_Persons'; Query.Open; UsersPersons := Reader.Read(Query); Writeln(' : ', UsersPersons.Count); for i := 0 to UsersPersons.Count - 1 do begin UserPerson := UsersPersons[i]; Writeln(Format('%d. %s %s %s %s', [i + 1, UserPerson.First_Name, UserPerson.Middle_Name, UserPerson.Last_Name, FormatDateTime('dd.mm.yyyy', UserPerson.Born)])); end; Writeln(' Enter ...'); Readln; finally Query.Free; Reader.Free; end; finally Connection.Free; if UsersPersons <> nil then FreeAndNil(UsersPersons); end; except on E: Exception do Writeln(E.ClassName, ': ', E.Message); end; end. The main thing in the code is the string UsersPersons: = Reader.Read (Query); . And that's all. Compact, however. And here is the output of the application:
What's next
This is just an opportunity check. Although for “flat” simple queries, the above mechanism is quite efficient.
And then - the automatic creation of a database contract and table entities, the creation of a reference database schema, linking lists of entities, updating data, serialization of lists and cross-platform reading.
Source: https://habr.com/ru/post/318318/
All Articles