current community

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 5 down vote favorite
1

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.

share|improve this question
 
Are you using the same connection with the previous SqlDataReader? Have you tried creating a new one? –  Thanos Papathanasiou Mar 1 '10 at 12:48
 
yes sure. i tried to create a new one, the thing is that i am using clr, it works when i deploy but when i use the stored procedure in my application an excepion occurs... –  scatman Mar 1 '10 at 12:51
add comment

2 Answers

up vote 10 down vote accepted

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

share|improve this answer
 
does this also work if i am using clr and stored procedures? –  scatman Mar 1 '10 at 12:53
 
yes, it definitely should (as long as they're using the same connection, e.g. an identical connection string - identical down to the last comma and whitespace!) –  marc_s Mar 1 '10 at 12:56
 
well it did not work when using clr. after i created my stored procedure, i tried to test it on MS-SQL and an exepction occured which stated that: System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first. –  scatman Mar 2 '10 at 6:45
 
@scatman: yes, unfortunately, that doesn't seem to be be supported - see my update..... sorry –  marc_s Mar 2 '10 at 7:51
add comment
up vote 4 down vote

The issue you're encountering is that you are trying to open multiple data readers against the same db connection. By default, this will give you an exception as it will say there's already one associated with the connection - as you've seen.

You could make use of MultipleActiveResultsets, whereby from .NET 2.0 & SQL Server 2005 onwards, you can specify an extra option in the connection string to enable multiple active resultsets against a single db connection by adding:

MultipleActiveResultSets=True;

The alternative is to use a different connection to open the inner data reader against.

Or even, it may be possible to rethink your original approach - maybe there's a way to do it without nested readers.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.