c# - How to log/get a SQL query auto-generated by Dapper Extensions?

Question

I am using Dapper Extensions (DE) as ORM. It is consumed in Data Access Layer which is implemented using Repository pattern. SQL Express is back-end RDBMS.

DE automatically generates most of the queries for me. I want to log those auto-generated queries for debugging purpose.

There are two ways I can see to achieve this: -

1. Get the SQL query generated by DE (before or after it is executed) and write it to log. This is preferred way for me as I already have my logging module (using log4net) in place. The only thing I need is the SQL generated by DE.
2. Integrate DE with some logging tool. I read this answer. It looks possible using MiniProfiler tool; but as I said above, I already have my logging module in place. I do not want to use other tool just for logging SQL queries.

How to log/get a SQL query auto-generated by Dapper Extensions without using any other logging tool?

The other similar question is about Dapper. This question is about Dapper Extensions.

Answer 1

Looking at the comment from @MarcGravell and this question about doing the same with Dapper, MiniProfiler.Integrations is better way to implement logging for Dapper Extensions.

Above linked question is about Dapper. But Dapper Extensions uses Dapper internally. So, if logging is implemented for Dapper, same works for Dapper Extensions as well.

More details could be found on GitHub.

Sample code is as below:

var factory = new SqlServerDbConnectionFactory(connectionString);
CustomDbProfiler cp = new CustomDbProfiler();
using(var connection = DbConnectionFactoryHelper.New(factory, cp))
{
    //DB Code
}
string log = cp.ProfilerContext.GetCommands();

You can use in-build CustomDbProfiler using CustomDbProfiler.Current if that suits your need. cp.ProfilerContext.GetCommands() will return ALL the commands (success and failed) no matter how many times you call the method. I am not sure but, it might be maintaining concatenated string (StringBuilder may be) internally. If this is the case, this may slow down the performance. But, in my case, logging is disabled by default. I only enable logging when I need to debug something. So this is not a problem for me.

This also may raise memory footprint issue if single connection is used over very large scope. To avoid this, make sure CustomDbProfiler instance is disposed properly.

As mentioned in question, initially, I wanted to avoid this way (using external tool/library). But, MiniProfiler.Integrations is NOT writing the log itself. I can simply get all the queries generated and provide those to my logger module to dump into the file. That is why, this looks more suitable to me now.

---

MiniProfiler.dll internally implements similar logic (in StackExchange.Profiling.Data.ProfiledDbConnection and StackExchange.Profiling.Data.ProfiledDbCommand classes) which is mentioned here and here. So, if I decide to (in future may be) bypass MiniProfiler, I can use this implementation myself.