0

I have created two different connections. One for DataReader and second for NonQuery.

DataReaderConnect = DBUtils.GetDBConnection(_DbUserName, _DbPassword);
DataReaderConnect.Open();

NonQueryConnect = DBUtils.GetDBConnection(_DbUserName, _DbPassword);
NonQueryConnect.Open();

I have 2 methods that is using those connections

public async Task UpdateUser(cUser.UserObject User)
{
    string SQL = $"UPDATE {_DbName}.dbUserTable SET Name = @Name....";
    using MySqlCommand Command = new MySqlCommand(SQL, NonQueryConnect);
    Command.Parameters.AddWithValue("@Name", User.Name);
    ...
    await Command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
public async Task<cUser.UserObject> GetUser(int ttvUserID)
{
    List<cUser.UserObject> Users = new List<cUser.UserObject>();
    string SQL = $"SELECT * FROM {_DbName}.dbUserTable WHERE TwitchID = @ID";
    using MySqlCommand Command = new MySqlCommand(SQL, DataReaderConnect);
    Command.Parameters.AddWithValue("@ID", ttvUserID);
    using var sqlReader = await Command.ExecuteReaderAsync().ConfigureAwait(false);
    while (await sqlReader.ReadAsync().ConfigureAwait(false))
    {
        Users.Add(new cUser.UserObject()
        {
            dbID = Convert.ToInt32(sqlReader[0]),
            TwitchID = Convert.ToInt32(sqlReader[1]),
            Name = sqlReader[2].ToString(),
            isSub = Convert.ToInt32(sqlReader[3]),
            isVip = Convert.ToInt32(sqlReader[4])
        });
    }                     
}

But im getting exception "There is already an open DataReader associated with this Connection which must be closed first." And I cant figure out why. Im not using DataReader for NonQuery and vice versa

edit: I figured it out and it was just me being silly and apparently I can't read. .NET not only cant support DataReader and NonQuery on the same connection but also more then one DataReader on the same connection.

user2213608
  • 57
  • 1
  • 5
  • 2
    Why do you (1) have two different open connections at the same time and (2) leave connections open *at all* instead of creating/using/disposing in as small a scope as possible? You appear to be using "global connections" of some kind, which is generally wrong to begin with, and this problem would become a moot point if you change that. – David Nov 04 '22 at 11:55
  • (1) .NET does not supporting using the same connection for DataReader and NonQuery. (2) Perfomance issues – user2213608 Nov 04 '22 at 13:16
  • 1
    1. You're trying to be "more clever" than the built-in connection pooling. 1b. Strongly consider to: Stop trying to be "more clever" than the built-in connection pooling. https://stackoverflow.com/questions/26089420/c-sharp-mysql-connection-pooling https://stackoverflow.com/questions/18336961/what-does-pooling-false-in-a-mysql-connection-string-mean 2. Long-term : IMHO (just my opinion) MySql is a "starter kit" RDBMS. Move to Postgres or MsSqlServer. – granadaCoder Nov 04 '22 at 13:26
  • At first I wasnt trying to be "more clever", and I had encountered some issues with MySQL server. But yes, im planning to try out ScyllaDB. – user2213608 Nov 04 '22 at 14:16

1 Answers1

1

Don't keep multiple open connections to the database. In fact, don't keep open connections at all to the database. It leaves hanging resources that the underlying system is better equipped to manage than our code is.

These interactions with the database should:

  1. Open the connection
  2. Use the connection
  3. Close the connection

So instead of this:

public async Task UpdateUser(cUser.UserObject User)
{
    string SQL = $"UPDATE {_DbName}.dbUserTable SET Name = @Name....";
    using MySqlCommand Command = new MySqlCommand(SQL, NonQueryConnect);
    Command.Parameters.AddWithValue("@Name", User.Name);
    //...
    await Command.ExecuteNonQueryAsync().ConfigureAwait(false);
}

Instead, use something like this (other issues notwithstanding):

public async Task UpdateUser(cUser.UserObject User)
{
    using var connection = DBUtils.GetDBConnection(_DbUserName, _DbPassword);

    var sql = $"UPDATE {_DbName}.dbUserTable SET Name = @Name....";
    using var command = new MySqlCommand(sql, connection);

    command.Parameters.AddWithValue("@Name", User.Name);
    //...
    await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}

This keeps the scope of the connection itself limited to just this operation, since both command and connection will be disposed at the end of their respective using blocks. That way you don't have open connections hanging around that aren't being used, and you don't run into errors like the one you're seeing now.

David
  • 208,112
  • 36
  • 198
  • 279
  • I specifically did it this way. Opening and Closing connection takes time. Also if i open and close connection for each operation, there would be hundreds of connections to sql server. – user2213608 Nov 04 '22 at 12:27
  • @user2213608: How much time does it take? Have you noticed and measured a specific performance problem? For the "hundreds of connections", are you saying that these operations are happening many times across many threads simultaneously? What specific problem was observed when that happened? – David Nov 04 '22 at 12:32
  • Yes. Operations are happening across different threads simultaneously. I dont remmember exactly what was the exceptions that was happening, but it was something about locked database. edit: Found my old logs. The exception was "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." Also 2-5 cpu load. – user2213608 Nov 04 '22 at 13:07