Yes you can. However, it depends on the internals of the System.Data.SqlClient assembly and so I would not recommend it. If it is truly required I would reassess if CLR is actually the correct way to do what you are trying to do.
The SqlTransaction is actually only a wrapper class around the SqlInternalTransaction class. The SqlInternalTransaction class already tracks the state of the transaction for you (if it didn't then how would the SqlClient know whether to rollback or not?)
You will need to use reflection to obtain the instance of the SqlInternalTransaction which again I would not recommend doing in a CLR procedure as this can be pretty costly in terms of performance - especially if it gets called many times.
That being said, here is how you do it.
public enum TransactionState
{
Pending = 0,
Active = 1,
Aborted = 2,
Committed = 3,
Unknown = 4,
}
public static class SqlExtensions
{
public static TransactionState GetXactState(this SqlTransaction transaction)
{
BindingFlags bindingFlags = BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance;
PropertyInfo property = transaction.GetType().GetProperties(bindingFlags).FirstOrDefault(x => x.Name.Equals("InternalTransaction", StringComparison.OrdinalIgnoreCase);
if (property == null)
return TransactionState.Unknown;
object internalTransaction = property.GetValue(transaction);
FieldInfo transactionState = property.PropertyType.GetField("_transactionState", bindingFlags);
if (property == null)
return TransactionState.Unknown;
return (TransactionState)Enum.Parse(typeof(TransactionState), Convert.ToString(transactionState.GetValue(internalTransaction)), true);
}
}
class Program
{
static void Main(string[] args)
{
SqlTransaction x = null;
// use transaction etc.. here.
TransactionState state = x.GetXactState();
}
}