CODEWITHSUNDEEP
c#sql-server
shlomtzi
shlomtzi

Reputation:

how to set the query timeout from SQL connection string

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

Upvotes: 49

Views: 151955

Answers (8)

parfilko
parfilko

Reputation: 1454

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; }
                }
            }
        }
    }
 
    //....
 
 }

Upvotes: 0

rajibdotnet
rajibdotnet

Reputation: 1596

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

Upvotes: 9

Carlos Gregorio
Carlos Gregorio

Reputation: 506

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.

Upvotes: 20

hamid reza Heydari
hamid reza Heydari

Reputation: 62

I find answer in FollowCode:

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

Upvotes: -1

hamid reza Heydari
hamid reza Heydari

Reputation: 62

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

Upvotes: -3

gbn
gbn

Reputation: 432180

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

Edit, May 2013

As requested in comment:

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?

Upvotes: 46

Iain Hoult
Iain Hoult

Reputation: 4005

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).

Upvotes: 3

AnthonyWJones
AnthonyWJones

Reputation: 189437

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

Upvotes: 5

Related Questions