SqlDataReader inside SqlDataReader

c# sql-server sqldatareader
scatman picture scatman · Mar 1, 2010 · Viewed 18.2k times · Source

How can I implement a SqlDataReader inside another SqlDataReader?

My problem is I have a SqlDataReader. I am issuing while (reader.read()) and inside the while loop I have to create another SqlDataReader to read from the database. But I'm getting exceptions about connection being already open.

So what's the best way to solve my problem?

Edit:

I am using clr to create my stored procedures. I tried to put MultipleActiveResultSets=true; within the connection string of both the clr and the project, and an exception occurred when I tested my stored procedure on SQL Server:

System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.

Answer

marc_s picture marc_s · Mar 1, 2010

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....

Related questions

Is there anything faster than SqlDataReader in .NET?

I need to load one column of strings from table on SqlServer into Array in memory using C#. Is there a faster way than open SqlDataReader and loop through it. Table is large and time is critical. EDIT I am …

Read More
SqlDataReader - How to convert the current row to a dictionary

Is there an easy way to convert all the columns of the current row of a SqlDataReader to a dictionary? using (SqlDataReader opReader = command.ExecuteReader()) { // Convert the current row to a dictionary } Thanks

Read More
How to get nullable DateTime out of the database

My SQL Server database contains nullable DateTime values. How can I convert them to a nullable DateTime object in my application in C#? This is what I would think it would look like, but it doesn't: DateTime? dt = (DateTime?) sqldatareader[0];

Read More