Transactions

##Overview

All SQL operations performed by Dapper.SimpleSave occur within a transaction. If you do not pass in a transaction, and you are not operating within a TransactionScope, Dapper.SimpleSave will create a transaction to encapsulate all the operations required to create, update, or delete the object(s) you supply to one of its extension methods.

Due to the use of transactions, extension methods behave atomically regardless of how many SQL INSERTs, UPDATEs, and DELETEs are performed within the scope of an individual call: they'll either all succeed, or they'll all fail.

If a transaction fails for any reason an exception will be thrown. Often this will be a SqlException, but there are other things that can go wrong. For example, Dapper.SimpleSave does perform some basic validation checks on your entities, so don't assume you'll only see SqlExceptions out of these methods.

If you didn't supply a transaction, and are not operating within a TransactionScope, any error or exception in executing one of Dapper.SimpleSave's extension methods will cause the transaction SimpleSave created to rollback. Exceptions are then rethrown so that you can handle or report them in your own code.

If you did supply a transaction, or call an extension method within the bounds of a TransactionScope you are effectively taking responsibility for managing the transaction. In other words both committing the transaction and, in the event of an error, rolling it back must be done in your code. Dapper.SimpleSave will propagate any exceptions up to your code without taking any action beyond logging.

##Using external transactions

Dapper.SimpleSave supports external transactions in two ways:

1. You can supply an IDbTransaction ^{(more info)} to any extension method you call.

2. You can use TransactionScope ^{(more info)} to manage transactions (recommended). In this case Dapper.SimpleSave will detect that a transaction is in progress and will behave exactly as if you'd passed an IDbTransaction into the extension method you called.

##Considerations for debugging

If you are using the Visual Studio debugger to step through your code you may find yourself using SQL Server Management Studio to look at what's in the database whilst you're doing it, probably by executing queries such as

SELECT * FROM myschema.MyTable WHERE MyColumn = someValue;

Under these circumstances you may be surprised to find that data you thought had been written to the database doesn't appear in the results.

This is due to transaction isolation levels. By default SQL Server sets each connection to READ COMMITTED transaction isoluation. What this means is that you can only read changes that have been committed to the database. For changes made within a transaction this doesn't happen until the transaction itself is committed.

Similar, albeit more stringent, restrictions apply to the REPEATABLE READ, SNAPSHOT, and SERIALIZABLE transaction isolation levels. See https://msdn.microsoft.com/en-GB/library/ms173763.aspx for more information.

If in the meantime you want to read rows that have been written to the database but not yet committed you'll need to switch to the READ UNCOMMITTED level.

This is easily achieved in SQL Server Management Studio by executing the following command:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Bear in mind that this is a connection level setting so you'll need to execute it for each new query window you open, and you'll need to execute it again if your query windows are disconnected from and reconnect to the database.

Having done this you should now be able to see data written by Dapper.SimpleSave even in the middle of a transaction.