CODEWITHSUNDEEP
c#sql-serverado.net
Stefan Zvonar
Stefan Zvonar

Reputation: 4309

Changing SqlConnection timeout at runtime

Is it possible to change the database connection timeout at runtime? As far as I know the only way is to modify the connection string, and then create a new connection instance with the modified connection string (replacing the timeout attribute of the string).

<add name="MyConnection" connectionString="Data Source=.\SQL2016;Initial Catalog=MyDatabase;Persist Security Info=False;User ID=DbUser;Password=DbPassword;Connection Timeout=6" providerName="System.Data.SqlClient" />

Some methods on a service may need to be super fast to fail when trying to connect across the internet to a database, and others can have more grace periods.

Is there a cleaner way to modify the connection time (as the database is on an internet resource)?

Note: Only interested in varying the database connection time at runtime, am aware of and not interested in changing the command execution time.

Kind regards,

Stefan

UPDATE

The answer provided by styx and Zohar Peled is more what I am after, and in particular making use of the SqlConnectiongStringBuilder class to modify connection timeout before creating an instance of a connection at runtime.

enter image description here

Upvotes: 1

Views: 6035

Answers (2)

fubo
fubo

Reputation: 45947

Some methods on a service may need to be super fast to fail, and others can have more grace periods.

I assume you want to change the CommandTimeout which is the time a query takes to run.

The ConnectionTimeout is the time, your application takes to establish the connection to the sql server. And this time is equal for each method / call / query.

using (SqlConnection sqlConnection = new SqlConnection(connstring))
{
    using (SqlCommand sqlCommand = new SqlCommand("SELECT * FROM FOO", sqlConnection))
    {
        sqlCommand.CommandTimeout = 60; //seconds here!

Upvotes: 6

styx
styx

Reputation: 1917

you can also alter the connstring itself (might be a little overkill)

        string constring = "Data Source=.\\SQL2016;Initial Catalog=MyDatabase;Persist Security Info=False;User ID=DbUser;Password=DbPassword;Connection Timeout=6\" providerName=\"System.Data.SqlClient";
        int index = constring.IndexOf("Connection Timeout=");
        var oldTimeOut = new String(constring.Substring(index).Split('=')[1].Where(Char.IsDigit).ToArray());            
        constring = constring.Replace("Connection Timeout="+oldTimeOut, "Connection Timeout=" + newTimeOut);

Upvotes: 1

Related Questions