How to read an SQL query generated by Dapper?

Question

I have a standard code:

public IEnumerable ExperimentSelect(object parameters)
{
    using (var connection = new SqlConnection(ConnectionString))
    {
        connection.Open();
        var dynamicparam = new DynamicParameters(parameters);

        var rows = connection.Query("[dbo].[ptbSapOrderSelect]", dynamicparam, 
                commandType: CommandType.StoredProcedure);

        if (rows.Any())
            TotalRows = ((long)rows.ToList()[0].TotalRows);

        return rows;
    }
}

How to automate saving queries generated by Dapper to the file using eg NLog? I am thinking of getting source of SQL query as shown in the SQL Server Profiler.

Executioner

Would be great if Dapper had an extension to SQLMapper.GridReader that would dump out the generated SQL. It is open source so you could tweak it yourself too. I just tried and the GitHub project doesn't even compile on my dev machine. :) Welcome to open source world. – Gomes

Answer 1

I managed to make this work in an ASP.Net MVC app using MiniProfiler.

First, configure MiniProfiler as per the docs. Make sure that you are wrapping your SqlConnection inside a ProfiledDbConnection.

Note that you don't need to enable the visual widget for this to work, just ensure that a profile is started before, and ended after, each request.

Next, in global.asax.cs where the profile for that request is stopped, amend it as follows:

protected void Application_EndRequest()
{
    // not production code!
    MiniProfiler.Stop();

    var logger = NLog.LogManager.GetCurrentClassLogger();

    var instance = MiniProfiler.Current;

    if (instance == null) return;

    var t = instance.GetSqlTimings();

    foreach (var sqlTiming in t)
    {
        logger.Debug(sqlTiming.CommandString);
    }
}

This literally dumps the SQL command executed, but there is a lot more information included in the model if you want to report more advanced information.

Gemology