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

User · Answer

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.

User · Answer

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

User · Answer

I found an excellent blogpost on this subject  https   improve dk controlling-sqlconnection-timeouts  Basically  you either set Connect Timeout in the connection string  or you set ConnectionTimeout on the command object  Be aware that the timeout time is in seconds

User · Answer

You can add Connection Timeout 180  to your connection string

User · Answer

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

User · Answer

A cleaner way is to set connectionString in xml file  for example Web Confing WepApplication  or App Config StandAloneApplication      lt connectionStrings gt       lt remove name  myConn   gt       lt add name  myConn  connectionString  User ID sa Password XXXXX Initial Catalog qualitaBorri Data Source PC NAME SQLEXPRESS Connection Timeout 60   gt     lt  connectionStrings gt    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

User · Answer

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

User · Answer

You could always add it to your Connection String   connect timeout 180

User · Answer

You need to use command CommandTimeout

User · Answer

You can also use the SqlConnectionStringBuilder  SqlConnectionStringBuilder builder   new SqlConnectionStringBuilder ConnectionString   builder ConnectTimeout   10  using  var connection   new SqlConnection builder ToString              code goes here

[c#] Changing SqlConnection timeout

Examples related to c#

Examples related to .net

Examples related to sql-server

Examples related to sqlconnection