Proposal: Built-in extensibility for structured logging (commands, parameters, connections, transactions)

[paulpeeters]

Hi,

I'm currently looking for a robust way to implement logging with Dapper and would like to propose (or ask for guidance on) a more integrated/extensible approach.

Use case:

• Log executed SQL statements including expanded parameters
• Log connection lifecycle events (open/close), ideally with relevant (safe) metadata from the connection string
• Optionally log transaction boundaries (begin/commit/rollback)
• Ability to enable/disable logging easily (e.g. enabled in development/staging, disabled in production)

Problem:
I’ve experimented with wrapping DbConnection / DbCommand, but this tends to:

• break or interfere with Dapper internals (e.g. list parameter expansion for IN (...))
• become fragile and hard to maintain across different providers

Question / proposal:
Would it be possible to introduce an official extensibility point (e.g. interceptor, pipeline hook, or diagnostic listener) that allows:

• inspecting the final command text + parameters as Dapper executes it
• observing connection and transaction events without wrapping providers
• plugging in logging in a clean, opt-in way

Something similar in spirit to logging/interception patterns in other ORMs would make this much easier and safer to implement.

I'm limited in knowledge to be able to contribute or help refine the design but I hope this is something that could fit within Dapper’s scope.

Thanks!

[mgravell]

The first bullet - do you mean things like in @foo being expanded to something involving foo0, @foo1 etc? Or do you mean the actual final parameter values?

Either way: maybe MiniProfiler already does everything you want here, or could with some minor tweaks - last time I touched it, it didn't log open/close/etc, but it definitely sees them, so could be enabled. We used MiniProfiler on peek Stack Overflow traffic back when the site was at its busiest, so it can definitely handle most load scenarios.

Pushing this through another logging API is probably doable, but we'd need to figure out the best of a few candidates, and it would almost certainly add dependencies.

[paulpeeters]

Being able to view the expansion from "column in @foo" into several parameters is not the real goal. Being able to view the SQL statement and the actual parameter values is what I want. The issue I had is that my wrapping of DbCommand/DbConnection, did break this expansion. I rarely use it and in a few cases I needed it, I converted @foo to a comma separated list of values, and wrote "FIND_IN_SET(column, @foo_as_csv) > 0" which works for MySQL but not for other providers.

I am more worried that my implementation of wrapping, could break some other functionality in Dapper.

The wrapper taking care of logging I that I constructed is something like this

    public class LoggingDbCommand : DbCommand
    {
        private readonly DbCommand _inner;
        private readonly ILogger _logger;
        private readonly DbConnectionLoggingOptions _options;

        public LoggingDbCommand(DbCommand inner, ILogger logger, DbConnectionLoggingOptions options)
        {
            _inner = inner ?? throw new ArgumentNullException(nameof(inner));
            _logger = logger ?? throw new ArgumentNullException(nameof(logger));
            _options = options ?? throw new ArgumentNullException(nameof(options));
        }

       public override int ExecuteNonQuery() => ExecuteWithLogging(() => _inner.ExecuteNonQuery());

       // ... other overrides here ...

       private T ExecuteWithLogging<T>(Func<T> execute)
       {
           if (!_logger.IsEnabled(_options.LogLevel))
               return execute();

           var sw = Stopwatch.StartNew();
           try
           {
               return execute();
           }
           finally
           {
               sw.Stop();
               Extensions.LoggerExtensions.LogSqlCommand(
                   _logger,
                   _inner.CommandText,
                   _options.LogParameters ? _inner.Parameters.Cast<DbParameter>() : null,
                   sw.ElapsedMilliseconds,
                   _options.LogLevel);
           }
       }
   }

This works, but the failing of the expansion of @foo in several parameters seems is likely caused by the override of CreateDbCommand in the wrapper of DbConnection

    public class LoggingDbConnection : DbConnection
    {
        private readonly DbConnection _inner;
        private readonly ILogger<LoggingDbConnection> _logger;
        private readonly DbConnectionLoggingOptions _options;

        public LoggingDbConnection(DbConnection inner, ILogger<LoggingDbConnection> logger, DbConnectionLoggingOptions options)
        {
            _inner = inner ?? throw new ArgumentNullException(nameof(inner));
            _logger = logger ?? throw new ArgumentNullException(nameof(logger));
            _options = options ?? throw new ArgumentNullException(nameof(options));
        }

       protected override DbCommand CreateDbCommand()
       {
           var cmd = _inner.CreateCommand();
           return new LoggingDbCommand(cmd, _logger, _options);
       }

[paulpeeters]

It turns out the issue with the expansion of @foo into multiple parameters was not caused by my wrapper, but by a custom TypeHandler I had registered for List<int>.

I originally introduced this handler to map CSV-formatted columns directly to List<int>. However, this interfered with Dapper’s internal parameter expansion (e.g. for IN (@foo) scenarios). After removing the handler, the wrapper correctly shows the expanded parameters.

For reference, this was the handler:

public class CsvIntListHandler : SqlMapper.TypeHandler<List<int>>
{
    // ...
}

static async Task Main()
{
    SqlMapper.AddTypeHandler(new CsvIntListHandler());
    // ...
}

That said, I would still strongly appreciate having a built-in or officially supported way to log:

• executed SQL commands (including expanded parameters)
• connection lifecycle events (open/close, with safe metadata)
• transaction boundaries (begin/commit/rollback)

An extensibility point (e.g. interceptor or diagnostic hook) for this would make such scenarios much easier to implement without relying on wrappers that can interfere with Dapper’s behavior.

Thanks!