Call SqlCommand.ExecuteReader inside another SqlCommand.ExecuteRea

[Guest]

Hi;

I need to call SqlCommand.ExecuteReader and then inside a while
(reader.Read()) I need to call another SqlCommand.ExecuteReader. I need to do
this because I have a FKK relationship and based on the FK read, then read
the row in that related table - if the FK is not null.

How can I do this?

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Guest]

ps - with all of this within the same transaction.

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Stephany Young]

Any SqlConnection object can only have one active SqlDataReader at a time.

To do what you need to do you need to have a second SqlConnection object for
the inner SqlDataReader.

I do not think that a 'transaction' can span multiple connections.

It appears that you are only 'reading' so there does not appear to be any
need for a transaction.

 

[Guest]

Hi;

I need the transaction because otherwise I can end up with two objects that
do not match. There must be a way to do this as it's pretty basic and I think
a lot of people would need this.

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Stephany Young]

Que?

Do you mean that you are concerned that a row of interest in table2 could be
updated after you execute the select on table1 but before you execute the
inner select on table2?

Try, using one SqlDataReader

begin transaction;
select * from table1;
select * from table2 where pk in (select fk from table1);
end transaction;

When you have finished cycling through the SqlDataReader (table1) then
execute it's NextResult method and cycle through it again for table2.

You can do it one way or you can do it the other way, but you can't do it
both ways at once.

 

[Guest]

Unfortunately we have N rows for the first select so that approach is a mess.
We can do it if we have to, but I am hoping someone here knows of an
alternative.

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Stephany Young]

That's the whole idea.

For the second select you get all the rows from table2 for all the rows
selected in table1.

It's not rocket science to write a couple of lines of code to match them up.

 

[Miha Markic [MVP C#]]

Hi David,

Why don't you create a stored procedure instead?
You can also check out MARS (Multiple Active Result Sets) capability - I am
not sure whether it will help you or not.

 

[WenYuan Wang]

Hi Dave,

Thanks Miha and Stephany,
In ADO.net 2.0, Multiple Active Result Sets (MARS) is a new feature that
works with SQL Server 2005 to allow the execution of multiple batches on a
single connection. I think this is maybe what you need.

You can get more detailed information from the following website.
http://msdn2.microsoft.com/en-us/library/cfa084cz.aspx

Please feel free to reply me if you have any further questions or concerns
on this, and we will follow up.
I'm glad to work with you.

Hope this will help!
Best Regards,
Wen Yuan
Microsoft Online Community Support
===============================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
===============================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Guest]

That's what I need - thanks

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm