How to change timeout the query

Question

For queries with many records, I have the timeout issue. How can I change the query timeout?

I tried changing the timeout connection this way, but it does not work:

connection.ConnectionTimeout = 60; // not working (ready)

Class:

public abstract class RepositoryBase<TEntity> : IRepositoryBase<TEntity>, IDisposable where TEntity : class
{
    protected SqlConnection _connection;
    protected string _connectionString;

    public RepositoryBase(string connectionString)
    {
        _connectionString = connectionString;
        SqlConnection connection = new SqlConnection(connectionString);
        if (connection.State == ConnectionState.Closed)
            connection.ConnectionTimeout = 60; // not working (ready)
            connection.Open();

        _connection = connection;
    }

    public List<T> GetEntitiesByQuery<T>(string Query)
    {
        using (var connection = _connection)
        {
            try
            {
                var entities = connection.Query<T>(Query);
                return entities.ToList();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
    }
}

Answer 1

You need to set the CommandTimeout property:

The time (in seconds) to wait for the command to execute. The default value is 30 seconds.

How do you set it depends on the data access technology used.

For plain ADO.NET:

IDbCommand cmd = ...;
cmd.CommandTimeout = 120; // 2 min

For EF6:

DbContext db = ...;
db.Database.CommandTimeout = 120; // 2 min

But looks like you are using Dapper. The Query<T> method used has currently the following signature:

public static IEnumerable<T> Query<T>(
    this IDbConnection cnn,
    string sql,
    object param = null,
    IDbTransaction transaction = null,
    bool buffered = true,
    int? commandTimeout = null,
    CommandType? commandType = null
)

As you can see, a lot of optional parameters, and one of them is the commandTimeout you need. So you can use something like this:

var entities = connection.Query<T>(Query, commandTimeout: 120);

Or you can set the default timeout for all queries:

SqlMapper.Settings.CommandTimeout = 120; // 2 min