Possible to view T-SQL syntax of a stored proc-based SqlCommand?
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!
3 Solutions collect form web for “Possible to view T-SQL syntax of a stored proc-based SqlCommand?”
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.
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.
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).