A way to see query after parameters are applied?

Question

In a C# application, I'm building a query by creating a query string with parameters, then to the command adding the parameters and their values. For example:

string query = "UPDATE USERS u SET u.username = @PARM_USERNAME " +
               "WHERE u.id = @PARM_USERID ";

command.Parameters.AddWithValue("@PARM_USERNAME", user.username);
command.Parameters.AddWithValue("@PARM_USERID", user.id);

command.Connection.Open();
int res = command.ExecuteNonQuery();

It would be beneficial to see the query with parameters applied, is this doable in C#/Visual Studio? I can check the command.CommandText, but it's only showing me the same content as the query above, with the parameter placeholders there. If it helps, this is against MySQL.

Answer 1

Not sure why you need this, but if it's for debugging purposes you can always turn on the global log on your local mysql database machine to see the query sent to the database (you don't want to turn it on on a production machine though - it might slow it down significantly).

Answer 2

The parameters remain separate all the way to the server, so the query string you see is what actually goes to the server, independently from the parameters. So I think you need to deal more directly with understanding how parameterized queries work rather than trying to see what the query would look like with the parameters in place. You can use SQL trace to see the query come in. The parameters will still be separate, but it will show you the values.

My experience is with SQL Server, so I'm not sure how applicable this is to MySQL.

Answer 3

If you would like to use a tool you could try using Toad for MySql which has a Profiler and you can see what is being sent to the server.

Answer 4

If you want to see the query with parameters applied:

string tmp = command.CommandText.ToString();
foreach (SqlParameter p in cmd.Parameters) {
    tmp = tmp.Replace('@' + p.ParameterName.ToString(),"'" + p.Value.ToString() + "'");
}

tmp will then hold the query with the parameters applied. Each parameter will be surrounded by single quotes.

Of course, it is NOT safe to execute. I use it for debugging purposes.

Answer 5

There's no guarantee that there is such a thing as "the query with the parameters applied". I would hope that a driver would simply send down the command as SQL and the parameters in an appropriate form to represent each value. Why go to the bother of escaping values etc, only for the query processor to unescape them and parse them at the other side? It's more efficient and less risky to just pass the data in a binary format of some description.

You should regard it as some code (the SQL) which uses some data (the parameters) and keep the two concepts very separate in your mind. If you need to log what's going on, I would log it as the parameterized SQL and the parameter values separately.

Answer 6

the @christopher answer was great but just string parameters will need ' (single quotation). the best is to use below method:

  private string getGeneratedSql(SqlCommand cmd)
    {
        string result = cmd.CommandText.ToString();
        foreach (SqlParameter p in cmd.Parameters)
        {
            string isQuted = (p.Value is string) ? "'" : "";
            result = result.Replace('@' + p.ParameterName.ToString(), isQuted + p.Value.ToString() + isQuted);
        }
        return result;
    }