" Live as if you were to die tomorrow. Learn as if you were to live forever.. "
- Mahatma Gandhi
" Live as if you were to die tomorrow. Learn as if you were to live forever.. "
- Mahatma Gandhi
|
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 toTruein 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 :
|