Connecting MySQL Connector / NET to multiple DataReaders per connection?

Question

Hi guys, I am switching from Java to C #, now that I realized that I prefer the features of C # over Java, but I have this little problem. In MySQL Connector / J and JDBC, I believe that one of my applications allowed several PreparedStatement to be executed, and the other is open, for example, I could execute a query that returns a ResultSet , and while the ResultSet is still open, I could open another PreparedStatement and get another ResultSet , or I could just perform an update based on the data received from my first ResultSet (i.e., Insert the salt value and update the password column using the SHA512 hash when I understand that the line has a plaintext password in the password column )

However, with Connector / NET, I realized when I try to do 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 an easy way to fix this error, perhaps any other implementations of the MySQL .NET bridge? I really do not want to create many database connections in one application, although I may need to create it for each thread in my application (as in ThreadLocal). A ThreadLocal DB connection will help when I execute two queries simultaneously in two different methods, but obviously I cannot separate these two commands from different threads, and I don't want to create extra threads.

By the way, here is the code itself. Yes, I can move the update code before closing my reader, but I have many other similar methods, and some of them are more difficult to fix than this:

 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 option, or if this is the best option, I suppose I will have to work with it, but first I want to get more ideas on the other options, and then choose the option.

Answer 1

No, and I bet it's also in the Java world.

A connection is actively used / held to retrieve this data if it works in the java world because it did one of:

• read / cache the entire result set
• did it in a separate connection backstage

I don’t see much in the problem, you just need to move the reader. Close the appropriate place in your code. However, you must pass this code anyway, as your dispose / close calls will not be correctly called if an exception occurs. Use the using statement to make sure everything is freed appropriately, under a modified version of your code with these changes (and even more pairs that make it 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 2

Ok guys, a little more research, I realized I was wrong. The Java ResultSets actually support an active database connection, as evidenced by this page: www.geekinterview.com/question_details/591

ResultSets must be connected in order for the ResultSet.next () method to work correctly to get the next row from the database. Note that this does not mean that the connection is occupied by the ResultSet service, but instead the ResultSet is only held in the connection so that it can move forward when the command is issued.

Apparently, the SQL server has something similar to this, which allows you to open several read-only queries, only for direct access, and the other is open in the same connection called MARS (several active result sets). http://www.codeguru.com/csharp/csharp/cs_network/database/article.php/c8715

With a little research, I realized that MySQL Connector / NET does not support this feature. This is too bad because I think it makes more sense than the current implementation, at least for the migration of Java developers.

Answer 3

From MSDN

As long as SqlDataReader is used, the associated SqlConnection connection is busy servicing SqlDataReader, and no other operations can be performed on the SqlConnection, except closing It. This takes place before closing the SqlDataReader method is called. For example, you cannot get output parameters until you Close

What I usually do to resolve this is to enclose my connections, which I need so that when I close all the other connections for the first time, they will be deleted.