Geek asks and answers

How to use a transaction with a typed dataset in C #?

Hi How can I use a transaction with a typed dataset? Say I have a record table and a detail table, and I have to save it in the record table and all the details in the detail table. How can i use? I found that a transaction can be used with an untyped dataset, but I cannot see it with a typed dataset. Can someone tell me how should I do this?

Kevin

+3
source share
3 answers

There's a good article (and code) in CodeProject on how to extend a typed dataset to include transactions without moving them to a distributed transaction when using TransactionScope.

Free information. Use the transaction pane with the method added to the partial class to modify the underlying SqlCommand objects to participate 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();
}

Sample code for the adapter from the link:

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;
        }
    }
}
+5
source

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

See the example provided in this link. The TransactionScope .

Below is a closer example here .

+1
source

, , , :

, ,

:

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

:

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

More articles:


All Articles