Using System.Diagnostic.DiagnosticSource to intercept database requests

.NET has built-in integration with DiagnosticSource. Through this class libraries can send events and applications can subscribe to these events. Each such event contains additional information that can help you diagnose what’s going on inside your application. The great thing is that DiagnosticSource is already used by libraries like AspNetCore, EntityFrameworkCore, HttpClient, and SqlClient, what makes it possible for developers to intercept incoming / outgoing http requests, database queries, and so on.

Let’s create a small example that allows us to intercept database requests coming from SqlClient:

• Add a reference to the System.Diagnostics.DiagnosticSource NuGet package

dotnet add package System.Diagnostics.DiagnosticSource

• Create an Observer class that implements the IObserver<DiagnosticListener> interface. We’ll complete the implementation later.

	public sealed class SqlClientObserver : IObserver<DiagnosticListener>
	{
	void IObserver<DiagnosticListener>.OnNext(DiagnosticListener diagnosticListener)
	{}
	void IObserver<DiagnosticListener>.OnError(Exception error)
	{ }
	void IObserver<DiagnosticListener>.OnCompleted()
	{ }
	}

view raw SqlClientObserver.cs hosted with ❤ by GitHub

• Subscribe an instance the Observer class through the DiagnosticListener.AllListeners object:

	var observer = new SqlClientObserver();
	IDisposable subscription = DiagnosticListener.AllListeners.Subscribe(observer);

view raw Subscribe.cs hosted with ❤ by GitHub

• Complete the Observer class implementation by only observing events coming from the SqlClientDiagnosticListener:

	public sealed class SqlClientObserver : IObserver<DiagnosticListener>
	{
	private readonly IList<IDisposable> _listeners = new List<IDisposable>();
	void IObserver<DiagnosticListener>.OnNext(DiagnosticListener diagnosticListener)
	{
	lock (_listeners)
	{
	if (diagnosticListener.Name == "SqlClientDiagnosticListener")
	{
	var subscription = diagnosticListener.Subscribe(this);
	_listeners.Add(subscription);
	}
	}
	}
	void IObserver<DiagnosticListener>.OnError(Exception error)
	{ }
	void IObserver<DiagnosticListener>.OnCompleted()
	{
	lock(_listeners)
	{
	_listeners.ForEach(x => x.Dispose());
	_listeners.Clear();
	}
	}
	}

view raw SqlClientObserver.cs hosted with ❤ by GitHub

• As a last step we need to implement a second interface IObserver<KeyValuePair<string, object>>. This requires use to implement a method IObserver<KeyValuePair<string, object>>.OnNext that takes as a parameter KeyValuePair<string, object>, where the key is the name of the event, and the value is an object that gives us some extra context:

	public sealed class SqlClientObserver : IObserver<DiagnosticListener>, IObserver<KeyValuePair<string, object>>
	{
	void IObserver<KeyValuePair<string, object>>.OnNext(KeyValuePair<string, object> pair)
	{
	Console.WriteLine(pair.Key);
	Console.WriteLine(pair.Value)
	}
	void IObserver<KeyValuePair<string, object>>.OnError(Exception error)
	{ }
	void IObserver<KeyValuePair<string, object>>.OnCompleted()
	{ }
	//Hided implementation of IObserver<DiagnosticListener>
	}

view raw SqlClientObserver.cs hosted with ❤ by GitHub

If we now run our application and do a database query(through Entity Framework, NHibernate or another library that is using the SqlClient) you’ll see output like this:

System.Data.SqlClient.WriteConnectionOpenBefore
{ OperationId = 3da1b5d4-9ce1-4f28-b1ff-6a5bfc9d64b8, Operation = OpenAsync, Connection = System.Data.SqlClient.SqlConnection, Timestamp = 26978341062 }
System.Data.SqlClient.WriteConnectionOpenAfter
{ OperationId = 3da1b5d4-9ce1-4f28-b1ff-6a5bfc9d64b8, Operation = OpenAsync, ConnectionId = 84bd0095-9831-456b-8ebc-cb9dc2017368, Connection = System.Data.SqlClient.SqlConnection,
Statistics = System.Data.SqlClient.SqlStatistics+StatisticsDictionary, Timestamp = 26978631500 }
System.Data.SqlClient.WriteCommandBefore
{ OperationId = 5c6d300c-bc49-4f80-9211-693fa1e2497c, Operation = ExecuteReaderAsync, ConnectionId = 84bd0095-9831-456b-8ebc-cb9dc2017368, Command = System.Data.SqlClient.SqlComman
d }
System.Data.SqlClient.WriteCommandAfter
{ OperationId = 5c6d300c-bc49-4f80-9211-693fa1e2497c, Operation = ExecuteReaderAsync, ConnectionId = 84bd0095-9831-456b-8ebc-cb9dc2017368, Command = System.Data.SqlClient.SqlComman
d, Statistics = System.Data.SqlClient.SqlStatistics+StatisticsDictionary, Timestamp = 26978709490 }
System.Data.SqlClient.WriteConnectionCloseBefore
{ OperationId = 3f6bfd8f-e5f6-48b7-82c7-41aeab881142, Operation = Close, ConnectionId = 84bd0095-9831-456b-8ebc-cb9dc2017368, Connection = System.Data.SqlClient.SqlConnection, Stat
istics = System.Data.SqlClient.SqlStatistics+StatisticsDictionary, Timestamp = 26978760625 }
System.Data.SqlClient.WriteConnectionCloseAfter
{ OperationId = 3f6bfd8f-e5f6-48b7-82c7-41aeab881142, Operation = Close, ConnectionId = 84bd0095-9831-456b-8ebc-cb9dc2017368, Connection = System.Data.SqlClient.SqlConnection, Stat
istics = System.Data.SqlClient.SqlStatistics+StatisticsDictionary, Timestamp = 26978772888 }

As you can see, we capture different event where every event includes a set of parameters.