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