Aniket Narvankar

Aniket Narvankar

  • 543
  • 2.1k
  • 581.3k

Transaction Rollback

Dec 16 2021 10:38 AM

Have two methods one insert data into table and sceond method update it base on another table. I want to use transaction to rollback the transaction if insert or updation fails. Used IDbConnection. Here is the code

   

        public int BulkImportSkipExportedCustomers()
        {
            int result = 0;
            try
            {
                using (IDbConnection conn = objDataConnection.dataConnection)
                {
                 
                    var param = new DynamicParameters();
                    param.Add("@amsexported", 0);
                    StringBuilder sb = new StringBuilder("insert into AMSLeadExportLog(EnteredDate,LeadId,SessionId,LeadStatus,PolicyNbr,LeadAuto) Select distinct GETDATE(),LeadId,SessionId,Status,PolicyNbr,Auto from LeadExportLog where AMSExported=@amsexported");
                   result = conn.Execute(sb.ToString(), param, null, null);
                }
            }
            catch (Exception)
            {
                throw;
            }
            return result;
        }

        public int UpdateSkipExportedCustomers()
        {
            int result = 0;
            try
            {
                using (IDbConnection conn = objDataConnection.dataConnection)
                {
                    var param = new DynamicParameters();
                    param.Add("@leadsamsexported", 0);
                    param.Add("@amsexported", 1);
                    StringBuilder sb = new StringBuilder("Update l set l.AMSExported=@amsexported,l.AMSExportDate=GETDATE() from LeadExportLog l where l.LeadID in(Select LeadID from AMSLeadExportLog) and l.AMSExported=@leadsamsexported");
                    result = conn.Execute(sb.ToString(), param, null, null);
                }
            }
            catch (Exception)
            {
                throw;
            }
            return result;
        }

 

If any one method gives error,entire transaction should fail. Suppose 10 records are inserted and updation fails then both methods must be rollback. please let me know how to do this


Answers (3)