SQL Server Publisher

Download source code

This sample application demonstrates how to trace messages into a Microsoft SQL Server 2005 Database.

Step 1: Create table for trace messages

To store the messages in a SQL Server Database we need a table with a specific structure. The following script creates a table to insert messages for the TraceSQLPublisher.

------------------------------------------------------------------------
-- SQL Script to create a trace log table for the TraceSQLPublisher
-- Usable with: Microsoft SQL Server 2000 and Microsoft SQL Server 2005
------------------------------------------------------------------------
-- Database 
USE ADVENTUREWORKS 
GO 
-- Drops the [CoyoteTraceLog] table if it exists
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[CoyoteTraceLog]') 
            AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    DROP TABLE [dbo].[CoyoteTraceLog]
GO
-- create the [CoyoteTraceLog] table
CREATE TABLE [dbo].[CoyoteTraceLog]( 
        [id] [uniqueidentifier] NOT NULL,
        [timestamp] [datetime] NOT NULL,
        [appDomainName] [varchar](255) NULL,
        [machineName] [varchar](255) NULL,
        [typeName] [varchar](255) NULL,
        [methodName] [varchar](1024) NULL,
        [category] [varchar](255) NULL,
        [level] [varchar](20) NOT NULL, 
        [body] [varchar](8000) NULL, 
        [threadIdentity] [varchar](255) NULL,
        [windowsIdentity] [varchar](255) NULL,
        [additionalInfo] [varchar](2024) NULL
) ON [PRIMARY]
GO

Step 2:  Configure your application

To enable tracing in your application it's important to add the “traceManagement” section into your application configuration file. The following sample shows the necessary configuration to trace into a database. Make sure that the connection string in the configuration file is adapted to your individual database settings.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
        <section
            name="traceManagement"
            type="Css.Diagnostics.TraceManagementSection,Css.Diagnostics.TraceComLib"/>
    </configSections>
    <traceManagement mode="On">
        <publishers>
            <!--
            TraceSQLPublisher writes the trace message to a SQL server 
      table of your choice
            
            connectionstring: A valid SQL connection string
            commandtext:    The SQL Command the SQL Publisher executes. 
      The parameter names (@Foo) must match the names of the sqlParam in the 
      commandparaeters element
            commandparameters: Maps the SQL parameter names to 
                         trace message property names.
            Format: sqlParam=;msgProp=#
            -->
            <publisher key="TraceSQLPublisher1" mode="On" 
                 assembly="Css.Diagnostics.TraceComLib" 
                 type="Css.Diagnostics.Publishers.TraceSQLPublisher">
                <attributes>
                    <attribute name="connectionstring" 
                     value="Data Source=.\SQLEXPRESS;
                            Initial Catalog=ADVENTUREWORKS;
                            Integrated Security=True;">
            
          	  </attribute>
                    <attribute name="commandtext" 
                     value="INSERT INTO CoyoteTraceLog(
                            [id],[timestamp],[machineName],[appDomainName],
                            [typeName],[methodName],[category],[level],[body],
                            [windowsIdentity],[threadIdentity],[additionalInfo]) 
                            VALUES (@id,@timestamp,@machineName,@appDomainName,
                            @typeName, @methodName,@category,@level,
                            left(@body,8000),@windowsIdentity,@threadIdentity,
                            @additionalInfo)">
            
          	  </attribute>
                    <attribute name="commandparameters" 
                     value="sqlParam=@id;msgProp=Id
                           #sqlParam=@timestamp;msgProp=TimeStamp
                           #sqlParam=@machineName;msgProp=MachineName
                           #sqlParam=@appDomainName;msgProp=AppDomainName
                           #sqlParam=@typeName;msgProp=TypeName
                           #sqlParam=@methodName;msgProp=MethodName
                           #sqlParam=@category;msgProp=Category
                           #sqlParam=@level;msgProp=Level
                           #sqlParam=@body;msgProp=Body
                           #sqlParam=@windowsIdentity;msgProp=WindowsIdentityName
                           #sqlParam=@threadIdentity;msgProp=ThreadIdentityName
                           #sqlParam=@additionalInfo;msgProp=AdditionalInfo">
            
          	  </attribute>
                </attributes>
                <filters>
                    <filter type="*" level="4"></filter>
                </filters>
            </publisher>
        </publishers>
        <filters>
            <filter type="*" level="4"></filter>
        </filters>
    </traceManagement>
</configuration>

Step 3: Reference Css.Diagnostics.TraceComLib

The whole tracing technology is located in the “Css.Diagnostics.TraceComLib” Library. Referencing this Library in your application offers you the possibility to create and trace messages.

Step 4: Tracing

The following snippet shows a possible implementation to trace messages in your application.

using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.Remoting;
using Css.Diagnostics;

namespace Css.Coyote.Samples.SqlPublisher
{
    class Program
    {
        static void Main(string[] args)
        {
            RemotingConfiguration.Configure
                (System.IO.Path.Combine(
                      AppDomain.CurrentDomain.BaseDirectory
                    , AppDomain.CurrentDomain.SetupInformation.ConfigurationFile)
                    , true);

            Exception ex = new Exception("If you read this, everything works fine ;-)");

            try
            {
                throw ex;
            }
            catch (Exception)
            {
                TraceAgent.Write(ex, TraceLevel.Error);
            }

            Console.WriteLine("Look in your test database!");
            Console.ReadKey();
        }
    }
}

Step 5: Look into your Database

After tracing with the above configuration the trace messages where stored in the database.