t3chb0t t3chb0t - 1 year ago 97
C# Question

Which transaction is better with Dapper: BEGIN TRAN or TransactionScope?

I've just started using Dapper and I was wondering which pattern for transactions would be better.

When I write SQL I prefer to use the transaction already in the script with:

BEGIN TRAN

-- insert, update etc.

COMMIT


because it's easier to test it but there is also the question transaction with dapper dot net
where .net transactions are used so now I'm not sure which one I should actually use.

Does either method have any dis/advantages over the other?

Answer Source

Actually, this has nothing to do with Dapper. Refer this answer.

TransactionScope or connection.BeginTransaction or "Transaction in stored procedure" decision is outside the scope of Dapper. Dapper simply exposes few extension methods on DBConnection object of ADO.NET those map the output of queries to object. Rest is up to you.

  1. TransactionScope is generally used for distributed transactions; transaction spanning different databases may be on different system. This needs some configurations on operating system and SQL Server without which this will not work. This is not recommended if all your queries are against single instance of database.

  2. connection.BeginTransaction is ADO.NET syntax to implement transaction (in C#, VB.NET etc.) against single database. This does not work across multiple databases.

  3. "Transaction in stored procedure" is implemented against single database in Stored Procedure instead of doing this in application code.

Nested transactions or combination of above types is another topic for discussion; try searching net for this.

My personal opinion is to use UnitOfWork to handle transactions in better way. I have already mentioned the details in the link above. It uses connection.BeginTransaction internally.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download