C# – Re-open SqlDataReader

ado.netcexception handlingnetsqldatareader

My code throws the error

Invalid attempt to call Read when reader is closed.

I'm using SqlDataReader to read values from database, and that is my code:

while (rd.Read())
{
    string stateincharge = rd["stateincharge"].ToString();
    string email = rd["email"].ToString();
    cmd.Dispose();
    rd.Close();
    cmd = con.CreateCommand();
    cmd.CommandText = "str_procedure";
    cmd.Parameters.AddWithValue("@stateincharge", stateincharge);
    cmd.CommandType = CommandType.StoredProcedure;
    ad.SelectCommand = cmd;
    ad.Fill(ds);
    count = ds.Tables[0].Rows.Count;
    DataTable dt = new DataTable();
}

This runs in a loop in ASP.NET code-behind.

My problem is that I think I need to close SqlDatReader because of an error shown.

How can I again open sqlDataReader at end of the while loop?

Best Answer

// connection for reader
using (SqlConnection connection1 = new SqlConnection(connectionString))
using (SqlCommand command1 = new connection1.CreateCommand())
{
    command1.CommandText = commandText1;

    connection1.Open();
    using (SqlDataReader reader = command1.ExecuteReader())
    {
        // fill table in loop
        while (reader.Read())
        {
            string stateincharge = reader["stateincharge"].ToString();
            string email = reader["email"].ToString();

            // connection for adapter
            using (SqlConnection connection2 = new SqlConnection(connectionString))
            using (SqlCommand command2 = new connection2.CreateCommand())
            {
                command2.CommandText = commandText2;

                command2.Parameters.AddWithValue("@stateincharge", stateincharge);
                command2.Parameters.AddWithValue("@email ", email );

                connection2.Open();

                DataTable table = new DataTable();
                using (SqlDataApapter adapter = new SqlDataAdapter(command2))
                {
                    adapter.Fill(table);
                    // yield return table;
                }
            }
        }
    }
}
Related Topic