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.

Agoraphobia

Answer 1

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 = ProfiledDbConnectionFactory.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.GetCommands());

Snowblind

Yes, there is a repository on github github.com/vndevpro/MiniProfiler.Integrations – Snowblind

Brilliant. You might want to change the project site to that github in the nuget - currently it seems to point to itself (or a previous version) – Agoraphobia

@Snowblind - Sorry! I just realized the typo. I meant "too bad MySql isn't supported by MiniProfiler". As your solution only works for Sql Server. – Fraktur

I think MiniProfile accepts an abstract db connection, so just instantiate a connection for MySQL then it should work – Snowblind

Is that System.Data.Entity.Infrastructure.SqlConnectionFactory in the example? Can't seem to find the "New" extension method or another implementation of SqlConnectionFactory. Is this example specific to a particular version of MiniProfiler.Integrations? – Ranite

@Silverfox, the class is particularly in MiniProfiler.Integrations and was changed to DbConnectionFactoryHelper in V2.0 to not being confused :) I will update the answer with usage on lastest version. Tks for remark ;) – Snowblind

Worked like a dream for MySql.. Figured out my problem in 10 minutes. Changed to CustomDbProfiler.Current.ProfilerContext.GetCommands(). Thanks! – Smolt

It works but is there anyway to remove the parameterized sql statements for this? – Tola

Is this answer valid for Net Core? CustomDbProfiler.Current.ProfilerContext is missing for me. – Headstock

@daniherrera Take a look on this, it's available: github.com/vndevpro/MiniProfiler.Integrations/tree/master/… – Snowblind

While using SQL server and dapper, profiler.GetCommands() is returning nothing in catch block after the call to _dbConnection.ExecuteScalarAsync<int>(insertQuery, entity, transaction: null); – Bouzoun

Answer 2

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.

Dmso

Hi Mark, could you give an example of the kind of attack you're worried about? – Bridle

Answer 3

You should consider using SQL profiler located in the menu of SQL Management Studio → Extras → SQL Server Profiler (no Dapper extensions needed - may work with other RDBMS when they got a SQL profiler tool too).

Then, start a new session.

You'll get something like this for example (you see all parameters and the complete SQL string):

exec sp_executesql N'SELECT * FROM Updates WHERE CAST(Product_ID as VARCHAR(50)) = @appId AND (Blocked IS NULL OR Blocked = 0) 
                    AND (Beta IS NULL OR Beta = 0 OR @includeBeta = 1) AND (LangCode IS NULL OR LangCode IN (SELECT * FROM STRING_SPLIT(@langCode, '','')))',N'@appId nvarchar(4000),@includeBeta bit,@langCode nvarchar(4000)',@appId=N'fea5b0a7-1da6-4394-b8c8-05e7cb979161',@includeBeta=0,@langCode=N'de'

If SQL Profiler does not work or has connection problems use dbForge Event Profiler for SQL Server tool from Devart. It's free and does the job.

Batchelder

There are some problems with this aproach, 1) if the DB is a common one (many processes and users in a test environment, for example), could be hard to filter the desired queries. 2) Azure Sql (and probably other DBMS) would not allow to connect using a profiler tool. – Ebberta

@zameb: You can filter by client ID (application name) and other properties, no problem. If you have enabled TCP connections to your SQL-Server and enabled access in your firewall, you can connect to your virtual SQL Server instance on Azure! – Batchelder

Answer 4

Try Dapper.Logging.

You can get it from NuGet. The way it works is you pass your code that creates your actual database connection into a factory that creates wrapped connections. Whenever a wrapped connection is opened or closed or you run a query against it, it will be logged. You can configure the logging message templates and other settings like whether SQL parameters are saved. Elapsed time is also saved.

In my opinion, the only downside is that the documentation is sparse, but I think that's just because it's a new project (as of this writing). I had to dig through the repo for a bit to understand it and to get it configured to my liking, but now it's working great.

From the documentation:

The tool consists of simple decorators for the DbConnection and DbCommand which track the execution time and write messages to the ILogger<T>. The ILogger<T> can be handled by any logging framework (e.g. Serilog). The result is similar to the default EF Core logging behavior.

The lib declares a helper method for registering the IDbConnectionFactory in the IoC container. The connection factory is SQL Provider agnostic. That's why you have to specify the real factory method:

services.AddDbConnectionFactory(prv => new SqlConnection(conStr));

After registration, the IDbConnectionFactory can be injected into classes that need a SQL connection.

private readonly IDbConnectionFactory _connectionFactory;
public GetProductsHandler(IDbConnectionFactory connectionFactory)
{
    _connectionFactory = connectionFactory;
}

The IDbConnectionFactory.CreateConnection will return a decorated version that logs the activity.

using (DbConnection db = _connectionFactory.CreateConnection())
{
    //...
}

Extravagant

The "Gotcha" to this is when you are trying to understand a legacy codebase that has literally thousands of places where the SQL connections are created, and all slightly difference, so there is no viable way to make the changes... Only something that is completely transparent to the existing code (except for a single place hook initialization at startuup) would be feasible. And yes, I do understand the security concerns... – Franke

Answer 5

This is not exhaustive and is essentially a bit of hack, but if you have your SQL and you want to initialize your parameters, it's useful for basic debugging. Set up this extension method, then call it anywhere as desired.

public static class DapperExtensions
{
    public static string ArgsAsSql(this DynamicParameters args)
    {
        if (args is null) throw new ArgumentNullException(nameof(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.

Drucill

Answer 6

Just to add an update here since I see this question still get's quite a few hits - these days I use either Glimpse (seems it's dead now) 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.

Agoraphobia

Glimpse not dead? – Whitmire

@Whitmire huh so it seems - haven't used it in quite a while so I never noticed. It's a shame - it was really good. – Agoraphobia