How to Set SQLCommandTimeout in App.config

Question

I have developed a Window Service using SQL Database currently in my DB is full of Record so query execuction taking much time while default command timeout is 30S but I want to increase it to 120S one option is

com.CommandTimeout = 120;

but I have many methods in my Application so I want to set it from APP.config file so it will be applicable for Application level, can anyone please tell me how could I achive this

Thanks

Answer 1

The easiest way to achieve this is to add a new entry in <appSettings> something like this:

<appSettings>
    <add key="commandTimeout" value="3000" />
</appSettings>

Afterwards, create a CommandFactory class that will populate the value

class CommandFactory
{
    public static int CommandTimeout
    {
        get
        {
            int commandTimeout = 0;
            var configValue = ConfigurationManager.AppSettings["commandTimeout"];
            if(int.TryParse(configValue, out commandTimeout))
               return commandTimeout;
            return 120; 
        }
    }

    public static SqlCommand CreateCommand(SqlConnection connection)
    {
        var command = new SqlCommand()
        {
            Connection = connection,
            CommandTimeout = CommandTimeout
        };
        return command;
    }
}

Now, in your code, instead of just instantiating a new SqlCommand just call the CommandFactory method:

using(var command = CommandFactory.CreateCommand(yourConnection))
{
    //
}