How to change timeout the query

user_9676722 0
Viewed 5918

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();
            }
        }
    }
}
entity-framework sql c#
3 Answers

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
user_52025630

Looks like query is taking more than 60sec. Set it as 0 (infinite) and check.

connection.ConnectionTimeout = 0;

Most accepted method of doing this is through the CommandTimeout property of the SqlCommand object.

user_102937410

If timeout is read-only it mean you are already connected to database other way you can change it . try set the time out in a connection string maybee it help organise code better.

 <connectionStrings>  
    <... connectionString="User ID=sa;Password=XXXXX;Initial Catalog=qualitaBorri;Data Source=PC_NAME\SQLEXPRESS;Connection Timeout=60"/>
  </connectionStrings>
user_32237790
Related