Using MySQL database to save .NET traces

I’m a great fan of the Essential.Diagnostics and this post will be again committed to this library. In our company we use MS SQL Server as our main database server and an instance of MySql to store logs from applications. If you are using System.Diagnostics tracing you probably lack more advanced trace listeners (like the ones provided by log4net). A remedy for this problem might be the aforementioned Essential.Diagnostics library. In this short post I will show you how to configure the SqlDatabaseTraceListener to work with a MySQL database.

First we need to prepare our log database. The Essential.Diagnostics library provides a sql script for MS SQL Server. I slightly modified it in order to make it work with MySQL database:

CREATE TABLE PaymentRouters_Trace(
	TraceId INT NOT NULL AUTO_INCREMENT,
	ApplicationName VARCHAR(256) NOT NULL,
	Source VARCHAR(64) NULL,
	Id INT NOT NULL,
	EventType VARCHAR(32) NOT NULL,
	UtcDateTime DATETIME NOT NULL,
	MachineName VARCHAR(32) NOT NULL,
	AppDomainFriendlyName VARCHAR(512) NOT NULL,
	ProcessId INT NOT NULL,
	ThreadName VARCHAR(512) NULL,
	Message VARCHAR(1500) NULL,
	ActivityId CHAR(36) NULL,
	RelatedActivityId CHAR(36) NULL,
	LogicalOperationStack VARCHAR(512) NULL,
	DATA TEXT NULL,
	PRIMARY KEY (TraceId)
  )
go


CREATE PROCEDURE diagnostics_Trace_AddEntry(
  `ApplicationName` VARCHAR(256),
  `Source` VARCHAR(64),
  `Id` INT,
  `EventType` VARCHAR(32),
  `UtcDateTime` DATETIME,
  `MachineName` VARCHAR(32),
  `AppDomainFriendlyName` VARCHAR(512),
  `ProcessId` INT,
  `ThreadName` VARCHAR(512),
  `Message` VARCHAR(1500),
  `ActivityId` CHAR(36),
  `RelatedActivityId` CHAR(36),
  `LogicalOperationStack` VARCHAR(512),
  `Data` TEXT
)
INSERT INTO PaymentRouters_Trace(
	`ApplicationName`, 
	`Source`, 
	`Id`, 
	`EventType`, 
	`UtcDateTime`, 
	`MachineName`, 
	`AppDomainFriendlyName`, 
	`ProcessId`, 
	`ThreadName`, 
	`Message`, 
	`ActivityId`, 
	`RelatedActivityId`, 
	`LogicalOperationStack`, 
	`Data`
) VALUES (
	`ApplicationName`, 
	`Source`, 
	`Id`, 
	`EventType`, 
	`UtcDateTime`, 
	`MachineName`, 
	`AppDomainFriendlyName`, 
	`ProcessId`, 
	`ThreadName`, 
	`Message`, 
	`ActivityId`, 
	`RelatedActivityId`, 
	`LogicalOperationStack`, 
	`Data`
)

You may want to add some indexes on columns that you will use in filtering the logs. After the database is set we are ready to setup its client.

In order to use MySql database from .NET you need to get a connector. You can download it from http://dev.mysql.com/downloads/connector/net/ or use a Nuget package. There are two versions available on the MySql page: MSI installer and a zip file. If you choose the MSI installer you need to keep in mind that it installs MySql assemblies in GAC and modifies your machine.config. That might be a problem if you are planning to deploy your application on a server and you don’t have administrative rights on it. That’s one of the reasons why I choose the zip version of the library (or Nuget package) – not to mention that it only requires few more lines in your web/app.config to work. Those few lines register the MySqlClientFactory:

<configuration>
...
  <system.data>
    <DbProviderFactories>
      <add name="MySql Data Provider" invariant="System.Data.MySqlClient" 
            type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data" description="MySql data provider" />
    </DbProviderFactories>
  </system.data>
...
</configuration>

Then you need to add a connection string to your MySQL database:

<configuration>
...
  <connectionStrings>
    <add name="MySqlTraceConnString" connectionString="Data Source=localhost;Initial Catalog=DiagnosticsDB;User Id=test;Password=test;" providerName="System.Data.MySqlClient" />
  </connectionStrings>
...
</configuration>

The last step is to add your application trace source to the system.diagnostics section and configure a SqlDatabaseTraceListener for it:

<configuration>
...
  <system.diagnostics>
    <trace autoflush="true" />
    <sources>
      <source name="TestTrace" switchValue="Verbose">
        <listeners>
          <add name="mysql" initializeData="MySqlTraceConnString" 
                            type="Essential.Diagnostics.SqlDatabaseTraceListener, Essential.Diagnostics"
                            applicationName="TestTrace"
                            commandText="call diagnostics_Trace_AddEntry(@ApplicationName, @Source, @Id, @EventType, @UtcDateTime, @MachineName, @AppDomainFriendlyName, @ProcessId, @ThreadName, @Message, @ActivityId, @RelatedActivityId, @LogicalOperationStack, @Data)" />
        </listeners>
      </source>
    </sources>
  </system.diagnostics>
...
</configuration>

Finally start using your brand new TraceListener

using System;
using System.Diagnostics;

public static class TestTrace 
{
    private static readonly TraceSource logger = new TraceSource("TestTrace");

    public static void Main(String[] args) {
        logger.TraceInformation("Start");
        
        Console.WriteLine("In the middle of tracing");
        
        logger.TraceInformation("End");
    }
}

As usually all the source codes are available on my blog samples repository.