dummy dummy - 6 months ago 34
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")
cmd.ExecuteNonQuery
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

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.