Use Multiple Active Result Sets(MARS) to Execute Multiple Commands on a Connection in ADO.net

In ADO.net, if you try to execute another command while the first command is executing,thre will have an InvalidOperationException: "There is already an open DataReader associated with this Connection which must be closed first".

We can use Mutilple Active Result Sets(MARS) to solve this issue.

The only change is in the connection string:

string constring=@"Data Source=....; MultipleActiveResultSets=True";

So in our code, we can write:

SqlConnection con=new SqlConnection(constring);
SqlCommand cmd1=....;
SqlCommand cmd2=...;
con.Open();
SqlDataReader reader=cmd1.ExecuteReader();
while(reader.Read())
{
cmd2.ExecuteScalar();
....
}
con.Close();

Notice that MARS will affect performace negatively. So if we don't need use MARS we shouldnot use it.

But in the following case MARS benefits:
1. Like the above example, if we don't use MARS, we open connection twice. That is we open the connection, loop the collection and read the results to a collection. Close the connection. Open the collection again do the second sqlcommand. If we don't use MARS we will loop the collection twice which is not efficient.
2. Some times it is expensive to create two connections. Some third party may charge twices for 2 connections.