4

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?

Kirk Woll
  • 76,112
  • 22
  • 180
  • 195
Miguel Moura
  • 36,732
  • 85
  • 259
  • 481
  • What exactly is your question? What do you mean, "how should you use" them? – Kirk Woll Mar 23 '12 at 22:35
  • 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? – Miguel Moura Mar 23 '12 at 22:49

1 Answers1

14

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);
}
Kirk Woll
  • 76,112
  • 22
  • 180
  • 195
  • But in Execute method there is an option for transaction. When should I use it? In that case I suppose it is only for one command ... – Miguel Moura Mar 23 '12 at 23:04
  • @Shapper, I added an example using that syntax. You probably don't need to use it if you're only executing one statement. If you're executing SQL that contains multiple statements, however, then you should use it if you need the atomicity. – Kirk Woll Mar 23 '12 at 23:08
  • yes that I understand now. But in Dapper Execute method you also one argument which is a transaction ... It is the third argument: 'int Execute(this IDbConnection cnn, string sql, dynamic param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null).' So it does the same as what you posted but only for that one command, correct? So why use a Transaction when you are only executing one command? – Miguel Moura Mar 23 '12 at 23:16
  • @Shapper, there are two possible scenarios when you want to pass in a transaction object. 1) You already have an existing transaction and you want to leverage it in the dapper call. Or 2) You have one single SQL string that performs multiple statements (separated by a semicolon), all of which you want to happen under a single transaction. – Kirk Woll Mar 23 '12 at 23:21