0

I have the following INSERT method in a C# web project. If I run the project without MySQL connection poling everything works fine, but when I activate Pooling=True in the DB connection string this method stops working, the insert statements never complete.

I realized how to modify the code to make it work, but I would like to understand what is happening and I hope you could help.

When I comment line //myR.Close(); everything works fine.

using MySql.Data.MySqlClient;
//query example consulta="INSERT INTO users (id, name) VALUES (1, 'Rob');

public static MySqlConnection GetWriteConnection()
    {
        string connStr = MySqlConnectionStrings.WriteConnectionString;
        MySqlConnection conn = new MySqlConnection(connStr);
        return conn;
    }

public static MySqlConnection GetReadConnection()
    {
        string connStr = MySqlConnectionStrings.ReadConnectionString;
        MySqlConnection conn = new MySqlConnection(connStr);
        return conn;
    }

public static bool Insert(string consulta)
    {
        MySqlConnection conn = BdaHelper.GetWriteConnection();
        conn.Open();

        using (conn)
        {
            try
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                MySqlCommand micomando = new MySqlCommand(consulta, conn);
                micomando.ExecuteNonQuery(); //still not working

                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

    }

My app has also multi-thread concurrency and two types of database connections, one specifically for only-read purposes and other different for write. When an insert statement fails I don't get any error simply the change doesn't commit in the database. Reading the article in the comments I don't think this applies to this issue but I would add an example of my main program:

MySqlConnection readConnection = BdaHelper.GetReadConnection();

    using (readConnection)
    {
        var users = GetUsers(readConnection);
        var credentials = GetCredentials(readConnection);

        //Example is the query that fails don't giving any exception
        Insert("INSERT INTO login_log (id_user, date) VALUES (1, now())");
    }
  • May the problem be caused because there are two concurrent connections?
  • I shouldn't reuse read connection, even is a different connection than the write connection?
Roberto Zamora
  • 250
  • 2
  • 11
  • Does this answer your question? [C# MySQL Connection Pooling](https://stackoverflow.com/questions/26089420/c-sharp-mysql-connection-pooling) – nbk May 25 '20 at 14:04
  • 1
    What does `BdaHelper.GetWriteConnection` do? If it doesn't create a new connection every time it's called then that's likely your issue. – juharr May 25 '20 at 14:55
  • 1
    There are several ways you could improve this code. If you don't need a `MySqlDataReader` (e.g., for an `INSERT` query), use `micomando.ExecuteNonQuery()`. When you do use a reader, call `while (myR.Read())` to read the rows. Always use new `MySqlConnection` and `MySqlDataReader` objects in a `using` block, e.g., `using (var myR = micomando.ExecuteReader())`. You probably have object leaks that are exhausting your connection pool because you're not cleaning up your objects properly. – Bradley Grainger May 25 '20 at 19:21
  • Thank you for the answers, I was not using ExecuteNonQuery because I was testing a differenst approachs but as you said is the most convenient for this case. I don't know it the corrurrent thread post has to do with this issue because MySQL connections are different and READ query allways works fine, the issue is only with READ. Something importa is that even the connections are different connects with the same database same. – Roberto Zamora May 26 '20 at 08:04

0 Answers0