How to get the parametrized SQL query (with parameters applyed) before execute it in Dapper?

Question

I need my sql query with the parameters applyed to log porposes.

private dynamic GetInfo(int cdEmpresa)
{
    dynamic info = new ExpandoObject();

    StringBuilder sql = new StringBuilder();
    sql.AppendLine(" SELECT * from FROM EMPRESA E");
    sql.AppendLine(" WHERE cdEmpresa = @cdEmpresa ");

    using (IDbConnection cn = GetConnection(cdEmpresa).Connection)
    {
        Logger.Debug("SQL: " + sql.ToString()); // Does not apply the parameters, obviously

        cn.Open();
        info = cn.Query<dynamic>(sql.ToString(), new
        {
            cdEmpresa = cdEmpresa // i need to execute te sql to parametrize it, is there a way to parametrize it first its execution?
        }).ToList();

    }
    return infoCadastro;
}

well, you kow the query (its in the `StringBuilder`, just log the `cdEmpresa`! — TheVillageIdiot, Oct 30 '17 at 22:15
This is similar requirement that I mentioned in my other question. https://stackoverflow.com/q/44194760/5779732. You can also find sample code at bottom if you get access to `SqlCommand`. — Amit Joshi, Oct 31 '17 at 08:00

Joel Coehoorn · Accepted Answer · 2022-09-19T03:41:39.620

What you are asking for does not exist at any time while processing your query.

Even when you execute the query, the parameter values are never substituted directly into the SQL command. The whole point of parameterized queries is so code is code, data is data, and the two never cross. This eliminates any possibility of injection attacks, no matter what new language feature, custom escape character, or unicode weirdness you might have to deal with.

On the server side, instead of this:

SELECT * FROM [table] WHERE ID=1234;

It's more as if you run code like this:

DECLARE @ID int;
SET @ID = LoadParameterValueFromClientQueryObject("ID");
SELECT * FROM [table] WHERE ID= @ID;

You're always dealing with a variable, where the value of the variable is held away from the sql language compiler/optimizer until after the command is already compiled into an execution plan.

How to get the parametrized SQL query (with parameters applyed) before execute it in Dapper?

1 Answers1