Today, Aneesh (the guy who blogged about SQL Encryption few months back in dot net thoughts), told me about the Multiple Active Result Sets (MARS), a new option available in SQL Server 2005, which helps developers to maintain Multiple active Statements on a connection. When using SQL Server default result sets, the application had to process or cancel all result sets from one batch before it could execute any other batch on that connection. SQL Server 2005 introduced a new connection attribute that allows applications to have more than one pending request per connection, and in particular, to have more than one active default result set per connection.
The MARS feature is disabled by default. It can be enabled by adding the “MultipleActiveResultSets=True” keyword pair to your connection string, as below:
string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI;MultipleActiveResultSets=True";
Without MARS
string sql1 = "SELECT * FROM gotMenus";
string sql2 = "SELECT * FROM gotUsers";
using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=GotCms;Integrated Security=SSPI;"))
{
conn.Open();
using (SqlCommand cmd1 = new SqlCommand(sql1, conn))
{
SqlDataReader dr1 = cmd1.ExecuteReader();
}
using (SqlCommand cmd2 = new SqlCommand(sql2, conn))
{
SqlDataReader dr2 = cmd2.ExecuteReader();
}
}
The above code will throw an invalid operation exception(There is already an open DataReader associated with this Command which must be closed first),unless you close the dr1 using dr1.Close();
Or you can modify the connection string like the below.
With MARS
string sql1 = "SELECT * FROM gotMenus";
string sql2 = "SELECT * FROM gotUsers";
using(SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=GotCms;Integrated Security=SSPI;MultipleActiveResultSets=True"))
{
conn.Open();
using (SqlCommand cmd1 = new SqlCommand(sql1, conn))
{
SqlDataReader dr1 = cmd1.ExecuteReader();
}
using (SqlCommand cmd2 = new SqlCommand(sql2, conn))
{
SqlDataReader dr2 = cmd2.ExecuteReader();
}
}
It will work fine without any issue.
You can get more detailed information about MARS from these links
http://technet.microsoft.com/en-us/library/ms345109
(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms131686.aspx
Tip : The server connection attribute, used in SQL Server connection string is optional. Only Database name and Security attributes required as mandatory for establishing a connection to SQL Server.
