Lesson Learned #130: Does MARS work in Azure SQL Managed Instance?

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

Today, we worked on a service request where our customer faced the following error message: "There is already an open DataReader associated with this Command which must be closed first" performing a SELECT operation and INSERT using the same connection.  Our customer asks about the availability to use MARS in Azure SQL Managed Instance. We provided an example how it works also in Azure SQL Managed Instance. 

 

1) As you know we enable MARS using the following connection string: data source=tcp:servername.virtualclustername.database.windows.net;initial catalog=DataseName;User ID=username;Password=Password;ConnectRetryCount=3;ConnectRetryInterval=10;Connection Timeout=30;Max Pool Size=100;MultipleActiveResultSets=true

 

2) We developed the following C# Code:

 

 

 

 

public void Inicia(int nRows, bool bPooling, bool bInstanciaCadaVez = false) { try { Stopwatch stopWatch = new Stopwatch(); stopWatch.Start(); C.SqlConnection oConn = new C.SqlConnection(); ClsRetryLogic oClsRetry = new ClsRetryLogic(); if (oClsRetry.HazUnaConexionConReintentos(GetConnectionString(bPooling), oConn, bInstanciaCadaVez)) { C.SqlCommand command = new C.SqlCommand("SELECT count(Id) FROM PerformanceVarcharNVarchar Where TextToSearch = @Name", oConn); command.CommandTimeout = 1200; command.Parameters.Add("@Name", SqlDbType.VarChar, 200); command.Prepare(); C.SqlCommand command2 = new C.SqlCommand("INSERT INTO Table_1 values(@Name)", oConn); command2.CommandTimeout = 1200; command2.Parameters.Add("@Name", SqlDbType.VarChar, 200); command2.Prepare(); Random rnd = new Random(); for (int tries = 1; tries <= nRows; tries++) { Console.WriteLine("Execution Nr.: " + tries.ToString()); Console.WriteLine(); command.Parameters["@Name"].Value = "Example " + rnd.Next(1, 450338).ToString(); command2.Parameters["@Name"].Value = rnd.Next(1, 450338); C.SqlDataReader SqlReaderC = command.ExecuteReader(); while (SqlReaderC.Read()) { Console.WriteLine("Valor {0}", SqlReaderC.GetValue(0)); command2.ExecuteNonQuery(); } SqlReaderC.Close(); } } oConn.Close(); stopWatch.Stop(); TimeSpan ts = stopWatch.Elapsed; string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds / 10); Console.WriteLine("Object type:{0}. Time: {1}", bPooling ? "Pooling" : "without Pooling", elapsedTime); Console.ReadLine(); } catch (Exception e) { Console.WriteLine("Ups!! " + e.Message); Console.ReadLine(); } }

 

 

 

 

Finally, we could see that both process are executing in the same connection. 

 

Enjoy!!!

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.