When working with an app that does not have much of a logger, or in an environment where one is not allowed to install their own nu-get packages to set up a proper logger.

I found this simple logger to be an ideal way to watch what SQL the application is sending over to SQL Server when working with ADO.NET. It outputs what stored procedures are being called, what the name of those procedures, what parameters are being passed (with what values), etc.

It provides a fast way to look at everything going across the database connection in the Visual Studio output window.

First import the diagnostics library and reflection library:

using System.Diagnostics;
using System.Reflection;

below….

results.Load(sqlCommand.ExecuteReader());

And above where the connection to the database is closed…

//close the connection
connection.Close();

Place this code:

MethodBase method = MethodBase.GetCurrentMethod();
logQuery(sqlCommand, method);

The above code simply uses reflection to grab the current method. It ‘saves’ the method as a MethodBase object.

Next, it executes the loqQuery method we are about to create. We pass the sqlCommand object, which contains all sorts of neat things, and the method we just grabbed.

Here is the loqQuery method:

private void logQuery(SqlCommand sqlCommand, MethodBase callingMethod)
{
	Trace.WriteLine("******************************");
	Trace.WriteLine(DateTime.Now);
	Trace.WriteLine("SQL query was issued by this method: " + callingMethod.ReflectedType.Name + "." + callingMethod.Name);
	Trace.WriteLine("Called the following SQL Command: ");
	Trace.WriteLine(sqlCommand.CommandText + " with " + sqlCommand.Parameters.Count + " parameter(s).");
	Trace.WriteLine("Parameters for: " + sqlCommand.CommandText + ":");

	foreach (SqlParameter parameter in sqlCommand.Parameters)
	{
		try
		{
			Trace.WriteLine(sqlCommand.CommandText + parameter.ParameterName.ToString() + " has SqlDbType: " + parameter.SqlDbType);
			if (parameter.SqlDbType != SqlDbType.Structured)
			{
				if (string.IsNullOrEmpty(parameter.Value.ToString()) || parameter.Value == null)
				{
					Trace.WriteLine(parameter.ParameterName.ToString() + " : Is NULL.");
					continue;
				}	
				else
				{
					Trace.WriteLine(sqlCommand.CommandText + parameter.ParameterName.ToString() + ": " + parameter.SqlValue.ToString() + " ");	
				}
			}	
			else
			{
				// loop through columns --
				Trace.WriteLine("A special Structured Table was passed.");
				var table = parameter.Value as DataTable;

				foreach (DataColumn column in table.Columns)
				{
					Trace.WriteLine(sqlCommand.CommandText + parameter.ParameterName.ToString() + " table has a column named: " + column.ColumnName);
				}
				Trace.WriteLine(sqlCommand.CommandText + parameter.ParameterName.ToString() + " has: " + table.Rows.Count + " rows.");
				Trace.WriteLine("The row contents are: ");
				foreach (DataRow row in table.Rows) 
				{
					foreach (var item in row.ItemArray)
					{
						Trace.WriteLine(item.ToString());	
					}	
				}
				Trace.WriteLine(parameter.Value.ToString());
			}
		}	
		catch (AggregateException ae)
		{
			Trace.WriteLine("One or more exceptions occurred when printing SQL parameters: ");
			foreach (var ex in ae.Flatten().InnerExceptions)
			{
				Trace.WriteLine("  {0}", ex.Message);
				Trace.WriteLine("");
			}	
		}
	}
	Trace.WriteLine("***********************************");
}