Adjusting CommandTimeout in Dapper.NET?

Question

I'm trying to run SQL backups through a stored procedure through Dapper (the rest of my app uses Dapper so I'd prefer to keep this portion running through it as well). It works just fine until the CommandTimeout kicks in.

using (var c = SqlConnection(connstring))
{
    c.Open();
    var p = new DynamicParameters();
    // fill out p

    c.Execute("xp_backup_database", p, commandType: CommandType.StoredProcedure);
}

The only CommandTimeout setting I know of is in SqlCommand. Is there a way to set this via Dapper?

For whatever reason, I can't answer my own question right now. But it seems just adding the named argument "commandTimeout: 0" to c.Execute() took care of this. — sh-beta, Jan 09 '12 at 21:15

score 140 · Accepted Answer · edited Oct 25 '17 at 15:08

140

Yes, there are multiple versions of the Execute function. One (or more) of them contains the commandTimeout parameters:

public static int Execute(this IDbConnection cnn, string sql, 
                dynamic param = null, IDbTransaction transaction = null, 
                            int? commandTimeout = null, CommandType? commandType = null)

Taken from SqlMapper.cs

edited Oct 25 '17 at 15:08

Liam

27,717
28
128
190

answered Jan 09 '12 at 21:22

jzacharuk

2,058
1
16
22

6

I had the same problem, but with Query method, however the solution worked for it too, as it has a commandTimeout parameter too. – jahu May 12 '15 at 09:11
From my experience the commandTimeout property is not used when using async await as stated in the msdn documentation (https://goo.gl/2NVlXL). Bit of a stinger hope it helps. – Dr Schizo Sep 14 '15 at 07:31
2

@DrSchizo why would it not be used, there's no reason for Async Await to avoid the Time out – Mrinal Kamboj Mar 31 '17 at 06:49
1

@DrSchizo The docs say its not used with asynchronous methods like BeginExecuteReader, not async/await. I assume this is because if you use BeginExecuteReader, it is assumed you will use your own timeout logic. – jugg1es Dec 02 '17 at 21:43
3

Is it possible to set this timeout for all queries? I tried with the `SqlConnection.ConnectionTimeout Property` but it says that it's read only. I would need it for some custom migration programs. It's tedious to type it with every statement. – tedi Jan 29 '18 at 14:08
7

@jedatkinports SqlMapper.Settings.CommandTimeout I believe is what you are after. – Shiv Jul 24 '18 at 20:26
1

@Tadej The `ConnectionTimeout` property is not a global timeout setting for all connections but rather the amount of time the system will wait for a connection to open. You can only set it in the connection string because once the connection is open it is too late, hence it is a readonly property. See https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.connectiontimeout – Caltor Apr 20 '21 at 09:19
If yoiu do want a global setting see the answer by Mozart below https://stackoverflow.com/a/62655346/470014 – Caltor Apr 20 '21 at 09:20

score 75 · Answer 2 · edited Jun 20 '18 at 09:46

75

Example from original question with accepted answer added, in case anyone wants it. (Timeout is set to 60 seconds):

using (var c = SqlConnection(connstring))
{
    c.Open();
    var p = new DynamicParameters();
    // fill out p

    c.Execute("xp_backup_database", p, commandTimeout: 60, 
                                       commandType: CommandType.StoredProcedure);
}

edited Jun 20 '18 at 09:46

SteveC

15,808
23
102
173

answered Aug 13 '15 at 18:38

Adrian Carr

3,061
1
34
38

Mozart · Answer 3 · 2021-10-19T08:16:07.090

46

There is no need to set command timeout for all queries/Db Calls. You can set it globally like below.

Dapper.SqlMapper.Settings.CommandTimeout = 0;

You can initialize this static property on the application load or in the database class constructor.

This helps in removing duplication, and in case you decide to change it later, you change it once in one place.

edited Oct 19 '21 at 08:16

answered Jun 30 '20 at 10:25

Mozart

2,117
2
20
38

where do we put this command? – Johnathan Li Oct 18 '22 at 03:06
@JohnathanLi It depends on the type of your application. but usually somewhere in the initialization code of you app. – Mozart Oct 18 '22 at 09:41

score 6 · Answer 4 · answered Jul 03 '20 at 19:19

6

I was able to solve my problem using connection.Query setting the timeout directly

int timeOutInSeconds = 60;
.
.
.
result = conn.Query<list>(stringQuery, new {parameters, ..}, null, true, timeOutInSeconds).ToList();

answered Jul 03 '20 at 19:19

Amanda Mata

117
2
4

Adjusting CommandTimeout in Dapper.NET?

4 Answers4

Linked