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.

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.