0

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:

  1. Open new connection with transaction for DB1
  2. Perform update operation on patient table for DB1 (successful)
  3. Open new connection with transaction for DB2
  4. Perform update operation on patient table for DB2 (successful)
  5. Commit transaction and close connetion for DB2
  6. Perform update operation on patient table for DB1(error occur)
  7. Rollback transaction for DB1
  8. 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();
            }
        }
    }
}
Nayan Rudani
  • 1,029
  • 3
  • 12
  • 21
  • Since you're using multiple connections you should look into [TransactionScope](https://learn.microsoft.com/en-us/dotnet/api/system.transactions.transactionscope?redirectedfrom=MSDN&view=netframework-4.7.2) rather than using SqlTransaction -- which is limited to a single connection. Or using one connection. – fourwhey Oct 27 '18 at 04:26
  • Thanks for reply, but in dotnet core transactionScope will not worked for distributed transactions. its throwing error [This platform does not support distributed transactions]. How can i use it in dot net core any idea? – Nayan Rudani Oct 27 '18 at 05:14
  • Can you be specific that which version of .NET Core and EF Core you are using ? – dotnetstep Oct 27 '18 at 08:01
  • answer update with .Net Core 2.1 – Nayan Rudani Oct 27 '18 at 08:25
  • In refactoring mood: replace `try..catch..finally`,with `using` for transaction object. – Fabio Oct 27 '18 at 08:45

0 Answers0