SQL Server transaction support

[mikebeaton]

The idea of Mighty transaction support is that you can optionally pass a DbConnection into all Mighty methods, and thus that you can get Mighty methods to automatically work with any transactions which are open on the connection.

Unfortunately SQL Server doesn't work like that! (All other ADO.Net SQL drivers which Mighty supports do.) In SQL Server you have to manually join a DbCommand to a transaction even if the command is operating on a connection which has a transaction open.

There is also no clean, official way to automatically join a DbCommand to the open transaction on a connection.

Fortunately, there is a slightly 'hacky' way to do it, which works and seems to have remained stable for a while, although it is NOT officially supported. As this makes transactions very much easier to work with in SQL Server in Mighty, I have added this code to Mighty and enabled it by default.

• The new flag is called SqlServerAutoEnlistCommandsToTransactions
• It is available in v4 onwards
• If you leave the flag on (default), then to use Mighty commands within a transaction you just have to pass the DbConnection with the open transaction to the Mighty command
• Without this support, you have to use Mighty.CreateCommand, then join the command to the transaction yourself, and then use one of the Mighty variants which takes a DbCommand to execute it; i.e. it's possible, but much more fiddly, without this support
• The flag is not required and has no effect on supported databases other than SQL Server, which already work in the non-fiddly way by default, with no custom code required within Mighty

[niwrA]

Here is another hacky way to do it probably - you'll have to add the check if it is indeed a SqlConnection first though of course:

private static readonly PropertyInfo ConnectionInfo = typeof(SqlConnection).GetProperty("InnerConnection", BindingFlags.NonPublic | BindingFlags.Instance);
private static SqlTransaction GetTransaction(IDbConnection conn) {
var internalConn = ConnectionInfo.GetValue(conn, null);
var currentTransactionProperty = internalConn.GetType().GetProperty("CurrentTransaction", BindingFlags.NonPublic | BindingFlags.Instance);
var currentTransaction = currentTransactionProperty.GetValue(internalConn, null);
var realTransactionProperty = currentTransaction.GetType().GetProperty("Parent", BindingFlags.NonPublic | BindingFlags.Instance);
var realTransaction = realTransactionProperty.GetValue(currentTransaction, null);
return (SqlTransaction) realTransaction;
}

source: https://stackoverflow.com/questions/417024/can-i-get-a-reference-to-a-pending-transaction-from-a-sqlconnection-object

Alternatively you could always use a transaction, with the setting to join an existing transaction if available.

[mikebeaton]

@niwrA :

Bit confused by the first part of your comments! You seem to have just retyped the same solution from the same source that I linked to in the original post?!

You're right that it does need to be a SqlConnection (not just any DbConnection)... but it always will be because this code is only ever activated against SQL Server.

Possibly what you suggest last might work, except that in the case of Mighty (at least) it is much better if user code outside of Mighty manages any transactions, because Mighty (following Massive) uses delayed execution a lot. So any transactions Mighty opens, it has to close, but only later when the user has enumerated through any enumerables. That probably isn't a good way to design things, because that would be complex to get right (given this architecture) and likely leave dangling transactions without the user understanding why.