How to apply a query after parameters?

Question

In a C # application, I create a query by creating a query string with parameters, and then into a command that adds parameters and their values. For instance:

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 useful to see a query with the applicable parameters, can this be done in C # / Visual Studio? I can check command.CommandText, but it only shows the same content as the request above, with where there are parameter labels. If this helps, it is against MySQL.

Answer 1

There is no guarantee that there is such a thing as a "query with applicable parameters." I hope that the driver will simply send the command as SQL and the parameters in the appropriate form to represent each value. Why worry about escaping values, etc., just so that the query processor can cancel them and analyze them on the other side? It is more efficient and less risky to simply transfer data in binary format of some description.

You should consider it as some code (SQL) that uses some data (parameters) and stores these two concepts separately. If you need to register what is happening, I would register it as parameterized SQL and parameter values ​​separately.

Answer 2

If you want to see a query with the applicable parameters:

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

tmp will hold the request using the parameters. Each parameter will be surrounded by single quotes.

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

Answer 3

The parameters remain separate up to the server, so the query string that you see is what actually goes to the server, regardless of the parameters. Therefore, I think you need to understand in more detail how parameterized queries work, and not try to see how a query with parameters in place will look. You can use SQL trace to view the query. The parameters will still be separate, but the values ​​will be displayed.

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

Answer 4

Not sure why you need this, but if for debugging purposes you can always turn on the global log on your local mysql database machine to see the request sent to the database (you do not want to include it in however - this can slow it down significantly) .

Answer 5

If you want to use the tool, you can try using Toad for MySql , which has a Profiler, and you can see that the server is being sent.

Answer 6

@christopher's answer was great, but string parameters would need ' (single quote). It is best to use the 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; } 

Answer 7

I use this for debugging in HTML. Enough time for testing with a simple copy paste;

 public static string GetQueryHtml(string query, List<SqlParameter> parameters = null) { string _return = ""; string _parmStringValue; string _varlenght = "50"; foreach (SqlParameter parameter in parameters) { if (parameter.SqlDbType == SqlDbType.DateTime) { _parmStringValue = "'" + ((DateTime)parameter.Value).ToString("yyyy-MM-dd hh:mm:ss") + "'"; _return += Environment.NewLine + "DECLARE " + parameter.ParameterName + " AS " + parameter.SqlDbType.ToString() + " SET " + parameter.ParameterName + " = " + _parmStringValue; } else if (parameter.SqlDbType == SqlDbType.NVarChar || parameter.SqlDbType == SqlDbType.Char) { _varlenght = parameter.Value.ToString().Length.ToString(); _parmStringValue = "'" + parameter.Value.ToString() + "'"; _return += Environment.NewLine + "DECLARE " + parameter.ParameterName + " AS " + parameter.SqlDbType.ToString() + "(" + _varlenght + ") SET " + parameter.ParameterName + " = " + _parmStringValue; } else { _return += Environment.NewLine + "DECLARE " + parameter.ParameterName + " AS " + parameter.SqlDbType.ToString() + " SET " + parameter.ParameterName + " = " + parameter.Value.ToString(); } } return "<div><pre><code class='language-sql'>" + _return + Environment.NewLine + Environment.NewLine + query + "</code></pre></div>"; }