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

This article is contributed. See the original author and article here.

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!!!

 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.