Dapper. IDbConnection and IDbTransaction

Question

How should I use an IDbConnection and an IDbTransaction with Dapper?

At the moment I am using only a IDbConnection. Something as follows:

using (SqlConnection connection = new SqlConnection(connectionString)) 
{
    connection.Execute(@"insert Roles(Name) values (@name)", new { name = "Role" }); 
}

But sometimes I need to send 2 commands? Should I use BeginTransation and EndTransaction?

Answer 1

Yes, if you need two separate commands to be atomic and fail together, then you should use a transaction.

using (new TransactionScope(TransactionScopeOption.Required)) 
{
    connection.Execute(...);
    connection.Execute(...);
}

Or if you want to use BeginTransaction and pass it in, you can also do:

using (var transaction = connection.BeginTransaction()) 
{
    connection.Execute(sql1, params1, transaction);
    connection.Execute(sql2, params2, transaction);
}