CODEWITHSUNDEEP
c#.netsql-serversqlconnection
Haymak3r
Haymak3r

Reputation: 1411

Changing SqlConnection timeout

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

Upvotes: 103

Views: 255757

Answers (10)

macnerd
macnerd

Reputation: 371

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.

Upvotes: 5

Jakir Hossain
Jakir Hossain

Reputation: 111

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

}

Upvotes: 8

Anil Mathew
Anil Mathew

Reputation: 2696

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.

Upvotes: 194

Kelvin
Kelvin

Reputation: 236

You can also use the SqlConnectionStringBuilder

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

Upvotes: 15

daniele3004
daniele3004

Reputation: 13880

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.

Upvotes: 26

Nick Thompson
Nick Thompson

Reputation: 73

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.

Upvotes: 6

Horev Ivan
Horev Ivan

Reputation: 268

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

Upvotes: 9

LewisT
LewisT

Reputation: 74

You need to use command.CommandTimeout

Upvotes: 2

KeithS
KeithS

Reputation: 71565

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.

Upvotes: 48

Justin Niessner
Justin Niessner

Reputation: 245389

You could always add it to your Connection String:

connect timeout=180;

Upvotes: 18

Related Questions