Is there any way to trace\log the sql using Dapper

Question

Is there a way to dump the generated sql to the Debug log or something? I'm using it in a winforms solution so the mini-profiler idea won't work for me.

Answer 1

This is not exhaustive and is essentially a bit of hack, but if you have your sql and you want to initialise your parameters it's useful for basic debugging.

public static class DapperExtensions
    {
        public static string ArgsAsSql(this DynamicParameters args)
        {
            var sb = new StringBuilder();
            foreach (var name in args.ParameterNames)
            {
                var pValue = args.Get<dynamic>(name);

                var type = pValue.GetType();

                if (type == typeof(DateTime))
                    sb.AppendFormat("DECLARE @{0} DATETIME ='{1}'\n", name, pValue.ToString("yyyy-MM-dd HH:mm:ss.fff"));
                else if (type == typeof(bool))
                    sb.AppendFormat("DECLARE @{0} BIT = {1}\n", name, (bool)pValue ? 1 : 0);
                else if (type == typeof(int))
                    sb.AppendFormat("DECLARE @{0} INT = {1}\n", name, pValue);
                else if (type == typeof(List<int>))
                    sb.AppendFormat("-- REPLACE @{0} IN SQL: ({1})\n", name, string.Join(",", (List<int>)pValue));
                else
                    sb.AppendFormat("DECLARE @{0} NVARCHAR(MAX) = '{1}'\n", name, pValue.ToString());
            }

            return sb.ToString();
        }
    }

You can then just use this in the immediate or watch windows to grab the SQL.

Answer 2

Just to add an update here since I see this question still get's quite a few hits - these days I use either Glimpse or Stackify Prefix which both have sql command trace capabilities.

It's not exactly what I was looking for when I asked the original question but solve the same problem.

Answer 3

I got the same issue and implemented some code after doing some search but having no ready-to-use stuff. There is a package on nuget MiniProfiler.Integrations I would like to share.

Update V2: it supports to work with other database servers, for MySQL it requires to have MiniProfiler.Integrations.MySql

Below are steps to work with SQL Server:

1.Instantiate the connection

var factory = new SqlServerDbConnectionFactory(_connectionString);
using (var connection = DbConnectionFactoryHelper.New(factory, CustomDbProfiler.Current))
{
 // your code
}

2.After all works done, write all commands to a file if you want

File.WriteAllText("SqlScripts.txt", CustomDbProfiler.Current.ProfilerContext.BuildCommands());

Answer 4

Dapper does not currently have an instrumentation point here. This is perhaps due, as you note, to the fact that we (as the authors) use mini-profiler to handle this. However, if it helps, the core parts of mini-profiler are actually designed to be architecture neutral, and I know of other people using it with winforms, wpf, wcf, etc - which would give you access to the profiling / tracing connection wrapper.

In theory, it would be perfectly possible to add some blanket capture-point, but I'm concerned about two things:

• (primarily) security: since dapper doesn't have a concept of a context, it would be really really easy for malign code to attach quietly to sniff all sql traffic that goes via dapper; I really don't like the sound of that (this isn't an issue with the "decorator" approach, as the caller owns the connection, hence the logging context)
• (secondary) performance: but... in truth, it is hard to say that a simple delegate-check (which would presumably be null in most cases) would have much impact

Of course, the other thing you could do is: steal the connection wrapper code from mini-profiler, and replace the profiler-context stuff with just: Debug.WriteLine etc.