Getting a string from SQL.CommandText

Question

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.

"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
– ProgrammingLlama, Commented Nov 2, 2022 at 6:37
Also the .ToString() part of CommandText.ToString() is redundant because CommandText is a string. — ProgrammingLlama
– ProgrammingLlama, Commented Nov 2, 2022 at 6:38

marc_s · Accepted Answer · 2022-11-02 08:05:41Z

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());
}

edited Nov 2, 2022 at 8:05

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Nov 2, 2022 at 6:47

Sachith Wickramaarachchi

5,9167 gold badges45 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

markdem Over a year ago

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.

Sachith Wickramaarachchi Over a year ago

@markdem Someone has downgraded my answer :P anyway happy to help you. try to use a profiler it will be clear

Collectives™ on Stack Overflow

Getting a string from SQL.CommandText

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related