SqlDataAdapter, a SqlCommandBuilder, and a SqlTransaction

[Kal]

I have been looking for the code to combine a SqlDataAdapter, a
SqlCommandBuilder, and a SqlTransaction. Lots of references for each, but
not one that shows it all together. I have been using the following:
SqlConnection connection = new SqlConnection
(configVals.ConnectionString);

string selectString = "SELECT * FROM tbl where stID = '" + aV.SiteNo +
"'";
SqlDataAdapter sda = new SqlDataAdapter(selectString, connection);
SqlCommandBuilder cmdBldr = new SqlCommandBuilder(sda);
// Set the MissingSchemaAction property to AddWithKey because Fill
will not cause primary
// key & unique key information to be retrieved unless AddWithKey is
specified.
sda.MissingSchemaAction = MissingSchemaAction.AddWithKey;
sda.Fill(ds,"tbl");
.... some update code
sda.Update(ds, "tbl");
connection.Close();

This has been working well, but now I need to add a second related table
update and want either both updates or neither to happen. THis is the exact
description of a SQLTransaction. That is one connection, one transaction,
two dataadapters and two tables. I will worry about the second update later,
but want to start by putting this code into a transaction. Unfortunately, I
have not found the correct syntax.



Kal

 

[Stephen Muecke]

Kal

VB code
1. Create a transaction (Dim trans as SqlTransaction)
2. Begin the transaction by assigning it to your connection (trans =
myConnection.BeginTransaction())
3. Assign the transaction to each of your commands (myCommand1.Transaction =
trans, myCommand2.Transaction = trans etc). In your case, I think you will
need to apply this to the Insert, Update and Delete commands of both
adaptors (sorry, but I only use stored procedures with ExecuteNonQuery so
I'm not sure)
4. Execute the commands (in your case, using DataAdapter.Update)
5. After updating, commit the transaction (trans.Commit())
Execute the commands in Try/Catch and if an exception is thrown, call
RollBack to undo (trans.Rollback())

Stephen

 

[Kal]

Thanks for your answer, but I was kind of hoping someone who has done it
would reply.