Articles → ADO.NET → MARS In Ado.Net

MARS In Ado.Net

MARS (Multiple Active Result Sets) is for executing multiple queries and stored procedure in a single connection.

When you use multiple data reader in your application, you have encountered the error

There is already an open Data Reader associated with this Command which must be closed first.

If you enable MARS then you can use multiple data readers in your application.

You can enable or disable MARS in your connection string using

MultipleActiveResultSets = False(or true)

in your connection string.

Let us elaborate an example in which we will enable MARS and use multiple data readers in our application.

First of all I will create 2 tables tblContent and tblContentTypes

tblContent

Column Name	Data Type
ContentID	int
ContentDisplayName	varchar(50)
IsActive	bool

tblContentType

Column Name	Data Type
ContentTypeID	int
ContentType	varchar(50)

Now let us start coding

Step 1 → Make connection string and enable MARS in it.

	string connectionString = "Server = TestServer;Database = test_delete;User ID = sa;Password = sa;Trusted_Connection = False;MultipleActiveResultSets = True";

As you can see above there is property MultipleActiveResultSets to enable or disable MARS

Step 2 → Create 2 variables that holds the queries

string content = "select top 1 * from tblContent";

string contenttype = "select top 1 * from tblContentType";

Step 3 → initialize connection object

using (SqlConnection conn = new SqlConnection(connectionString))

Step 4 → open connection

conn.Open();

Step 5 → Create 2 command objects one for content and other is for contentType

SqlCommand cmdContent = new SqlCommand(content, conn);

SqlCommand cmdContentType = new SqlCommand(contenttype, conn);

Step 6 → Create an instance of sql data reader for content and execute it

using (SqlDataReader rdContent = cmdContent.ExecuteReader())

Step 7 → Execute Read() function for datareader

while (rdContent.Read())

Step 8 → Create another instance of datareader for contentType inside the scope of while loop.

SqlDataReader rdcontentType = cmdContentType.ExecuteReader();

Step 9 → Execute Read() function for datareader

while (rdcontentType.Read())

When you try the code using the steps defined above you can see clearly that you are using multiple data readers that are executing in a single connection. While using this code you will not encounter

There is already an open Data Reader associated with this Command which must be closed first.

Error as we have enabled MARS.

Posted By -	Karan Gupta

Posted On -	Saturday, August 21, 2010

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**