Want to Execute Multiple Queries on a Single Connection? Go to MARS

November 24, 2004

[From developer.com]

Multiple Active Result Sets (MARS) is a new feature released with ADO.NET 2.0 that allows you to execute multiple queries or stored procedures on a single connection. The result is multiple forward-only, read-only result sets. In previous versions of ADO.NET, you could execute only one query at any given time using a single connection. Now, with the introduction of SQL Server 2005 and ADO.NET 2.0, you very easily can execute multiple logical batches in a single connection. This article demonstrates how to utilize MARS in an ASP.NET Web page. It also discusses the situations in which MARS is appropriate and can provide performance benefits.

Welcome to MARS

If you use DataReader in your applications, you already might have seen the dreaded error message: "There is already an open DataReader associated with this Connection which must be closed first." MARS allows you to avoid this message by allowing you to open multiple SqlDataReader objects on a single connection. MARS enables an application to have more than one SqlDataReader open on a connection when each instance of SqlDataReader is started from a separate command. Each SqlCommand object you add adds an additional session to the connection.

The article continues at http://www.developer.com/db/article.php/3440161