1

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!

Improve this question

3 Answers 3

Reset to default
2

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.

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

1 Comment

Ah-ha. I'm in the latter case, and that's kind of what I was afraid of... It's not a huge deal to send the command object elsewhere and pull everything back out of it (or do it entirely differently.) I was just thinking that'd be a super-easy way to go about it. Anyway, thanks for the info... I should've read up more on CommandType.StoredProcedure. Thanks!
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.

Improve this answer

Comments

1

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).

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.