0

I'm executing some statements like this using Dapper (the connection is an NpgsqlConnection):

using var transaction = connection.BeginTransaction();
connection.Execute("INSERT INTO table1(id) VALUES (0);", transaction);
connection.Execute("invalid sql command", transaction);
transaction.Commit();

My understanding, based on various Dapper tutorials, was that all the statements should be committed at once when .Commit() is called, but I'm getting an NpgsqlException immediately when the second statement is executed. When I look at the database, the table is empty so the first statement is not committed either. Does Dapper automatically commit and rollback transactions for you and is it therefore unnecessary to manually call .Commit() and Rollback() when using Dapper?

Amit Joshi
  • 15,448
  • 21
  • 77
  • 141
B Yellow
  • 129
  • 6
  • Commit should be explicit, rollback is automatic when not committed – Hans Kesting Feb 17 '22 at 18:54
  • Is there an example of an error that would require manual rollback; i.e. during the commit instead of prior? – B Yellow Feb 17 '22 at 18:58
  • A scenario: you execute a bunch of statements, then you find you shouldn't have done that and roll back – Hans Kesting Feb 17 '22 at 19:01
  • 1
    Commands are executed immediately but the result is only stored permanently after a commit - that's why you get the error immediately – Hans Kesting Feb 17 '22 at 19:03
  • This is ADO.Net, not Dapper, doing this. When you have a `using` on the transaction (as you rightly should) it will always attempt a rollback if not yet committed. See https://learn.microsoft.com/en-us/dotnet/api/system.data.common.dbtransaction.dispose?view=netcore-1.1. As long as you have a `using` you are safe with all SQL ADO providers commonly used – Charlieface Feb 17 '22 at 21:39
  • 1
    Does this answer your question? [Will a using statement rollback a database transaction if an error occurs?](https://stackoverflow.com/questions/641660/will-a-using-statement-rollback-a-database-transaction-if-an-error-occurs) Also https://stackoverflow.com/questions/18388852/is-rollback-automatic-in-a-using-scope-with-c-sharp-sql-server-calls – Charlieface Feb 17 '22 at 21:40

0 Answers0