Nested mysql C # data reading issue

Question

I wrote my own class to handle database queries against the remote and local MySQL database, however, when I do a nested loop, I get the following error:

MySql.Data.MySqlClient.MySqlException was unhandled Message=There is already an open DataReader associated with this Connection which must be closed first. Source=MySql.Data ErrorCode=-2147467259 Number=0 

At the moment, my class is as follows

 public class MySQLManager { private MySqlConnection _MySQLRemoteConnection { get; set; } public void setup(string remoteUser, string remotePass, string remoteServerAddress, string remoteDb, string localUser, string localPass, string localServerAddress, string localDb) { _remote_server_address = remoteServerAddress; _remote_database = remoteDb; _remote_username = remoteUser; _remote_password = remotePass; } public void connect() { try { _MySQLRemoteConnection = new MySqlConnection() { ConnectionString = string.Format("server={0};database={1};uid={2};password={3};", _remote_server_address, _remote_database, _remote_username, _remote_password) }; _MySQLRemoteConnection.Open(); _RemoteConnection = true; } catch (MySqlException ex) { _RemoteConnection = false; } } public MySqlCommand run(string query, List<MySqlParameter> dbparams = null) { connect(); MySqlCommand sql = getConnection().CreateCommand(); sql.CommandText = query; if (dbparams != null) { if (dbparams.Count > 0) { sql.Parameters.AddRange(dbparams.ToArray()); } } //disconnect(); return sql; } public MySqlDataReader fetch(MySqlCommand cmd) { //connect(); var t = cmd.ExecuteReader(); //disconnect(); return t; } 

And the code that I run to create the error, now I understand that I can do the following example in one request, this is an EXAMPLE request to recreate the error, writing it to one request will not work with live examples.

 query = "SELECT field1 FROM tmp WHERE field1 < 3"; using (var sql = db.run(query)) { txtResponse.Text += "Query ran" + nl; using (var row = db.fetch(sql)) { txtResponse.Text += "Query fetched" + nl; db.connect(); while (row.Read()) { txtResponse.Text += "Row : " + row[0].ToString() + nl; query = "SELECT val1 FROM tmp2 WHERE field1 = '" + row[0].ToString() + "'"; //db.disconnect(); using (var sql2 = db.run(query)) { txtResponse.Text += "Query ran" + nl; db.disconnect(); using (var row2 = db.fetch(sql)) { txtResponse.Text += "Query fetched" + nl; db.connect(); while (row.Read()) { txtResponse.Text += " Val : " + row2[0].ToString() + nl; } } } } } } 

So how can I make the second loop work?

Answer 1

For SQL Server, you can use MultipleActiveResultSets = true in the connection string, but this most likely will not work for MySQL.

Another option is to use 2 connections, one for each data reader.