Raw SQL sent to SQL Server from .NET on stored procedure call

Question

Is there a way to get the raw text that is sent to SQL Server, as seen in SQL Profiler, from the ADO.NET call?

using(SqlConnection conn = new SqlConnection(connString)) {
    SqlCommand cmd = conn.CreateCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "GetSomeData";
    cmd.Parameters.Add("@id").Value = someId;
    cmd.Parameters.Add("@someOtherParam").Value = "hello";

    conn.Open();
    SqlDataReader dr = cmd.ExecuteReader();

    // this sends up the call: exec GetSomeData @id=24, @someOtherParam='hello'
    // how can I capture that and write it to debug?

    Debug.Write("exec GetSomeData @id=24, @someOtherParam='hello'");

}

Answer 1

I don't think there is a way to get what you want out of a single property or method. We used to write a simple, static function to dump the text and then iterate over the parameters collection to dump out the parameter values. These days, you could even make it an extension method so it looks like a method of the SqlCommand object.

Answer 2

Not in your C# program - you'd have to connect up SQL Profiler and monitor what gets sent across to your SQL Server.

Answer 3

You could potentially create another stored procedure for this purpose. Your stored procedure would take as an argument the name of another stored procedure, and then look up its definition in the syscomments table.

Or you could just query syscomments directly from your ASP.NET application.

select text from syscomments where id = OBJECT_ID('NameOfSproc')