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)));
}
}