An implementation of a TraceListener that writes to a SQL Server database. The SqlTraceListener makes extensive use of PropertyReaders and Tokens to create a very extensible and configurable Listener that can write to a SQL database via a Stored Procedure or a parameterised Text command.

Example Usage

The first step is to add the SqlTraceListener to your configured trace source:

<source name="myTraceSource" switchValue="All">
    <add type="Ukadc.Diagnostics.Listeners.SqlTraceListener, Ukadc.Diagnostics" name="sqlTraceListener" initializeData="initKey" />

The initializeData attribute refers to a seperate piece of configuration stored in the ukadc.Diagnostics section. Specifically, there should be

  <section name="ukadc.diagnostics" type="Ukadc.Diagnostics.Configuration.UkadcDiagnosticsSection, Ukadc.Diagnostics"/>

    <sqlTraceListener name="initKey"
        commandType="Text" commandText="INSERT INTO TestLog (ActivityId, Level, EventType) VALUES (@ActivityId, @Level, @EventType)"
        connectionString="Data Source=.\SQLEXPRESS; Integrated Security=True;AttachDbFilename=|DataDirectory|\Test.mdf;Initial Catalog=Test">
        <parameter name="@ActivityId" propertyToken="{ActivityId}" />
        <parameter name="@Level" propertyToken="{EventType}" />
        <parameter name="@EventType" propertyToken="{EventType}" callToString="True" />

The nature of this configuration should be fairly self explanatory.
  • <sqlTracelisteners> - this contains a list of <sqlTraceListener> nodes
    • <sqlTraceListener> - this node represents the configuration for a SqlTracelistener and is keyed by name (e.g. initKey in the example above). Note that any number of TraceListeners can refer to the same sqlTraceListener configuration section. Note that the commandType (Text or StoredProcedure), connectionString and commandText are also configured on this node.
      • <parameters> - contains a list of parameters to be passed to the SqlCommand when an event is logged.
        • <parameter> - specifies the name of the parameter (e.g. @ActivityId) which should map to a parameter on the stored procedure or in the commandText, the propertyToken (see below) and callToString is a boolean that indicates whether ToString() should be executed on the value before writing to the database (defaults to false).

callToString attribute

The callToString attribute on the parameter node instructs the SqlTraceListener to call ToString on the value extracted from the log event prior to writing to SQL Server. This is useful in a number of scenarios. For example, when writing an enum value to the database (such as EventType, using the {EventType} token) you may want to write the string (e.g. Information, Critical etc) instead of the numeric value of the enum (8, 6 etc).


You can use any of the pre-configured tokens or even your own. For more information see Tokens

Dynamic Properties

It is possible for developers to log objects (or even an array of objects) of any type to the System.Diagnostics plumbing using either of these methods (members of TraceSource). This uses the DynamicPropertyReader which is discussed in the PropertyReaders section.

public void TraceData(TraceEventType eventType, int id, object data);
public void TraceData(TraceEventType eventType, int id, params object[] data);

You can configure a PropertyFilter to evaluate properties on any type of object (or array of objects) passed as the data parameter. For example, in this configuration snippet we are specifying that we want to filter log events if the data object is of type YourNamespace.YourClass and its YourClassProperty does not contain "some value".

<parameter name="@DynamicExample" callToString="True">
   <dynamicProperty sourceType="YourNamespace.YourClass, YourAssembly" propertyName="YourProperty" />

Note, if you pass an array of objects, the SqlTraceListener will loop through each item in the object array looking for an object of the appropriate type (note, once a matching type has been found the filter does not continue to loop through any remaining items).

If the object is not of type YourNamespace.YourClass then the SqlTraceListener will write a DBNull to the parameter.

Note: It is invalid to specify a propertyToken value on the propertyFilter element and a dynamicProperty element. Doing so will result in a ConfigurationErrorsException the first time the filter is used.

See the SqlTraceListener Sample for a fuller example of its usage:


Obviously, logging a lot of events to SQL Server could significantly increase the load on your database and potentially reduce the performance of your system. Consider reducing the volume (see this for more information]) and always test your application to ensure that your hardware can cope with the expected load.

In many cases, system designers will choose to log a large volume of data to a database server but with a number of optimisations in place, such as:
  • logging to a seperate database on a separate disk that has been configured for a high number of writes
  • logging call is abstracted behind a Stored Procedure (supported by the SqlTraceListener) allowing for table partitioning
  • data is aggregated and ETL

Last edited Apr 15, 2008 at 11:38 AM by joshtwist, version 22


No comments yet.