async await throwing error for Polly code while connecting to Oracle DB

Question

Below is the code that I am trying to maintain Consistent Connection with Oracle Database using Polly.

public async Task<bool> Execute()
{
    var retryTimes = 3000;
    var waitBetweenExceptions = TimeSpan.FromSeconds(10);

    var retryPolicy = await Policy
        .Handle<OracleException>(e => e.Message.Contains("ORA-12543") || e.Message.Contains("ORA-12170"))
        .WaitAndRetry(retryTimes, i => waitBetweenExceptions);

    retryPolicy.Execute(() =>
    {
        string cs = "Data Source=SampleDB;User Id=userid;Password=password;Connection Timeout=600; Max Pool Size=150;";
        using (OracleConnection connection = new OracleConnection(cs))
        {
            for (int i = 0; i < 1000; i++)
            {
                connection.Open();
                Console.WriteLine("Counter :" + i);
                Thread.Sleep(1000);
                connection.Close();
            }
        }
    });
    return true;
}

I am getting error as Cannot await void compilation error.. Also, I want to try WaitForever method of Polly.

I tried something like the below question but little confused on how to use it successfully.

Getting a Cannot await void, on a method that I have want to await on

EDIT Modified Question for Synchronous way

I tried the below code and still it is not working...

class Program
{
    static void Main(string[] args)
    {
        Connect connect = new Connect();
        connect.Execute();
        Console.ReadKey();
    }
}

public class Connect
{
    public void Execute()
    {
        var retryTimes = 3000;
        var retryableErrorCodes = new[] { "ORA-12543", "ORA-12170" };
        RetryPolicy retryPolicy = Policy
            .Handle<OracleException>(ex => retryableErrorCodes.Any(errorCode => ex.Message.Contains(errorCode)))
            .WaitAndRetry(retryTimes, _ => TimeSpan.FromSeconds(10));

        retryPolicy.Execute(() =>
        {
            string cs = "Data Source=SampleDB;User Id=userid;Password=password;Connection Timeout=600; Max Pool Size=150;";
            using (OracleConnection connection = new OracleConnection(cs))
            {
                for (int i = 0; i < 1000; i++)
                {
                    connection.Open();
                    Console.WriteLine("Counter :" + i);
                    Thread.Sleep(1000);
                    connection.Close();
                }
            }
        });
    }
}

I tried using Synchronous way. But, after disconnecting the Network cable, it just silently stops but does not Auto Reconnect.

Answer 1

The to be decorated code is either sync or async.

Sync case

Policy declaration

var retryableErrorCodes = new[] { "ORA-12543", "ORA-12170" };
RetryPolicy retryPolicy = Policy
    .Handle<OracleException>(ex => retryableErrorCodes.Any(errorCode => ex.Message.Contains(errorCode)))
    .WaitAndRetry(retryTimes, _ => TimeSpan.FromSeconds(10));

Policy usage

retryPolicy.Execute(() => 
{
  //...
});

Async case

Policy declaration

var retryableErrorCodes = new[] { "ORA-12543", "ORA-12170" };
AsyncRetryPolicy retryPolicy = Policy
    .Handle<OracleException>(ex => retryableErrorCodes.Any(errorCode => ex.Message.Contains(errorCode)))
    .WaitAndRetryAsync(retryTimes, _ => TimeSpan.FromSeconds(10));

Policy usage

await retryPolicy.ExecuteAsync((ct) =>
{
  //...
}, CancellationToken.None);

---

WaitAndRetryForever and WaitAndRetryForeverAsync would be same but you don't have to specify there the maximum retry count.