15

How to add CommandTimeout to connection string in web.config?

I tried:

<add name="ConnectionString" connectionString="Data Source=;Initial Catalog=;Persist Security Info=True;User ID=sa;Password=sa@123;Connect Timeout=200" providerName="System.Data.SqlClient"/>

</connectionStrings>

and this:

<add name="MyProject.ConnectionString"  
         connectionString="Data Source=127.0.0.1;Initial Catalog=MyDB;Persist Security Info=True;CommandTimeout=60;User ID=sa;Password=saPassw0rd"
         providerName="System.Data.SqlClient" />

but it didnt' work for me.

Thanks

Frederik Struck-Schøning
  • 12,471
  • 8
  • 59
  • 64
  • Possible duplicate of [how to set the query timeout from SQL connection string](https://stackoverflow.com/questions/1198951/how-to-set-the-query-timeout-from-sql-connection-string) – Michael Freidgeim Dec 19 '18 at 05:04

6 Answers6

13

I made it like this:

private readonly MyDbContext _context;

    public LinqToSql() : this(new MyDbContext())
    {
    }

    private LinqToSql(MyDbContext context)
    {
        _context = context;
        _context.CommandTimeout = 500; 

    }
Frederik Struck-Schøning
  • 12,471
  • 8
  • 59
  • 64
8

As far as I know there is no global way of setting Command timeout property, you have to set CommandTimeout property individually for each command object you create.

Manish Singh
  • 136
  • 1
2

You can set the timeout in a setting and refer to that value when the command timeout is set.
In config under appSettings add a key for CommandTimeout:

<add key="ContextCommandTimeout" value="500" />

Then in your code:

int cmdTimeout = -1;
string timeoutSettings = ConfigurationManager.AppSettings["ContextCommandTimeout"];

if(!string.IsNullOrEmpty(timeoutSettings)) 
{
    int.TryParse(timeoutSettings, out cmdTimeout);
}

if(cmdTimeout >=0)
    _context.CommandTimeout = cmdTimeout;
Frederik Struck-Schøning
  • 12,471
  • 8
  • 59
  • 64
Charles Byrne
  • 816
  • 2
  • 11
  • 20
0

Going to assume this is a .NET web-app, since it looks like one & it wasn't specified in the description / tags. This works for an app I've been developing, sets the DB command timeout to be 15 seconds:

<connectionStrings>
  <add name="ConnectionString" 
       connectionString="Server=localhost; Port=3306; Database=<DB>;
                         uid=<usr>; pwd=<pw>; default command timeout=15;" 
       providerName="MySql.Data.MySqlClient" />
</connectionStrings>

The default command timeout is applied app-wide to each individual DB request. As far as I'm aware, the default is 30 seconds if this isn't specified. For a web-app, individual DB commands that take longer than 30 seconds is a bit extreme though; you may be better served having a rethink of how you architected the solution.

Frederik Struck-Schøning
  • 12,471
  • 8
  • 59
  • 64
Lovethenakedgun
  • 617
  • 5
  • 21
  • 10
    It should be noted that this only works because the MySql ConnectionStrings element allows that property. Its not available for MSSQL for instance (so not supported using System.Data.SqlClient) – Jordan Dec 18 '19 at 01:37
  • 1
    It doesn't work for SQL Azure – Roman M Mar 18 '22 at 11:43
-3

You can add connection time out in your web.config file. Set it to 0 to have it infinite as below.

Connection Timeout=0;
Ajay Kumar Singh
  • 11
  • 1
  • 1
  • 2
    Connection Timeout is not the same as Command timeout. Connection timeout is for making the connection to the database, whereas Command timeout is for the SQL command that is then run, and can't be set via the connection string. – Stephen Mar 13 '19 at 12:07
-6

If you want to put it into your web.config, you need to add a space. Try:

Command Timeout=60;

instead of:

CommandTimeout=60;
Cody Gray
  • 230,875
  • 49
  • 477
  • 553
user8441312
  • 7
  • 1
  • 3
    It doesn't work when adding to SqlClient provider connection string, I am getting: ArgumentException: Keyword not supported: 'command timeout' – Michael Freidgeim Dec 19 '18 at 05:00