How to get the generated SQL-Statment from a SqlCommand-Object?

Question

Hi there. 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 ike this:

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

If anyone wonders why I would do this:

• for logging (failed) statments
• for having the possibility to copy & paste it to the Enterprise Manager for testing purposes

Thanks and have a nice day.

Answer 1

If you're using SQL Server, you could use SQL Server Profiler (if you have it) to view the command string that is actually executed. That would be useful for copy/paste testing purpuses but not for logging I'm afraid.

Answer 2

You can't, because it does not generate any SQL.

The parameterized query (the one in CommandText) 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.

You can use SQL Profiler to take a look behind the scenes.

Answer 3

Profiler is hands-down your best option.

You might need to copy a set of statements from profiler due to the prepare + execute steps involved.

Answer 4

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.