3

Is it possible to see the sql statement after parameters have been replaced?

using(SqlCommand cmdInsert = new SqlCommand("INSERT INTO Table(Value_fkey) VALUES(@ValueKey)", Database.Connection))
{
    cmdInsert.Parameters.AddWithValue("@ValueKey", ValueKey);
    System.Convert.ToInt32(cmdInsert.ExecuteScalar());
}

I'd like it to log my sql statements, so rather not via SQL Server, but I don't mind whether it is before, or after calling the statement.

Mario S
  • 11,715
  • 24
  • 39
  • 47
Richard Whitehouse
  • 679
  • 3
  • 14
  • 28
  • 1
    Parameters don't really get 'replaced', they just get used as parameters. You can loop through the existing parameters and see their values at any time. – cbp Nov 04 '12 at 13:19
  • possible duplicate http://stackoverflow.com/questions/2611446/what-sql-is-being-sent-from-a-sqlcommand-object – Stefan P. Nov 04 '12 at 13:19
  • 1
    Use the profiler to see what happens. You'll see one event prepare the statement, and another/others using that prepared statement, by ID. There is no substitution. – MatBailie Nov 04 '12 at 13:22
  • 1
    The sql you are looking for does not exist, will **never** exist. That is the _whole point_ of using query parameters. Because this sql is never assembled, the chance of _data_ in your query being mistakenly interpreted as code is _zero_. – Joel Coehoorn Nov 05 '12 at 00:03

1 Answers1

2

This seems to be the simplest way to do it then:

public void OutputSQLToTextFile(SqlCommand sqlCommand)
{
        string query = sqlCommand.CommandText;
        foreach (SqlParameter p in sqlCommand.Parameters)
        {
            query = query.Replace(p.ParameterName, p.Value.ToString());
        }
        OutputToTextFile(query);
    }
Richard Whitehouse
  • 679
  • 3
  • 14
  • 28
  • 1
    Unfortunately this is wrong when using parameter with composed names such as `@Par` and `@ParAdditional`. In this case the Replace method will replace `"@Par"` and so we could have in the second parameter [valuie]Additional. – peterboccia Oct 15 '15 at 10:49
  • @peterboccia What if you were to sort the parameters from longest to shortest before replacing? – jocull Nov 30 '16 at 17:36