How to use transaction with typed dataset in C#?

Question

Hi How can I use transaction with typed dataset? Let's say I've a record table and record detail table and I've to save one in the record table and all the details in the record detail table. How can I use ? I found transaction can be used with untyped dataset but I don't see it with typed dataset. Can someone tell me how I should do?

Kevin

Answer 1

There's a nice article (and code) at CodeProject on how to extend the typed dataset to enable transactions without having them get promoted to a distributed transaction when using the TransactionScope.

Summary: Use the transaction scope with a method added on the partial class to modify the underlying SqlCommand objects to take part in the same transaction.

using (SqlTransaction transaction = connection.BeginTransaction())
{
       // These methods will update all relevant command objects’ transaction property
       adapter1.EnlistTransaction(transaction);
       adapter2.EnlistTransaction(transaction);

       adapter1.Update(table1);
       adapter2.Update(table2);

       transaction.Commit();
}

Code example for adapter from reference:

public partial class [TableAdapterName]
{
    public void EnlistTransaction(System.Data.SqlClient.SqlTransaction transaction)
    {
        System.Data.SqlClient.SqlTransaction _transaction;

        if (this._transaction != null)
        {
            throw new System.InvalidOperationException
        ("This adapter has already been enlisted in a transaction");
        }
        else
        {
            this._transaction = transaction;
            Adapter.UpdateCommand.Transaction = _transaction;
            Adapter.InsertCommand.Transaction = _transaction;
            Adapter.DeleteCommand.Transaction = _transaction;
        }
    }
}

Answer 2

You can use TransactionScope and call both the updates within the scope of the TransactionScope object.

Please see the example provided in this link. TransactionScope.

An more close example is provided here.