Updating Parent and Child at same time

  • Thread starter Thread starter SouthSpawn
  • Start date Start date
S

SouthSpawn

Hello Everyone,

I have a quick question.

Let's say you have the following situation. You have a parent table and a
child table. If I wanted to update both parent and child at the same time.
What would be the best way to do this. The reason why I am asking. Doing
two different inserts might be a bad idea. The reason being, what is the
first insert works, and the second one fail. If you have any suggestions
that would be great.

Thanks,
Mark
 
Basically, you use Transaction in two ways.

1. Apply transaction in you ADO.NET code to wrap up the two insertions, such
as

SqlConnectioon cn=new SqlConnection(cnString);
SqlTransaction tran=cn.BeginTransaction();

//Set up DataAdapter and insertion/Update/ command and make the command part
of transaction
//That is, cmd.Transaction=tran

try
{
//Insert /update parent table
//Insert/update child table

//Commit transaction
tram.Commit();
}
catch
(
//Roll back transaction
}

2. You can do the Transaction inside SQL Server, say, using stored procedure
by passing all data (a parent row and all child rows) as input parameters to
the procedure.
 
There is more to this problem than meets the eye.

First the simple answer(s) -
a) You can use ADO.NET transactions.
b) You can use database transactions.
c) Sql Server specific: You can pass in XML update grams directly from the
dataset to the stored procedure and have this change applied in one shot.
(Explained in my book - Chapter #12)

Now for the complex part --

For a dataset that has multiple parent/child relations, getting the changed
rows, and applying them properly in sequence is a royal pain in the butt.
But hey atleast in ADO.NET it is possible to deal with multi dimensional
data. For more on this - David Sceppa's ADO.NET book.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik
Please reply to the newsgroups instead of email so everyone can benefit from
your reply.
 
Back
Top