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