question

AndyNakamura-7969 avatar image
0 Votes"
AndyNakamura-7969 asked karenpayneoregon edited

Best way to copy a master/detail record

I'm just wondering what is the best way to copy a master/detail record.
Like an Order/Order Items record.

Only way I can think to do it at the moment is
1. Copy the Master data to a new record. this will generate a new id field (Auto generated primary key)
2. Query the db for max value of id in the Master.
3. Copy the Details data 1 record at a time with the foreign key set to the Master id (The details record also have Auto generated primary key).

There is always the danger that another record gets inserted into the Master between copying the data and querying for the new auto generated id.
This is unlikely as I could open the db in exclusive mode. The database file is an Access .accdb

Just wondering if .net has a better way?





dotnet-csharpdotnet-visual-basic
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered karenpayneoregon edited

I took an existing code sample using SQL-Server and added code to clone the current master row and child records. Now I realize you are using MS-Access and I used SQL-Server but at the level for getting the current master row and child rows is not relevant in regards to getting data although the backend code is which can be adapted to MS-Access by changing the data provider from SqlClient to OleDb, change the connection string. The only thing that is SQL-Server specific is Operations.GenerateInvoice method.

Edit To protect against database issues you would need to alter my code to add orders by using a AddOrder method that would accept all data rows and loop, perform the inserts wrapped in a try/catch with a transaction, not much of a change and only needed when there is a chance of multiple users adding rows at the exact same time.


Full source, code that does the cloning.



Code is in the lightbulb button.
100783-masterdetails1.png




masterdetails1.png (42.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.