dummy dummy - 1 year ago 88
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 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.