SqlTraceListener QuickStart


A fuller example of how to use the SqlTraceListener. In this sample we are using a SQLEXPRESS database but there is nothing stopping you using a full-fat version of SQL Server (in fact, we'd recommend it!).

Firstly, here is the full config file that wires up our source and SqlTraceListener. Note that the source is called "realSource".

<?xml version="1.0" encoding="utf-8" ?>
    <section name="ukadc.diagnostics" type="Ukadc.Diagnostics.Configuration.UkadcDiagnosticsSection, Ukadc.Diagnostics"/>
      <source name="realSource" switchValue="All">
          <add type="Ukadc.Diagnostics.Listeners.SqlTraceListener, Ukadc.Diagnostics" name="sqlTraceListener" initializeData="realTest" />
      <sqlTraceListener name="realTest"
        commandType="Text" commandText="INSERT INTO LogExample (ActivityId, Message, Level, CustomerName, Timestamp, ThreadId, ProcessId, Source) 
VALUES (@ActivityId, @Message, @EventType, @CustomerName, @Timestamp, @ThreadId, @ProcessId, @Source)"
        connectionString="Data Source=.\SQLEXPRESS; Integrated Security=True;AttachDbFilename=|DataDirectory|\Test.mdf;Initial Catalog=Test">
          <parameter name="@ActivityId" propertyToken="{ActivityId}" />
          <parameter name="@EventType" propertyToken="{EventType}" callToString="True" />
          <parameter name="@Message" propertyToken="{Message}" />
          <parameter name="@CustomerName">
            <dynamicProperty sourceType="YourNamespace.Customer, YourAssembly" propertyName="Name" />
          <parameter name="@Timestamp" propertyToken="{DateTime}" />
          <parameter name="@ThreadId" propertyToken="{ThreadId}" />
          <parameter name="@ProcessId" propertyToken="{ProcessId}" />
          <parameter name="@Source" propertyToken="{Source}" />

Those playing close attention will have noticed that we're using a DynamicPropertyReader by specifying a dynamicProperty node for the @CustomerName parameter. This will look for a type 'YourNamespace.Customer' in an assembly called 'YourAssembly' which, in this sample, happens to look just like this:

public class Customer
    public string Name { get; set; }
    public int Id {get; set; }

And we're almost done. All that's left is to actually instrument some code... and this is all you need.

// create a TraceSource - uses the realSource source in config
TraceSource source = new TraceSource("realSource");

// create a new activity id - this lives in CallContext/TLS and will be picked up by all logevents on this thread
Trace.CorrelationManager.ActivityId = Guid.NewGuid();

// log the start of our operation
source.TraceEvent(TraceEventType.Start, 0, "RealTest operation")

// log the customer data
Customer customer = new Customer() { Name = "Josh Twist" };
source.TraceData(TraceEventType.Information, 0, customer);

// log the end of our operation
source.TraceEvent(TraceEventType.Stop, 0, "RealTest operation");

Et voila! Here are the results shown in our SQL database:


See the QuickReferenceExample for an example of how this data might be consumed.

Last edited Apr 11, 2008 at 11:56 AM by joshtwist, version 7


No comments yet.