Multiple Active Result Sets, or MARS, is a new feature supported by ADO.NET that allows the implementation of multiple batches on a single connection.
MARS has the ability to have more than one pending request under a given SQL Server connection. In most cases, it allows you to have more than one default result set pending , while simultaneously, other operations can perform within the same session.
To understand the concept, let us consider an example. There are two databases with a MARS enabled connection.
Here DATASOURCE1 is named PURCHASING and DATASOURCE2 is named PRODUCTION.
PURCHASING has the first table, VENDOR, with the records VENDORID and NAME.
The next table in PURCHASING is PRODUCTVENDOR that has the records, PRODUCTID and VENDORID.
PRODUCTION has the table PRODUCT.It has the records PRODUCTID and PRODUCTNAME.
This is the structure. Now, the connection is opened. A value is retrieved by executing a query from the vendor table. The query is being executed. The retrieved value is stored in the first result set, RESULTSET1.
The first query execution is complete.
Now, the second query is being executed. Here a READER is used to read the RESULTSET1.
The records from the PRODUCTVENDOR table are retrieved.
There is asynchronous processing of the records from RESULTSET1 with that of the records from PRODUCTVENDOR table.
This processing helps you to retrieve the relevant record from the PRODUCT table.
The record is retrieved, and this is the second result set, RESULTSET2.This RESULTSET is thus obtained using the principle of MARS.