C#MySql TransactionScope [英] C# MySql TransactionScope

问题描述

我的系统在相同的MySql中有2个数据库.业务是确保数据完全在同一事务中插入2个数据库中.下面是我的示例:

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;
}

我跑步时出现错误:

当前不支持多个并发连接或同一事务中具有不同连接字符串的连接.

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

为什么会发生?

如果无法解决，请给我其他解决方案.

if cannot fix, please give me others solutions.

推荐答案

错误是不言自明的，我们不能在单个事务中使用不同的连接，您可以做的就是共享例如，通过重构代码在两个查询中使用相同的连接对象.只是为了证明您可以做到:

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;
}

，您可以这样称呼它:

using (TransactionScope scope = new TransactionScope())
{

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

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

connection.Open();

connection.Open();

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

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

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

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

希望这会有所帮助！

Hope this helps!

这篇关于C#MySql TransactionScope的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！