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

Rijwan AnsariPosted Dec 16, 2021, 5:09 PM
Srinivasan RamamoorthiPosted Dec 16, 2021, 1:12 PM
Jishan SiddiquePosted Dec 16, 2021, 11:29 AM