SqlConnection timeout change

Question

I am trying to override the default SqlConnection timeout of 15 seconds and I get an error

a property or indexer cannot be assigned because it is read-only.

Is there any 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 specify a timeout for a specific request, then CommandTimeout is the way forward.

Its use:

 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 have connected it 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 determines how long the ADO driver will wait when a new connection is established. Your use seems to indicate that you need to wait longer than usual to execute a specific SQL query, in which case Anil is fine; use CommandTimeout (this is R / W) to change the expected completion time for an individual SqlCommand.

Answer 3

You can always add it to the connection string:

 connect timeout=180; 

Answer 4

A cleaner way is to set the connectionString in an XML file, such as 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 the connection 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 for instantiation. When an instance is created, you do not change this value.

Answer 5

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

Answer 6

Old post, but since it fits with what I was looking for, I thought I would add some information to this topic. I was about to add a comment, but I lack a reputation.

As others said:

connection.ConnectionTimeout is used for the initial connection

command.CommandTimeout is used for individual searches, updates, etc.

But:

connection.ConnectionTimeout is also used to complete and roll back transactions.

Yes, this is an absolutely crazy design decision.

So, if you use a timeout when committing or rolling back, you need to increase this value using the connection string.

Answer 7

You need to use command.CommandTimeout

Answer 8

You can also use SqlConnectionStringBuilder

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

Answer 9

You can set the connection timeout to the connection level and command level.

Add "Connection Timeout = 10" to the connection string. Now the 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 CommandTimeout 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; } }