3

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.

Improve this question

3 Answers 3

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Would that make a local version of the table?
All records are loaded into dt at once and the reader is free
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.
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.

Improve this answer

5 Comments

Thank you very much for you answer. It is such an obvious and easy solution :)
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).
@JonHanna - I didn't think it did, but a MS user could read this question so I covered the base.
Good point. Oh hai MS user! Read msdn.microsoft.com/en-us/library/h32h3abf%28v=vs.80%29.aspx before using MARS.
I am not using that mars thingy, just the multiple connections :) 3 extra lines of code :)
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.

Improve this answer

5 Comments

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.
@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.
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!" :)
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)
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 stackoverflow.com/a/3845924/400547

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.