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:

<!-- language: lang-sql -->

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

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.

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.

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.

This solution works for me right now. Maybe it is usefull to someone. Please excuse all the redundancy.


Public Shared Function SqlString(ByVal cmd As SqlCommand) As String
Dim sbRetVal As New System.Text.StringBuilder()
For Each item As SqlParameter In cmd.Parameters
Select Case item.DbType
Case DbType.String
sbRetVal.AppendFormat("DECLARE {0} AS VARCHAR(255)", item.ParameterName)
sbRetVal.AppendLine()
sbRetVal.AppendFormat("SET {0} = '{1}'", item.ParameterName, item.Value)
sbRetVal.AppendLine()

Case DbType.DateTime
sbRetVal.AppendFormat("DECLARE {0} AS DATETIME", item.ParameterName)
sbRetVal.AppendLine()
sbRetVal.AppendFormat("SET {0} = '{1}'", item.ParameterName, item.Value)
sbRetVal.AppendLine()

Case DbType.Guid
sbRetVal.AppendFormat("DECLARE {0} AS UNIQUEIDENTIFIER", item.ParameterName)
sbRetVal.AppendLine()
sbRetVal.AppendFormat("SET {0} = '{1}'", item.ParameterName, item.Value)
sbRetVal.AppendLine()

Case DbType.Int32
sbRetVal.AppendFormat("DECLARE {0} AS int", item.ParameterName)
sbRetVal.AppendLine()
sbRetVal.AppendFormat("SET {0} = {1}", item.ParameterName, item.Value)
sbRetVal.AppendLine()

Case Else
Stop

End Select
Next

sbRetVal.AppendLine("")
sbRetVal.AppendLine(cmd.CommandText)

Return sbRetVal.ToString()
End Function

If it's only to check how a parameter is formatted in the result query, most DBMS's will allow querying literals from nothing. Thus:

Using cmd As SqlCommand = Connection.CreateCommand
cmd.CommandText = "SELECT @Value"
cmd.Parameters.AddWithValue("@Value", "myValue")
Return cmd.ExecuteScalar
End Using

That way you can see if quotes are doubled, etc.