Show parameter values in sql query


I run some queries against an in-memory database. See gist.

Is it possible to get the parameter values of the last sql query?

I see this:
SELECT “Id”, “Content”, “Order”, “Done” FROM “Todo” WHERE “Id” = @Id

I want to see this:
SELECT “Id”, “Content”, “Order”, “Done” FROM “Todo” WHERE “Id” = 12

You wont be able to see in-line SQL because OrmLite uses parameterized queries, the output you’re seeing shows the SQL that is generated by OrmLite and executed on the RDBMS.

There’s a few ways to view the db parameter values, you can use the db.GetLastSqlAndParams() API instead, you can specify a Debug logger which will log both the SQL and parameter values, e.g:

LogManager.LogFactory = new ConsoleLogFactory();

Or you can use a CapturedSqlFilter.

Hey @mythz, it looks like GetLastSqlAndParams() hangs off dbCmd not the connection. It would be cool if it could work off the connection though :smile:

Right, but it can’t hang onto the IDbCommand as it can’t be accessed after it’s disposed and capturing it after every command will impact perf, maybe I can add a OrmLiteConfig.DebugMode flag to control this.

Actually I’m ok with just using a custom OrmLiteExecFilter during debug.

public class CaptureLastSqlAndParamsFilter : OrmLiteExecFilter
    public override void DisposeCommand(IDbCommand dbCmd, IDbConnection dbConn)
        var sql = dbCmd.GetLastSqlAndParams();
        base.DisposeCommand(dbCmd, dbConn);

The DB params are already available in the built-in CapturedSqlFilter.

Tried to use the CaptureSqlFilter in gistlyn but then I got a
“Type ‘ServiceStack.OrmLite.OrmLiteState’ in Assembly ‘ServiceStack.OrmLite, Version=, Culture=neutral, PublicKeyToken=null’ is not marked as serializable.”

You can just set the Console Logger at the top in Gistlyn, i.e:

LogManager.LogFactory = new ConsoleLogFactory();

Everyday I learn something new about ServiceStack is a good day :smile:

1 Like