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

Solution 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.

Solution 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.

Solution 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.

Solution 4

You could always add it to your Connection String:

connect timeout=180;

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

View more solutions

Comments

[m0g]

Connection Timeout is different from CommandTimeout. Connection Timeout is for the amount of time it takes to resolve the initial connection to the database. CommandTimeout changes the timeout period for the particular query.

[m0g]

Connection Timeout is different from CommandTimeout. Connection Timeout is for the amount of time it takes to resolve the initial connection to the database. CommandTimeout changes the timeout period for the particular query.

[Bacon Bits]

@m0g I don't understand why this isn't the accepted answer. The question here probably is very clearly about the Connection Timeout, which defaults to 15, and is a read-only property of an SqlConnection that must be defined in the Connection String. The line of code the poster lists specifically tries to set SqlConnection.ConnectionTimeout. The Command Timeout defaults to 30, and is a read/write parameter of the SqlCommand, a completely different object. The question's error message shows a value of 15, and the question specifically says SqlConnection, not SqlCommand.

[m0g]

@BaconBits what the question poster described and asked versus what he wants is different therefore the accepted answer is something different.

[LarryBud]

He's not talking about the command timeout, he's talking about the connection timeout.

[Triynko]

"Connection.ConnectionTimeout is also used for committing and rolling back transactions. Yes, this is an absolutely insane design decision." Really? I don't believe this is true. If it was true, then any transaction that lasts longer than the connection timeout of 15 seconds would fail. You need to clarify.

[Triynko]

Now, if you mean it used as the timeout for submitting just the commit or rollback command by itself, then no... that's not an insane decision. It's basically an instantaneous command that should return immediately, since it's just signaling the server to commit or roll back. If it fails to reach the server and get a response back immediately, then that's basically a connection timeout, which is why it would make sense to use that way.

[Tomer Shetah]

Hello and welcome to SO! Please read the tour, and How do I write a good answer? Adding code samples is always helpful.

[ColinWa]

Avoid using links only, the links could change in the future. Add some valuable tips and some source code to help clarify your answer if necessary

[Mark Longmire]

I think your answer is the cleanest. My application is through CLI, and I like adding to the connection string. Thanks.