C# and many SQLDataReaders

[Guest]

hi,
I need to use a few SQLDataReader objects in my application. I need to do
something like that:

System.Data.SqlClient.SqlConnection conn = new SqlConnection(string);
conn.Open();
System.Data.SqlClient.SqlCommand com = new SQLCommand(string, conn);
SqlDataReader data1 = com.ExecuteReader();
while(data1.Read())
{
//read a new SQLDataReader
}
conn.Close();


the problem is in the loop. I need to read a new datareader for each data in
data1

the problem is that I must create a new connection to database and associate
that with a new sqldatareader. It is stupid - for any new sqldatareader a new
connection to db must be established.

If I try to do:

com.CommandText="new statement";
SQLDataReader data2 = com.ExecuteReader();

I am thrown the error (something like this):
There is already an open dataset associated with this connection. Close this
first

 

[Marc Gravell]

Unless your connection supports multiple readers (SQL-Server 2005?),
then do what it says and use a second connection. Alternatively,
arrange your data so that it can all be read with a single reader; e.g.
return one grid with both the parent and child data (switching parent
when you spot e.g. a primary key change value), or return multiple
grids, one per parent; personally, I tend to return a single parent
grid and a single child grid, e.g.)

ParentID, ParentColumn1, ParentColumn2,...
10012,x,x,x
12041,x,x,x

ParentID, ChildID, ChildColumn1, ChildColumn2,...
10012,14411,y,y,y
10012,14134,y,y,y
12041,12452,y,y,y

etc

DataReaders can almost all handle multiple grids.

Marc