CODEWITHSUNDEEP
c#.netsql-servervisual-studio-2008ado.net
George2
George2

Reputation: 45761

Timeout setting for SQL Server

I am using VSTS 2008 + ADO.Net + C# + .Net 3.5 + SQL Server 2008. I am using ADO.Net at client side to connect to database server to execute a store procedure, then return result from the store procedure.

Here is my code. I have two issues about timeout,

  1. If I do not explicitly set any timeout related settings, for the connection to database server, are there any timeout settings (e.g. if can not connect to database server for some default amount of time, there will be some timeout exception?)?

  2. If I do not explicitly set any timeout related settings, for the execution of the store procedure, are there any timeout settings (e.g. if can not retrieve results from server to ADO.Net client for some default amount of time, there will be some timeout exception?)?

        using (SqlConnection currentConnection = new SqlConnection("Data Source=.;Initial Catalog=TestDB;Trusted_Connection=true;Asynchronous Processing=true"))
    {
        // check current batch conut
        currentConnection.Open();
        using (SqlCommand RetrieveOrderCommand = new SqlCommand())
        {
            RetrieveOrderCommand.Connection = currentConnection;
            RetrieveOrderCommand.CommandType = CommandType.StoredProcedure;
            RetrieveOrderCommand.CommandText = "prc_GetOrders";
            RetrieveBatchCountCommand.Parameters.Add("@Count", SqlDbType.Int).Direction = ParameterDirection.Output;
            RetrieveBatchCountCommand.ExecuteNonQuery();
            int rowCount = Convert.ToInt32(RetrieveOrderCommand.Parameters["@Count"].Value);
        }
    }

Upvotes: 27

Views: 98660

Answers (3)

user3775101
user3775101

Reputation: 1

In sqlconnection class, there is a property by name ConnectionTimeout.

This cannot be directly used to set desired connection timeout value as it is readonly i.e. only "get" is implemented & "set" is not implemented on this property.So we have to use keyword "Connection Timeout" in the connection string itself & set the desired value.

exi:

Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=SSPI;Connection Timeout=30";(30 means 30 seconds)

30 seconds is the maximum time given to establish connection to the server(like 172.160.0.2 OR something like ADMINISTRATOR\\SQLEXPRESS).If it could not immediately establish connection to server , then it will try up to 30 seconds.

If server is valid & is able to connect to server & if database name or login credentials are invalid , then this timeout won't be applicable.It immediately throws exception for invalid credentials or database

Upvotes: 0

marc_s
marc_s

Reputation: 754230

As gbn already mentioned, there are two types of timeouts:

1) Connection Timeout: this is controlled by your connection string:

Data Source=.;Initial Catalog=TestDB;
   Trusted_Connection=true;Asynchronous Processing=true

If you add a Connect Timeout=120 to this string, your connection will try for 120 seconds to get opened and then aborts.

Data Source=.;Initial Catalog=TestDB;
   Trusted_Connection=true;Asynchronous Processing=true;
   Connect Timeout=120;

2) Command timeout: for each command, you can also specify a timeout - ADO.NET will wait for that amount of time before cancelling out your query. You specify that on the SqlCommand object:

    using (SqlCommand RetrieveOrderCommand = new SqlCommand())
    {
       RetrieveOrderCommand.CommandTimeout = 150;
    }

Upvotes: 52

gbn
gbn

Reputation: 432180

Yes, there are 2 kinds of timeout that can be set

  1. Connection timeout
  2. Command timeout

Both default to 30 seconds in VBA, .net etc

Upvotes: 13

Related Questions