What is the best way to manage multiple instances of the same dataset class across multiple instances of the same form?

Question

Apologies for the long winded question - I am experienced with the basics but this is first time working with datasets and databases (previously applications involved records, arrays and text files). My question is one of best practice and what is the best way to implement this, but to answer you will need to know what I am trying to achieve...

I am building an application that allows a telephone operator to take messages, write them into a form and save them to a DB. The main form contains a grid showing the messages already in the DB, and I have a 'message form' which is a VCL form containing edit, combo and checkboxes for all the respective fields the operator must log, some are mandatory, some are optional. There are lots of little helpers and automation's that run depending on the user input, but my question is related to the underlying data capture and storage.

There are two actions the user can perform:

• Create new/blank message by clicking a button
• Edit message by clicking on respective line in grid

Both of these actions cause an instance of the form to be created and initialized, and in the case of EDIT, the fields are then populated with the respective data from the database. In the case of NEW a blank dataset/record is loaded - read on...

The message form captures four distinct groups of information, so for each one I have defined a specific record structure in a separate unit (lets call them Group1, Group2, Group3 and Group4) each containing different numbers/types of elements. I have then defined a fifth record structure with four elements - each element being one of the previous four defined record structures - this is called TMessageDetails.

Unlike perhaps other applications, I am allowing the user to have up to 6 instances of the message form open at any one time - and each one can be in either NEW or EDIT mode. The only restriction is that two forms in EDIT mode cannot be editing the same message - the main form prevents this.

To manage these forms, I have another record (TFormDetails) with elements such as FormName (each form is given a unique name when created), an instance of TMessageDetails, FormTag and some other bits. I then have an array of TFormDetails with length 6. Each time a form is opened a spare 'slot' in this array is found, a new form created, the TMessageDetails record initialized (or data loaded into it from the DB) and a pointer to this record is given to the form. The form is then opened and it loads all the data from the TMessageDetails record into the respective controls. The pointer is there so that when the controls on the form make changes to the record elements, the original record is edited and I don't end up with a 'local' copy behind the form and out of sync with the original.

For the DB interaction I have four FDQuery components (one per group) on the message form, each pointing to a corresponding table (one per group) in an SQLite DB.

When loading a message I have a procedure that uses FDQuery1 to get a row of data from Table1, and then it copies the data to the Group1 record (field by field) in the respective TMessageDetails record (stored in the TFormDetails array) for that form. The same then happens with FDQuery2, 3, 4 etc...

Saving is basically the same but obviously in reverse.

The reason for the four FDQuery's is so that I can keep each dataset open after loading, which then gives me an open dataset to update and post to the DB when saving. The reason for copying to the records is mainly so that I can reference the respective fields elsewhere in the code with shorter names, and also when the VCL control tries to change a field in the dataset the changes don't 'stick' (the data I try and save back to the DB is the same as what I loaded), whereas in a record they do. The reason for breaking the records down into groups is there are places where the data in one of the groups may need to be copied to somewhere else, but not the whole message. It was also more natural to me to use records than datasets.

So my question is...

Is my use of; the record structures, a global TFormsDetails array, pointers, four FDQuery's per form (so up 6 forms open means up to 24 datasets open), and copying between records and datasets on save/load; a good way to implement what I am trying to achieve?

OR

Is there a way I can replace the records with datasets (making copying from FDQuery easier/shorter surely?) but still store them in an a global 'message form' array so I can keep track of them. Should I also try and reduce the instances of FDQuery and number of potential open datasets by having say one FDQuery component, and re-using it to load the tables into other global datasets etc?

My current implementation works just fine and there is no noticeable lag/hang when saving/loading, I just can't find much info on what is considered best practice for my needs (namely having multiple instances of the same form open - other example refer to ShowModal and only having one dataset to worry about) so I'm not sure if I'm leaving myself open to problems like memory leaks (I understand the 'dangers' of using pointers), performance issues or just general bad practice.

Currently using RAD 10.3 and the latest version of SQLite.

Answer 1

I don't know if what I'll say is the "best" practice but it is how I would do it.

The form would have his own TFDQuery using a global FDConnection. When several instance of the form are created, you have a FDQuery for each instance.

You add an 'Execute' method in the form. That method will create the FDQuery, get all data from the dataset with one or more queries, populate the form and show the form. Execute method receive argument (or a form's property) such as primary key to be able to get the data. If the argument is empty, then it is for a new record.

If the form has to update the grid on the fly, then an event will be used. The main form (containing the grid) will install an event handler and update the grid accordingly to the data given by the form.

When the form is done, it will use the primary key to store data back to the database.