Using MARS (Multiple Active RecordSets)

Multiple Active RecordSets is a fairly complex topic, and there are a lot of unseen factors that can determine whether or not you should use this technology. This chapter has a small preview of this technology. For more information on MARS and the impact of using MARS on performance and the database itself, you should consult your favorite SQL Server 2005 reference or the SQL Server 2005 Books Online reference that installs with the server itself.

In previous versions of SQL Server, many developers remember getting a common error message that indicates that there is already an open DataReader on the connection, and that in order to open the current DataReader, the previous reader needs to be closed. This message arose from the limitation that you could only have one open forward-only result set on any given connection at any given time. The workaround developers used for this was just to make sure that they used new connections all the time, or they opted for low-performance options like reading all the data into a DataSet before opening the next DataReader.

With the introduction of SQL Server 2005, developers now have the ability to create multiple active result sets on a single connection. This essentially removes the block created by previous versions of SQL Server. The code in Listing 21.6 contains code that would otherwise fail miserably on previous versions of SQL Server 2005 and the .NET Framework.

Listing 21.6. Using MARS

[View full width]

using System; using System.Threading; using System.Data; using System.Data.SqlClient; using System.Collections.Generic; using System.Text; namespace MarsDemo { class Program { static void Main(string[] args) {     // comment this out and use the other conn string     // to break the app     SqlConnection conn = new SqlConnection(       "data source=localhost; initial catalog=SampleDB; integrated security=sspi;  multipleactiveresultsets=yes");     // uncomment below to break the sample     //SqlConnection conn = new SqlConnection(     //    "data source=localhost; initial catalog=SampleDB; integrated security=sspi;");     conn.Open();     SqlCommand cmd1 = conn.CreateCommand();     cmd1.CommandText = "SELECT * FROM Customers";     SqlCommand cmd2 = conn.CreateCommand();     cmd2.CommandText = "SELECT * FROM GameObjects";     SqlDataReader dr1 = cmd1.ExecuteReader();     SqlDataReader dr2 = cmd2.ExecuteReader();     while (dr1.Read())     {         Console.WriteLine("{0}, {1}", (String)dr1["LastName"], dr1["FirstName"]);     }     while (dr2.Read())     {         Point3D currentLoc = (Point3D)dr2.GetValue(dr2.GetOrdinal("Location"));         Console.WriteLine("{0} is at {1}", dr2["Name"], currentLoc.ToString());     }     dr2.Close();     dr1.Close();     Console.ReadLine(); } } } 

The preceding code creates one connection and two commands. Each of these commands is then used to create a new SqlDataReader.

MultipleActiveResultSets=true 

If you remove this option from the connection string, the behavior resembles the previous versions of SQL Server and the .NET Framework, and you get the following error message:

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

The downside to MARS is that the execution of the commands on the server is interleaved. This means that a portion of the first command will be executed, and then a portion of the second command will be executed, and so on. You can't rely on which portion will be executed first. If two commands are executed requesting the same rows on the same connection, you could potentially create a deadlock situation where neither command can complete.

You can learn more about these issues in more detail in other SQL Server 2005 references. The important point here is that with MARS, it is possible to have multiple result sets executed on the same connection at the same time. However, this might not always be such a good idea. If the multiple result sets might be competing for the same information or the same table, you might want to consider using separate connections. Just because it is possible doesn't mean it should be overused.