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.

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

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

• 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:

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.