4

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 log the executed sql statement. In this case I would like to log the string:

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

Question

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

Alberto De Caro
  • 5,147
  • 9
  • 47
  • 73
  • 1
    That statement is never executed - when using parameters, the **parametrized** query and the list of parameters and their values is sent to SQL Server. Only there are parameter values introduced into the query. – marc_s Aug 13 '12 at 10:23
  • What is the *purpose* of the logging? More context might help – Marc Gravell Aug 13 '12 at 10:29
  • @Marc Gravell: if I log the sql statement it can be quickly executed in a sql client in case of error, exception, check or whatever. – Alberto De Caro Aug 13 '12 at 10:34
  • In that case, I'll undelete my answer; I think the tool could really help for that – Marc Gravell Aug 13 '12 at 10:36

3 Answers3

4

There is no magic way to do this, but there are a number of tools that can play nicely with ADO.NET to capture what is happening. For example, mini-profiler does this by wrapping ADO.NET, which usually involves just a single change to your "create a connection" code - it then logs operations internally. The code is open source, so it would be trivial to tweak it to your own logging framework.

As an example, if I go (logged in) to SEDE, and look at a random page, here, say, I can see all the logged SQL operations (no code changes were necessary to get this logging), here - or in case that becomes unavailable:

enter image description here

The only minor glitch is your explicit use of SqlParameter, which might need changing to cmd.CreateParameter(). Alternatively, use something like dapper to make it easier:

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

Note that the declare statements show above were not part of the original query; the tool has added these to make it easy to run the query in SSMS, etc.

Community
  • 1
  • 1
Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
1

no, unfortunatelly you would have to loop through all the paramenters to get their values and concatenate it with the command text

Diego
  • 34,802
  • 21
  • 91
  • 134
  • 2
    yep, that's how I ended up doing it. If memory serves I might have made the access pretty (an extension method of ToString on the Command springs to mind) but under the hood I ended up doing a foreach on the parameters. But this had its advantages as I was also able to output values and directions. Surprising how useful this was for debugging! – PeteH Aug 13 '12 at 10:27
0

or edit your stored procedure to do the logging by editing it to add the equivalent of:

insert into my_sp_log 
values ('sp_trk_getSuspiciousVisitor', @channel, @data, getdate())
paul
  • 21,653
  • 1
  • 53
  • 54