CODEWITHSUNDEEP
c#.netsqlsql-serversql-server-2005
Chong
Chong

Reputation: 604

Open DataReader error with SqlConnection

In my project,i have one function to retrieve data from Sql2005 Server like this,

SqlConnection sqlCnn = new SqlConnection("");
SqlCommand sqlCmd = new SqlCommand("Select user_id from users");
SqlDataReader sqlReader = null;

sqlCnn.Open();
sqlCmd.Connection = sqlCnn;
sqlReader = sqlCmd.ExecuteReader();

if (sqlReader.HasRows)
{
    while (sqlReader.Read())
    {
        if(user_id == 1)
        {
            SqlCommand sqlCmd2 = new SqlCommand("Select mobile from tbl");
            sqlCmd2.Connection = sqlCnn;
            sqlCmd2.ExecuteReader();
        }
    }
}

I got the following error when sqlcmd2 is executed.

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

I don't want to create a new sqlconnection at there. Is there any way to solve this problem?

Upvotes: 2

Views: 2079

Answers (4)

Zenwalker
Zenwalker

Reputation: 1919

In .net 4, u dont need check hasrows condition. Reader.Read() method returns true/false based on table contents. Just checked with empty table. Worked!!

Upvotes: 0

Alex Aza
Alex Aza

Reputation: 78487

To open two readers per connection you need to enable MARS (multiple active result sets). You can do this via connection string:

 Server=myServerAddress;Database=myDataBase;Trusted_Connection=True; MultipleActiveResultSets=true;

However, it is not recommended approach.
I'm sure you can retrieve the data you need with one query.

You need to explain what you need to retrieve, if you want and advice on a single query.
I assumed that the code you showed just a sample, not a real one, as it does not make much sense.

Look at 'Is MARS all good news, or is there any downside?' section here.

Upvotes: 3

Joel Coehoorn
Joel Coehoorn

Reputation: 416111

The best way to solve this is to join the two tables together at the database. That should give you at least an order of magnitude performance increase, especially if there's an index on the appropriate column(s).

Data structure doesn't enter into this; it's all in how you order the results and how you go about reading them. Done correctly, you can read that into any data structure imaginable.

But if that's not an option, make sure you're using at least Sql Server 2005 and add MultipleActiveResultSets=true to your connection string.

And while you're at it, your existing code is broken and can eventually lead to your database being unavailable because you are not guaranteed to close your connections in the case where an exception occurs. To solve this, create your SqlConnection object inside a using block.

Upvotes: 3

Michael Petito
Michael Petito

Reputation: 13161

If you must not create a second SQL connection, then you could enable MARS (Multiple Active Result Sets).

Why do you not want a second connection?

Upvotes: 1

Related Questions