Is it possible to easily convert SqlCommand to T-SQL string ?

Question

I have a populated SqlCommand object containing the command text and parameters of various database types along with their values. What I need is a T-SQL script that I could simply execute that would have the same effect as calling the ExecuteNonQuery method on the command object.

Is there an easy way to do such "script dump" or do I have to manually construct such script from the command object ?

Answer 1

I don't think you can get the complete sql statement from the SqlCommand object directly. If you log all commands sent to your sql server you will see that the command text that is passed is the same as the command text of your SqlCommand object. The parameter values are passed separately.

So I think that your only option will be to build the command text yourself. This is quite easy really; You start with the CommandText of the SqlCommand and iterate through the Parameters collection replacing the parameter place holders with (escaped) parameter values. This should result in a complete sql query that does what you are after.