Gokul's Blog

Multiple data readers in one sql connection

Leave a comment

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 sameSqlConnection 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…
http://stackoverflow.com/questions/2355801/sqldatareader-inside-sqldatareader/2355827#2355827

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s