DataReader error when i us it in the second time

Question

var filterConditions = new[] {
       CreateSqlFilter("CIVILIDD", ID_No, selectCommand, false),


};

        string filterCondition = filterConditions.Any(a => a != null) ? filterConditions.Where(a => a != null).Aggregate((filter1, filter2) => String.Format("{0} AND {1}", filter1, filter2)) : (string)null;

        using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["my"].ConnectionString))
        {
            selectCommand.Connection = connection;
            selectCommand.CommandText = filterCondition == null ? "SELECT * FROM _4" : "SELECT * FROM _4 WHERE " + filterCondition;
            connection.Open();
            SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
            DataTable dataSource = new DataTable();
            adapter.Fill(dataSource);
            dataGridView2.DataSource = dataSource;

            using (SqlCommand command2 = new SqlCommand("SELECT * FROM [_4] WHERE CIVILIDD = @id", mycon))
            {

                command2.Parameters.AddWithValue("@id", ID_No.Text);

                SqlDataReader dr = command2.ExecuteReader();





                while (dr.Read())
                {

                    txtname1.Text = (dr["name1"].ToString());
                    txtname2.Text = (dr["name2"].ToString());
                    Governorate.Text = (dr["Governorate"].ToString());
                    City.Text = (dr["City"].ToString());
                    Block.Text = (dr["Block"].ToString());
                    Street.Text = (dr["Street"].ToString());
                    Avenue.Text = (dr["Avenue"].ToString());
                    House.Text = (dr["House"].ToString());
                    Floor.Text = (dr["Floor"].ToString());
                    flat.Text = (dr["flat"].ToString());
                }
            }

        }

i use this code to filter my database and i get that error when i run it in the first time it work good in the second time i get this error

System.InvalidOperationException: 'There is already an open DataReader associated with this Command which must be closed first.'

in this line SqlDataReader dr = command2.ExecuteReader();

Answer 1

You will need to close the datareadre after you use it.

This is how your code should look like when you are not using Using()

Se the comment to understand

var filterConditions = new[] {
       CreateSqlFilter("CIVILIDD", ID_No, selectCommand, false),


};

        string filterCondition = filterConditions.Any(a => a != null) ? filterConditions.Where(a => a != null).Aggregate((filter1, filter2) => String.Format("{0} AND {1}", filter1, filter2)) : (string)null;

        using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["my"].ConnectionString))
        {
            selectCommand.Connection = connection;
            selectCommand.CommandText = filterCondition == null ? "SELECT * FROM _4" : "SELECT * FROM _4 WHERE " + filterCondition;
            connection.Open();
            SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
            DataTable dataSource = new DataTable();
            adapter.Fill(dataSource);
            dataGridView2.DataSource = dataSource;

            using (SqlCommand command2 = new SqlCommand("SELECT * FROM [_4] WHERE CIVILIDD = @id", mycon))
            {

                command2.Parameters.AddWithValue("@id", ID_No.Text);

                SqlDataReader dr = command2.ExecuteReader();





                while (dr.Read())
                {

                    txtname1.Text = (dr["name1"].ToString());
                    txtname2.Text = (dr["name2"].ToString());
                    Governorate.Text = (dr["Governorate"].ToString());
                    City.Text = (dr["City"].ToString());
                    Block.Text = (dr["Block"].ToString());
                    Street.Text = (dr["Street"].ToString());
                    Avenue.Text = (dr["Avenue"].ToString());
                    House.Text = (dr["House"].ToString());
                    Floor.Text = (dr["Floor"].ToString());
                    flat.Text = (dr["flat"].ToString());
                }
                 // Close and Dispose the datareader
                 dr.Close();
                 dr.Dispose();
            }

        }

Answer 2

You have to dispose the first datareader that the adapter creates. The using statement will take care of the disposal of resources out of the box.

You can use associate a data reader with one command only (just as the error states). Create 2 using statements and prepare 2 datareaders. This can be in the same using( connection ) because a connection can be used for multiple commands, but you have to have separate datareaders for each command.

using (SqlDataAdapter a = new SqlDataAdapter("SELECT * FROM EmployeeIDs", c))
{
}

using( ..command2.. )
{
 SqlDataReader dr = command2.ExecuteReader();
}

Read more about there HERE