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 ?

To my knowledge there is not such function, im afraid you're gonna have to write something like this yourself by passing the SqlCommand object into a (static) method. — Fabian
– Fabian, Commented Mar 30, 2010 at 8:55

Rune Grimstad · Accepted Answer · 2010-03-30 08:56:46Z

4

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.

answered Mar 30, 2010 at 8:56

Rune Grimstad

36.5k10 gold badges66 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tomas Vana Over a year ago

That sounds fine, but is there an easy way to escape these values ?

AdaTheDev Over a year ago

+1. Used this approach for logging SQL exceptions out to include a "TSQL script" dump ready to copy and paste to try manually/debug in SSMS

Rune Grimstad Over a year ago

If you are passing complex types like blobs or xml data then escaping them will be hard. But assuming you are passing simple values like varchars and numbers it should be as simple as enclosing the values in apostrophes (') and replacing any contained apostrophes with doulbe ones (replace ' with ''). One thing to look out for is dates. They must be formatted properly, but I don't remember the excact format right now.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related