CODEWITHSUNDEEP
c#sql-server
Sora
Sora

Reputation: 2551

Connection timeout example

Is there any example where I can see how connection timeout error get throws ?

I tried to put a a select query where it select like 50 000 rows and insert it in a GridView. I set the connection string, open the connection and set System.Threading.Thread.Sleep(30000). I set the IIS Connection Timeout to be 60 sec, but it's not working and not throwing the error.
I am using Sql Server

Upvotes: 1

Views: 452

Answers (2)

user1429080
user1429080

Reputation: 9166

The Connection Timeout that you can set in the connection string is used to determine how long the client will wait for the connection to be initialized (for lack of a better word). It does not control how long the client will wait for the results of a database operation.

In order to set that timeout, you can do it using the CommandTimeout property on a SqlCommand:

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["test"].ConnectionString))
using (SqlCommand comm = new SqlCommand("testTimeout", conn))
{
    // next line is where the Connection Timeout will take 
    // effect if the connection cannot be opened in time
    conn.Open();
    comm.CommandType = System.Data.CommandType.StoredProcedure;
    comm.CommandTimeout = 60;
    // next line is where the Command Timeout takes effect
    // if the operation takes a long time to complete
    comm.ExecuteNonQuery();
}

To test the command timeout, create the "testTimeout" stored procedure and have it delay (see answer from Blorgbeard) for longer then the command timeout used. The default value for the command timeout is 30 (=30 seconds). See also MSDN.

Upvotes: 0

Blorgbeard
Blorgbeard

Reputation: 103437

In SQL Server, use the waitfor command:

waitfor delay '01:00'

Upvotes: 3

Related Questions