Re-try to open a SQL Server connection if failed

Question

I have a C# application which has several methods which connect to a SQL Server database in order to execute a query.

Sometimes the connection fails and then the program exits.

A db administrator is looking on the database nevertheless I have to adapt the program in order to retry 2-3 times when a connection fails before to exiting.

I don't really know who doing this "smartly".

My connection code:

 using (SqlConnection SqlCon = new SqlConnection(myParam.SqlConnectionString))
 {
    SqlCon.Open();
    string requeteFou = "select XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";

    using (SqlCommand command = new SqlCommand(requeteFou, SqlCon))
    {
        using (SqlDataReader reader = command.ExecuteReader())
        {
            if (reader.HasRows)
            {
                while (reader.Read()) 
                {
                    // do job
                }
            }
        } 
   }
}

Since I use several methods, is there a simply way to overwrite the "connection" or "read" method in order to retry the connection 3 times for example ?

Best regards

score 0 · Answer 1 · answered Apr 18 '17 at 13:09

I would use Polly for retry logic.

Very basic example retrying 3 times when there is a SqlException (not tested):

        static void Main(string[] args)
        {
           var policy = Policy
                .Handle<SqlException>()
                .Retry(3);

            try
            {
               policy.Execute(() => DoSomething());
            }
            catch (SqlException exc)
            {
               // log exception   
            }
        }

        private static void DoSomething()
        {
            using (SqlConnection conn = new SqlConnection(""))
            {
                conn.Open();
                string requeteFou = "select XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";

                using (SqlCommand command = new SqlCommand(requeteFou, conn))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (!reader.HasRows) return;
                        while (reader.Read())
                        {
                            // do job
                        }
                    }
                }
            }
        }

Thanks for the tips, problem is i have a lot of function using SQL so i cannot directly use this generic solution with "DoSomething". Maybe the solution to use a FOR loop is a solution to try ! — Walter Fabio Simoni, Apr 18 '17 at 13:47
@WalterFabioSimoni It's just an example. The point I was making was to use a Retry framework like Polly. I'm sure you have a calling method that you could wrap Polly with. — William Xifaras, Apr 18 '17 at 13:50
@WalterFabioSimoni Polly is the way to go if you need to implement retry logic. — The Pax Bisonica, Apr 18 '17 at 20:44

score 0 · Answer 2 · answered Oct 22 '19 at 09:23

private static  function()
        {
            DataTable dt = new DataTable();

            string connectionString = "//your connection string";


            String strQuery = //"Yourquery";

            const int NumberOfRetries = 3;
            var retryCount = NumberOfRetries;
            var success = false;
            while (!success && retryCount > 0)
            {
                try
                {
                    SqlConnection conn = new SqlConnection(connectionString);
                    SqlCommand cmd = new SqlCommand();
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = strQuery;
                    cmd.Connection = conn;
                    cmd.CommandTimeout = 180;

                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    dt.Load(dr);


                catch (Exception ex)
                {
                    retryCount--;
                    Thread.Sleep(1000 * 60 * 15);

                    if (retryCount == 0)
                    {
                       //yourexception
                    }
                }

            }

        }

Add success=true; after the dt.Load(dr); – adnan umar Jun 13 '22 at 06:56 — adnan umar, Jun 13 '22 at 06:56

score -2 · Answer 3 · answered Apr 18 '17 at 09:06

-2

Maybe wrap your using in a try block. Log a connection error in a catch block if you want. Put whole try{ }catch{ } in a for loop that will loop 3 times. If try block runs to the end of itself, break out of loop.

for(int i = 0; i < 3; i++)
{
    try {
        using (SqlConnection SqlCon = new SqlConnection(myParam.SqlConnectionString))
        {
              // your code
        }
        Thread.Sleep(1000); // wait some time before retry
        break; // connection established, quit the loop
    }
    catch(Exception e) {
        // do nothing or log error
    }
}

You'd however have to handle differentiating SQL connection exception from other exceptions that you might encounter in your code.

answered Apr 18 '17 at 09:06

schroedingersKat

189
8

Why this solution is under-rated ? Bad idea ? – Walter Fabio Simoni Apr 18 '17 at 09:23
I'd like to know too – schroedingersKat Apr 18 '17 at 09:42
1

I didn't downvote, but shouldn't Thread.Sleep() be called if it fails (in the catch statement) instead of occurring when the code in the using block successfully runs ? – JonoB Dec 03 '18 at 13:58
Yes, far better solution in this case – schroedingersKat Dec 06 '18 at 08:15

Re-try to open a SQL Server connection if failed

3 Answers3

Linked