CODEWITHSUNDEEP
c#sqlentity-framework
Rodrigo Martins
Rodrigo Martins

Reputation: 129

How to change timeout the query

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();
            }
        }
    }
}

Upvotes: 7

Views: 12056

Answers (3)

Ivan Stoev
Ivan Stoev

Reputation: 205819

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

Upvotes: 10

Zwan
Zwan

Reputation: 642

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>

Upvotes: 0

CBS
CBS

Reputation: 50

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.

Upvotes: 0

Related Questions