MySQL Connector/NET connection multiple DataReaders per Connection?

Question

I am migrating from Java to C# now that I've realized I prefer the C# language features over the ones in Java, but I have this small issue. In MySQL Connector/J and JDBC, I believe that one of my applications allowed multiple PreparedStatements to be executed while another one is open, like I could perform a query that returns a ResultSet and while that ResultSet is still open, I could open another PreparedStatement and get another ResultSet or I could just execute an update, based on the data I got from my first ResultSet (i.e., insert a salt value and update the password column with a SHA512 hash when I realize that the row has a plaintext password in the password column).

However, with Connector/NET, I've come to realize whenever I try doing this, I get this error: MySql.Data.MySqlClient.MySqlException: There is already an open DataReader associated with this Connection which must be closed first.

Is there a easy way to fix this error, maybe any other implementations of a MySQL to .NET bridge? I don't really want to create a lot of DB connections in one application, although I might want to create one for every thread in my application (as in a ThreadLocal). A ThreadLocal DB connection will help when I perform two queries at the same time in two different methods, but obviously I cannot separate these two commands into different threads and I don't want to create excess threads.

By the way, here's the code itself. Yes, I can move the update code down to after I close the reader, but I have many more similar methods and some of them are more difficult to fix up than this one:

MySqlConnection con = DatabaseConnection.GetConnection();
MySqlCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT `id`,`password`,`salt`,`pin`,`gender`,`birthday` FROM `accounts` WHERE `name` = '" + AccountName + "'";
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
    AccountId = reader.GetInt32(0);
    string passhash = !reader.IsDBNull(1) ? reader.GetString(1) : null;
    string salt = !reader.IsDBNull(2) ? reader.GetString(2) : null;
    m_pin = !reader.IsDBNull(3) ? reader.GetString(3) : null;
    Gender = !reader.IsDBNull(4) ? reader.GetByte(4) : WvsCommon.Gender.UNDEFINED;
    m_birthday = !reader.IsDBNull(5) ? reader.GetInt32(5) : 0;
    if (!HashFunctions.HashEquals(pwd, HashAlgorithms.SHA512, passhash + salt))
    {
        if (passhash == pwd || salt == null && HashFunctions.HashEquals(pwd, HashAlgorithms.SHA1, passhash))
        {
            salt = HashFunctions.GenerateSalt();
            passhash = HashFunctions.GenerateSaltedSha512Hash(pwd, salt);
            MySqlCommand update = con.CreateCommand();
            update.CommandText = "UPDATE `accounts` SET `password` = '" + passhash + "', `salt` = '" + salt + "' WHERE `id` = " + AccountId;
            update.ExecuteNonQuery();
            update.Dispose();
        }
    }
}
reader.Close();
cmd.Dispose();

If moving the update code is the only possibility, or if it's the best one, I suppose I'll have to make do with it, but I want to get more ideas on other possibilities first and then pick an option.

Answer 1

No, and I bet that's the case as well in the java world.

The connection is being actively used/hold to retrieve that data, if that worked in the java world is because it did one of:

• read/cached the whole result set
• did it in a separate connection behind the scenes

I don't see much on an issue, you just have to move the reader.Close to the appropriate place in your code. That said, you should go through that code anyway, as your dispose/close calls won't be correctly called if an exception occurs. Use the using statement to ensure everything is freed appropriately, below a modified version of your code with these changes (and a couple others that make it be less deep to the right):

using(MySqlConnection con = DatabaseConnection.GetConnection())
using(MySqlCommand cmd = con.CreateCommand())
{
    cmd.CommandText = "SELECT `id`,`password`,`salt`,`pin`,`gender`,`birthday` FROM `accounts` WHERE `name` = '" + AccountName + "'";
    using(MySqlDataReader reader = cmd.ExecuteReader())
    {
        if(!reader.Read()) return;
        AccountId = reader.GetInt32(0);
        string passhash = !reader.IsDBNull(1) ? reader.GetString(1) : null;
        string salt = !reader.IsDBNull(2) ? reader.GetString(2) : null;
        m_pin = !reader.IsDBNull(3) ? reader.GetString(3) : null;
        Gender = !reader.IsDBNull(4) ? reader.GetByte(4) : WvsCommon.Gender.UNDEFINED;
        m_birthday = !reader.IsDBNull(5) ? reader.GetInt32(5) : 0;
        reader.Close();
        if (HashFunctions.HashEquals(pwd, HashAlgorithms.SHA512, passhash + salt))
            return;
        if(passhash != pwd && !(salt == null && HashFunctions.HashEquals(pwd, HashAlgorithms.SHA1, passhash)))
            return;
        salt = HashFunctions.GenerateSalt();
        passhash = HashFunctions.GenerateSaltedSha512Hash(pwd, salt);
        using(MySqlCommand update = con.CreateCommand())
        {
           update.CommandText = "UPDATE `accounts` SET `password` = '" + passhash + "', `salt` = '" + salt + "' WHERE `id` = " + AccountId;
           update.ExecuteNonQuery();
        }
    }
}