dummy dummy - 2 years ago 105
Vb.net Question

Get the generated SQL statement from a SqlCommand object?

I have the following code:

Using cmd As SqlCommand = Connection.CreateCommand
cmd.CommandText = "UPDATE someTable SET Value = @Value"
cmd.CommandText &= " WHERE Id = @Id"
cmd.Parameters.AddWithValue("@Id", 1234)
cmd.Parameters.AddWithValue("@Value", "myValue")
End Using

I wonder if there is any way to get the final SQL statment as a String, which should look like this:

UPDATE someTable SET Value = "myValue" WHERE Id = 1234

If anyone wonders why I would do this:

  • for logging (failed) statements

  • for having the possibility to copy & paste it to the Enterprise Manager for testing purposes

Kon Kon
Answer Source

For logging purposes, I'm afraid there's no nicer way of doing this but to construct the string yourself:

string query = cmd.CommandText;

foreach (SqlParameter p in cmd.Parameters)
    query = query.Replace(p.ParameterName, p.Value.ToString());

Sorry, I forgot.. p.Value.ToString() should do the job.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download