Enterprise Library Data Access Block Transaction Management Best Practice

Question

What is the best practice for processing a batched series of CRUD operations in a single transaction with the Enterprise Library Data Access Block that it won't be esclated to a distributed transaction?

Edit Full Source:

public void BatchInsertEvents(IList<EventItem> events)
{
    _dataAccessBase = new DataAccessBase("[dbo].[EventInsert]");
    int count = 0;

    try
    {
        using (var scope = 
                    new TransactionScope(TransactionScopeOption.RequiresNew))
        {
            foreach (var eventItem in events)
            {
                _dataAccessBase.ClearParameters();

                _dataAccessBase.AddInParameter("@time", 
                                            DbType.String, eventItem.Time);
                ...more params

                _dataAccessBase.ExecuteNonQuery();
                count++;
            }

            scope.Complete();
        }
}

My DataAccessBase is just a wrapper class for the Database object

public class DataAccessBase
{
    private readonly DbCommand _command;
    private readonly Database _database;

    public DataAccessBase(string storedProcName) : this(null, storedProcName)
    {
    }

    public DataAccessBase(string connectionString, string storedProcName)
    {
        _database = string.IsNullOrEmpty(connectionString) ?
                     DatabaseFactory.CreateDatabase() : 
                     DatabaseFactory.CreateDatabase(connectionString);
        _command = _database.GetStoredProcCommand(storedProcName);
    }

    public void AddInParameter<T>(string parameterName, 
                                            DbType parameterType, T value)
    {
        _database.AddInParameter(_command, 
                                parameterName, parameterType, value);
    }


    public void AddOutParameter<T>(string parameterName, 
                                DbType parameterType, int parameterLength)
    {
        _database.AddOutParameter(_command, 
                                parameterName, parameterType, parameterLength);
    }

    public void ClearParameters()
    {
        _command.Parameters.Clear();
    }

    public void ExecuteNonQuery()
    {
        _database.ExecuteNonQuery(_command);
    }
}

Answer 1

I am guessing that what is happening is that, you are using EntLib with connection pooloing.

What happens then is that you get a transaction that is spread over more than one connection. This then gets escalated to a distributed transaction.

One thing Entlib is very good at is closing connections. You need to write your code in a way that it will reuse a single connection.

Some of this is explained in this link: http://msdn.microsoft.com/en-us/library/cc511672.aspx

If you still have problems, post your code within the for each loop.

EDIT

Try moving the line:

_dataAccessBase = new DataAccessBase("[dbo].[EventInsert]");

inside the transaction scope.

EDIT 2

Can you also move the declaration of _dataAccessBase inside the transaction scope

DataAccessBase _dataAccessBase = new DataAccessBase("[dbo].[EventInsert]");

This is just to make sure that the connection is not used outside the transaction scope.

Answer 2

Edit: After posting this I realized I nearly fully duplicated the advice already given by Shiraz Bhaiji. Please ignore.

Second attempt:

Please provide the code for DatabaseFactory.CreateDatabase(). Do you have an additional TransactionScope in there by chance? If so, and you use the same TransactionScopeOption.RequiresNew, you could easily escalate to DTC.