Why can't open the second DataReader ?

[jiatiejun]

our C# program:


SqlCommand cmd = new SqlCommand();
SqlCommand cmd2 = new SqlCommand();
cmd.Connection = this.sqlConnection1;
cmd2.Connection = this.sqlConnection1;

cmd.CommandText = "select * from table1";
dr = cmd.ExecuteReader();
while(dr.Read())
{
cmd2.CommandText = "select * from table2 where myid=" +
dr[0].ToString();
dr2 = cmd2.ExecuteReader();

.... ... ... ...


the cmd.ExecuteReader() can open DataReader,
but cmd2.ExecuteReader can't

why?

 

[Nicole Calinoiu]

A datareader uses a fire-hose cursor, which blocks the connection. While dr
is still open against sqlConnection1, you cannot use sqlConnection1 for
anything else, including the execution of cmd2. You basically have three
options:

1. Use separate connection objects,
2. Use a dataset, filling one table with the results from cmd and another
with the results from cmd2 (and setting up an appropriate datarelation
between the two datatables), or
3. Change your application logic to not require that both datareaders be
open at the same time.

#1 would require the smallest change to your code, but #2 is probably the
best approach if you can handle the change.

HTH,
Nicole

 

[David]

A datareader uses a fire-hose cursor, which blocks the connection. While dr
is still open against sqlConnection1, you cannot use sqlConnection1 for
anything else, including the execution of cmd2. You basically have three
options:

<snip>

or
4. Use a single DataReader
cmd.CommandText = "SELECT table1.*, table2.* FROM table1 LEFT JOIN
table2 on table1.my_id = table2.my_id ORDER BY table1.my_id";

// or possibly INNER JOIN depending on your needs

And then check for a change in my_id when you loop through the records.

I agree with Nicole, though, a DataSet is probably your best bet here.
In any case, requiring a new round trip to the database on every
iteration of the loop is probably A Bad Thing here.

 

[Nicole Calinoiu]

David,

I didn't suggest the join because of the potentially large volume of
redundant data that could be involved. The dataset is supposed to help us
avoid this sort of thing and, except in some very "particular"
circumstances, there's little reason to even consider the join alternative.
Of course, it would still be much better than the looping db round-trip...
<g>

Nicole

 

[David]

David,

I didn't suggest the join because of the potentially large volume of
redundant data that could be involved. The dataset is supposed to help us
avoid this sort of thing and, except in some very "particular"
circumstances, there's little reason to even consider the join alternative.
Of course, it would still be much better than the looping db round-trip...
<g>

Except that one of the reasons for using the DataReader in the first
place would be because there's huge amounts of data involved, too much
to bring down in a disconnected DataSet. Of course, I have know idea if
that concern is applicable at all here...

 

[Nicole Calinoiu]

I guess we were just making some very different assumptions about what the
purpose of the sample code might be. My assumption was that it's probably
for the generation of a nested tabular UI, which is what 99% of similar code
I've seen or written does, in which case memory had better not be an issue.
If the 'puter can't handle the amount of data a human can read via the UI,
blocking of connections is the least of one's worries... <g> Since all the
data comes from the same db, the entire operation probably belongs in the db
if this is pure data manipulation that doesn't involve the retrieved data
being displayed to the user.