CODEWITHSUNDEEP
c#sqlsql-server
Lightsout
Lightsout

Reputation: 3767

C# Run same query multiple times to check if anything returned

I have a SQL query that I want to check if something returns. The goal is to have nothing returned and I want to retry for 15 seconds if something gets returned. However I'm having a hard time getting this loop to work properly. Can anyone suggest fix to this code? It goes to "SQL table good!" after 2 tries even though the query should have returned something.

//SqlDataReader readerPromo = promoTablecmd.ExecuteReader();
var stopwatch = System.Diagnostics.Stopwatch.StartNew();
//Loop so that if less than 10 seconds, keep retrying SQL cmd above.
while (true)
{
    double elapsedTime = stopwatch.ElapsedMilliseconds;
    SqlDataReader readerPromo = promoTablecmd.ExecuteReader();
    // If query returned something
    if (readerPromo.Read())
    {


        if (elapsedTime > 15000)
        {
            Console.WriteLine("Error found!");
            break;
        }
        else
        {
            Console.WriteLine("Found descrepency in SQL table retrying again for 10 seconds...");
            System.Threading.Thread.Sleep(1000);
            continue;
        }

    }
    else {

         Console.WriteLine("SQL table good!");
    }
}

Upvotes: 0

Views: 407

Answers (1)

eocron
eocron

Reputation: 7546

Using async here is more correct. This should work:

    [Test]
    public async Task Repro()
    {
        var sqlCmd = new SqlCommand();//mock
        using var cts = new CancellationTokenSource();
        cts.CancelAfter(TimeSpan.FromSeconds(15));//timeout after 15 seconds
        await WaitEndOfDataAsync(sqlCmd, cts.Token, TimeSpan.FromSeconds(1));//setting check interval
    }

    private static async Task WaitEndOfDataAsync(SqlCommand cmd, CancellationToken cancellationToken, TimeSpan checkInterval)
    {
        while (true)
        {
            cancellationToken.ThrowIfCancellationRequested();
            using(var reader = await cmd.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false))
            {
                var hasData = await reader.ReadAsync(cancellationToken).ConfigureAwait(false);
                await reader.CloseAsync().ConfigureAwait(false);

                if (!hasData)
                    return;
            }
            cancellationToken.ThrowIfCancellationRequested();
            await Task.Delay(checkInterval, cancellationToken);
        }
    }

It will loop through data, and if timeout reached - throws OperationCancelledException.

Upvotes: 1

Related Questions