In my last post we touched on what we get from EF Core in our Serilog log entries. But what if we are using dapper? How can we trace the SQL and get query execution times?
We already have our Seriog outputting to SQL Server and our middleware from the last post.
Unfortunately, we don’t get any logging out of the box for dapper like we do with EF Core - so, we need to do a bit of work.
We’ll create a class that wraps the dapper methods we use and we’ll instrument those methods. We turn stats on for the connection and get them after the query has finished, passing them to a central method that does the logging.
public async Task<IEnumerable<T>> GetRecords<T>(string sql, object parameters = null)
{
IEnumerable<T> records = default(IEnumerable<T>);
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.StatisticsEnabled = true;
await connection.OpenAsync();
try
{
records = await connection.QueryAsync<T>(sql, parameters);
}
catch (Exception originalException)
{
throw AddAdditionalInfoToException(originalException, "Error: GetRecords: " + typeof(T).Name, sql, parameters);
}
var stats = connection.RetrieveStatistics();
LogInfo("GetRecords: " + typeof(T).Name, stats, sql, parameters);
}
return records;
}
public async Task<T> GetRecord<T>(string sql, object parameters = null)
{
T record = default(T);
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.StatisticsEnabled = true;
await connection.OpenAsync();
try
{
record = await connection.QueryFirstOrDefaultAsync<T>(sql, parameters);
}
catch (Exception originalException)
{
throw AddAdditionalInfoToException(originalException, "Error: GetRecord: " + typeof(T).Name, sql, parameters);
}
var stats = connection.RetrieveStatistics();
LogInfo("GetRecord: " + typeof(T).Name, stats, sql, parameters);
}
return record;