MySQL ADO.Net Nested DataReaders

Question

I want to retrieve a list of accounts with one SQL statement, and then loop through them while running more SQL statements. When I try to, I get this error:

Unhandled Exception: MySql.Data.MySqlClient.MySqlException: There is already an open DataReader associated with this Connection which must be closed first.

Here's an example:

using (MySqlConnection connection = new MySqlConnection("host=..."))
{

    connection.Open();

    using (MySqlCommand cmdAccounts = connection.CreateCommand())
    {

        cmdAccounts.CommandText = "SELECT id, name FROM accounts";

        using (MySqlDataReader accounts = cmdAccounts.ExecuteReader())
        {

            while (accounts.Read())
            {

                Console.WriteLine("Account {0}:", account.GetString("name"));

                using (MySqlCommand cmdPictures = connection.CreateCommand())
                {

                    cmdPictures.CommandText = "SELECT id, width, height FROM pictures WHERE account_id = @accountId";
                    cmdPictures.Parameters.AddWithValue("@accountId", accounts.GetInt32("id"));

                    using (MySqlDataReader pictures = cmdPictures.ExecuteReader())
                    {

                        while (pictures.Read())
                        {
                            Console.WriteLine("\tPicture #{0}: {1} x {2}", pictures.GetInt32("id"), picture2.GetInt32("width"), picture2.GetInt32("height"));
                        }

                    }
                }
            }
        }
    }
}

Do I have to use a DataSet, or is there a way to do this in MySQL with just DataReaders?

Answer 1

You can do this with DataReaders, but you will need a separate connection object for both levels of reader:

using (MySqlConnection connection = new MySqlConnection("host=..."))
using (MySqlCommand cmdAccounts = MySqlCommand("SELECT id, name FROM accounts" , connection))
{
    connection.Open();

    using (MySqlConnection connection2 = new MySqlConnection("host=..."))
    using (MySqlCommand cmdPictures = new MySqlCommand("SELECT id, width, height FROM pictures WHERE account_id = @accountId", connection2))
    using (MySqlDataReader accounts = cmdAccounts.ExecuteReader())
    {
        cmdPictures.Parameters.Add("@accountId", MySqlDbType.Int32); 
        connection2.Open()

        while (accounts.Read())
        {    
            Console.WriteLine("Account {0}:", account.getString("name"));

            cmdPictures.Parameters["@accountId"].Value = accounts.GetInt32("id");

            using (MySqlDataReader pictures = cmdPictures.ExecuteReader())
            {
                while (pictures.Read())
                {
                    Console.WriteLine("\tPicture #{0}: {1} x {2}", pictures.GetInt32("id"), picture2.GetInt32("width"), picture2.GetInt32("height"));
                }
            }
        }
    }
}

But even as much better as this is, it's still the wrong way to think about the problem in the first place. You really want to JOIN the two tables in the database:

string sql = 
    "SELECT a.id as AccountID, a.name, p.id as PictureID, p.width, p.height" + 
    " FROM accounts a" +
    " INNER JOIN pictures p on p.account_id = a.id" +
    " ORDER BY a.name, a.id";

using (var cn = new MySqlConnection("host=..."))
using (var cmd = new MySqlCommand(sql, cn))
{
    cn.Open();
    using (var rdr = cmd.ExecuteReader())
    {
        bool reading = rdr.Read();
        while (reading)
        {
            int CurrentAccount = rdr.GetInt32("AccountId");
            Console.WriteLine("Account {0}:", rdr.GetString("name"));

            while (reading && CurrentAccount == rdr.GetInt32("AccountId"))
            {
               Console.WriteLine("\tPicture #{0}: {1} x {2}", 
                 rdr.GetInt32("PictureId"), rdr.GetInt32("width"), rdr.GetInt32("height"));
               reading = rdr.Read();
            }
        }
    }
}