Search code examples
databasedelphitransactionsdelphi-7data-aware

Preferable way to write Delphi database apps with transactions & data-aware components

What is the preferable way to write Delphi database applications using transactions and also data-aware components?

I have to write a client app that access InnoDB tables, and do some master-detail kind of things inside transactions. After doing some research on transactions (from general point-of-view), then I humbly make a conclusion that non data-aware components and hand-coded SQL would be the "perfect match" of transactions; But the data-aware components wouldn't be. They don't seem to be made for each other.

I have the real need to use transactions, but on the other hand I could not just throw the data-aware components away because they greatly simplify things.

Could somebody please enlighten me? I have been Googling it, but I have not found any useful answer. Perhaps because my English is not good enough that my keywords are limited.

BTW, I'm using Delphi 7 and currently evaluating UniDAC as the data access library.

Thank you.

EDIT

Example to describe an aspect of my question:

Imagine a form with 2 DBGrids on it. The first grid is MasterGrid and above it are these buttons: Add, Edit & Delete. The second grid is DetailGrid. If the user click Add, then it go like this:

  • Connection.StartTransaction
  • Master.Append then Master.Post then Master.Edit (so the master dataset has the autoincrement primary key, and it is editable now)
  • Show the editing form modally, in which the user fills the master records, and also add some detail records using another form.
  • If the user click OK, the app would do Master.Post and Connection.Commit. If the user click Cancel, then the app would do Connection.Rollback.

I know that transactions should be as short as possible, but you can see above that the transaction is only as short as the speed of the user filling the form.

If I were using non data-aware components, I would make custom insert SQLs based from user input, then execute the SQL between StartTransaction and Commit. So I can achieve very short transaction.

EDIT 2

I thank all of you for your kind participation. I pick the answer from vcldeveloper because it is the closest solution to my current need.

Solution

  • Others mentioned using a combination of DatasetProvider and ClientDataset to have a batch update, but in case of using ADO or UniDAC components, you do not need the extra layer of DatasetProvider + ClientDataset, because both ADO and UniDAC support batch updates.

    For ADO, what you should do is to set LockType of your dataset to ltBatchOptimistic. For UniDAC, you should set CacheUpdate property to True.

    This change makes your dataset to cache all the changes you make on its in-memory recordset, and send them alltogether to database only when you call UpdateBatch method (ADO) or ApplyUpdates method (UniDAC).

    Now what you should do is to let your user insert/edit a record in the master dataset and whatever records he/she wants in the details dataset using whatever data-aware components you like. All the changes would be cached. When your user is done, you can start a new transaction, and first call UpdateBatch (or ApplyUpdate in case of UniDAC) for the master dataset, and then for the details dataset, and if everything goes fine, commit the transaction.

    This will make your transactions short without needing the extra layer of ClientDataset.

    Regards