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.
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();
}
}
}