Dapper - Get information about the executed query statement

Question

I replaced the MySQL.Data package with Dapper. To fetch one user from the database all I have to do now is

public Task<User> GetUser(string username) 
{
    using IDbConnection databaseConnection = new MySqlConnection("connectionString");
        
    DynamicParameters parameters = new DynamicParameters();
    parameters.Add("@username", username, DbType.String);
        
    return databaseConnection.QueryFirstOrDefaultAsync<User>(
        "SELECT * FROM person WHERE username = @username",
        parameters);
}

but I would like to add some trace level logging. Is there a way I can fetch the query string before executing it?

As explained [here](https://stackoverflow.com/a/50875558/5779732) MiniProfiler may help. That question is about Dapper-Extensions; but same thing is applicable for Dapper as well. — Amit Joshi, Aug 17 '20 at 05:31
Alternatively, you can also do that with wrapper over `DbConnection` as explained [here](https://stackoverflow.com/q/44194760/5779732). — Amit Joshi, Aug 17 '20 at 05:32
@AmitJoshi thank you very much but I don't want to use an external tool for this, it's just about accessing the generated sql command :) — Question3r, Aug 17 '20 at 18:32
and thanks for your second comment, but this feels really wrong for me. I have to reimplement / wrap the whole thing just to get access to it ... there has to be a better solution for this — Question3r, Aug 17 '20 at 18:33
Dapper doesn't expose this functionality because Dapper is intended to be as simple and lightweight as possible. — Ian Kemp, Aug 18 '20 at 19:40

score 2 · Answer 1 · answered Aug 29 '20 at 16:45

Create your own IDbCommand which would wrap your real IDbCommand and log what you want to intercept.

But since Dapper creates the IDbCommand automatically, you also have to create your own IDbConnection (see CreateCommand below) which would wrap your real IDbConnection and in case would use your own IDbCommand.

Example:

/// <summary>
/// This is just a wrapper around IDbConnection, 
/// which allows us to build a wrapped IDbCommand for logging/debugging
/// </summary>
public class InterceptedDbConnection : IDbConnection
{
    private readonly IDbConnection _conn;

    public InterceptedDbConnection(IDbConnection connection)
    {
        _conn = connection;
    }

    public string ConnectionString { get => _conn.ConnectionString; set => _conn.ConnectionString = value; }

    public int ConnectionTimeout => _conn.ConnectionTimeout;

    public string Database => _conn.Database;

    public ConnectionState State => _conn.State;

    public IDbTransaction BeginTransaction() => _conn.BeginTransaction();

    public IDbTransaction BeginTransaction(IsolationLevel il) => _conn.BeginTransaction(il);

    public void ChangeDatabase(string databaseName) => _conn.ChangeDatabase(databaseName);

    public void Close() => _conn.Close();

    public IDbCommand CreateCommand()
    {
        // Wrap real command under InterceptedDbCommand
        IDbCommand underlyingCommand = _conn.CreateCommand();
        return new InterceptedDbCommand(underlyingCommand);
        // you could also save this into a "LastCommand" to expose it 
        // and explore parameter types that Dapper used
    }

    public void Dispose() => _conn.Dispose();

    public void Open() => _conn.Open();
}

/// <summary>
/// This is just a wrapper around IDbCommand, 
/// which allows us to log queries
/// or inspect how Dapper is passing our Parameters
/// </summary>
public class InterceptedDbCommand : IDbCommand
{
    private readonly IDbCommand _cmd;
    public InterceptedDbCommand(IDbCommand command)
    {
        _cmd = command;
    }

    public string CommandText { get => _cmd.CommandText; set => _cmd.CommandText = value; }
    public int CommandTimeout { get => _cmd.CommandTimeout; set => _cmd.CommandTimeout = value; }
    public CommandType CommandType { get => _cmd.CommandType; set => _cmd.CommandType = value; }
    public IDbConnection Connection { get => _cmd.Connection; set => _cmd.Connection = value; }

    public IDataParameterCollection Parameters => _cmd.Parameters;

    public IDbTransaction Transaction { get => _cmd.Transaction; set => _cmd.Transaction = value; }
    public UpdateRowSource UpdatedRowSource { get => _cmd.UpdatedRowSource; set => _cmd.UpdatedRowSource = value; }

    public void Cancel() => _cmd.Cancel();

    public IDbDataParameter CreateParameter() => _cmd.CreateParameter();

    public void Dispose() => _cmd.Dispose();
    
    public void Prepare() => _cmd.Prepare();

    public int ExecuteNonQuery()
    {
        // TODO: Log _cmd.CommandText + _cmd.Parameters
        return _cmd.ExecuteNonQuery();
    }

    public IDataReader ExecuteReader()
    {
        // TODO: Log _cmd.CommandText + _cmd.Parameters
        return _cmd.ExecuteReader();
    }

    public IDataReader ExecuteReader(CommandBehavior behavior)
    {
        // TODO: Log _cmd.CommandText + _cmd.Parameters
        return _cmd.ExecuteReader(behavior);
    }

    public object ExecuteScalar()
    {
        // TODO: Log _cmd.CommandText + _cmd.Parameters
        return _cmd.ExecuteScalar();
    }

}

Last, just replace your connection by the wrapped one:

IDbConnection databaseConnection = new InterceptedDbConnection(MySqlConnection("connectionString"));

Dapper - Get information about the executed query statement

1 Answers1