1

Is there an easy way to get the actual SQL query that is sent from a parametrized CommandText?

If I have

mySQLCommand.CommandText = ("UPDATE dbo.Users SET LimitTime=@limitTime WHERE userID=@userID");

how can I save that to a string with the values of limitTime and userID? I need to be able to log the actual SQL sent.

I have tried to do a mySQLCommand.CommandText.toString() but that does not fill in the values.

EDIT: sorry, this is not mySQL, it is for SQL Server. mySQLCommand is just what I called the SqlCommand variable. Did not even think that is could be confusing.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
markdem
  • 51
  • 1
  • 8
  • _"I need to be able to log the actual SQL sent."_ - which is `"UPDATE dbo.Users SET LimitTime=@limitTime WHERE userID=@userID"`. I assume MySQL is similar to SQL Server in this respect. Basically the parameters are not spliced into the query, they are sent in binary alongside the query, not as part of it. I doubt there is a way to produce an equivalent SQL query built into the library. – ProgrammingLlama Nov 02 '22 at 06:37
  • Also the `.ToString()` part of `CommandText.ToString()` is redundant because `CommandText` _is a `string`_. – ProgrammingLlama Nov 02 '22 at 06:38
  • see [enter link description here](https://stackoverflow.com/q/265192/11169272) for details – Triloknath Nalawade Nov 02 '22 at 07:46

1 Answers1

1

I assume MySQL is similar to SQL Server. In SQL Server, the parameterized query is sent to the SQL Server as the equivalent of a prepared statement. When you execute the command, the parameters and the query text are treated separately. At no point in time, a complete SQL string is generated.

Try this,you can construct the string yourself like this

string query = mySQLCommand.CommandText;

foreach (SqlParameter p in cmd.Parameters)
{
    query = query.Replace(p.ParameterName, p.Value.ToString());
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sachith Wickramaarachchi
  • 5,546
  • 6
  • 39
  • 68
  • This is what I was wondering. I was not sure if the SQL string got created on the client or is the server was responsible for some magic. Thanks for clearing it up. – markdem Nov 02 '22 at 07:07
  • @markdem Someone has downgraded my answer :P anyway happy to help you. try to use a profiler it will be clear – Sachith Wickramaarachchi Nov 02 '22 at 11:47