How can I avoid a SqlClient timeout error during execution of a C# Azure Function?

Question

I have created a function app time trigger with C#. Inside the logic, I call and execute a stored procedure that I have created in SQL Server.

The execution of the stored proc takes more than 8 minutes, and I have got the following error in Azure function apps log :

2018-04-02T11:03:46.409 [Error] Exception while executing function: Functions.AbarIomWeeklyUsage. mscorlib: Exception has been thrown by the target of an invocation. .Net SqlClient Data Provider: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The wait operation timed out. 2018-04-02T11:03:46.425 [Error] Function completed (Failure, Id=1b85e7ef-ea52-4fd3-ab79-f27d188c5cac, Duration=30323ms)

So far I tried 2 things : 1. leverage the connection timeout in application connection string 2. add timeout in host.json

But it still gives me the same error 30 seconds timeout.

Anyone experienced the same or have solution for this?

Answer 1

You are certainly exceeding the default command timeout (the default is 30 seconds). If you don't set SqlCommand.CommandTimeout you will get a SqlException. Set the command's CommandTimeout in code (there is no configuration for this) to 10 minutes (600 seconds), same as your function timeout.

Answer 2

Here's a code snippet on how to do this:

using (SqlCommand cmd = new SqlCommand(text, conn))
{
        cmd.CommandTimeout = 60 * 60; // seconds
        cmd.Parameters.AddWithValue("@parameter1", myparameter1);

        // Execute the command and log the # rows affected.
        var rows = await cmd.ExecuteNonQueryAsync();

        log.Info($"{rows} rows were deleted");
}