Using Multiple Active Result Sets in ASP.NET 2

If you have a SqlConnection connecting to a SQL Server database, and you want to do this:

• open the connection
• create a SqlCommand on this connection to select data
• read this data using a SqlDataReader
• create a second SqlCommand on this connection
• read the second set of data
• close the connection

then you will encounter an error message saying:

There is already an open DataReader associated with this Command which must be closed first

The reason for the error is that only a single DataReader can be open on a single SqlCommand on one connection. The solution to this problem is to enable Multiple Active Result Sets (known as "MARS") on the SqlConnection.

To enable MARS simply add the parameter "MultipleActiveResultSets=true;" to your database connection string. This is a new feature of ASP.NET 2 which allows multiple database queries on a single database connection. The big advantage of this is that you don't need multiple database connections for multiple queries, which does two things:

1. It improves performance because only one connection needs to be opened, saving memory.
2. It makes your code simpler / easier to read.

Here is an example of MARS in action:

con = new SqlConnection(connectString);
con.Open();


string sql = @"select x from y";
SqlCommand command = new SqlCommand(sql, con);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    // Do something
}
reader.Close();

sql = @"select a from b";
command = new SqlCommand(sql, con);
reader = command.ExecuteReader();
while (reader.Read())
{
    // Do something else
}
reader.Close();
con.Close();

As you can see, only one connection is opened, but two sets of data are read from it. So that's it, the power of MARS. Enjoy!

P.S. It only works for SQL connections, not MS Access ones.