Click here to Skip to main content
15,867,686 members
Articles / Database Development / MySQL

Using MySQL database to save .NET traces

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
6 Jun 2012CPOL2 min read 13.4K   5   2
How to configure the SqlDatabaseTraceListener to work with a MySQL database.

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 Smile | :)

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
Poland Poland
Interested in tracing, debugging and performance tuning of the .NET applications.

My twitter: @lowleveldesign
My website: http://www.lowleveldesign.org

Comments and Discussions

 
Questionerror Pin
Robert Marck12-Jul-12 10:21
Robert Marck12-Jul-12 10:21 
AnswerRe: error Pin
Sebastian Solnica12-Jul-12 11:06
Sebastian Solnica12-Jul-12 11:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.