Ido Samuelson's blog

sqlDataAdapter1.Update(ds, "Parent");

sqlDataAdapter2.Update(ds, "Child");

or

sqlDataAdapter1.Update(ds);

sqlDataAdapter2.Update(ds);

I admit, maybe I have failed to understand your question. I have tested this code with a small test program and it works.

If some error occures while updating the parent table the whole transaction will be rollbacked. The same will happen with the child table.

So I cannot see any potential flaw with this code.

Can you ellaborate?

Hi,

I still don't see the problem. As ids in the DSs weren't specified I have assumed that the ids (Parent.Id & Child.Id) are identities and have been properly declared in the typed DS schema (with their AutoIncrementSeed & AutoIncrementStep), otherwise you wouldn't use such a syntax. Off course you still have to update the parent's table first and the child's table second. The child's records refer to the first record "Parent A" of the parent's table.

Hi,

very quick code for proof of concept:

(make sure please that the typed ds's schema contains the AutoIncrementSeed & AutoIncrementStep definitions)

TDS ds = new TDS();

ds.t1.Addt1Row("t1r1");

ds.t1.Addt1Row("t1r2");

ds.t2.Addt2Row(ds.t1[0], "t2r1");

ds.t2.Addt2Row(ds.t1[0], "t2r2");

try

{

sqlConnection1.Open();

SqlTransaction tran = sqlConnection1.BeginTransaction(IsolationLevel.ReadCommitted);

sqlDataAdapter1.InsertCommand.Transaction = tran;

sqlDataAdapter2.InsertCommand.Transaction = tran;

try

{

 sqlDataAdapter1.Update(ds, "t1");

 sqlDataAdapter2.Update(ds, "t2");

 tran.Commit();

}

catch

{

 tran.Rollback();

}

}

catch(Exception ex)

{

MessageBox.Show(ex.Message);

}

finally

{

sqlConnection1.Close();

}

1. The insert command is generated automatically while defining the SqlDataAdapter (I have used the simplest way and generated all the commands after specifying the select command [e.g. select * from t1]). As I have writtern I was trying to provide the quickest way of proof of concept.

Here is a sample from VS.NET generated code:

INSERT INTO t1(data) VALUES (@data)

2. In DS you will have.

t1-> 1, t1r1

    2, t1r2

t2 ->

    1, 1, t2r1

    2, 1, t2r2

The same will be the result in DB!

The next result will increment the identities in DB!

3. Where do you see the problem? All the referential integrities are kept and none are violated. If you applying on the existing rows in DB, it's not a problem, because the identities are managed on the DB level, therefore you don't provide them.

I have tested it either.

I will happily send you all the solution and the SQL code if you are willing to test the results by yourself.

1. I sended you T.zip file. It includes the solution and the script.sql which creates all the necessary tables with its' definitions.

2. You are repeating by telling that I have a problem in my solution but you never have pinpointed it. I really don't see any problem here. As I am the only one who participates in the Quiz :), may be you will post what is exactly the problem in your opinion that I could address the issue.

Do you get off on these quizes?

.. keep doing it! :D