C# use a reader in another reader solution?

Question

I need to get some mysql data into another mysql reader request anyway to workaround that I apparently can't have 2 readers open at the same time it will all end up in a datagrid

    public void DBSelectPraktikanter(object sender)
    {
        string Command = "SELECT * FROM forlob WHERE firmaid = N'" + firmaid + "'";

        MySqlConnection sqlConnection1 = new MySqlConnection(connectionString);
        MySqlCommand command = new MySqlCommand(Command, sqlConnection1);

        sqlConnection1.Open();
        MySqlDataReader reader = command.ExecuteReader();
        var items = new List<praktikanter>();

        if (reader.HasRows)
        {
            while (reader.Read())
            {
                string praktikantid = String.Format("{0}", reader["praktikantid"]);

                string Command2 = "SELECT * FROM praktikanter WHERE id = N'" + praktikantid + "'";
                MySqlCommand command2 = new MySqlCommand(Command, sqlConnection1);
                MySqlDataReader reader2 = command.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader2.Read())
                    {
                        Praktikant = String.Format("{0}", reader["Navn"]);
                    }
                }

                string Fra = String.Format("{0}", reader["fra"]);
                string Til = String.Format("{0}", reader["til"]);

                items.Add(new praktikanter(Praktikant, Fra, Til));
            }
        }

        sqlConnection1.Close();

        var grid = sender as DataGrid;
        grid.ItemsSource = items;
    }

This is not an answer to your question, but rather someting you need to pay attention for. Your code us vulnerable to SQL Injection. See this post for more information: http://stackoverflow.com/questions/24547714/insert-user-defined-variables-in-to-the-sql-statements/24548152#24548152 — Complexity, Jan 27 '15 at 10:06
possible duplicate of [C# MySQL second DataReader in DataReader while loop](http://stackoverflow.com/questions/11961682/c-sharp-mysql-second-datareader-in-datareader-while-loop) — Adriano Repetti, Jan 27 '15 at 10:15
In short (from duplicate): enable MARS, if your provider does not support it then create a second connection for the second data reader... — Adriano Repetti, Jan 27 '15 at 10:16
An unhandled exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll Additional information: There is already an open DataReader associated with this Connection which must be closed first. thats the error message — Kaalund, Jan 27 '15 at 10:19
@Kaalund the message is clear. You can't have two readers with the same connection. Adriano already provided the answer — Panagiotis Kanavos, Jan 27 '15 at 10:21
Have you considered using full stops (`.`) in your question to help aid reading clarity? Don't make it any harder for us to help you than it needs to be. — Enigmativity, Jan 27 '15 at 10:22

score 2 · Accepted Answer · answered Jan 27 '15 at 10:23

Instead of nesting MySqlCommands and looping the first resultset to query again the database to collect all of your data you should really use one query. Also use the using-statement to ensure that the connection gets closed even on error and use sql-parameters to avoid sql-injection issues:

var items = new List<praktikanter>();
string sql = @"SELECT p.*, f. Navn 
               FROM praktikanter p INNER JOIN forlob f ON p.id = f.praktikantid
               WHERE f.firmaid = @firmaid";
using (var con = new MySqlConnection(connectionString))
using (var command = new MySqlCommand(sql, con))
{
    command.Parameters.Add(new MySqlParameter("@firmaid", MySqlDbType.VarChar).Value = firmaid);
    con.Open();
    using (var rd = command.ExecuteReader())
    {
        while (rd.Read())
        {
            string praktikant = rd.GetString("Navn");
            string fra = rd.GetString("Fra");
            string til = rd.GetString("Til");
            items.Add(new praktikanter(praktikant, fra, til));
        }
    }
}

Hah, I was just writing the same thing. – BCdotWEB Jan 27 '15 at 10:23 — BCdotWEB, Jan 27 '15 at 10:23

C# use a reader in another reader solution?

1 Answers1