c# - How to get last executed SQL query by SqlConnection?

Question

I have a SqlConnection object accessible to my code. All other ADO.NET objects like SqlCommand, SqlParameter etc are not accessible to me. These other objects are consumed by Dapper Extensions ORM.

My application executes SQL queries using SqlConnection object and Dapper Extensions method. SQL query is auto generated by Dapper Extensions; generated query is not accessible to me. I want to log this SQL query.

I already have my logging module in place and the only thing I need is the last SQL query executed by connection object.

How to get last executed SQL query by SqlConnection?

Following does not work because SqlCommand is not accessible.

If I get underlying SqlCommand, I can build the query from it using the code below; unfortunately, it is not accessible to me.

public string GetCommandLogString(IDbCommand command)
{
    string outputText;
    if(command.Parameters.Count == 0)
    {
        outputText = command.CommandText;
    }
    else
    {
        StringBuilder output = new StringBuilder();
        output.Append(command.CommandText);
        output.Append("; ");

        IDataParameter objIDataParameter;
        int parameterCount = command.Parameters.Count;
        for(int i = 0; i < parameterCount; i++)
        {
            objIDataParameter = (IDataParameter)command.Parameters[i];
            output.Append(string.Format("{0} = '{1}'", objIDataParameter.ParameterName, objIDataParameter.Value));

            if(i + 1 < parameterCount)
            {
                output.Append(", ");
            }
        }
        outputText = output.ToString();
    }
    return outputText;
}

Answer 1

An approach that I've used in the past, when I didn't want to rely upon any external tools (or when the tools were lacking, like when working with MS Access) is to use database connection and command "wrapper" classes so that I can add logging to any of their methods or properties.

To use it, you pass whatever connection you want to use into the WrappedDbConnection's constructor as below:

using (var conn = new WrappedDbConnection(GetMyConnection()))
{
    // Do work using Dapper here against "conn"
}

(Note: When the WrappedDbConnection instance's Dispose method is called, that will be passed onto the underlying connection and so you don't need a using for the WrappedDbConnection and a separate using for your connection - you only need one using, as shown above).

The two classes that you need are defined below.

Note that there are Console.WriteLine calls in the methods ExecuteNonQuery, ExecuteReader, ExecuteReader and ExecuteScalar that will write out what query is about to be executed. You may want to change this for your requirements to write out the query after it's completed or you might want to use a different output that Console.Writeline but those should be simple enough changes to make.

public class WrappedDbConnection : IDbConnection
{
    private readonly IDbConnection _conn;
    public WrappedDbConnection(IDbConnection connection)
    {
        if (connection == null)
            throw new ArgumentNullException(nameof(connection));

        _conn = connection;
    }

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

    public int ConnectionTimeout
    {
        get { return _conn.ConnectionTimeout; }
    }

    public string Database
    {
        get { return _conn.Database; }
    }

    public ConnectionState State
    {
        get { return _conn.State; }
    }

    public IDbTransaction BeginTransaction()
    {
        return _conn.BeginTransaction();
    }

    public IDbTransaction BeginTransaction(IsolationLevel il)
    {
        return _conn.BeginTransaction(il);
    }

    public void ChangeDatabase(string databaseName)
    {
        _conn.ChangeDatabase(databaseName);
    }

    public void Close()
    {
        _conn.Close();
    }

    public IDbCommand CreateCommand()
    {
        return new WrappedDbCommand(_conn.CreateCommand());
    }

    public void Dispose()
    {
        _conn.Dispose();
    }

    public void Open()
    {
        _conn.Open();
    }
}

public class WrappedDbCommand : IDbCommand
{
    private readonly IDbCommand _cmd;
    public WrappedDbCommand(IDbCommand command)
    {
        if (command == null)
            throw new ArgumentNullException(nameof(command));

        _cmd = command;
    }

    public string CommandText
    {
        get { return _cmd.CommandText; }
        set { _cmd.CommandText = value; }
    }

    public int CommandTimeout
    {
        get { return _cmd.CommandTimeout; }
        set { _cmd.CommandTimeout = value; }
    }

    public CommandType CommandType
    {
        get { return _cmd.CommandType; }
        set { _cmd.CommandType = value; }
    }

    public IDbConnection Connection
    {
        get { return _cmd.Connection; }
        set { _cmd.Connection = value; }
    }

    public IDataParameterCollection Parameters
    {
        get { return _cmd.Parameters; }
    }

    public IDbTransaction Transaction
    {
        get { return _cmd.Transaction; }
        set { _cmd.Transaction = value; }
    }

    public UpdateRowSource UpdatedRowSource
    {
        get { return _cmd.UpdatedRowSource; }
        set { _cmd.UpdatedRowSource = value; }
    }

    public void Cancel()
    {
        _cmd.Cancel();
    }

    public IDbDataParameter CreateParameter()
    {
        return _cmd.CreateParameter();
    }

    public void Dispose()
    {
        _cmd.Dispose();
    }

    public int ExecuteNonQuery()
    {
        Console.WriteLine($"[ExecuteNonQuery] {_cmd.CommandText}");
        return _cmd.ExecuteNonQuery();
    }

    public IDataReader ExecuteReader()
    {
        Console.WriteLine($"[ExecuteReader] {_cmd.CommandText}");
        return _cmd.ExecuteReader();
    }

    public IDataReader ExecuteReader(CommandBehavior behavior)
    {
        Console.WriteLine($"[ExecuteReader({behavior})] {_cmd.CommandText}");
        return _cmd.ExecuteReader();
    }

    public object ExecuteScalar()
    {
        Console.WriteLine($"[ExecuteScalar] {_cmd.CommandText}");
        return _cmd.ExecuteScalar();
    }

    public void Prepare()
    {
        _cmd.Prepare();
    }
}