SQL Constraints

[PhillD]

I am fairly new to .net development but I have worked with Access and SQL for over 14 years. Hence to say, I should know at least a few things about database development. I just ran into a situation where I had to remove a constraint within the SQL database because VB.net could not process data in the correct order.

It was a "simple" master/detail setup, for practical purposes, I will use the order/order detail example. First you enter the order header and then enter the order lines. In SQL there is a constraint on the Orders table and OrderLines table based on the OrderID field with cascade delete enforced. The OrderID is a hidden "meaningless" key so it will never change.

When I save data in my form to the SQL database, I save the header first, then the lines. I must do this because the constraint will NOT allow any OrderLine records without a matching Order record. This is good DB design!

When creating a DataSet with the Data Wizard or by hand coding, the code used to trigger the save to the DB is.

OrderAdapter.Update(OrderDataSet, "Orders")
OrderLinesAdapter.Update(OrderDataSet, "OrderLines")

Although the .update command is used, this is misleading, it will not just trigger an update, it will trigger an INSERT, UPDATE or DELETE statement based on the action required relevant to the row that it is currently processing.

This is where the problem comes in. Like I said earlier, I must save the Order HEADER first, then the Order LINES. However, when an order is DELETED, you need to save the Order LINES first, then Order Header to prevent a conflict with the SQL constraint.

So to solve the issue, I had to remove the Database constraint which I would argue is bad database design! Unless I was to cycle through all of the datatables and write my own individual INSERT, UPDATE & DELETE logic, I cannot see a way around this.

Anyone have any suggestions?

 

[PhillD]

I need to add (before someone suggests it) even if I remove the cascade delete option, I will still encounter a problem because I will not be able to delete the Order Header records if Order Lines records exist.

 

[PhillD]

This got moved to a SQL forum but it's really more of an ADO.net question.

 

[PhillD]

I came across some code on the net that resolves this issue. With a little more code, you are able to tell the adapter what kinds of updates to perform.

                'Update DB with Added & Modified Master records only.  We will process the deletes later
                'due to constrainst in the database
                MasterAdapter.Update(YourDataSetName.Tables("MasterTable").Select("", "", DataViewRowState.Added Or DataViewRowState.ModifiedCurrent))

                'process all Detail records
                DetailAdapter.Update(YourDataSetName, "ChildTable")

                'Process remainder of Master records.
                MasterAdapter.Update(YourDataSetName, "MasterTable")

The only question I have now is, does calling MasterAdapter.Update(YourDataSetName, "MasterTable") result in the same records being saved again that were processed the first time. If so, this is a little inefficient.

 

[cjard]

When I save data in my form to the SQL database, I save the header first, then the lines. I must do this because the constraint will NOT allow any OrderLine records without a matching Order record. This is good DB design!

Indeed, when a relationship is active in a database that enforces referential integrity, adding data into the database must go in this order:
PARENT
CHILDREN

When deleting, deletes must be performed in this order:
CHILDREN
PARENT

This is logical: it prevents orphans


The problem is that for a dataset that ontains a mix of inserts and deletes in multiple related tables you get the problem that the tableadapter can't single scan a table and perform either an insert or an update depending on what it found as the row state (added/deleted)

I don't know if the TableAdapterManager in .net 3+ solves this problem for you, by first deleting working up related hierarchies it knows about, then inserting/updating down hierarchies it knows about. You can try it and see, or see what MSDN says

If it comes to it there are other ways to skin the cat:

In the child table, call GetChanges(RowState.Deleted) and tell your tableadapter to update() those rows, then update() your parent table, then update() your child. You might have to scan the child yourself to remove rows in a rowstate of deleted, or it may be possible to tell a tableadapter to update(datatable, rowstate) ad have it only work on those rows of a given rowstate (which should alleviate the merging problem) - I don't recall ever checking..

The other option you have is to use a database that will cascade delete the children, and then just update the parent. As the parent row is deleted from the db, the db itself will remove the related child rows as a related operation. You can then either have the relation in the dataset delete the rows in the child (so they are not attempted to be sent to the db) or you can cause the teableadapter to only update() the added/modified rows

Although the .update command is used, this is misleading, it will not just trigger an update, it will trigger an INSERT, UPDATE or DELETE statement based on the action required relevant to the row that it is currently processing.

I too, always took issue with Microsoft's decision to call it Update() - it's even fairly meaningless in the context - "update a datatable" is such a meaningless instruction. Calling it Persist(), Save() or Upload() would have been much better for me

 

[cjard]

The only question I have now is, does calling MasterAdapter.Update(YourDataSetName, "MasterTable") result in the same records being saved again that were processed the first time. If so, this is a little inefficient.

No. After saving, the rowstate is updated to Unchanged and thus the row participates no further

 

[PhillD]

Well then that is excellent, I have the best of both worlds. I have re-enabled the constraint in the database and have coded the procedure into the form.

Finally, me and you actually agree on something.

 

[cjard]

Finally, me and you actually agree on something.

Veritably, my Holy Grail..