C# MySQL second DataReader in DataReader while loop

Question

As you might have guessed from the title am I trying to do this:

    #region check new nations
    private void checknewnations()
    {
        addtolog("server","Checking for new nations");
        string sql = "SELECT * FROM " + variables.tbl_nations + " WHERE nations_new=0";
        MySqlCommand cmd = new MySqlCommand(sql, connection);
        MySqlDataReader reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            addtolog("mysql",reader["nations_name"].ToString());

            int nation_ID = int.Parse(reader["nations_ID"].ToString());
            string nation_name = reader["nations_name"].ToString();
            string user_ID = reader["nations_user"].ToString();


            addnation(nation_ID, nation_name, user_ID);
        }
        addtolog("server","Finished checking for new nations.");
        //connection.Close();
        reader.Dispose();
    }
    #endregion

which calls this in the while loop:

    #region addnation
    private void addnation(int nationIDnot, string nationName, string userID)
    {
        string nationID = makesixdigits(nationIDnot);
        string userName = "";

        string sql = "SELECT * FROM " + variables.tbl_users + " WHERE users_ID=" + userID;
        MySqlCommand cmd = new MySqlCommand(sql, connection);
        MySqlDataReader reader = cmd.ExecuteReader();


        while (reader.Read())
        {
            userName = reader["users_name"].ToString();
        }
        reader.Dispose();
        addtolog("add", "[" + nationID.ToString() + "] " + nationName + " [" + userID + "] " + userName);
    }
    #endregion

This gives me an error in the second block of code (the one that is called upon in the while loop) saying that there already is a datareader associated with the connection. How am I to get this to work, because I am sure there is a way.

score 5 · Answer 1 · answered Aug 14 '12 at 22:36

5

DataReader holds on to connection until it iterates through all its records. Try this

using(reader)
{
   System.Data.DataTable dt = new System.Data.DataTable();
   dt.Load(reader);

   foreach(DataRow row in dt.Rows)
   {
        addtolog("mysql",row["nations_name"].ToString());

        int nation_ID = int.Parse(row["nations_ID"].ToString());
        string nation_name = row["nations_name"].ToString();
        string user_ID = row["nations_user"].ToString();


        addnation(nation_ID, nation_name, user_ID);
   }
}

This way the two reader are not sharing the same connection

answered Aug 14 '12 at 22:36

codingbiz

26,179
8
59
96

Would that make a local version of the table? – Dirk Aug 14 '12 at 22:38
All records are loaded into `dt` at once and the reader is free – codingbiz Aug 14 '12 at 22:41
This approach of loading the DataReader result set into a DataTable looks handy for some small result set scenarios I run into. For example, a top-level "Client/Person" row, which then has 0..n child rows to loop through, where "n" typically is a "few" i.e., not "thousands". If each of those child rows potentially relates to 0..n additional data rows in multiple other tables, this approach would be useful for looping through those first-level child rows without tying up a connection. – Developer63 Nov 13 '15 at 18:42

score 1 · Accepted Answer · answered Aug 14 '12 at 22:31

1

In 2008 connection strings you can have multiple active results sets via MultipleActiveResultSets=true; I've not used mySQL so I'm not sure, but maybe if you are using an ADO provider.

Otherwise, just make another connection -- don't re-use the same one.

answered Aug 14 '12 at 22:31

Hogan

69,564
10
76
117

Thank you very much for you answer. It is such an obvious and easy solution :) – Dirk Aug 14 '12 at 22:37
Does MARS work with MySQL? I thought it was still specific to SQLServer 2005 and later. (I mostly just use multiple connections anyway unless I really need to do stuff from interleaving calls in the same transaction). – Jon Hanna Aug 14 '12 at 22:46
@JonHanna - I didn't think it did, but a MS user could read this question so I covered the base. – Hogan Aug 14 '12 at 22:51
Good point. Oh hai MS user! Read http://msdn.microsoft.com/en-us/library/h32h3abf%28v=vs.80%29.aspx before using MARS. – Jon Hanna Aug 14 '12 at 22:54
I am not using that mars thingy, just the multiple connections :) 3 extra lines of code :) – Dirk Aug 14 '12 at 23:05

score 1 · Answer 3 · answered Aug 14 '12 at 22:38

1

Open another connection in the loop, and use it to obtain the second reader. Since the loop is tight, you might prefer to open both connections in the first method, pass the second through to the called method, and then close them both at the end of the first ("outer") method. It should be a short enough time between calls that the normal rule about closing connections as soon as possible won't matter.

answered Aug 14 '12 at 22:38

Jon Hanna

110,372
10
146
251

Thank you for your reply :) I am now using 2 connections, though I am opening them at the start of the program and close them at the end of the program, because the first while loop is also part of a loop/timer_tick so the connections get almost constantly used. – Dirk Aug 14 '12 at 22:42
1

@user1599326 if it's a single-threaded console app that fits into the lose spec of "do db stuff, then end" (!) then that's fine; there isn't anything else sharing the connection pool anyway. – Jon Hanna Aug 14 '12 at 22:44
1

Just remember that it falls under the category of bending rules because it's just a small app that does stuff and doesn't end; "hey what's the scalability impact of the way we..." "who cares, the program ended before you got to the end of that question!" :) – Jon Hanna Aug 14 '12 at 22:51
Well, the program is actually designed to be a constant running server program (only with a connection with the MySql database, not directly with the clients) – Dirk Aug 14 '12 at 23:03
1

In that case you *really* want to create and open the connections at the start of the method, and close them at the end (ideally with `using` to make sure this happens in the face of an exception). Concurrent threads dealing with concurrent client connections (as in the server app's clients) will have separate connections (otherwise you get the same exception as above in one of the clients and don't know why) and pooling keeps connection overheades and total DB connection count low as per http://stackoverflow.com/a/3845924/400547 – Jon Hanna Aug 14 '12 at 23:12

C# MySQL second DataReader in DataReader while loop

3 Answers3

Linked