C# System.InvalidOperationException: The current TransactionScope is already complete

Question

I have encounter this error while executing my source code. But it seems not much information I could get on the internet. Thus, I am hoping may get some clue from the professionals here.

Source Code

public List<string> GetData (List<long> Id)
{
    List<string> data;
    string sql = "select * from tblSample with(nolock) where SampleId in @sampleId";
    Dapper.DynamicParameters param = new Dapper.DynamicParameters();
    param.Add("@sampleId", Id);

    try
    {
        data = this.queryrunner.QueryList(sql, param);
    }
    catch (Exception ex)
    {
        logger.Error(ex.Message, ex);
    }

    return data;
}

QueryRunner class method

    private void OpenConnection()
    {
        if (this.conn.State != ConnectionState.Open)
        {
            this.conn.Open();
        }
    }

    public List<t> QueryList(String sql, DynamicParameters param)
    {
        List<t> t;

        try
        {
            OpenConnection();
            t = this.conn.Query<t>(sql, param).ToList();
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            logger.Debug(ex.Message, ex);
            LogSqlErrorException(this.conn.Database, sql, param);
            throw;
        }
        finally
        {
            CloseConnection();
        }

        return t;
    }

Here is part of the error log:-

System.InvalidOperationException: The current TransactionScope is already complete.
at System.Transactions.Transaction.get_Current()
at System.Data.ProviderBase.DbConnectionPool.GetFromTransactedPool(Transaction& transaction)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry)
at System.Data.SqlClient.SqlConnection.Open()

most likely you're trying to use transaction after it was closed, but without piece of related code House MD would fail to diagnose exception — Traveler
Exception trow when calling this.queryrunner.QueryList(sql, param); > OpenConnection(); > this.conn.Open(); — JackyLoo

TheGeneral TheGeneral · Accepted Answer · 2018-02-05T04:10:13

This line OpenConnection is a bit of a smell, i'm guessing this is where your problem is

try
{
    OpenConnection();
    ...

It would make more sense

try
{
    //OpenConnection();
    conn.Open();
    queryrunner.QueryList(sql, param)
}
finally
{
    //CloseConnection();
    conn.Close();
}

This way you are forcing yourself to control the connection life cycle and not trying to be tricky and second guess it within another context

Its actually better if you put it a using statement, however i'm not sure of the framework you are using, and/or the syntax

C# System.InvalidOperationException: The current TransactionScope is already complete

1 Answers