Is there some way in Delphi to cache master-detail rows and post both master and detail child rows at the same time

Question

I want to post in memory some child rows, and then conditionally post them, or don't post them to an underlying SQL database, depending on whether or not a parent row is posted, or not posted. I don't need a full ORM, but maybe just this:

1. User clicks Add doctor. Add doctor dialog box opens.
2. Before clicking Ok on Add doctor, within the Add doctor dialog, the user adds one or more patients which persist in memory only.
3. User clicks Ok in Add doctor window. Now all the patients are stored, plus the new doctor.
4. If user clicked Cancel on the doctor window, all the doctor and patient info is discarded.

Try if you like, mentally, to imagine how you might do the above using delphi data aware controls, and TADOQuery or other ADO objects. If there is a non-ADO-specific way to do this, I'm interested in that too, I'm just throwing ADO out there because I happen to be using MS-SQL Server and ADO in my current applications.

So at a previous employers where I worked for a short time, they had a class called TMasterDetail that was specifically written to add the above to ADO recordsets. It worked sometimes, and other times it failed in some really interesting and difficult to fix ways.

Is there anything built into the VCL, or any third party component that has a robust way of doing this technique? If not, is what I'm talking about above requiring an ORM? I thought ORMs were considered "bad" by lots of people, but the above is a pretty natural UI pattern that might occur in a million applications. If I was using a non-ADO non-Delphi-db-dataset style of working, the above wouldn't be a problem in almost any persistence layer I might write, and yet when databases with primary keys that use identity values to link the master and detail rows get into the picture, things get complicated.

Update: Transactions are hardly ideal in this case. (Commit/Rollback is too coarse a mechanism for my purposes.)

Elasticize

Two options that come to my mind immediately: 1) use a transaction to rollback if they cancel, or 2) use a nested client dataset with a provider. – Immortal

Wondering why transactions won't work? – Isacco

+1 good question, I've always resorted to rolling my own but this involves a lot of extra work/specific code – Ostracize

I guess transaction and rollback is a reasonable idea, although it could get really complicated. – Elasticize

The transaction and rollback approach is actually pretty straight forward as long as you are not doing anything else in the database. The main complication I see is, the key value assigned to the “doctor” record must be retrieved from the database before the “patient” records can be added. – Streamer

Some third party components implement cached updates directly in their query components, such as AnyDac. Once the input is acceptable, you ApplyUpdates similar to using a TClientDataSet and TDataSetProvider. The records are abandoned by CancelUpdates; – Streamer

That last approach (Cached updates in the DAC layer) seems the best to me. Rolling your own two layer persistence with TClientDataSet seems like too much work to me. – Elasticize

I do something very similar (with doctors/hospitals). I use two CDS. In the OnInsert of the MD CDS, I create an empty row in the Hosp CDS as well (they're required to be on-staff at one). The dialog allows adding multiple hosp. rows, but doesn't populate any of the MD IDs. In the OnPost event, I do the update of the underlying MD table (fetching the new ID), update the rows in the hosp CDS, and insert those as well. In the dialog, the Cancel button simply discards the rows in both the MD and hosp. CDS. It's not quite auto-master-child, but it works, and it's all in a datamodule. :-) – Levo

Answer 1

Your asking two separate questions:

1. How do I cache updates?
2. How can I commit updates to related tables at the same time.

Cached updates can be accomplished a number of different ways. Which one is best depends on your specific situation:

ADO Batch Updates

Since you've already stated that you're using ADO to access the data this is a reasonable option. You simply need to set the LockType to ltBatchOptimistic and CursorType to either ctKeySet or ctStatic before opening the dataset. Then call TADOCustomDataset.UpdateBatch when you're ready to commit.

Note: The underlying OLEDB provider must support batch updates to take advantage of this. The provider for SQL Server fully supports this.

I know of no other way to enforce the master/detail relationship when persisting the data than to call UpdateBatch sequentially on both datasets.

Parent.UpdateBatch;
Child.UpdateBatch;

Client Datasets

Data caching is one of the primary reasons for TClientDataset's existence and synchronizing a master/detail relationship isn't difficult at all.

To accomplish this you define the master/detail relationship on two dataset components as usual (in your case ADOQuery or ADOTable). Then create a single provider and connect it to the master dataset. Connect a single TClientDataset to the provider and you're done. TClientDatset interprets the detail dataset as a nested dataset field, which can be accessed and bound to data aware controls just like any other dataset.

Once this is in place you simply call TClientDataset.ApplyUpdates and the client dataset will take care of ordering the updates for the master/detail data correctly.

ORMs

There is a lot that can be said about ORMs. Too much to fit into an answer on StackOverflow so I'll try to be brief.

ORMs have gotten a bad rap lately. Some pundits have gone so far as to label them an anti-pattern. Personally I think this is a bit unfair. Object-relational mapping is an incredibly difficult problem to solve correctly. ORMs attempt to help by abstracting away a lot of the complexity involved in transferring data between a relational table and an instance of an object. But like with everything else in software development there are no silver bullets and ORMs are no exception.

For a simple data entry application without a lot of business rules an ORM is probably overkill. But as an application becomes more and more complex an ORM starts to look more appealing.

In most cases you'll want to use a third party ORM rather than rolling your own. Writing a custom ORM that perfectly fits your requirements sounds like a good idea and its easy to get started with simple mappings but you'll soon start running into issues like parent/child relationships, inheritance, caching and cache invalidation (trust me I know this from experience). Third party ORMs have already encountered these issues and spent an enormous amount of resources to solve them.

With many ORMs you trade code complexity for configuration complexity. Most of them are actively working to reduce the boilerplate configuration by turning to conventions and policies. If you name all your primary keys Id rather than having to map each table's Id column to a corresponding Id property for each class you simply tell the ORM about this convention and it assumes all tables and classes its aware of follow the convention. You only have to override the convention for specific cases where it doesn't apply. I'm not familiar with all of the ORMs for Delphi so I can't say which support this and which don't.

In any case you'll want to design your application architecture so you can push off the decision of which ORM framework (or for that matter any framework) to use as long as possible.

Geometrid

set the LockType and CursorType appropriately to which values? – Mauney