CODEWITHSUNDEEP
c#sqlt-sql
user4451265
user4451265

Reputation:

There is already an open DataReader associated with this Command which must be closed first. C#

When I start debugging that error shows, and it associted with the line:

textBox1.Text = cmd.ExecuteReader().ToString();

private void Form1_Load(object sender, EventArgs e)
{
    SqlConnection conn = new SqlConnection(@"server= M_SHAWAF\ORCHESTRATE; integrated security=true; database=MyData"); 

    try
    {
        conn.Open();

        SqlCommand cmd = new SqlCommand();
        cmd = new SqlCommand(@"select MAX(Nodelevel) from Org", conn);
        int s = Int32.Parse(cmd.ExecuteScalar().ToString());
        for (int i = 0; i <= s; i++)
        {
            cmd = new SqlCommand(@"select Name from Org where NodeLevel=" + i.ToString(),conn);
            textBox1.Text = cmd.ExecuteReader().ToString();
        }

    }


    catch (SqlException ex)
    {
        MessageBox.Show(ex.Message);
    }
    finally
    {
        conn.Close();
    }

}

How can I fix that??

Upvotes: 0

Views: 2433

Answers (3)

StuartLC
StuartLC

Reputation: 107357

You don't need to continually execute readers in order to obtain the next row of data. If all you need to do is to iterate through all row values of Name from table Org, you can execute a single Sql query to return all rows into the reader, and then to traverse the reader, e.g.:

    try
    {
        using (var conn = new SqlConnection(@"..."))
        {
            conn.Open();

            using (var cmd = new SqlCommand(@"select Name from Org", conn))
            {
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        textBox1.Text = reader["Name"].ToString();
                    }
                }
            }
        }
    }
    catch (SqlException ex)
    {
        MessageBox.Show(ex.Message);
    }

Edit, Re Hierarchical table structures

If you do need to retain separate iterators while navigating through multiple levels of a hierarchy, you will need multiple readers. As per @Philips answer, in order to have more than one active result set per SqlConnection, you'll need to enable MARS (or open multiple connections).

try
{
    using (var conn = new SqlConnection(@"...;MultipleActiveResultSets=True"))
    using (var cmdOuter = new SqlCommand(@"select distinct NodeLevel from Org", conn))
    {
        conn.Open();
        using (var outerReader = cmd.ExecuteReader())
        {
            while (outerReader.Read())
            {
                var nodeLevel = reader.GetInt32(0);
                Console.WriteLine("Node Level {0}", nodeLevel);
                using (var cmdInner = new SqlCommand(@"select Name from Org WHERE NodeLevel = @NodeLevel", conn))
                {
                    cmdInner.Parameters.AddWithValue("@NodeLevel", nodeLevel);
                    using (var innerReader = cmdInner.ExecuteReader())
                    {
                        while (innerReader.Read())
                        {
                            Console.WriteLine("Name: {0}", innerReader.GetString(0));
                        }
                    }
                }
            }
        }
    }
}
catch (SqlException ex)
{
    MessageBox.Show(ex.Message);
}

Upvotes: 3

paparazzo
paparazzo

Reputation: 45096

Reader is the wrong tool
And you do have an open reader 

textBox1.Text = cmd.ExecuteScalar();

Upvotes: 0

Philip Stuyck
Philip Stuyck

Reputation: 7467

modify your connection string to allow multiple results :

connectionString="Data source=localhost; initial catalog=Interstone; integrated security=True; multipleactiveresultsets=True;"

scroll to the right for the right information ;-)

But there are lots of alternatives to avoid needing multiple queries at the same time. Each query you issue that is still pending is a resource that is being used at the server which should be minimized.

So first consider algorithms that don't require multiple cursors and if there is no alternative then setup mars.

Upvotes: 2

Related Questions