How to use transaction with typed dataset in C#?

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

---

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;
        }
    }
}

---

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.

---

I don't recommend using transactionscope, since it's difficult for handling code refactor since you can wrap a huge amount of code, this is how I recommend:

This answer it's different because it's adding the transaction for all commands and not only for persistence

Extend your partial Adapter class:

partial class YourTableAdapter
{
    public SqlTransaction Transaction
    {
        set
        {
            if (this.CommandCollection != null)
            {
                for (int i = 0; i < this.CommandCollection.Length; i++)
                {
                    this.CommandCollection[i].Connection = value.Connection;
                    this.CommandCollection[i].Transaction = value;
                }
            }

            this.Connection = value.Connection;
            this._adapter.AplicaTransaction(value);
        }
    }
}

Extension method:

namespace System
{
    public static class DALSqlExtension
    {
        public static void AplicaTransaction(this SqlDataAdapter _adapter, SqlTransaction transaction)
        {
            if (_adapter == null)
            {
                return;
            }
            if (_adapter.InsertCommand != null)
            {
                _adapter.InsertCommand.Transaction = transaction;
                _adapter.InsertCommand.Connection = transaction.Connection;
            }
            if (_adapter.UpdateCommand != null)
            {
                _adapter.UpdateCommand.Transaction = transaction;
                _adapter.UpdateCommand.Connection = transaction.Connection;
            }
            if (_adapter.DeleteCommand != null)
            {
                _adapter.DeleteCommand.Transaction = transaction;
                _adapter.DeleteCommand.Connection = transaction.Connection;
            }
            if (_adapter.SelectCommand != null)
            {
                _adapter.SelectCommand.Transaction = transaction;
                _adapter.SelectCommand.Connection = transaction.Connection;
            }
        }
    }
}