Multiple Dapper Execute() statements inside transaction throws NpgsqlTransaction has completed; it is no longer usable exception

Question

I'm using dapper with npgsql. (.net core 3.1)

Multiple connection.Execute(query, params, transaction) for one connection and inside one transaction throws This NpgsqlTransaction has completed; it is no longer usable. exception when calling commit() at the end.

No exceptions occurs on each sql execute statement.

When I've debugged code noticed that transaction's IsCompleted property set to true after first Execute() call.

So I'm not able to run Execute() multiple times inside one transaction?

using (var connection = new NpgsqlConnection(_connectionString)) {
              connection.Open();
             using (var tr = connection.BeginTransaction()) {
                    foreach (var script in scripts)
                    {
                        try
                        {
                            connection.Execute(script.SqlQuery, script.Params);
                        } 
                        catch (Exception e)
                        {
                            throw; //nothing crashes here
                        }
                    }

                    tr.Commit(); //NpgsqlTransaction has completed; it is no longer usable
                }
}

When the transaction is not used everything works fine, but I need it

---

Upd.

Thanks @Marc Gravell for pointing me to look at the executed sql code.

I've DDL script CREATE TABLE IF NOT EXISTS Name (Column type); END;. The END; operator was not necessary here but it led to completion of the transaction.

Answer 1

Add connection.Open() yourself after creating the connection and before creating the transaction:

using (var connection = new NpgsqlConnection(_connectionString))
{
    connection.Open();
    using (var tr = connection.BeginTransaction())
    {

Normally Dapper will deal with this internally, but it can't if you have multiple operations that must be on the same underlying connection.

---

Also: tell Dapper about the transaction:

connection.Execute(script.SqlQuery, script.Params, transaction: tr);