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