MARS- Multiple Active Result Sets is a feature supported in sqlserver 2005. In this method having a single connection to the 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 within a single connection to the database, it's possible to retrieve all data. This helps your application performance and code management greatly.

In the previous methods each sqlconnection object must be disposed of correctly and datareaders associated with each query must be disposed of. 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.

Let's 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 of MARS.

using System.Data;  
using System.Data.SqlClient;  

public class MARSdemo {  
Public static void Main() {  
    String MarsConnectionStr = "Data Source=TestServer;" + "Initial Catalog=Pubs;IntegratedSecurity=SSPI;" + "MultipleActiveResultSets=True";  
    string Mars_Command_Str1 = "select * from Salary;";  
    string Mars_Command_Str2 = "select * from Employees;";  

    SqlCommand cmd1 = new SqlCommand(Mars_Command_Str1, MarsConnectionStr);  
    SqlCommand cmd2 = new SqlCommand(Mars_Command_Str1, MarsConnectionStr);  
    SqlDataReader rdr1 = null;  
    SqlDataReader rdr2 = null;  
    try {  
        con.Open();  
        rdr1 = cmd1.ExecuteReader();  
        rdr2 = cmd2.ExecuteReader();  
    } catch (SqlException ex) {} finally {  
        if (con.State == ConnectionState.Open) {  
            con.Close();  
        }  
        rdr1.Close();  
    }  
}  
}

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 a multiplexed enabled statement. These multiplexed enabled statements can pause its execution in between and allow a non-multiplexed statement to execute like an INSERT statement.

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

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

HostForLIFE.eu SQL Server 2019 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.