How to execute another MySqlDataReader for each read ID?

Question

I'm trying to get from my database some data, each of that data may have some attributes, so my logic was while i'm getting all data so while the MySqlDataReader is executed i was going to execute the query for each id of data i got to get it's attributes.

But i run in to error: 'There is already an open DataReader associated with this Connection' so my guess is that i can't run at the same time the MySqlDataReader so at this point, which would be the best approach to get attributes for each data?

Should i just cycle on each Plu element after i've added them to the list or is there a better solution?

Here is the function where i get the data (Plu object)

        public IEnumerable<Plu> GetPlu(string piva, int menu)
        {
            string connectionString = $"CONSTR";
            using var connection = new MySqlConnection(connectionString);
            connection.Open();

            var sql = @"QUERY";

            using var cmd = new MySqlCommand(sql, connection);
            cmd.Parameters.AddWithValue("@menu", menu);
            cmd.Prepare();

            using MySqlDataReader reader = cmd.ExecuteReader();

            List<Plu> plu = new List<Plu>();

            while (reader.Read())
            {
                plu.Add(new Plu(
                    (int)reader["ID_PLUREP"],
                    (string)reader["CODICE_PRP"],
                    (string)reader["ESTESA_DES"],
                    (string)reader["DESCR_DES"], (float)reader["PRE_PRP"],
                    reader.IsDBNull(reader.GetOrdinal("IMG_IMG")) ? null : (string)reader["IMG_IMG"],
                    Attributi(connection, (int)reader["ID_PLUREP"])
                    ));
            }

            return plu; 
        }

And here is function Attributi which return the IEnumerable of attributes for each Plu

        public IEnumerable<Plu.Attributi> Attributi(MySqlConnection connection, int idplu)
        {
            var sql = @"QUERY";

            using var cmd = new MySqlCommand(sql, connection);
            cmd.Parameters.AddWithValue("@idplu", idplu);
            cmd.Prepare();

            List<Plu.Attributi> attributi = new List<Plu.Attributi>();

            using MySqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                attributi.Add(new Plu.Attributi(
                        reader.IsDBNull(reader.GetOrdinal("BCKCOL_ATT")) ? null : (string)reader["BCKCOL_ATT"],
                        reader.IsDBNull(reader.GetOrdinal("FORCOL_ATT")) ? null : (string)reader["FORCOL_ATT"],
                        reader.IsDBNull(reader.GetOrdinal("DESCR_ATT")) ? null : (string)reader["DESCR_ATT"]
                        ));
            }

            return null;
        }

Answer 1

You can't use an open connection with a reader already executing. Open a new connection in Attributi.

public IEnumerable<Plu.Attributi> Attributi(int idplu)
{
    var sql = @"QUERY";

    using var connection = new MySqlConnection(connectionString)
    {
        connection.Open();
        using var cmd = new MySqlCommand(sql, connection)
        {
            cmd.Parameters.AddWithValue("@idplu", idplu);
            cmd.Prepare();

            List<Plu.Attributi> attributi = new List<Plu.Attributi>();

            using MySqlDataReader reader = cmd.ExecuteReader()
            {
                while (reader.Read())
                {
                    attributi.Add(new Plu.Attributi(
                        reader.IsDBNull(reader.GetOrdinal("BCKCOL_ATT")) ? null : (string)reader["BCKCOL_ATT"],
                        reader.IsDBNull(reader.GetOrdinal("FORCOL_ATT")) ? null : (string)reader["FORCOL_ATT"],
                        reader.IsDBNull(reader.GetOrdinal("DESCR_ATT")) ? null : (string)reader["DESCR_ATT"]
                        ));
            }

            return null;
        }
    }
}

BTW, your usage of using is totally off. You need a block after the using statement where you deal with everything regarding the IDisposable object.

EDIT: Apparently that's a new .NET Core 3.1 feature.

Answer 2

For the more general case, my experience with MySQL has lead me to always "free" my reader with:

MySqlDataReader reader = cmd.ExecuteReader();
DataTable dataTable = new DataTable();
dataTable.Load(reader);

Then working from the DataTable rather than the MySqlDataReader, you can then reuse the connection as you prefer.