1

I have code that executes a query in sqlserver accessing with dapper in the error exception I want to get the query executed with the specified parameter:

StringBuilder sql = new StringBuilder();
sql.AppendLine(" SELECT " + campoChave + " AS CODIGO, ");
sql.AppendLine("        " + campoSelecao + " AS DESCRICAO ");
sql.AppendLine("   FROM " + tabela);
sql.AppendLine("   where codigoidioma = @codigoIdioma");
sql.AppendLine("  ORDER BY " + campoSelecao);

IEnumerable entities2 = conexao.Query<ComboBoxGenerico>(sql.ToString(), new { codigoIdioma = 1 });

this code is with error I want to get the query after executing the dapper with the command

Ex: select teste as codigo ..... where codigoidioma = 1

@codigoIdioma ---> 1

Elletlar
  • 3,136
  • 7
  • 32
  • 38
user3576807
  • 11
  • 1
  • 1
    small note: if you're going to use `StringBuilder`, it would be better to use it *throughout*, i.e. `sql.Append(" SELECT ").Append(campoChave).AppendLine(" AS CODIGO, ");`, etc; it also goes without saying that you need to be *really* careful not to allow user input into `campoChave`, `campoSelecao`, etc - as that would allow SQL injection – Marc Gravell Feb 05 '19 at 16:30
  • If you want to log the queries, [this](https://stackoverflow.com/a/50875558/5779732) may help even though it uses Dapper Extensions. Internally, it creates [profiled connection](https://stackoverflow.com/questions/44194760/how-to-get-last-executed-sql-query-by-sqlconnection#comment88851870_50925790) which is then used for logging. – Amit Joshi Feb 06 '19 at 07:08

1 Answers1

0

The query after executing the command will almost always be... exactly what you passed in. Dapper doesn't replace parameter tokens - that would be a SQL-injection hole. Even with tools that monitor SQL traffic (mini-profiler, SQL server profiler, etc): the command shown will include parameter tokens, with the parameters sent separately.

Mini-profiler spoofs this, by (in the display) prepending the command with declare statements that look like the parameters, so you can run it in SSMS - that will give you something like:

declare @codigoidioma int = 1;
select teste as codigo ..... where codigoidioma = @codigoidioma

but note that it remained parameterized (ish) even here.

In the case of dapper specifically, there are some exceptions to the "it'll be what you passed in" general rule, concerning some specific scenarios such as in @foo (for something like a List<int> for foo), where it will generate an appropriate parameter expansion, but it will still be parameterized.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • in this example above to get easy the generated query, but in case it is an insert or update code that in the case would be passed as a parameter a language object with all the properties how would I get this query? – user3576807 Feb 05 '19 at 16:29
  • @user3576807 which exact methods are you using? are you using dapper.contrib etc, for example? in those cases, I would again say "miniprofiler" (https://www.nuget.org/packages/MiniProfiler/) - not directly tied to dapper, but it is by the same team, and it is what we use in conjunction with dapper for our own system/query monitoring (we use it on every single page request here on Stack Overflow, for example); but again: it will *only* give you the parameterized form, not the form with parameters replaced, because **dapper doesn't do that** – Marc Gravell Feb 05 '19 at 16:33