Changing SqlConnection timeout

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

You could always add it to your Connection String:

connect timeout=180;

Answer 5

You can also use the SqlConnectionStringBuilder

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(ConnectionString);
builder.ConnectTimeout = 10;
using (var connection = new SqlConnection(builder.ToString()))
{
    // code goes here
}

Answer 6

You can add Connection Timeout=180; to your connection string

Answer 7

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.

Answer 8

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

}

Answer 9

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.

Answer 10

You need to use command.CommandTimeout