You need to have two nested data readers, and this requires the ADO.NET “MARS” feature – Multiple Active Result Sets.
This is available as of ADO.NET 2.0, and requires a specific setting (
MultipleActiveResultSets=true;) in the connection string:
Server=.\SQLEXPRESS;Database=master;Integrated Security=SSPI; MultipleActiveResultSets=true;
See this blog post for an excellent discussion.
Once you have this, you should be able to have more than one
SqlDataReader shared on the same
SqlConnection in your code, and use them independently of one another.
UPDATE: this blog post here mentions that the MARS feature is not available inside the SQL CLR environment 😦 So that won’t work inside a SQL CLR stored proc…