Lesson Learned #180: Creating a retry-logic for TSQL command execution.

[Jose_Manuel_Jurado]

As you know, one of the recommendations that we have in Azure SQL Database is to have a connection retry-logic, however, I would like to share with you other recommendation for command execution.

I developed the following piece of code that receives, four parameters. 

• ConnectionString: Contains the connection string.
• QueryText: Contains the query to execute.
• retryIntervalSeconds: how much time the command timeout will be at the begining.
• HowManyRetries: how many time the execution will be retried until fails.

Basically, as you could see in the example source code the operation will be:

• 1) Connect to the DB. This function has a connection retry-logic that we shared in previous article.
• 2) Try to execute the query (in this example will be NonQuery type) using the CommandTimeout specified in the parameter value called retryIntervalSeconds. 
• 3) If the execution failed, the next command execution timeout will be multiple by 2.5. It and will be executed again until success or fails after retrying specified in the parameter value called HowManyRetries. 
• 4) Also, I added an useful information, about how much the execution took, connection time, execution time and ConnectionID, etc..

Source code example:

public bool RunQueryWithRetriesNonQuery(string ConnectionString, 
                                                string QueryText,  
                                                int retryIntervalSeconds = 5, 
                                                int HowManyRetries = 5)
        {
            bool returnBool = false;
            int totalNrRows = 0;
            Stopwatch stopWatchWholeProcess = new Stopwatch();
            Stopwatch stopWatchRealTime = new Stopwatch();
            ClsRetryLogic oClsRetry = new ClsRetryLogic();
            C.SqlConnection SqlConnection = new C.SqlConnection();
            C.SqlCommand SqlCommand = new C.SqlCommand(QueryText, SqlConnection);

            SqlConnection.StatisticsEnabled = true;

            stopWatchWholeProcess.Start();

                for (int tries = 1; tries <= HowManyRetries; tries++)
                {
                 try
                 {
                    if (oClsRetry.ConnectDBWithRetry(ConnectionString, SqlConnection, false))
                    {

                        if (tries > 1) { retryIntervalSeconds = Convert.ToInt32(retryIntervalSeconds * 2.5); }

                        SqlCommand.CommandTimeout = retryIntervalSeconds;

                        Console.WriteLine("Executing the query {0} - Attempt Nr.:{1} - CommandTimeout: {2} - ConnectionID:{3}", QueryText, tries, SqlCommand.CommandTimeout, SqlConnection.ClientConnectionId);
                        Console.WriteLine();
                        stopWatchRealTime.Start();
                        totalNrRows = (int)SqlCommand.ExecuteNonQuery();
                        stopWatchRealTime.Stop();
                        stopWatchWholeProcess.Stop();

                        TimeSpan ts = stopWatchWholeProcess.Elapsed;
                        string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
                            ts.Hours, ts.Minutes, ts.Seconds,
                            ts.Milliseconds / 10);

                        ts = stopWatchRealTime.Elapsed;
                        string elapsedTimeRealTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
                            ts.Hours, ts.Minutes, ts.Seconds,
                            ts.Milliseconds / 10);

                        Console.WriteLine("Query Executed Text:{0} - Total ElapsedTime:{1} - Real Time:{5} - Rows:{2} - Tries:{3} - CommandTimeout:{4}",QueryText, elapsedTime, totalNrRows, tries, SqlCommand.CommandTimeout, elapsedTimeRealTime);
                        Console.WriteLine();

                        IDictionary currentStatistics = SqlConnection.RetrieveStatistics();

                        Console.WriteLine("---- BytesReceived: {0}",(long)currentStatistics["BytesReceived"]);
                        Console.WriteLine("---- BytesSent: {0}", (long)currentStatistics["BytesSent"]);
                        Console.WriteLine("---- ExecutionTime: {0}", (long)currentStatistics["ExecutionTime"]);
                        Console.WriteLine("---- ConnectionTime: {0}", (long)currentStatistics["ConnectionTime"] );

                        returnBool = true;
                        SqlConnection.Close();
                        break;
                    }
                    else
                    {
                        Console.WriteLine("Connectivity Error..");
                    }
                }
                catch (C.SqlException sqlExc)
                {
                    if (sqlExc.Number == -2)
                    {
                        Console.WriteLine("Query Error Exec:{0} Error Code:{1} Error Message:{2}", QueryText, sqlExc.Number, sqlExc.Message);
                        Console.WriteLine();
                        SqlConnection.ResetStatistics();
                        SqlConnection.Close();
                        stopWatchRealTime.Reset();
                        continue;
                    }
                    else
                    {
                        Console.WriteLine("---- Unknown Error: " + sqlExc.Number.ToString() + '-' + sqlExc.Message);
                        Console.WriteLine();
                        break;
                    }
                }
               }
            return returnBool;
        }

Output example: 

Enjoy!