C# Retry Sql Query if Timeout Failed

Question

at time my query times out and cause a failure. What is the best way to retry to execute a query?

I validate that the connection is open before executing the query. However, due to the server load at any given time, it may take <1 min to 5+ minutes. I thought about extending the CommandTimeout but I do not believe that is really the way to go.

Here is my sql query code. Thanks for all the assistance.

    private static void ExecuteQuery(string connectionString, string query)
    {
        SqlConnection connection = new SqlConnection(connectionString);
        DataTable output = new DataTable();

        try
        {
            //create new SqlAdataAdapter
            SqlDataAdapter command = new SqlDataAdapter {SelectCommand = new SqlCommand(query, connection)};

            //connect to Sqldb
            connection.Open();

            //validate connection to database before executing query
            if (connection.State != ConnectionState.Open) return;
            Console.WriteLine("Connection successful\nExecuting query...");

            //set connection timeout
            command.SelectCommand.CommandTimeout = 200;

            //create new dataSet in order to input output of query to
            DataSet dataSet = new DataSet();

            //fill the dataSet
            command.Fill(dataSet, "capacity");
            DataTable dtTable1 = dataSet.Tables["capacity"];

            Console.WriteLine("There are " + dtTable1.Rows.Count + " clusters within the capacity anlaysis.");

            output = dtTable1;
        }
        catch (Exception e)
        {
            Console.WriteLine("Unable to execute capacity (all records) query due to {0}", e.Message);
        }
        finally
        {
            connection.Close();

            Declarations.NumOfClusters = output.Rows.Count;
            Declarations.finalIssues = Issues(output, 2m, 20, true);

            Console.WriteLine("\n---------------Successfully Created Capacity DataSet---------------\n");
        }
    }

Answer 1

Re-structure your code in a manner that allows it to call the query recursively until you get the desired result.

Eg.

private static void ExecuteQuery(string connectionString, string query)
{
    SqlConnection connection = new SqlConnection(connectionString);
    DataTable output = null;

    while output is null
    {
        output = getDataFromDB(query);
    }

    if(output is DataTable && output.Rows.Count > 0)
    {
        Console.WriteLine("There are " + output.Rows.Count + " clusters within the capacity analysis.");
    }
}

private DataTable getDataFromDB(string query)
{
    DataTable oDTResult = null;

    try
    {
        //create new SqlAdataAdapter
        SqlDataAdapter command = new SqlDataAdapter {SelectCommand = new SqlCommand(query, connection)};

        //connect to Sqldb
        connection.Open();

        //validate connection to database before executing query
        if (connection.State != ConnectionState.Open) return;
        Console.WriteLine("Connection successful\nExecuting query...");

        //set connection timeout
        command.SelectCommand.CommandTimeout = 200;

        //create new dataSet in order to input output of query to
        DataSet dataSet = new DataSet();

        //fill the dataSet
        command.Fill(dataSet, "capacity");
        DataTable dtTable1 = dataSet.Tables["capacity"];            

        oDTResult = dtTable1;
    }
    catch (Exception e)
    {
        Console.WriteLine("Unable to execute capacity (all records) query due to {0}", e.Message);
    }
    finally
    {
        connection.Close();

        Declarations.NumOfClusters = output.Rows.Count;
        Declarations.finalIssues = Issues(output, 2m, 20, true);

        Console.WriteLine("\n---------------Successfully Created Capacity DataSet---------------\n");
    }

    return oDTResult;
}

You would want to incorporate the ability for a number of retries the program should attempt executing this to give it some flexibility.

Further if your query is taking such a long time you should look into ways to optimize the query and the SQL Server Database to cut down on the execution time including the aid of Views/Indexes etc. and normal methods to reduce the burden of the query within the database request.

Answer 2

Use Palmer library: https://github.com/mitchdenny/palmer

  Retry.On<Exception>().For(TimeSpan.FromSeconds(15)).With(context =>
  {
    // Code that might periodically fail due to some issues.
       ExecuteQuery(string connectionString, string query)
       if (contect.DidExceptionLastTime)
           Thread.Sleep(200); // what ever you wish
   });

Refer to the API on the github page. You can for example check the context for exceptions and decide to sleep for a while if an exception did happen. You can Retry on more specific exception. You can try forever, etc.

Answer 3

I write a sample code in asp.net web form which runs for 10 times .

        static int noOfTries = 0;
        protected void Page_Load(object sender, EventArgs e)
        {
            function();
        }

        private void function()
        {

            try
            {

                if (noOfTries == 10) goto XX;
                noOfTries++;
                int a = 0;
                int b = 1 / a;


            }
            catch (Exception ew)
            {
                Response.Write(ew.Message + "<br/>");
                function();

            }
            XX:
            int c = 0;
        }

Note: Its is not thread safe as use of static variable

static int noOfTries=0 Multi-thread execution may not work as you except because static variable will be shared in multi-thread.

Solve use Session["noOfTries"]

if multi-thread execution environment .

Answer 4

A generic method for retry the action

 public static class Retry
    {
       public static void Do(
           Action action,
           TimeSpan retryInterval,
           int retryCount = 3)
       {
           Do<object>(() => 
           {
               action();
               return null;
           }, retryInterval, retryCount);
       }

       public static T Do<T>(
           Func<T> action, 
           TimeSpan retryInterval,
           int retryCount = 3)
       {
           var exceptions = new List<Exception>();

           for (int retry = 0; retry < retryCount; retry++)
           {
              try
              { 
                  if (retry > 0)
                      Thread.Sleep(retryInterval);
                  return action();
              }
              catch (Exception ex)
              { 
                  exceptions.Add(ex);
              }
           }

           throw new AggregateException(exceptions);
           }
        }

You can now use this utility method to perform retry logic:

Retry.Do(() => SomeFunctionThatCanFail(), TimeSpan.FromSeconds(1));