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!!!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.