How to override SqlCommand to grab generated TSQL

Question

Is there any way to intercept the SQL that's generated by SqlCommand?

I currently have a method that will execute a stored procedure:

public int ExecSP(string spName, params objec[] params)
{
     using (SqlConnection con = new SqlConnection("AdventureWorks")) 
     using (SqlCommand cmd = new SqlCommand(spName, con)) 
     {
         cmd.CommandType = CommandType.StoredProcedure;

        //..calls method to add params and values to cmd object

        con.Open();
        return cmd.ExecuteNonQuery();
     }
}

When I use this to call the following:

ExecSP("HumanResources.uspUpdateEmployeePersonalInfo", 1, "295847284", new DateTime(1963, 3, 2), "S", "M");`

I get the following in SQLProfiler:

exec HumanResources.uspUpdateEmployeePersonalInfo @BusinessEntityID=1,@NationalIDNumber=N'295847284',@BirthDate='1963-03-02 00:00:00',@MaritalStatus=N'S',@Gender=N'M'

What I would like to do is intercept that SQL command so that I may add a comment to the end of it that will contain some pertinent information so that it looks like:

exec HumanResources.uspUpdateEmployeePersonalInfo @BusinessEntityID=1,@NationalIDNumber=N'295847284',@BirthDate='1963-03-02 00:00:00',@MaritalStatus=N'S',@Gender=N'M' -- IMPORTANT INFORMATION HERE

I can't change the CommandType to Text and I can't add an extra parameter to the stored procedure. I tried looking at these other questions but had no luck:

Can I override SqlCommand functions?

SqlCommand to T-SQL

I am not aware of anything but the source code can be found online. You could create your own version and use that instead to accomplish this. — Igor, Mar 28 '17 at 21:25
I was hoping I could avoid this if possible as creating a custom version could open up unexpected bugs. Thanks though. — ODotN, Mar 28 '17 at 22:57
Possible duplicate of a old question of mine http://stackoverflow.com/questions/29153589/is-there-a-easy-way-to-get-the-sp-executesql-query-net-generates-for-a-parame — Scott Chamberlain, Mar 28 '17 at 23:50
@ScottChamberlain yeah that's very similar. Thanks for the link. Were you able to implement it? — ODotN, Mar 29 '17 at 00:09
No. We went with a different approach due to time concerns with writing a parser. And the accepted answer came months later after the project was done. — Scott Chamberlain, Mar 29 '17 at 00:28
@ScottChamberlain Oh ok. Mind if I ask what approach you took? Trying to decide if i should try implementing the `BuildParamList` method — ODotN, Mar 29 '17 at 00:34
The approach we did was to not do anything at all. I was trying to change something that stored unparameterized queries that would be executed later in to parameterized ones. We ended up just keeping the old method of not parameterizing the queries. — Scott Chamberlain, Mar 29 '17 at 02:33

Ben Gribaudo · Accepted Answer · 2017-03-30T01:09:01.670

1

In the case of CommandType = CommandType.StoredProcedure, this isn't possible.

Why? The SQL you see in SQL Profiler isn't generated by the client. :-/

When SqlCommand executes a CommandType.StoredProcedure command, it sends SQL Server an execute remote procedure call message with the name of the stored procedure and a data structure containing the parameters.

The TextData SQL Profiler displays for the RPC:Starting/RPC:Completed events is generated server-side. Since this text isn't generated client-side, it's not possible to modify it client-side.

edited Mar 30 '17 at 01:09

answered Mar 28 '17 at 22:27

Ben Gribaudo

4,842
1
34
72

That's unfortunate. Would you happen to know if there's any documentation where I can read about this? Thanks. – ODotN Mar 28 '17 at 22:58
It's not possible for CommandType.Text or CommandType.TableDirect, either. – Joel Coehoorn Mar 28 '17 at 23:48
with CommandType.Text I would just append the information to query since it's just a sql comment and that works fine. I was just trying not to change CommandType.StoredProcedure to CommandType.Text – ODotN Mar 29 '17 at 00:08
Hi @ODot, for documentation, take a look at how TDS defines its [RPC Request](https://msdn.microsoft.com/en-us/library/dd357576.aspx). Notice how the proc name (or special proc ID) is included in the call but no T-SQL command text is passed. – Ben Gribaudo Mar 29 '17 at 12:25

score 0 · Answer 2 · edited May 23 '17 at 12:17

What you're seeing is a debug/profile message, but that doesn't perfectly represent what was actually executed (the database will use sp_executesql() behind the scenes).

What you are looking for doesn't exist. The whole point of using parameter objects is that parameter values are NEVER, at any time, included as part of the sql command string, even on the server.

This accomplishes three main things:

It prevents any possibility of sql injection attacks via that query. You'll never have a problem with some weird unicode character set problem allowing an attacker to insert a command into the data for your query, like they did a couple years back with php/mysql, or some new language feature creating a situation where your old escape code wasn't good enough. The user-data portion of a query is always separated from the command logic portion of the query at every stage.
It improves performance, by allowing the server to cache the execution plan. This saves compile steps as your application executes what may be the same query over and over, just with different parameter data.
It avoids problems with formatting for things like dates, text data with apostrophes, and the like.

If you want to get debugging data, write a method to output the query and the parameter data that goes with it. If you want to add info the your profile trace, you can switch to CommandType.Text and call the stored procedure via your own EXEC procedurename @param1, @param2, ... @paramN -- INFO HERE string.

Hi @Joel Coehoorn! When you say "the database will use sp_executesql() behind the scenes", wouldn't that be true for a `CommandType = CommandType.CommandText` where the query uses parameters but not for `CommandType - CommandType.StoredProcedure` (as in the OP's question)? Or, are you saying that the database takes the TDS RPC request (packet header type 0x03) and converts it to T-SQL then calls sp_executesql()?? — Ben Gribaudo, Mar 29 '17 at 12:29

How to override SqlCommand to grab generated TSQL

2 Answers2