Debugging Tips

Access Resolved SQL

There are three ways to get the SQL resolved from a query -- the "breakpoint method", using traces and exceptions, and overriding some protected logging methods.

"Breakpoint Method"

Make a small change to how you normally execute a query, then set a breakpoint on any of the Execute* methods. Right after the method executes, inspect the ResolvedSql or DebugSql properties. The DebugSql property is meant to be copied and pasted into SSMS and run as it contains parameter declarations to make it immediately executable. The ResolvedSql property is the same thing minus the parameter declarations. Let's say you're starting with this line to execute a query called Labels:

Labels = await new Labels() { OrgId = OrgId, IsActive = isActive }.ExecuteAsync(cn);

Change this line to this:

var query = new Labels() { OrgId = OrgId, IsActive = isActive };
Labels = await query.ExecuteAsync(cn); // set breakpoint here

Now debug your application. When you hit your query breakpoint, inspect the query.DebugSql property. It will have the query and any parameters it uses. Copy and paste this into SSMS to see how it behaves.

Copy and paste the contents of DebugSql into SSMS and run from there. Note that the whitespace/indents may be messed up because of how the text of your SQL is saved in your C# source file. Also note that some parameter types don't currently translate to this generated SQL. For example array parameters are not currently supported in DebugSql.

Note that the implementation for this is here.

Tracing and Exceptions

The Execute* methods all accept an optional List<QueryTrace> argument you can use to capture info about queries you're running. See QueryTrace to see what's captured. In addition to the DebugSql described above, the query duration and parameters are also captured. You can log or present trace data in your application to make troubleshooting easier. Here's what it looks like to capture traces:

var traces = new List<QueryTrace>();
Labels = await new Labels() { OrgId = OrgId, IsActive = isActive }.ExecuteAsync(cn, traces: traces);

In a real app, I suggest having a List<QueryTrace> instance property on your controller or page to log all queries together. Then, decided whether to present in your UI based on a developer account flag.

If a Dapper method throws an exception, it's wrapped and thrown as a QueryException, which is mostly the same info as a QueryTrace minus the elapsed time.

OnQueryExecuted(Async)

If you want to implement a global logging approach to all your queries in an application, subclass Query<T> and override OnQueryExecuted and/or OnQueryExecutedAsync. Then use your subclass for all queries in place of Query<T>.