How to handle exception in C Sharp Using transactions

Question

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.