3

I am porting a set of T-SQL stored procedures to C#/CLR. In the T-SQL, we use XACT_STATE() to determine if an error requires rolling back to a savepoint or rolling back the transaction entirely.

I cannot find an equivalent check of the transaction using the System.Data.SqlClient.SqlTransaction or System.Transactions.TransactionScope objects.

Does anyone know how to check the transaction state from within C#/CLR?

2

XACT_STATE() is not something that SqlClient has any special insight on, much like transaction level. So, just query it with SqlCommand.

// If you already have an open connection...
public int GetXactState (SqlConnection connection)
{   
    using (SqlCommand command = new SqlCommand("Select XACT_STATE();", connection)) {
        return (int)command.ExecuteScalar();
    }
}
2

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();
    }
}
  • Hm is this value really accurate? For example if a statement on the server crashed and killed the tran, would this variable be updated in all cases? – usr Jul 9 '15 at 12:41
  • Yes, it does. You would need to check it after each command executed against the transaction but the SqlClient library can handle dead transactions perfectly fine - indeed, there is even a method titled "TransactionEndedByServer" which handles this specific scenario. – Mr.Brownstone Jul 9 '15 at 13:13
  • 1
    I wish ADO.NET was exposing more of the TDS protocol. Command sets and the bulk insert ORDER hint come to mind as similar unexposed facilities. – usr Jul 9 '15 at 13:26
  • This is quite nice. Thanks for sharing! And @usr : agreed, there are quite a few "private" properties and methods that should be public and I cannot figure out what the motivation was to keep them private. Things like the Collation info: LCID, CompareOptions, etc. The SqlBulkCopy class uses those things, so what is the harm in exposing those values to everyone? – Solomon Rutzky Jul 13 '18 at 18:12
  • @SolomonRutzky you are right. It feels to me like ADO.NET was done and forgotten. I also would like perf improvements. The CPU usage from reading big data sets seems disproportionately high to me. I have written many serializers and this seems too much. – usr Jul 14 '18 at 8:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.