Hi there, As mentioned above you can't nest a datareader within another datareader.
However, what you are wanting to do is very easily accomplished with a redesign to your sql statement.
Rather than selecting one set of data, and returning it to database to be used in another query, you could use a JOIN statement in your sql code.
Say I have 2 tables. Each table has related data and share a primary key. Lets use the following tables and columns:
Table1
Id (uniqueidentifier)
FirstName (nvarchar(MAX))
Table2
Id (uniqueidentifier)
LastName (nvarchar(MAX))
Now to do an SQL select statement where I can gather all rows that intersect by using an INNER JOIN on the tables:
SELECT TB1.Id, TB1.FirstName, TB2.LastName
FROM dbo.Table1 AS TB1
INNER JOIN dbo.Table2 AS TB2
ON TB1.Id = TB2.Id
This will get all rows that share a primary key between the 2 tables. Basically, it creates a virtual table that simulates combining the 2 tables together on the primary key.
the TB1 and TB2 are just aliases we create for the tables so that we can tell the query engine what table each column we are requesting is located in. This is to resolve any ambiguous calls between data.
And when you are gathering the data from the datareader object, you just access the column names in the same way you normally would (do not use the appended table aliases from in the sql but rather, just the column name as normal)
Hope this helps :)
When the going get's tough, the tough outsource and take a vacation... lol I wish :(
lionelsiu
Member
30 Points
38 Posts
SQLReader in a SQLReader
Apr 24, 2012 07:39 PM|LINK
Is it possible to have a SQLreader within a SQLreader, without opening another connection? Thank you!
... OleDbDataReader SqlReader = cmd.ExecuteReader(); while (sqlReader.Read()){ OleDbDataReader SqlReader2 = cmd.ExecuteReader(); }adamturner34
Contributor
4394 Points
1102 Posts
Re: SQLReader in a SQLReader
Apr 24, 2012 07:41 PM|LINK
No and why would you want to create a cursor in the first place?
Write proper T-SQL.
Chen Yu - MS...
All-Star
21829 Points
2513 Posts
Microsoft
Re: SQLReader in a SQLReader
Apr 27, 2012 07:58 AM|LINK
Hi,
Would you please tell us what do you want to finish by above code?
Thanks.
Feedback to us
Develop and promote your apps in Windows Store
lionelsiu
Member
30 Points
38 Posts
Re: SQLReader in a SQLReader
May 01, 2012 05:44 AM|LINK
The first SqlReader is to find IDs from TABLE1 ( There can be no ID, 1 ID or more than 1 ID).
Then, the second SqlReader will use the IDs from TABLE1:
SELECT * FROM TABLE2 WHERE TABLE1.ID = TABLE2.ID
for each ID, there will be no. of records, I want to find the top 2 records group by each ID.
Finally, the result should display top 2 records for each ID.
Thank you.
magicmike201...
Contributor
2021 Points
481 Posts
Re: SQLReader in a SQLReader
May 01, 2012 06:35 AM|LINK
Hi there, As mentioned above you can't nest a datareader within another datareader.
However, what you are wanting to do is very easily accomplished with a redesign to your sql statement.
Rather than selecting one set of data, and returning it to database to be used in another query, you could use a JOIN statement in your sql code.
Say I have 2 tables. Each table has related data and share a primary key. Lets use the following tables and columns:
Table1
Id (uniqueidentifier)
FirstName (nvarchar(MAX))
Table2
Id (uniqueidentifier)
LastName (nvarchar(MAX))
Now to do an SQL select statement where I can gather all rows that intersect by using an INNER JOIN on the tables:
SELECT TB1.Id, TB1.FirstName, TB2.LastName FROM dbo.Table1 AS TB1 INNER JOIN dbo.Table2 AS TB2 ON TB1.Id = TB2.IdThis will get all rows that share a primary key between the 2 tables. Basically, it creates a virtual table that simulates combining the 2 tables together on the primary key.
the TB1 and TB2 are just aliases we create for the tables so that we can tell the query engine what table each column we are requesting is located in. This is to resolve any ambiguous calls between data.
And when you are gathering the data from the datareader object, you just access the column names in the same way you normally would (do not use the appended table aliases from in the sql but rather, just the column name as normal)
Hope this helps :)
lionelsiu
Member
30 Points
38 Posts
Re: SQLReader in a SQLReader
May 05, 2012 11:52 AM|LINK
Thank you for your kindly reply.
It should help me to think further.