CODEWITHSUNDEEP
c#sql-serverconnection-stringsqlconnection
raja_89
raja_89

Reputation: 3

Connection Timeout property in SqlServer Connection string not Working

I have been trying to Check if the Database is reachable and do custom actions according to the Connection state. My code is as follows

string connectionString = "Data Source =" + textBox1.Text + ";Initial Catalog=master;Integrated Security=true;Connect Timeout=1";

        SqlConnection connection = new SqlConnection(connectionString);
        try
        {
            connection.Open();
             //do some action
            connection.Close();
        }
        catch (SqlException)
        {
            connection.Close();
            //do some action
        }

I provide the Target machine name through a TextBox. In the above code whenever a connection is successfully made, the code within the try block executes within seconds. But if the connection can't be made, it ignores the Connect Timeout property and waits for 30 seconds before moving to the catch block.

I tried the following link connection timeout property in connection string ignored and found no success. I have tried for a Connect Timeout value of 5,10,15 and 20 seconds and nothing seems to work.

Help me out if something is wrong with my approach.

Thanks!

Upvotes: 0

Views: 816

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46193

You need to specify the correct connection string keyword Connection Timeout=1.

In my experience, the actual time before the exception is raised may a couple of seconds longer than specified when the error is due to the server being unreachable rather than a full connection pool, which results in an InvalidOperationException rather than a SqlException. I suggest a using block to ensure to test connection is immediately disposed:

try
{
    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();
    }
}
catch (SqlException ex)
{
    //do some action
}
catch (InvalidOperationException ex)
{
    //do some action
}

Consider using the SqlConnectionStringBuilder class to avoid injection vulnerability.

Upvotes: 1

Related Questions