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;
        }

Palle Due · Accepted Answer · 2020-05-25T09:29:41.883

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.

i've read that in .NET Core 3.1 `using` doesn't need anymore of the block statement, quotes from Microsoft docs: `Beginning with C# 8.0, you can use the following alternative syntax for the using statement that doesn't require braces` — NiceToMytyuk, May 25 '20 at 08:52
@Igor [Yep, you're right](https://learn.microsoft.com/en-us/dotnet/csharp/whats-new/csharp-8#using-declarations), and I'm glad this has come to C#. — ProgrammingLlama, May 25 '20 at 09:01
Thanks for enlightening me on the `using` pattern. I wasn't aware of that. I'll keep using the old syntax though. I'm not on .NET Core, and I actually think it's helpful that the code is in an indented block. IT makes it very clear where the disposing is taking place. — Palle Due, May 25 '20 at 09:27

score 0 · Answer 2 · answered May 25 '20 at 08:47

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.

How to execute another MySqlDataReader for each read ID?

2 Answers2