The System. Transactions infrastructure makes transactional programming simple and efficient throughout the platform by supporting transactions initiated in SQL Server, ADO.NET, MSMQ, and the Microsoft Distributed Transaction Coordinator (MSDTC).
Here the code snippet:
using System.Transactions;
....
using (var transactionScope = new TransactionScope())
{
DoYourDapperWork();
transactionScope.Complete();
}
Note that you need to add reference to System.Transactions
assembly because it is not referenced by default.
I preferred to use a more intuitive approach by getting the transaction directly from the connection:
// This called method will get a connection, and open it if it's not yet open.
using (var connection = GetOpenConnection())
using (var transaction = connection.BeginTransaction())
{
connection.Execute(
"INSERT INTO data(Foo, Bar) values (@Foo, @Bar);", listOf5000Items, transaction);
transaction.Commit();
}
You should be able to use TransactionScope
since Dapper runs just ADO.NET commands.
using (var scope = new TransactionScope())
{
// open connection
// insert
// insert
scope.Complete();
}
Considering all your tables are in single database, I disagree with TransactionScope
solution suggested in some answers here. Refer this answer.
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.
But, with single database this may be useful when you need to include the code in transaction that is not under your control. With single database, it does not need special configurations either.
connection.BeginTransaction
is ADO.NET syntax to implement transaction (in C#, VB.NET etc.) against single database. This does not work across multiple databases.
So, connection.BeginTransaction()
is better way to go.
Even the better way to handle the transaction is to implement UnitOfWork as explained in this answer.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With