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
How to change timeout the query
You need to set the
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
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,
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('=').Where(Char.IsDigit).ToArray());
constring = constring.Replace("Connection Timeout="+oldTimeOut, "Connection Timeout=" + newTimeOut);