.NET/C#: extension method shows what SQL + parameter values are sent to the server (via StackOverwlow)
Posted by jpluimers on 2013/08/28
Every once in a while you are in situation where you are not allowed to use SQL Server Profiler, nor to see any query plans, but you still want see the SQL going from your .NET apps to the database server.
With that SQL, you can feed it through your favourite database tool, and see where the culprit is.
There are various ways of getting rudimentary or a bit more advanced SQL out of this. Flapper posted a solution that is specific for SQL Server (and requries both ObjectExtensions.cs and StringExtensions.cs from the DotNetX library), but posted more ready to use SQL.
I opted – and wanted to hank Justin Harris – for this piece of code on StackOverflow, which works on any IDbCommand (so you can use it for any ADO.NET data provider, like SQL Server, OLE DB, Oracle, etc):
While you will not be able to plug is into something like Enterprise Manager to run it works for logging.
public static string ToReadableString(this IDbCommand command) { StringBuilder builder = new StringBuilder(); if (command.CommandType == CommandType.StoredProcedure) builder.AppendLine("Stored procedure: " + command.CommandText); else builder.AppendLine("Sql command: " + command.CommandText); if (command.Parameters.Count > 0) builder.AppendLine("With the following parameters."); foreach (IDataParameter param in command.Parameters) { builder.AppendFormat( " Paramater {0}: {1}", param.ParameterName, (param.Value == null ? "NULL" : param.Value.ToString())).AppendLine(); } return builder.ToString(); }answered Apr 12 ’10 at 20:26; juharr
You saved my day! Not being allowed to use the profiler, this is a great way to get the actual SQL, then run it from SSMS or the Enterprise Manager. – Jeroen Wiert Pluimers
I pasted it in a DataExtensions class like this:
using System;
using System.Text;
using System.Data;
namespace BeSharp.Data
{
public static class DataExtensions
{
public static string ToReadableString(this IDbCommand command)
/* ... */
}
}
then used it like this:
try
{
db.LoadDataSet(dbCommandWrapper, databaseDataset, command.Tables);
}
catch(Exception ex)
{
string sql = dbCommandWrapper.Command.ToReadableString();
throw new DataLayerException("DataWrapper Exception" + Environment.NewLine + sql, ex);
}
I know the usage code is very dirty (it was refactored afterwards), but it was the quickest way of getting the full SQL out of the app (it had about a dozen layers of exception handling, and a maze of logging settings complicating getting the information out in a regular way).
–jeroen
via: c# – What SQL is being sent from a SqlCommand object – Stack Overflow.
The Wiert Corner - irregular stream of stuff 
Leave a Reply