0

I wrote a simple websocket server with multiple clients working executing mysql queries through the server using a single MySQL DB connection kept open at all time (from program start to program close). The problem is, when multiple clients send the same command (thus same query is executed but with different values) or multiple queries are executed anyway at same time, the connection will return error with datareader already open.

This is a example of code/query executed by the clients (which may happen a concurrent identical query)

public bool Id(int Id, string name)
        {
            try
            {
                checkConnection();
                using (var sqlCommand = new MySqlCommand($"SELECT * FROM ids WHERE name = '{name}'", Program.SQL.conn))
                {
                    var check = sqlCommand.ExecuteReader();
                    if (check.HasRows)
                    {
                        check.Close();
                        return false;
                    }
                    else
                    {
                        check.Close();
                        using (MySqlCommand cmd = Program.SQL.conn.CreateCommand())
                        {
                            cmd.CommandText = "INSERT INTO ids (id, name) VALUES (@id, @name)";
                            cmd.Parameters.AddWithValue("@id", Id);
                            cmd.Parameters.AddWithValue("@name", name);
                            int x = cmd.ExecuteNonQuery();
                            if (x == 0) { return false; } else { return true; }
                        }
                    }
                }
            }
            catch (MySqlException e)
            {
                return false;
            }
        }

How would you fix that? I did some research, seems it has to do with MultipleActiveResultSets ruleset, but I've also read that this is not supported on MySQL, so any idea?

0 Answers0