Why can't open the second DataReader ?

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
Sun Oct 26 10:13:27 CST 2003

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



"jiatiejun" <jiahome@263.net> wrote in message
news:eyMjho9mDHA.2732@TK2MSFTNGP11.phx.gbl...
> 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?
>
>

David
Sun Oct 26 13:40:39 CST 2003

In article <OTkEfw9mDHA.1004@TK2MSFTNGP09.phx.gbl>, Nicole Calinoiu wrote:
> 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.

--
David
dfoster at
hotpop dot com

Nicole
Sun Oct 26 13:48:54 CST 2003

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" <dfoster@127.0.0.1> wrote in message
news:slrnbpo8po.lg7.dfoster@woofix.local.dom...
> In article <OTkEfw9mDHA.1004@TK2MSFTNGP09.phx.gbl>, Nicole Calinoiu wrote:
> > 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.
>
> --
> David
> dfoster at
> hotpop dot com

David
Sun Oct 26 17:30:05 CST 2003

In article <OZ$k0o$mDHA.3288@tk2msftngp13.phx.gbl>, Nicole Calinoiu wrote:
> 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...


--
David
dfoster at
hotpop dot com

Nicole
Mon Oct 27 07:56:33 CST 2003

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.


"David" <dfoster@127.0.0.1> wrote in message
news:slrnbpom80.m2f.dfoster@woofix.local.dom...
> In article <OZ$k0o$mDHA.3288@tk2msftngp13.phx.gbl>, Nicole Calinoiu wrote:
> > 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...
>
>
> --
> David
> dfoster at
> hotpop dot com