Uncommitted Transaction Isolation Level

630 views Asked by At

I am trying to understand how Transactions work in c# using the TransactionScope object. I have an understanding about them in SQL server but I don't know how to verify this in c#. For example I want to check if I set the IsolationLevel to ReadUnCommitted, how can I verify this behavior in my code?

Currently this is my simple code, What I want to test is while my first Update command is executing and not committed/rolled back how can I verify that other transactions would be able to read the data it affects? Can anybody help me with a code example?

Here is my code:

public void TransferAmount(Account a)
{
    bool debit = false;
    bool credit= true;
    var option = new TransactionOptions();
    option.IsolationLevel = System.Transactions.IsolationLevel.ReadUnCommitted;
    try
    {
        using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.Required, option))
        {
            using (IDbCommand cmd = Idbconnection.CreateCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "usp_Update_Debit";
                IDataParameter param = cmd.CreateParameter();
                param.ParameterName = "@mDebitAmount";
                param.Value = a.Amount;
                cmd.Parameters.Add(param);
                Idbconnection.Open();
                debit = cmd.ExecuteNonQuery() == 1;
            }
            using (IDbCommand cmd = Idbconnection.CreateCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "usp_Update_Credit";
                IDataParameter param = cmd.CreateParameter();
                param.ParameterName = "@mCreditAmount";
                param.Value = a.Amount;
                cmd.Parameters.Add(param);
                credit = cmd.ExecuteNonQuery() == 1;
            }
            if (debit == credit)
                transactionScope.Complete();
        }
    }
    catch (Exception ex)
    {
        System.ServiceModel.Web.WebOperationContext.Current.OutgoingResponse.StatusCode = System.Net.HttpStatusCode.OK;
        throw new FaultException(new FaultReason(new FaultReasonText(ex.Message)));
    }
}
0

There are 0 answers