In this article, we are going to learn how to handle CommandTimeout with Dapper in .NET.
So, let’s start.
About Dapper
Dapper is a popular ORM tool for .NET applications that maps SQL query result to C# objects. When queries take longer to execute than expected, a Timeout value is essential to prevent performance issues. Dapper provides options for handling CommandTimeout, the maximum time in seconds a command can execute before timing out.
Prerequisites
To use Dapper, we must include the Dapper and SQL client package in our project:
- Dapper – PM>
Install-Package Dapper - SQL Client – PM>
Install-Package Microsoft.Data.SqlClient
As Dapper does not support migrations, we need to incorporate additional tools to accomplish this task. In this article, we will be using FluentMigrator to create and manage our database migrations.
- FluentMigrator – PM>
Install-Package FluentMigrator.Runner
If you want to learn how to use the FluentMigrator library to create data migrations with Dapper and ASP.NET Core you can read our article on that topic.
Entities
After the installation, we are going to create a Company entity in the Model folder:
public class Company
{
public Guid Id { get; set; }
public string Name { get; set; }
public string Address { get; set; }
public string Country { get; set; }
}
And the Employee entity in the same folder:
public class Employee
{
public Guid Id { get; set; }
public string Name { get; set; }
public int Age { get; set; }
public string Position { get; set; }
public Guid CompanyId { get; set; }
}
Here are some ways to configure CommandTimeout in Dapper:
Setting CommandTimeout in Connection String
When working with a database in a .NET Core application, the first step is to define a connection string in the appsettings.json file. We use this file to store configuration settings for the application, including database connection information. The connection string typically includes parameters such as the server name, database name, user ID, and password:
{
"ConnectionStrings": {
"SqlConnectionWithTimeout": "server=your_server; Integrated Security=false; database=DapperASPNetCore;
User Id=your_user;Password=your_password;Application Name=DapperASPNetCore; TrustServerCertificate=True;
Connection Timeout=5;"
}
}
The Connection Timeout specifies the wait time in seconds before the database connection throws an error due to the lack of response. In this case, we have set it to 5 seconds.
Setting CommandTimeout for All Queries
In Dapper, the SqlMapper class provides a variety of settings that can be configured to control various aspects of how Dapper operates. One of these settings is the CommandTimeout property, which specifies the amount of time that Dapper should wait for a SQL command to complete before timing out.
For example, if we want to set a command timeout of 50 seconds for a specific query:
SqlMapper.Settings.CommandTimeout = 50;
This will cause Dapper to wait for up to 50 seconds for the SQL command to complete before timing out and throwing an exception. Note that this setting applies to all subsequent queries that use Dapper until it is changed again. So we should be sure to set it back to the default value if we only need a custom timeout for a specific query or operation.
Note that when setting the timeout value using SqlMapper.Settings.CommandTimeout, it will override any timeout value specified in the appsettings connection string(s). Keep this in mind when setting custom timeouts for specific queries or operations using Dapper.
Setting CommandTimeout in Query Methods
The Query methods allow executing SQL queries in a single command. For this example, we are going to use the QueryMultipleAsync method, but the same applies to other Query methods as well. To set the CommandTimeout for this method, we can pass it as a parameter when calling the method:
public async Task<IEnumerable<Company>> GetCompaniesWithTimeoutInInQueryMultiple()
{
string query = @"SELECT * FROM Company";
using var connection = _context.CreateConnectionWithoutTimeout();
var multipleResult = await connection.QueryMultipleAsync(query, commandTimeout: TimeoutInSeconds);
var companies = await multipleResult.ReadAsync<Company>();
return companies;
}
This method fetches a list of companies from the database using a connection without a timeout. It then sets a timeout TimeoutInSeconds on the connection and executes the query using the QueryMultipleAsync method. The commandTimeout parameter of the QueryMultipleAsync method ensures that the query is aborted if it takes longer than the specified timeout value. The results are mapped to the Company class and returned as a list of companies.
Conclusion
Handling CommandTimeout exceptions is an important consideration when working with databases, particularly when dealing with large amounts of data. Dapper provides several ways to handle these exceptions, including setting the timeout value on individual queries and intercepting command executions to set the timeout value. By using these techniques, you can ensure that your code runs smoothly and efficiently, even when working with large amounts of data.