How to handle exception in C Sharp Using transactions

Aniket Narvankar

In Console Application there are three layers DataAccess,Business Layer and Main Layer

Here is the method in DataAccess Layer

public string ImportLeadsFromLeadExportLog()
{
string message;
using(IDbConnection conn = objDataConnection.dataConnection)
{
conn.Open();
using (var tran = conn.BeginTransaction())
{
try
{
var param = new DynamicParameters();
param.Add("@amsexported", 0);
param.Add("@leadsamsexported", 1);
StringBuilder sbInsert = new StringBuilder("insert into tbl1(EnteredDate,LeadId,SessionId,LeadStatus,PolicyNbr,LeadAuto) Select distinct top 1 GETDATE(),LeadId,SessionId,Status,PolicyNbr,Auto from temp where LeadId not in (Select LeadId from tbl1);");

result = conn.Execute(sbInsert.ToString(), param, tran, null);

if(result > 0){message = "Successful number of rows inserted :" +result; }
tran.Commit();
conn.Close();
}
catch(Exception ex)
{
message = ex.Message;
conn.Close();
tran.Rollback();
throw;
}
}
}
return message;
}

Here I am using dapper and transaction,commit the transaction if insertion is successful other wise rollback,also from this method i am returning message which is handle in business layer

Here is the business layers code

public string ImportLeadsFromLeadExportLog()
{
string result;
result = prepareDataDE.ImportLeadsFromLeadExportLog();
return result;
}

From this i am returning the message back to main layer

here is the code

public void ImportLeadsFromLeadExportLog()
{
string result;
result = prepareDataBL.ImportLeadsFromLeadExportLog();
Logger.LogMessage(MethodBase.GetCurrentMethod().Name + " " + result);
}

The issue facing here is,if there is exception in Data Access layer method then exact error message is not return to business layer and main layer. Suppose that table name is wrong,then message which should be return is invalid table name,but instead of this getting message This SqlTransaction has completed; it is no longer usable. For some reasons can not do this in sql and also can not log the error message in Data Access layer. Please do let me know how to return exact message from DataAccess layer like when transaction is successful return number of rows affected and error then rollback transacation and return error message from data access layer to main layer.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rijwan Ansari4y ago
You are using throw in exception.
You can do like this.
public string ImportLeadsFromLeadExportLog()
        {
                string message;
                using(IDbConnection conn = objDataConnection.dataConnection)
                {
                    conn.Open();
                    using (var tran = conn.BeginTransaction())
                    {
                        try
                        {
                            var param = new DynamicParameters();
                            param.Add("@amsexported", 0);
                            param.Add("@leadsamsexported", 1);
                            StringBuilder sbInsert = new StringBuilder("insert into tbl1(EnteredDate,LeadId,SessionId,LeadStatus,PolicyNbr,LeadAuto) Select distinct top 1 GETDATE(),LeadId,SessionId,Status,PolicyNbr,Auto from temp where LeadId not in (Select LeadId from tbl1);");

                            result = conn.Execute(sbInsert.ToString(), param, tran, null);

if(result > 0){message = "Successful number of rows inserted :" +result; }
                            tran.Commit();
                            conn.Close();
                        }
                        catch(Exception ex)
                        {
                            message = ex.Message;
                            conn.Close();
                            tran.Rollback();
                            //throw; remove this throw to return the message
                        }
                    }
                }
          return message;
        }
Remove throw in DataAccess Layer
+1
Ranganath Prasad4y ago
catch(Exception ex)
{
message = ex.Message;
conn.Close();
tran.Rollback();
throw; // Remove this line so that the message variable ( which has error message ) will be returned.
}

So, what i understood is that, you just want to return the error message instead of the entire stacktrace. But, since you have put that throw statement at the end of the catch block, it will rethrow the exception and the flow stops there and your return message will never get executed.

Btw, shouldn't you rollback the transaction first and then close the connection in your catch block?

And always try to check if the conn is open before you call conn.close. And the last suggestion is that, do this cleanup ( close/release connections ) in finally block instead of catch block.
+1