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:

  • Create view in sql server “names in each view or function must be unique”
  • Quering current cursor row - without selecting into variables
  • Associated table Not getting updated
  • Updating “Hierarchyid” in SQL Server
  • Most efficient way to combine data from different database when join is not possible
  • How to get data preferentially from the joined tables in SQL Server
  • 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!

  • SqlCeCommand ExecuteNonQuery performance issue
  • Does for loop open and close a database connection on each iteration?
  • “Invalid attempt to call Read when reader is closed” error (for lengthy operations only)
  • How can I count rows returned by the stored procedure?
  • Best way to access a SQL Server database using C# .Net
  • Data SYNC between SQL Server and Mongo DB
  • 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.