Easy way of Tracing open Data Reader

9. February 2012 11:32 by Mrojas in SQL Server  //  Tags: , , , ,   //   Comments (0)

Finding DataReaders that are not being close in an .NET application can be something difficult.

Thats why I use this trick to detect which DataReaders where not close.

1. First add a new code file to your project.

2. Enter this code:

public static class SqlReaderTracer
	{
		static Dictionary<WeakReference,System.Diagnostics.StackTrace> traceInfo = new Dictionary<WeakReference,System.Diagnostics.StackTrace>();
		public static System.Data.SqlClient.SqlDataReader ExecuteReaderEx(this System.Data.SqlClient.SqlCommand command)
		{
			var stack = new System.Diagnostics.StackTrace(1, true);
			var reader = command.ExecuteReader();
			traceInfo.Add(new WeakReference(reader), stack);
			return reader;
		}
		public static void PrintTraceState()
		{
			Console.WriteLine("*******SQL Trace Results************");
			foreach (var reader in traceInfo.Keys)
			{
				if (reader.IsAlive)
				{
					var traceInfoFrame =  traceInfo[reader].GetFrame(0);
					var methodWhereItWasCreated =traceInfoFrame.GetMethod().DeclaringType.FullName + "." + traceInfoFrame.GetMethod();
					Console.Write("Reader alive created in " + methodWhereItWasCreated);
					if (traceInfoFrame.GetFileName() != null)
					{
						Console.Write(string.Format("at {0} ({1},{2})",traceInfoFrame.GetFileName(),traceInfoFrame.GetFileLineNumber(),traceInfoFrame.GetFileColumnNumber()));
					}
					Console.WriteLine();
						
				}
			}
		}

	}

And then to a Find /Replace All and change all the .ExecuteReader() for .ExecuteReaderEx(). 3. Ok Now. You are all set. To test it do something like this:

class Program
	{
		
		static void Main(string[] args)
		{
			var xxx = new System.Data.SqlClient.SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\MyProjects\Phonebook.mdf;Integrated Security=True");
			xxx.Open();
			var ccc = xxx.CreateCommand();
			ccc.CommandText = "SELECT * from Phonebook";
			var reader = ccc.ExecuteReaderEx();
			xxx.Close();
			SqlReaderTracer.PrintTraceState();
			return;
		}
	}

And you will see an output like: