Time ago, we discussed about ir MARS works in Azure SQL Managed Instance or Azure SQL Database in this URL Today I would like to include the change that we need to perform in our connection retry-logic policy if you want to use MARS.
Basically, we need to understand that using MARS you can open multiple DataReaders using the same connection, but, if, for example, retrieving the data, we have an issue at networking level, we are going to receive the following error message: System.Exception.Message.get returned "A transport-level error has occurred when receiving results from the server. (provider: Session Provider, error: 19 - Physical connection is not usable)" . This issue caused the connection will be closed.
In this scenario, the best approach is to detect that the connection has been closed and reopen a new one and continue working using MARS, if we don't perform this operation the following resultset that we want to open will give error because the connection is closed.
Following, I would like to share with you a piece of code of C#.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Collections;
using System.Data;
using C = System.Data.SqlClient;
using System.Diagnostics;
using System.Threading;
using System.Configuration;
using System.Transactions;
public void StartWithRetry(int iIterations, bool bPooling, bool bMars = true )
{
try
{
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
C.SqlConnection oConn = new C.SqlConnection();
ClsRetryLogic oClsRetry = new ClsRetryLogic();
for (int tries = 1; tries <= iIterations; tries++)
{
Console.WriteLine("Execution Nr.: " + tries.ToString());
Console.WriteLine();
if (bReaderExecutor(oClsRetry, "SELECT *, Replicate('xxxxxxx',1000000) as x FROM PerformanceVarcharNVarchar", oConn, 1200, 3, bPooling, bMars))
{
Console.WriteLine("Success");
Console.WriteLine();
}
else
{
Console.WriteLine("Error");
Console.WriteLine();
}
if (bMars == false && oConn.State == ConnectionState.Open) { oConn.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();
}
}
If this function detects if the connection is closed, try to open a new one if not use the same connection.
public bool bReaderExecutor(ClsRetryLogic oClsRetry, string sSQL, C.SqlConnection oConn, Int32 iTimeout, Int32 iRetryTimes =0, bool bPooling = true, bool bMars = true)
{
try
{
Int32 iTimes = 0;
while (iTimes < iRetryTimes)
{
iTimes++;
if (oConn.State == ConnectionState.Closed)
{
if (!oClsRetry.ConnRetryLogic(GetConnectionString(bPooling, bMars), oConn, false))
{
Console.WriteLine("Ups!! Imposible to connect..");
return false;
}
}
if (bExecuteReader(sSQL, oConn, 1200)) { return true;}
}
return false;
}
catch (Exception e)
{
Console.WriteLine("Ups!! " + e.Message);
Console.ReadLine();
return false;
}
}
Finally, we have the function bExecuteReader that will execute and obtain the values.
public bool bExecuteReader(string sSQL, C.SqlConnection oConn, Int32 iTimeout)
{
try
{
C.SqlCommand command = new C.SqlCommand(sSQL, oConn);
command.CommandTimeout = iTimeout;
C.SqlDataReader SqlReaderC = command.ExecuteReader();
while (SqlReaderC.Read())
{
Console.WriteLine("Value {0}", SqlReaderC.GetValue(0));
}
SqlReaderC.Close();
return true;
}
catch (Exception e)
{
Console.WriteLine("Ups!! " + e.Message);
return false;
}
}
With this simple modification, our source code is able to use the same connection when MARS is enabled and adapt the connection retry-logic policy in both scenariosn with/without MARS.
Enjoy!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.