Introduction

MARS- Multiple Active Result Sets is a feature supported in sqlserver 2005. In this method having a single connection to database, our applications can execute.

Multiple queries and stored procedures and divides each result of a query or stored procedure as active results. These results are forward-only, read-only data. Previous to MARS, it was required multiple database connections to retrieve data for a separate query or stored procedure, but with in a single connection to database its possible to retrieve all data. This helps your application performance and code management greatly.

In the previous methods each sqlconnection object must be disposed correctly and datareaders associated with each query must be disposed. Otherwise it will lead to some errors like "There is already an open DataReader associated with this Connection- error".

With the introduction of MARS in Sqlserver2005 bye bye to all these problems and performance bottlenecks.

Lets start the Implementation



Settings

You must change your connection string as follows :

String connectionString = "Data Source=TestServer;" +"Initial Catalog=Pubs;IntegratedSecurity=SSPI;" + "MultipleActiveResultSets=True";

MARS is disabled by default on the Connection object. You have to enable it with the addition of MultipleActiveResultSets=true in your connection string.

Program:

This c# code snippet demonstrates the use MARS.

    }
}

If you try to execute this code with sqlserver 2000 or lower versions, u will get an InvalidOperationException and the following error message.

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

MARS works on the concept of "Multiplexing" and "interleaving". Sql's Select statement is multiplexed enabled statement. These multliplexed enabled statements can pause its execution in between and allow a non-multiplexed statement to execute like INSERT statement.

For example consider you are retrieving 1 million of records using select statement and in between a insert query comes via a MARS enabled application under the same connection object, then select statement pauses until this insert operation completes and afterwards resumes execution.

Note: Managed code (stored procedures, functions, triggers) can't be multiplexed.