how to set the query timeout from SQL connection string

Question

I want to set the querytimeout from the connection string. not the connection timeout, is it possible?

Peaceable

Answer 1

No. It's per command, not per connection.

Edit, May 2013

As requested in comment:

• SQLCommand.CommandTimeout for command execution
• There is no matching SQLConnection property (the questions says not the SqlConnection.ConnectionTimeout property

Some more notes about commands and execution time outs in SQL Server (DBA.SE). And more SO stuff: What happens to an uncommitted transaction when the connection is closed?

Minos

A link to any documentation that covers this would be useful. – Pamalapamela

Answer 2

You have always been able to specify the Connect Timeout via the SqlClient connection string, this applies to establishing a connection with the database server, not executing commands / running queries. The default Connect Timeout is 15 seconds.

With the release of Microsoft.Data.SqlClient v2.1 it's introduced the "Command Timeout" connection string property to override, if required, the default of 30 seconds for this property. Hence it is now possible to set the default command timeout via the connection string.

In order to use this new feature, with EF Core 3 and 5, you must add an explicit dependency on the updated SqlClient package by adding the following to your project file:

<PackageReference Include="Microsoft.Data.SqlClient" Version="2.1.0" />

In addition, you must update your connection string in order to increase the default command timeout - keep in mind that this will apply to your entire application, unless overridden in code by setting the SqlCommand.CommandTimeout property.

Connection string examples:

"YourDatabaseAlias": "Server={serverURL}; Initial Catalog={db}; Integrated Security=true; Command Timeout=60"

The connection string above sets the command timeout to 1 minute (60 seconds).

Hope this is useful.

Opossum

Answer 3

I have tried different values for the parameter Command Timeout in the below connection string and it worked every time as expected.

Data Source=Your_Db_Server;Initial Catalog=Your_DB;Integrated Security=true;TrustServerCertificate=true;Connect Timeout=600;Command Timeout=120

Leadwort

Answer 4

See:- ConnectionStrings content on this subject. There is no default command timeout property.

Rivy

Answer 5

You can only set the connection timeout on the connection string, the timeout for your query would normally be on the command timeout. (Assuming we are talking .net here, I can't really tell from your question).

However the command timeout has no effect when the command is executed against a context connection (a SqlConnection opened with "context connection=true" in the connection string).

Tambratamburlaine

thanks guys , its make sense casue the connection string can be used for other queris – Peaceable

Answer 6

Only from code:

namespace xxx.DsXxxTableAdapters {
    partial class ZzzTableAdapter
    {
        public void SetTimeout(int timeout)
        {
            if (this.Adapter.DeleteCommand != null) { this.Adapter.DeleteCommand.CommandTimeout = timeout; }
            if (this.Adapter.InsertCommand != null) { this.Adapter.InsertCommand.CommandTimeout = timeout; }
            if (this.Adapter.UpdateCommand != null) { this.Adapter.UpdateCommand.CommandTimeout = timeout; }
            if (this._commandCollection == null) { this.InitCommandCollection(); }
            if (this._commandCollection != null)
            {
                foreach (System.Data.SqlClient.SqlCommand item in this._commandCollection)
                {
                    if (item != null)
                    { item.CommandTimeout = timeout; }
                }
            }
        }
    }
 
    //....
 
 }

Motch

Answer 7

I find answer in FollowCode:

 SqlDataAdapter da = new SqlDataAdapter(Query, ConnectionString);
 da.SelectCommand.CommandTimeout = queryTimeoutInSeconds;

Wallin

Answer 8

you can set Timeout in connection string (time for Establish connection between client and sql). commandTimeout is set per command but its default time is 30 secend

Wallin