Possible to view T-SQL syntax of a stored proc-based SqlCommand?

Question

Hello! I was wondering if anybody knows of a way to retrieve the actual T-SQL that is to be executed by a SqlCommand object (with a CommandType of StoredProcedure) before it executes...

My scenario involves optionally saving DB operations to a file or MSMQ before the command is actually executed. My assumption is that if you create a SqlCommand like the following:

Using oCommand As New SqlCommand("sp_Foo")
    oCommand.CommandType = CommandType.StoredProcedure
    oCommand.Parameters.Add(New SqlParameter("@Param1", "value1"))
    oCommand.ExecuteNonQuery()
End Using

It winds up executing some T-SQL like:

EXEC sp_Foo @Param1 = 'value1'

Is that assumption correct? If so, is it possible to retrieve that actual T-SQL somehow? My goal here is to get the parsing, validation, etc. benefits of using the SqlCommand class since I'm going to be using it anyway. Is this possible? Am I going about this the wrong way? Thanks in advance for any input!

Answer 1

You're trying to the log the SP call with its parameters in a friendly form? Or you want to save this text to run it as a script later?

I think you're out of luck in the second case, because, I don't think SQLCommand calls SPs with SQL when you use it in CommandType.StoredProcedure mode. If you look in the SQL Server profiler at the difference between CommandType.StoredProcedure with an SP "sp_whatever" and CommandType.Text with "EXEC sp_whatever", it's a different interface - the RPC interface. I was always under the impression ADO.NET was never making a SQL string in the first case. That's why you never have to worry about injection at that point, because parameters are always sent out of band - even if you have CommandType.Text with a parametrized query.

See this blog post.

If you are trying to log operations, I guess you could pass your SQLCommand object to some generic object/method you write which extracts the command text and all the parameters and puts them in a nicely organized text form for logging.

Answer 2

SQL Profiler might be of use here. If you can execute the SP in SSMS with Profiler running, you can set it up to see the T-SQL commands being executed.

Answer 3

Use SQL Profiler, connect to server, use the TSQL_SPs template (after you connect to the server, you get the "Trace Properties" box, fourth line down is your template selection).