Changing SqlConnection timeout
C#.NetSql ServerSqlconnectionC# Problem Overview
I am trying to override the default SqlConnection
timeout of 15 seconds and am getting an error saying that the
> property or indexer cannot be assigned because it is read only.
Is there a way around this?
using (SqlConnection connection = new SqlConnection(Database.EstimatorConnection))
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
connection.ConnectionTimeout = 180; // This is not working
command.CommandText = "sproc_StoreData";
command.Parameters.AddWithValue("@TaskPlanID", order.Projects[0].TaskPlanID);
command.Parameters.AddWithValue("@AsOfDate", order.IncurDate);
command.ExecuteNonQuery();
}
}
C# Solutions
Solution 1 - C#
If you want to provide a timeout for a particular query, then CommandTimeout is the way forward.
Its usage is:
command.CommandTimeout = 60; //The time in seconds to wait for the command to execute. The default is 30 seconds.
Solution 2 - C#
You can set the timeout value in the connection string, but after you've connected it's read-only. You can read more at http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectiontimeout.aspx
As Anil implies, ConnectionTimeout may not be what you need; it controls how long the ADO driver will wait when establishing a new connection. Your usage seems to indicate a need to wait longer than normal for a particular SQL query to execute, and in that case Anil is exactly right; use CommandTimeout (which is R/W) to change the expected completion time for an individual SqlCommand.
Solution 3 - C#
A cleaner way is to set connectionString in xml file, for example Web.Confing(WepApplication)
or App.Config(StandAloneApplication)
.
<connectionStrings>
<remove name="myConn"/>
<add name="myConn" connectionString="User ID=sa;Password=XXXXX;Initial Catalog=qualitaBorri;Data Source=PC_NAME\SQLEXPRESS;Connection Timeout=60"/>
</connectionStrings>
By code you can get connection in this way:
public static SqlConnection getConnection()
{
string conn = string.Empty;
conn = System.Configuration.ConfigurationManager.ConnectionStrings["myConn"].ConnectionString;
SqlConnection aConnection = new SqlConnection(conn);
return aConnection;
}
You can set ConnectionTimeout
only you create a instance.
When instance is create you don't change this value.
Solution 4 - C#
You could always add it to your Connection String:
connect timeout=180;
Solution 5 - C#
You can add Connection Timeout=180;
to your connection string
Solution 6 - C#
You can also use the SqlConnectionStringBuilder
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(ConnectionString);
builder.ConnectTimeout = 10;
using (var connection = new SqlConnection(builder.ToString()))
{
// code goes here
}
Solution 7 - C#
Old post but as it comes up for what I was searching for I thought I'd add some information to this topic. I was going to add a comment but I don't have enough rep.
As others have said:
connection.ConnectionTimeout is used for the initial connection
command.CommandTimeout is used for individual searches, updates, etc.
But:
connection.ConnectionTimeout is also used for committing and rolling back transactions.
Yes, this is an absolutely insane design decision.
So, if you are running into a timeout on commit or rollback you'll need to increase this value through the connection string.
Solution 8 - C#
You can set the connection timeout to the connection level and command level.
>Add "Connection Timeout=10" to the connection string. Now connection timeout is 10 seconds.
var connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;Connection Timeout=10";
using (var con = new SqlConnection(connectionString))
{
}
}
>Set the of CommandTimeout property to SqlCommand
var connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword";
using (var con = new SqlConnection(connectionString))
{
using (var cmd =new SqlCommand())
{
cmd.CommandTimeout = 10;
}
}
}
Solution 9 - C#
You need to use command.CommandTimeout
Solution 10 - C#
I found an excellent blogpost on this subject: https://improve.dk/controlling-sqlconnection-timeouts/
Basically, you either set Connect Timeout in the connection string like this:
Data Source=server;Initial Catalog=databaseUser Id=username;Password=password;Connect Timeout=30
Or you set ConnectionTimeout on the command object like this:
sqlCommand.CommandTimeout = 30;
Be aware that the timeout time is in seconds.
Furthermore, this timeout does not account for loss of connection because of situation like a dead or overloaded server. Those would eventually trigger a TCP timeout. See the blogpost for a nice extension example to handle that too.