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:

  • Should I store HTML as nvarchar(MAX) or ntext?
  • Migrating from Postgres to SQL Server 2008
  • Uniqueidentifier vs. IDENTITY vs. Material Code --which is the best choice for primary key?
  • Display multiple rows and column values into a single row, multiple column values
  • Prepared Statement in ASP.Net C# when using SQL Server
  • connecting to SQL Server from Netbeans
  • Using oCommand As New SqlCommand("sp_Foo")
        oCommand.CommandType = CommandType.StoredProcedure
        oCommand.Parameters.Add(New SqlParameter("@Param1", "value1"))
    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!

  • SQL server and .NET memory constraints, allocations, and garbage collection
  • Connecting to sql server database mdf file without installing sql server on client machine?
  • How do I obtain a list of all schemas in a Sql Server database
  • error occurred while establishing a connection to SQL Server
  • Is there a way to programmatically convert a SQL Server query plan to an image?
  • SQL decimal equivalent in .NET
  • 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).

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.