How to Set the Query Timeout from SQL Connection String

sql: set connection timeout in the connection string

There are two timeouts relating to SQL connections/commands - there is a connection timeout, that affects how long a connection is willing to wait when you try to open it, and a command timeout that affects how long an individual command being executed will wait.

You need to adjust the second of these - by e.g. setting the CommandTimeout property on the SqlCommand object.

How to change timeout the query

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

What is "Connect Timeout" in sql server connection string?

That is the timeout to create the connection, NOT a timeout for commands executed over that connection.

See for instance http://www.connectionstrings.com/all-sql-server-connection-string-keywords/
(note that the property is "Connect Timeout" (or "Connection Timeout"), not just "Timeout")


From the comments:

It is not possible to set the command timeout through the connection string. However, the SqlCommand has a CommandTimeout property (derived from DbCommand) where you can set a timeout (in seconds) per command.

Do note that when you loop over query results with Read(), the timeout is reset on every read. The timeout is for each network request, not for the total connection.

How to set or disable the Query Timeout with a C++'s CDatabase ODBC connection to a SQL Server database

Query timeout is not a connection string parameter, at least not for SQL Server. You're probably looking for the CDatabase::SetQueryTimeout member function.

How to add CommandTimeout to connection string in web.config

I made it like this:

private readonly MyDbContext _context;

public LinqToSql() : this(new MyDbContext())
{
}

private LinqToSql(MyDbContext context)
{
_context = context;
_context.CommandTimeout = 500;

}

Sql Server connection timeout

The problem is with the command timeout, i solved the problem with the following line of code :

command.CommandTimeout = 30000;

but the question now : how can i set commandTimeout default value over the application

Changing SqlConnection timeout at runtime

you can also alter the connstring itself (might be a little overkill)

        string constring = "Data Source=.\\SQL2016;Initial Catalog=MyDatabase;Persist Security Info=False;User ID=DbUser;Password=DbPassword;Connection Timeout=6\" providerName=\"System.Data.SqlClient";
int index = constring.IndexOf("Connection Timeout=");
var oldTimeOut = new String(constring.Substring(index).Split('=')[1].Where(Char.IsDigit).ToArray());
constring = constring.Replace("Connection Timeout="+oldTimeOut, "Connection Timeout=" + newTimeOut);


Related Topics



Leave a reply



Submit