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
Linq to SQL Left Outer Join Using Lambda Syntax and Joining on 2 Columns (Composite Join Key)
Moq - Verify Exception Was Thrown
Model Id Property Null in ASP.NET MVC C#
Create Global Variable in .Net Core 2.0 to Use in Razor Views
Select Multiple Records Based on List of Id'S With Linq
File Being Used by Another Process After Using File.Create()
Response to Preflight Request Doesn't Pass Access Control Check (Angular2)
A Better Way to Use Automapper to Flatten Nested Objects
Accessing a Shared File (Unc) from a Remote, Non-Trusted Domain With Credentials
How to Change a Image on a Button Using Windows Forms
Get File Name from Byte Array or Stream
C#: How to Force Trailing Zero in Numeric Format String
How to Send a File Document to the Printer and Have It Print
How to Add a Run Button and Compile Button on the Toolbar in Visual Studio
How to Prevent the Print Progress Dialog Appearing When Performing a Print Preview