How to register a executed SQL query

Question

Scenario

I am executing a stored procedure using ADO.NET SqlCommand:

cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "sp_Trk_GetSuspiciusVisitor"; cmd.Parameters.Add(new SqlParameter("channel","gb")); cmd.Parameters.Add(new SqlParameter("data", DateTime.Today.AddDays(-1))); cmd.Connection = conn; 

I would like to record a executed SQL query. In this case, I would like to write a line:

 sp_Trk_GetSuspiciusVisitor 'gb', '2012-08-12' 

Question

Is there any property of the SqlCommand or SqlConnection classes that do the job?

Answer 1

no, unfortunately, you would have to iterate over all the parasites to get their values ​​and combine them with the command text

Answer 2

There is no magic way to do this, but there are a number of tools that can play well with ADO.NET to capture what is happening. For example, the mini-profiler does this by wrapping ADO.NET, which usually only includes one change to the "create connection" code - it then logs the operations inside. The code is open source, so it would be trivial to configure it to your own logging structure.

As an example, if I logged in (SEDE) and looked at a random page, here, let's say , I can see all the registered SQL operations (no code changes were necessary to get this log), here - or in case this becomes unavailable:

The only minor glitch is your explicit use of SqlParameter , which may need to be changed to cmd.CreateParameter() . Also, use something like dapper to simplify it:

 conn.Execute("sp_Trk_GetSuspiciusVisitor", new { channel = "gb", data = DateTime.Today.AddDays(-1) }, commandType: CommandType.StoredProcedure); 

Note that the above declare statements were not part of the original request; the tool added them to simplify the launch of the request in SSMS, etc.

Answer 3

or edit your stored procedure to complete the registration by editing it to add an equivalent:

 insert into my_sp_log values ('sp_trk_getSuspiciousVisitor', @channel, @data, getdate())