MySQL Connector/NET connection multiple DataReaders per Connection?

Question

Hey guys, I'm 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

From MSDN

While the SqlDataReader is being used, the associated SqlConnection is busy serving the SqlDataReader, and no other operations can be performed on the SqlConnection other than closing it. This is the case until the Close method of the SqlDataReader is called. For example, you cannot retrieve output parameters until after you call Close

What I typically do to resolve this is to nest my connections I need so that when I the first using closes all the other connections are disposed.

Answer 2

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

Answer 3

Alright guys, with a bit more of research, I realized I was wrong. Java's ResultSets do in fact hold an active connection to the database, as evidenced by this page: www.geekinterview.com/question_details/591

ResultSets must be connected so that the ResultSet.next() method works properly to fetch the next row from the database. Note that this does not mean that the connection is busy servicing the ResultSet, but instead the ResultSet only holds on to the connection so that it could move forward when given the command to.

Apparently SQL server has something similar to this that allows you to open multiple read-only, forward-only queries while another is open on the same connection, called MARS (Multiple Active Result Sets). http://www.codeguru.com/csharp/csharp/cs_network/database/article.php/c8715

With a little bit more research, I realized MySQL Connector/NET does not support this feature. It's too bad because I believe it makes more sense than the current implementation, at least for migrating Java developers.