How can I log the auto generated query created by Dapper Extensions?

c# dapper dapper-extensions

Question

Dapper Extensions (DE) is the ORM that I'm utilizing. The Data Access Layer, which employs the Repository design, consumes it. Back-end RDBMS is SQL Express.

The majority of my queries are generated automatically by DE. For the sake of debugging, I want to record such automatically created queries.

I can think of two methods to do this: -

  1. This is how I like to do things. as I already have my logging module (using log4net) in place. Get the SQL query created by DE (before or after it is performed) and put it to log. I simply need the SQL that DE generates.
  2. Connect DE to a logging tool. I read the response this. The MiniProfiler tool seems to make it feasible, however as I said before, I already have my logging module set up. I don't want to use another program only for SQL query logging.

How can a SQL query that Dapper Extensions automatically created be logged or obtained without using another logging tool?

The same query is about Dapper in other. This inquiry is in regards to Dapper Extensions.

1
6
10/12/2018 12:02:49 PM

Accepted Answer

In response to @MarcGravell's comment and @this query about doing the same with Dapper,MiniProfiler.Integrations is an improved method of logging for Dapper Extensions.

The Dapper-related query is above. But internally, Dapper Extensions makes use of Dapper. So, if logging is developed for Dapper, Dapper Extensions will also benefit from it.

Zzz-24-Zzz has more information.

Here is some sample code:

var factory = new SqlServerDbConnectionFactory(connectionString);
CustomDbProfiler cp = new CustomDbProfiler();
using(var connection = DbConnectionFactoryHelper.New(factory, cp))
{
    //DB Code
}
string log = cp.ProfilerContext.GetCommands();

You may use built-inCustomDbProfiler using CustomDbProfiler.Current if doing so meets your needs.cp.ProfilerContext.GetCommands() will, regardless of how many times you call the method, return ALL the instructions (both successful and unsuccessful). I'm not certain, but it may be keeping a concatenated string (StringBuilder (Possibly) internally If this is the case, the performance could be slowed down. However, logging is deactivated by default in my situation. Only when I need to troubleshoot anything do I activate logging. So for me, this is not an issue.

If a single link is utilized across an extremely wide scope, this may also generate memory footprint concerns. Make careful to prevent this soCustomDbProfiler instance is appropriately disposed of.

As was first specified in the query, I wanted to stay away from utilizing an outside tool or library.. But,MiniProfiler.Integrations NOT really writing the log. I can easily get all the queries produced and provide them to my logger module for file-dumping purposes. This now seems more appropriate to me because of it.


Similar logic is implemented internally by MiniProfiler.dll (inStackExchange.Profiling.Data.ProfiledDbConnection and StackExchange.Profiling.Data.ProfiledDbCommand classes), of which here and here are listed. Therefore, I can use this code myself if I wish to skip MiniProfiler (in the future, maybe).

4
6/19/2018 10:50:57 AM

Popular Answer

Everyone is aware that the Dapper Extensions project is open source. I grabbed it from GitHub and made the necessary adjustments.

Internally, Dapper Extensions create or produce SQL queriesSqlGeneratorImpl class. This class has a number of methods that produce the different types of queries.

I included the upcoming property inDapperExtensions.DapperExtensionsstatic class:

static string lastGeneratedQuery;
public static string LastGeneratedQuery
{
    get
    {
        lock(_lock)
        {
            return lastGeneratedQuery;
        }
    }
    internal set
    {
        lock(_lock)
        {
            lastGeneratedQuery = value;
        }
    }
}

Set this attribute in a variety of ways as well.SqlGeneratorImpl class. Here is an illustration of how I set it inSelect method.

public virtual string Select(IClassMapper classMap, IPredicate predicate, IList<ISort> sort, IDictionary<string, object> parameters)
{
    ......
    ......

    StringBuilder sql = new StringBuilder(string.Format("SELECT {0} FROM {1}",
    ......
    ......

    DapperExtensions.LastGeneratedQuery = sql.ToString();

    return sql.ToString();
}

Basic tests pass without issue, however I haven't really tried this yet. In the event of a change, I will update this response.

Please note that I do not recommend this as standard solution; this is just a hack that works for my needs. I would really like to see this as a regular feature in library. Please post an answer if you have better solution. Otherwise, please comment to improve the solution suggested here.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow