Nestled SqlCommand

[MattTC]

I need to nestle two SqlCommand calls but I get the exception 'System.InvalidOperationException' and it says that "There is already an open DataReader associated with this Command which must be closed first."

How should I code this one? Here follows what I´ve been trying so far:

SqlConnection ^conn = gcnew SqlConnection;
conn->ConnectionString = "Data Source=localhost\\SQLEXPRESS;"
"Initial Catalog=Test;Integrated Security=SSPI;";
try
{
conn->Open();
SqlCommand ^cmdA = gcnew SqlCommand();
cmdA->Connection = conn;
cmdA->CommandType = CommandType::Text;
cmdA->CommandText = (String^)"SELECT * FROM mytable1";
SqlDataReader ^readerA = cmdA->ExecuteReader();

while(readerA->Read())
{
SqlCommand ^cmdB = gcnew SqlCommand();
cmdB->Connection = conn;
cmdB->CommandType = CommandType::Text;
cmdB->CommandText = String::Format("SELECT * FROM mytable2 "
"WHERE mytable2.id= '{0}'",(int)readerA["id"]);
SqlDataReader ^readerB = cmdB->ExecuteReader(); // HERE COMES THE EXCEPTION!
if(readerB->Read())
{
...
}
}
}
catch(...)
...

[darwen]

Use an SQL join on the 2 tables instead. This will be MUCH more efficient than doing it the way you're trying to (on client) anyway.

Darwen.

[MattTC]

Ok, I can see that, but what if I need to do something like this? The example could be a lot more complex. I sure should be able to use another SqlCommand while processing the first one, shouldnt I?

[darwen]

No you can't on the same connection. You could open another connection though.

The usual way of doing this is to load all data from the first select into memory then execute the second select i.e. don't have a nested loop.

However 99.9% of the time you don't need to - because you get SQL server to do the matching for you and return the dataset which you require.

Darwen.

[MattTC]

Ok, that was what I wanted to know. Thankyou.