I am using two different DB in my project(.Net Core 2.1). I am not able to rollback child transaction if error occurs in main transactions.
Below are the steps:
- Open new connection with transaction for DB1
- Perform update operation on patient table for DB1 (successful)
- Open new connection with transaction for DB2
- Perform update operation on patient table for DB2 (successful)
- Commit transaction and close connetion for DB2
- Perform update operation on patient table for DB1(error occur)
- Rollback transaction for DB1
- How to rollback DB2 transactions?
Below are my code snippets.
public class CheckTransations
{
public void CheckTran()
{
string strConnString1 = @"data source = 177.16.8.68\sqlexpress; initial catalog = DB1; persist security info = True; user id = sa; password = 99$indd; MultipleActiveResultSets = True; ";
string strConnString2 = @"data source = 177.16.8.68\sqlexpress; initial catalog = DB2; persist security info = True; user id = sa; password = 99$indd; MultipleActiveResultSets = True; ";
SqlTransaction objTrans1 = null;
SqlTransaction objTrans2 = null;
using (SqlConnection objConn1 = new SqlConnection(strConnString1))
{
objConn1.Open();
objTrans1 = objConn1.BeginTransaction();
SqlCommand objCmd1 = new SqlCommand("update Patient set chartNumber = 'C5' where PatientId = 5", objConn1, objTrans1);
try
{
var PatientId = objCmd1.ExecuteNonQuery();
using (SqlConnection objConn2 = new SqlConnection(strConnString2))
{
objConn2.Open();
try
{
objTrans2 = objConn2.BeginTransaction();
SqlCommand objCmd2 = new SqlCommand("update Patient set ChartNumber = 'C029' where PatientId = 29", objConn2, objTrans2);
var PatientId2 = objCmd2.ExecuteNonQuery();
objTrans2.Commit();
}
catch (Exception ex)
{
objTrans2.Rollback();
throw;
}
finally
{
objConn2.Close();
}
}
objCmd1 = new SqlCommand("update Patient set chartNumber = 'C5' where PatietId = 1036", objConn1, objTrans1);
var PatientId1 = objCmd1.ExecuteNonQuery();
objTrans1.Commit();
}
catch (Exception ex)
{
objTrans1.Rollback();
}
finally
{
objConn1.Close();
}
}
}
}