none
C# MySql TransactionScope

    Question

  • My system have 2 databases in the sames MySql. Business is make sure data insert into 2 databases in the same transaction completely. Below is my sample:

    public void test()
    {
        int returnValue = 0;
        System.IO.StringWriter writer = new System.IO.StringWriter();
        try
        {
            using (TransactionScope scope = new TransactionScope())
            {
                returnValue = TestA(returnValue, writer);
    
                returnValue = TestB(returnValue, writer);
    
                scope.Complete();
            }
        }
        catch (Exception ex)
        {
        }
    }
    
    private static int TestB(int returnValue, System.IO.StringWriter writer)
    {
        using (MySqlConnection connection2 = new MySqlConnection("server=localhost;database=test;user id=root;password=root;port=3307;characterset=utf8;connectiontimeout=72000;"))
        {
            connection2.Open();
    
            // Execute the second command in the second database.
            returnValue = 0;
            MySqlCommand command2 = new MySqlCommand("Insert tbb (`time`)value ('10:00:00')", connection2);
            returnValue = command2.ExecuteNonQuery();
            writer.WriteLine("Rows to be affected by command2: {0}", returnValue);
        }
        return returnValue;
    }
    
    private static int TestA(int returnValue, System.IO.StringWriter writer)
    {
        using (MySqlConnection connection1 = new MySqlConnection("server=localhost;database=test1;user id=root;password=root;port=3307;characterset=utf8;connectiontimeout=72000;"))
        {
            connection1.Open();
    
            // Create the SqlCommand object and execute the first command.
            MySqlCommand command1 = new MySqlCommand("Insert tb1 (`Name`, `Value`)value ('ai', '2017-04-26')", connection1);
            returnValue = command1.ExecuteNonQuery();
            writer.WriteLine("Rows to be affected by command1: {0}", returnValue);
        }
    
        return returnValue;
    }

    When I run, I got error:

    Multiple simultaneous connections or connections with different connection strings inside the same transaction are not currently supported.

    Why does it happen?

    if cannot fix, please give me others solutions.

    Wednesday, April 26, 2017 5:00 AM

All replies

  • The error is pretty self explanatory, we cannot use different connections in a single transaction, what you can do is share same connection object in both queries by refactoring the code, for example. just to demonstrate you can do:

    private static int TestB(int returnValue, System.IO.StringWriter writer,MySqlConnection connection)
    {
    
            // Execute the second command in the second database.
            returnValue = 0;
            MySqlCommand command2 = new MySqlCommand("Insert tbb (`time`)value ('10:00:00')", connection);
            returnValue = command2.ExecuteNonQuery();
            writer.WriteLine("Rows to be affected by command2: {0}", returnValue);
        }
    
        return returnValue;
    }
    
    private static int TestA(int returnValue, System.IO.StringWriter writer, MySqlConnection connection)
    {
       
      connection.Open();
    
      // Create the SqlCommand object and execute the first command.
      MySqlCommand command1 = new MySqlCommand("Insert tb1 (`Name`, `Value`)value ('ai', '2017-04-26')", connection1);
       returnValue = command1.ExecuteNonQuery();
       writer.WriteLine("Rows to be affected by command1: {0}", returnValue);
    }
    
    
        return returnValue;
    }

    and you can call it like:

    using (TransactionScope scope = new TransactionScope()) {

    using (MySqlConnection connection = new MySqlConnection("server=localhost;database=test;user id=root;password=root;port=3307;characterset=utf8;connectiontimeout=72000;")) {

    connection.Open();

    returnValue = TestA(returnValue, writer, connection); returnValue = TestB(returnValue, writer, connection);

    connection.Close(); } scope.Complete(); }

    Hope this helps!


    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered"Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


    Blog | LinkedIn | Stack Overflow | Facebook
    profile for Ehsan Sajjad on Stack Exchange, a network of free, community-driven Q&A sites


    Wednesday, April 26, 2017 2:00 PM