Using MARS technique in C#.NET 2.0

MARS (Multiple Active Result Set) is a new concept in Visual Studio 2005. MARS means we can work with the same reader without closing the opened reader. This means opening and closing the connection for each time use of a DataReader is no longer a requisite

 

Suppose we take a SqlDataReader object in our application such as:

 

      SqlDataReader dr = cmd.ExecuteReader ();

         

To use the same data reader on the same program multiple times we need to open connection, then do some task with the reader object and aging close the connection after the task is done with the reader, something like this:

 

    SqlDataReader dr = cmd.ExecuteReader ();               

    conn.Open();

    // do some task with dr object

    conn.Close();

 

    conn.Open();

    // do some task with dr object

    conn.Close();

 

So the repeat process of opening and closing of connection can cause the slower performance of the application, even if the connection is stored in the connection pool.

 

To avoid this we need to use MARS, this provides an ease way to keep track of objects that need to be closed before using same object. To use the MARS concept we need to set the Multiple Active Result set parameter  to Truein the  connection string else it will gives  an error that “There is already an open DataReader associated with this Command which must be closed first”.

Sample Example :

 

protected void Page_Load(object sender, EventArgs e)

{

        //--Define Connection object with MARS option as true.

        SqlConnection objConn = new SqlConnection("MultipleActiveResultSets=True;server=192.168.10.2;database=TestDB;uid=sa;pwd=sync_master@793s");

      

        //--Define Reader Object

        SqlDataReader objReader;

 

        //--Define Command object

        SqlCommand objCmd = new SqlCommand();

 

        //--Open connection

        objConn.Open();

 

        //--Exceute command and save data in the SqlDataReader object.

        objCmd = new SqlCommand("Select ContactID from Contact", objConn);

        objCmd.CommandType = CommandType.Text;

        objReader = objCmd.ExecuteReader();

      

        //--Check data in the reader object

        while(objReader.Read())

        { 

            //--Exceute the second query using reader object data and store the data on the same Reader object

            string query = "SELECT * FROM Person WHERE PersonID = '" + objReader[0].ToString() + "'";

 

            objCmd = new SqlCommand(query, objConn);

            objReader = objCmd.ExecuteReader();

 

            //--Bind the data to a gridview to display the Output

            grdDetails.DataSource = objReader;

            grdDetails.DataBind();           

        }

 

        //--Close the connection

        objConn.Close();

}

The output will be :

 

PersonID	FirstName	LastName
111	Allen	Smith