How do I increase the Command Timeout in OrmLite ServiceStack?

Question

I am using ServiceStack OrmLite SqlServer v3.9.71 and have the following connection string:

<add key="ConnStr" value="Data Source=my-db;Initial Catalog=Users;Integrated Security=SSPI;Connection Timeout=666"/>

and am using the following to run a query which takes 2-3 minutes to come back:

using (Db)
{
    var result = new ResultDto();

    Parallel.Invoke(
       () => { result.StatOne = Db.Dictionary<DateTime, int>(query1); },
       () => { result.StatTwo = Db.Dictionary<DateTime, int>(query2); }
    );

    return result;
}

When putting a break point on the Db object, I can see the connection time out to be 666 but I can't figure out how to set/increase the Command Timeout every time I run the above it times out after 30 seconds which is the default timeout.

Any ideas?

Digged

This looks like a Command Timeout not a Connection Timeout. – Greff

@bummi, thanks I have now updated the question. – Digged

I'm not familiar with OrmLite, but maybe this could be useful? OrmLite Command Timeout – Greff

Answer 1

The timeout can be set in OrmLite with OrmLiteConfig.CommandTimeout that as a global config can either be statically configured either once on StartUp:

OrmLiteConfig.CommandTimeout = 666;

Or you can instead set the CommandTimeout scoped to a specific db connection with:

using (var db = DbFactory.Open())
{
    db.SetCommandTimeout(60);
    db.Select(...);
}

Neoplasty

tnx, is that a global config? where do you suggest I set this up? when setting up the container? any way to set it per query as opposed to global? – Digged

@Digged I've updated the answer to show how to set it for specific requests. – Neoplasty

Be very careful using db.SetCommandTimeout in a threaded application as in our case it seemed to cause running commands to instantly timeout on other connections on other threads. Setting the Global one fixed the issue. Just took us 3 days to work out why doing 1 thread worked and multiple timed out after 30 seconds when we set timeout to unlimited by setting 0. Use global setting if possible to avoid lots of pain. – Sides

@Seer: Do you recall how you were opening connections in each thread? Was each thread creating a new IDbConnection instance and setting the timeout, or was each thread setting the timout on a shared instance? The IDbConnection instance is not threadsafe, and in my case led to issues when trying to share it amongst queries in different threads -- I haven't tried with timeout settings yet. – Insistent