Using SqlCommandBuilder with Transactions

[Andreas Nimptsch]

Hi,

i have a DataSet with 3 Tables. For each table i have
defined a SqlDataAdapter Object. The SqlCommands for
Deleting, Inserting und Updating were created by the
SqlCommandBuider Object. I have wrote the following Code,
to write the Content DataSet.

public void WriteData()
{
SqlCommandBuilder buildCmd1;
SqlCommandBuilder buildCmd2;
SqlCommandBuilder buildCmd3;

buildCmd1 = new SqlCommandBuilder(this.adapter1);
buildCmd2 = new SqlCommandBuilder(this.adapter2);
buildCmd3 = new SqlCommandBuilder(this.adapter3);

this.sqlConn.Open();

this.adapter1.Update(this.dataSet, table1);
this.adapter2.Update(this.dataSet, table2);
this.adapter3.Update(this.dataSet, table3);

this.sqlConn.Close();
}

Now i want to encapsulate the three Updates in one
Transaction? How can i assign the dynamic generated
SqlCommand Objects to a SqlTransaction Object?

Thanks

Andreas

 

[Miha Markic]

Hi,

You might create a method that assigns a transaction to an adapter
AssignTransaction(adapter1, yourTransaction);
AssignTransaction(adapter2, yourTransaction);
AssignTransaction(adapter3, yourTransaction);

private void AssignTransaction(SqlDataAdapter adapter, SqlTransaction
transaction)
{
adapter.InsertCommand.Transaction = transaction;
adapter.UpdateCommand.Transaction = transaction;
adapter.DeleteCommand.Transaction = transaction;
}

 

[thomas_w_brown]

-----Original Message-----
Hi,

i have a DataSet with 3 Tables. For each table i have
defined a SqlDataAdapter Object. The SqlCommands for
Deleting, Inserting und Updating were created by the
SqlCommandBuider Object. I have wrote the following Code,
to write the Content DataSet.

public void WriteData()
{
SqlCommandBuilder buildCmd1;
SqlCommandBuilder buildCmd2;
SqlCommandBuilder buildCmd3;

buildCmd1 = new SqlCommandBuilder(this.adapter1);
buildCmd2 = new SqlCommandBuilder(this.adapter2);
buildCmd3 = new SqlCommandBuilder(this.adapter3);

this.sqlConn.Open();

this.adapter1.Update(this.dataSet, table1);
this.adapter2.Update(this.dataSet, table2);
this.adapter3.Update(this.dataSet, table3);

this.sqlConn.Close();
}

Now i want to encapsulate the three Updates in one
Transaction? How can i assign the dynamic generated
SqlCommand Objects to a SqlTransaction Object?

Thanks

Andreas
.

Assign the transaction to the SelectCommand of each
adapter and then call RefreshSchema() on each command
builder. I guess you may not need this latter step if you
create the command builders *after* assigning the
transaction to the SelectCommand.

Here is the basic approach I use for general updates...

sqlConn.Open();
SqlTransaction tx = sqlConn.BeginTransaction();
daAdapter1.SelectCommand.Transaction = tx;
daAdapter2.SelectCommand.Transaction = tx;
...

cbBuilder1.RefreshSchema();
cbBuilder2.RefreshSchema();
...

try
{
dataSet.Update();
tx.Commit();
}
catch (Exception)
{
tx.Rollback();
}

There is no general need to invoke Update individually on
the adapters, you can update the entire DataSet. However,
you may well have cases where you have to handle deleted
rows separately and before modified/added rows and make
sure you operate in a specific order on the tables.

-- TB