Getting the MARS Sample working in the June CTP

Article
08/29/2005

Multiple Active Result Sets (MARS) is a cool new feature in SQL Server 2005 which allows you to start and begin processing another query while you are in the middle of processing a different query on the same connection. Unfortunately the MARS sample which ships in the June CTP (see https://www.microsoft.com/sql/2005/productinfo/ctp.mspx for more information) has some bugs. Fixing those bugs can help you understand the MARS feature a little bit better.

Once you have installed the SQL Server 2005 samples in the June CTP (see the topic “Installing Samples” in Books Online), you will find the MARS sample located in drive:\Program Files\Microsoft SQL Server\90\Samples\Engine\Data Access\ADO\MARS where drive is the drive you installed the samples on (typically your system drive).

Connection String Changes: First you need to change the connection string. You can do this easily using the settings menu item in the project properties window in Visual Studio, or edit the CS\MARS\Properties\Settings.Settings file and the CS\MARS\app.config file with a text editor. The incorrect connection string looks like this:
server=(local);database=AdventureWorks;Integrated Security=true
The correct connection string should look like this:
server=(local);database=AdventureWorks;Integrated Security=true;MultipleActiveResultSets=true
MARS is off by default, but you can enable MARS by adding MultipleActiveResultSets=true to the connection string.

Multiple Transactions? : Second you need to change where the transaction starts to earlier in the Main method. The incorrect Main method starts like this:
static void Main()
{
//Use the priceIncreases constant to initialize entries in the increaseDictionary collection.
InitializeIncreaseDictionary();

SqlConnection myConnection = null;
SqlTransaction myTransaction = null;
int errorCount = 0;
try
{
//Initialize the command to get most popular bikes
myConnection
= new SqlConnection(Properties.Settings.Default.ConnectionString);
SqlCommand popularBikesCommand = myConnection.CreateCommand();
popularBikesCommand.CommandText = "usp_GetMostPopularBikes";
popularBikesCommand.CommandType = CommandType.StoredProcedure;

//Get the list of the most popular bikes
myConnection.Open();
SqlDataReader popularBikesReader = popularBikesCommand.ExecuteReader();

//Initialize the command to update the price of a product
SqlCommand updateCommand = myConnection.CreateCommand();
updateCommand.CommandText = "usp_UpdateProductPrice";
updateCommand.CommandType = CommandType.StoredProcedure;

SqlParameter productIDParameter = new SqlParameter("@ProductID", SqlDbType.Int);
updateCommand.Parameters.Add(productIDParameter);
SqlParameter standardCostParameter = new SqlParameter("@StandardCost", SqlDbType.Money);
updateCommand.Parameters.Add(standardCostParameter);
SqlParameter listPriceParameter = new SqlParameter("@ListPrice", SqlDbType.Money);
updateCommand.Parameters.Add(listPriceParameter);

//Make sure that either all updates succeed, or all price increases are reverted to
//what they were before this program is invoked.
myTransaction = myConnection.BeginTransaction();
updateCommand.Transaction = myTransaction;

You need to change the beginning of the Main method to look like this instead:
static void Main()
{
//Use the priceIncreases constant to initialize entries in the increaseDictionary collection.
InitializeIncreaseDictionary();

SqlConnection myConnection = null;
SqlTransaction myTransaction = null;
SqlDataReader popularBikesReader = null;
int errorCount = 0;
try
{
//Initialize the command to get most popular bikes
myConnection
= new SqlConnection(Properties.Settings.Default.ConnectionString);
SqlCommand popularBikesCommand = myConnection.CreateCommand();
popularBikesCommand.CommandText = "usp_GetMostPopularBikes";
popularBikesCommand.CommandType = CommandType.StoredProcedure;

myConnection.Open();
//Make sure that either all database operations succeed,
//or all price increases are reverted to
//what they were before this program is invoked.
myTransaction = myConnection.BeginTransaction();
popularBikesCommand.Transaction = myTransaction;

//Get the list of the most popular bikes
popularBikesReader = popularBikesCommand.ExecuteReader();

updateCommand.Transaction = myTransaction;

As you can see from the bolded text, the transaction is created earlier and applied to both the query and the update in the correct version. In the incorrect version the transaction we created only applied to the update. The problem with the incorrect approach is that multiple transactions on the same connection are not supported, and in the incorrect version we had an implicit transaction active on the query at the same time as we had the explicit transaction active on the update. By creating the transaction sooner and applying it to both the query and update commands we ensure there is only a single transaction active on the connection at a time.

Closing DataReader: Also, it is helpful to modify the finally clause to close the popularBikesReader if we managed to open it. It is important to ensure all your data readers get closed under all circumstances.
finally
{
if (popularBikesReader != null)
popularBikesReader.Close();
if (myConnection != null)
{
if (myTransaction != null)
{
if (errorCount == 0)
{
myTransaction.Commit();
Console.WriteLine("All updates completed successfully.");
}
else
{
myTransaction.Rollback();
Console.WriteLine("{0} errors occurred. All updates reverted.", errorCount);
}
}
myConnection.Close();
}
}

These fixes should be included in the August CTP release.

Bonnie Feinberg, Lead Software Dev Engineer, UE

Getting the MARS Sample working in the June CTP

Additional resources