I have recently worked on a project that was using enterprise library logging capabilities. Enterprise Libiary information can be viewed on pattern and practices web site. These library includes a number of useful components, The one I am going to cover here is database logging capabilities.
Logging features allows an application to be configured to log information about itself into a destination such as Windows event log, text file or database. We decided to use database logging because of easy of access, less possibility of contention than text file and ability to easy report off this information. To install logging you just have to run a script to create and configure the database. If you installed Enterprise Library, you will find the script in BlocksLoggingSrcDatabaseTraceListenerScripts folder under the source code location which you will need to install as well. Then, you can use configuration tool that ships with the library to configure it. If you have application configuration file already part of your solution, you can right-click on it and choose Edit Enterprise Library Configuration menu. Once, we completed those steps, we noticed that some errors where not logged. What was strange is that I saw gaps in identity column values in log table. This led me to believe that errors were logged, but not committed. Upon further investigation, I found out that logging transactions where rolled back when application errors occurred. Main reason for that was that our data access code was wrapped inside TransactionScope block. This happen to be a feature that logging to database has – it does not opt out of ambient transaction scope. This can be done by using overloaded constructor:
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Suppress))
{
This setup allows the code to opt out of ambient transaction established with TransactionScope.
Next I am going to create a couple of my own classes to replace the ones that come with Enterprise Library database logging: FormattedDatabaseTraceListener and FormattedDatabaseTraceListenerData. You need to replace both because they work in conjunction. The only difference in FormattedDatabaseTraceListenerData is the creation of trace listener based on our new type:
using System;
using System.Configuration;
using System.Diagnostics;
using System.Linq.Expressions;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration.ContainerModel;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration.Design;
using Microsoft.Practices.EnterpriseLibrary.Data.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Logging.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Logging.Formatters;
namespace Logging
{
/// <summary>
/// Configuration object for a <see cref="FormattedDatabaseTraceListener"/>.
/// </summary>
[AddSateliteProviderCommand("connectionStrings", typeof(DatabaseSettings), "DefaultDatabase", "DatabaseInstanceName")]
public class FormattedDatabaseTraceListenerData : TraceListenerData
{
private const string addCategoryStoredProcNameProperty = "addCategoryStoredProcName";
private const string databaseInstanceNameProperty = "databaseInstanceName";
private const string formatterNameProperty = "formatter";
private const string writeLogStoredProcNameProperty = "writeLogStoredProcName";
/// <summary>
/// Initializes a <see cref="FormattedDatabaseTraceListenerData"/>.
/// </summary>
public FormattedDatabaseTraceListenerData()
: base(typeof(FormattedDatabaseTraceListener))
{
this.ListenerDataType = typeof(FormattedDatabaseTraceListenerData);
}
/// <summary>
/// Initializes a named instance of <see cref="FormattedDatabaseTraceListenerData"/> with
/// name, stored procedure name, databse instance name, and formatter name.
/// </summary>
/// <param name="name">The name.</param>
/// <param name="writeLogStoredProcName">The stored procedure name for writing the log.</param>
/// <param name="addCategoryStoredProcName">The stored procedure name for adding a category for this log.</param>
/// <param name="databaseInstanceName">The database instance name.</param>
/// <param name="formatterName">The formatter name.</param>
public FormattedDatabaseTraceListenerData(string name,
string writeLogStoredProcName,
string addCategoryStoredProcName,
string databaseInstanceName,
string formatterName)
: this(
name,
writeLogStoredProcName,
addCategoryStoredProcName,
databaseInstanceName,
formatterName,
TraceOptions.None,
SourceLevels.All)
{
}
/// <summary>
/// Initializes a named instance of <see cref="FormattedDatabaseTraceListenerData"/> with
/// name, stored procedure name, databse instance name, and formatter name.
/// </summary>
/// <param name="name">The name.</param>
/// <param name="writeLogStoredProcName">The stored procedure name for writing the log.</param>
/// <param name="addCategoryStoredProcName">The stored procedure name for adding a category for this log.</param>
/// <param name="databaseInstanceName">The database instance name.</param>
/// <param name="formatterName">The formatter name.</param>
/// <param name="traceOutputOptions">The trace options.</param>
/// <param name="filter">The filter to be applied</param>
public FormattedDatabaseTraceListenerData(string name,
string writeLogStoredProcName,
string addCategoryStoredProcName,
string databaseInstanceName,
string formatterName,
TraceOptions traceOutputOptions,
SourceLevels filter)
: base(name, typeof(FormattedDatabaseTraceListener), traceOutputOptions, filter)
{
DatabaseInstanceName = databaseInstanceName;
WriteLogStoredProcName = writeLogStoredProcName;
AddCategoryStoredProcName = addCategoryStoredProcName;
Formatter = formatterName;
}
/// <summary>
/// Gets and sets the database instance name.
/// </summary>
[ConfigurationProperty(databaseInstanceNameProperty, IsRequired = true)]
public string DatabaseInstanceName
{
get { return (string)base[databaseInstanceNameProperty]; }
set { base[databaseInstanceNameProperty] = value; }
}
/// <summary>
/// Gets and sets the stored procedure name for writing the log.
/// </summary>
[ConfigurationProperty(writeLogStoredProcNameProperty, IsRequired = true, DefaultValue = "WriteLog")]
public string WriteLogStoredProcName
{
get { return (string)base[writeLogStoredProcNameProperty]; }
set { base[writeLogStoredProcNameProperty] = value; }
}
/// <summary>
/// Gets and sets the stored procedure name for adding a category for this log.
/// </summary>
[ConfigurationProperty(addCategoryStoredProcNameProperty, IsRequired = true, DefaultValue = "AddCategory")]
public string AddCategoryStoredProcName
{
get { return (string)base[addCategoryStoredProcNameProperty]; }
set { base[addCategoryStoredProcNameProperty] = value; }
}
/// <summary>
/// Gets and sets the formatter name.
/// </summary>
[ConfigurationProperty(formatterNameProperty, IsRequired = false)]
public string Formatter
{
get { return (string)base[formatterNameProperty]; }
set { base[formatterNameProperty] = value; }
}
/// <summary>
/// Returns a lambda expression that represents the creation of the trace listener described by this
/// configuration object.
/// </summary>
/// <returns>A lambda expression to create a trace listener.</returns>
protected override Expression<Func<TraceListener>> GetCreationExpression()
{
return () =>
new FormattedDatabaseTraceListener(
Container.Resolved<Microsoft.Practices.EnterpriseLibrary.Data.Database>(DatabaseInstanceName),
WriteLogStoredProcName,
AddCategoryStoredProcName,
Container.ResolvedIfNotNull<ILogFormatter>(Formatter));
}
}
}
I kept the rest of the code the same. Listener itself has more changes, specifically, I am wrapping all database access code with new transaction scope:
using System;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using System.Globalization;
using System.Transactions;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Logging;
using Microsoft.Practices.EnterpriseLibrary.Logging.Formatters;
using Microsoft.Practices.EnterpriseLibrary.Logging.TraceListeners;
namespace Logging
{
/// <summary>
/// A <see cref="System.Diagnostics.TraceListener"/> that writes to a database, formatting the output with an <see cref="ILogFormatter"/>.
/// </summary>
[ConfigurationElementType(typeof(FormattedDatabaseTraceListenerData))]
public class FormattedDatabaseTraceListener : FormattedTraceListenerBase
{
string writeLogStoredProcName = String.Empty;
string addCategoryStoredProcName = String.Empty;
Microsoft.Practices.EnterpriseLibrary.Data.Database database;
/// <summary>
/// Initializes a new instance of <see cref="FormattedDatabaseTraceListener"/>.
/// </summary>
/// <param name="database">The database for writing the log.</param>
/// <param name="writeLogStoredProcName">The stored procedure name for writing the log.</param>
/// <param name="addCategoryStoredProcName">The stored procedure name for adding a category for this log.</param>
/// <param name="formatter">The formatter.</param>
public FormattedDatabaseTraceListener(
Microsoft.Practices.EnterpriseLibrary.Data.Database database,
string writeLogStoredProcName,
string addCategoryStoredProcName,
ILogFormatter formatter)
: base(formatter)
{
this.writeLogStoredProcName = writeLogStoredProcName;
this.addCategoryStoredProcName = addCategoryStoredProcName;
this.database = database;
}
/// <summary>
/// The Write method
/// </summary>
/// <param name="message">The message to log</param>
public override void Write(string message)
{
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Suppress))
{
using (DbConnection connection = database.CreateConnection())
{
connection.Open();
try
{
using (DbTransaction transaction = connection.BeginTransaction())
{
try
{
ExecuteWriteLogStoredProcedure(0, 5, TraceEventType.Information, string.Empty, DateTime.Now, string.Empty,
string.Empty, string.Empty, string.Empty, null, null, message, database);
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}
}
finally
{
connection.Close();
}
}
scope.Complete();
}
}
/// <summary>
/// The WriteLine method.
/// </summary>
/// <param name="message">The message to log</param>
public override void WriteLine(string message)
{
Write(message);
}
/// <summary>
/// Delivers the trace data to the underlying database.
/// </summary>
/// <param name="eventCache">The context information provided by <see cref="System.Diagnostics"/>.</param>
/// <param name="source">The name of the trace source that delivered the trace data.</param>
/// <param name="eventType">The type of event.</param>
/// <param name="id">The id of the event.</param>
/// <param name="data">The data to trace.</param>
public override void TraceData(TraceEventCache eventCache, string source, TraceEventType eventType, int id, object data)
{
if ((this.Filter == null) || this.Filter.ShouldTrace(eventCache, source, eventType, id, null, null, data, null))
{
if (data is LogEntry)
{
LogEntry logEntry = data as LogEntry;
if (ValidateParameters(logEntry))
ExecuteStoredProcedure(logEntry);
}
else if (data is string)
{
Write(data as string);
}
else
{
base.TraceData(eventCache, source, eventType, id, data);
}
}
}
/// <summary>
/// Declare the supported attributes for <see cref="FormattedDatabaseTraceListener"/>
/// </summary>
protected override string[] GetSupportedAttributes()
{
return new string[4] { "formatter", "writeLogStoredProcName", "addCategoryStoredProcName", "databaseInstanceName" };
}
/// <summary>
/// Validates that enough information exists to attempt executing the stored procedures
/// </summary>
/// <param name="logEntry">The LogEntry to validate.</param>
/// <returns>A Boolean indicating whether the parameters for the LogEntry configuration are valid.</returns>
private bool ValidateParameters(LogEntry logEntry)
{
bool valid = true;
if (writeLogStoredProcName == null ||
writeLogStoredProcName.Length == 0)
{
return false;
}
if (addCategoryStoredProcName == null ||
addCategoryStoredProcName.Length == 0)
{
return false;
}
return valid;
}
/// <summary>
/// Executes the stored procedures
/// </summary>
/// <param name="logEntry">The LogEntry to store in the database</param>
private void ExecuteStoredProcedure(LogEntry logEntry)
{
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Suppress))
{
using (DbConnection connection = database.CreateConnection())
{
connection.Open();
try
{
using (DbTransaction transaction = connection.BeginTransaction())
{
try
{
int logID = Convert.ToInt32(ExecuteWriteLogStoredProcedure(logEntry, database, transaction));
ExecuteAddCategoryStoredProcedure(logEntry, logID, database, transaction);
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}
}
finally
{
connection.Close();
}
}
scope.Complete();
}
}
/// <summary>
/// Executes the WriteLog stored procedure
/// </summary>
/// <param name="eventId">The event id for this LogEntry.</param>
/// <param name="priority">The priority for this LogEntry.</param>
/// <param name="severity">The severity for this LogEntry.</param>
/// <param name="title">The title for this LogEntry.</param>
/// <param name="timeStamp">The timestamp for this LogEntry.</param>
/// <param name="machineName">The machine name for this LogEntry.</param>
/// <param name="appDomainName">The appDomainName for this LogEntry.</param>
/// <param name="processId">The process id for this LogEntry.</param>
/// <param name="processName">The processName for this LogEntry.</param>
/// <param name="managedThreadName">The managedthreadName for this LogEntry.</param>
/// <param name="win32ThreadId">The win32threadID for this LogEntry.</param>
/// <param name="message">The message for this LogEntry.</param>
/// <param name="db">An instance of the database class to use for storing the LogEntry</param>
/// <returns>An integer for the LogEntry Id</returns>
private int ExecuteWriteLogStoredProcedure(int eventId, int priority, TraceEventType severity, string title, DateTime timeStamp,
string machineName, string appDomainName, string processId, string processName,
string managedThreadName, string win32ThreadId, string message, Microsoft.Practices.EnterpriseLibrary.Data.Database db)
{
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Suppress))
{
DbCommand cmd = db.GetStoredProcCommand(writeLogStoredProcName);
db.AddInParameter(cmd, "eventID", DbType.Int32, eventId);
db.AddInParameter(cmd, "priority", DbType.Int32, priority);
db.AddParameter(cmd, "severity", DbType.String, 32, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, severity.ToString());
db.AddParameter(cmd, "title", DbType.String, 256, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, title);
db.AddInParameter(cmd, "timestamp", DbType.DateTime, timeStamp);
db.AddParameter(cmd, "machineName", DbType.String, 32, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, machineName);
db.AddParameter(cmd, "AppDomainName", DbType.String, 512, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, appDomainName);
db.AddParameter(cmd, "ProcessID", DbType.String, 256, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, processId);
db.AddParameter(cmd, "ProcessName", DbType.String, 512, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, processName);
db.AddParameter(cmd, "ThreadName", DbType.String, 512, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, managedThreadName);
db.AddParameter(cmd, "Win32ThreadId", DbType.String, 128, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, win32ThreadId);
db.AddParameter(cmd, "message", DbType.String, 1500, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, message);
db.AddInParameter(cmd, "formattedmessage", DbType.String, message);
db.AddOutParameter(cmd, "LogId", DbType.Int32, 4);
db.ExecuteNonQuery(cmd);
int logId = Convert.ToInt32(cmd.Parameters[cmd.Parameters.Count – 1].Value, CultureInfo.InvariantCulture);
scope.Complete();
return logId;
}
}
/// <summary>
/// Executes the WriteLog stored procedure
/// </summary>
/// <param name="logEntry">The LogEntry to store in the database.</param>
/// <param name="db">An instance of the database class to use for storing the LogEntry</param>
/// <param name="transaction">The transaction that wraps around the execution calls for storing the LogEntry</param>
/// <returns>An integer for the LogEntry Id</returns>
private int ExecuteWriteLogStoredProcedure(LogEntry logEntry, Microsoft.Practices.EnterpriseLibrary.Data.Database db, DbTransaction transaction)
{
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Suppress))
{
DbCommand cmd = db.GetStoredProcCommand(writeLogStoredProcName);
db.AddInParameter(cmd, "eventID", DbType.Int32, logEntry.EventId);
db.AddInParameter(cmd, "priority", DbType.Int32, logEntry.Priority);
db.AddParameter(cmd, "severity", DbType.String, 32, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, logEntry.Severity.ToString());
db.AddParameter(cmd, "title", DbType.String, 256, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, logEntry.Title);
db.AddInParameter(cmd, "timestamp", DbType.DateTime, logEntry.TimeStamp);
db.AddParameter(cmd, "machineName", DbType.String, 32, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, logEntry.MachineName);
db.AddParameter(cmd, "AppDomainName", DbType.String, 512, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, logEntry.AppDomainName);
db.AddParameter(cmd, "ProcessID", DbType.String, 256, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, logEntry.ProcessId);
db.AddParameter(cmd, "ProcessName", DbType.String, 512, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, logEntry.ProcessName);
db.AddParameter(cmd, "ThreadName", DbType.String, 512, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, logEntry.ManagedThreadName);
db.AddParameter(cmd, "Win32ThreadId", DbType.String, 128, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, logEntry.Win32ThreadId);
db.AddParameter(cmd, "message", DbType.String, 1500, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, logEntry.Message);
if (Formatter != null)
db.AddInParameter(cmd, "formattedmessage", DbType.String, Formatter.Format(logEntry));
else
db.AddInParameter(cmd, "formattedmessage", DbType.String, logEntry.Message);
db.AddOutParameter(cmd, "LogId", DbType.Int32, 4);
db.ExecuteNonQuery(cmd, transaction);
int logId = Convert.ToInt32(cmd.Parameters[cmd.Parameters.Count – 1].Value, CultureInfo.InvariantCulture);
scope.Complete();
return logId;
}
}
/// <summary>
/// Executes the AddCategory stored procedure
/// </summary>
/// <param name="logEntry">The LogEntry to store in the database.</param>
/// <param name="logID">The unique identifer for the LogEntry as obtained from the WriteLog Stored procedure.</param>
/// <param name="db">An instance of the database class to use for storing the LogEntry</param>
/// <param name="transaction">The transaction that wraps around the execution calls for storing the LogEntry</param>
private void ExecuteAddCategoryStoredProcedure(LogEntry logEntry, int logID, Microsoft.Practices.EnterpriseLibrary.Data.Database db, DbTransaction transaction)
{
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Suppress))
{
foreach (string category in logEntry.Categories)
{
DbCommand cmd = db.GetStoredProcCommand(addCategoryStoredProcName);
db.AddInParameter(cmd, "categoryName", DbType.String, category);
db.AddInParameter(cmd, "logID", DbType.Int32, logID);
db.ExecuteNonQuery(cmd, transaction);
}
scope.Complete();
}
}
}
}
The last step is to replace preloaded listener types with our own as in below:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<section name="loggingConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Logging.Configuration.LoggingSettings, Microsoft.Practices.EnterpriseLibrary.Logging, Version=5.0.414.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="true" />
<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=5.0.414.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="true" />
</configSections>
<loggingConfiguration name="" tracingEnabled="true" defaultCategory="General">
<listeners>
<add name="Database Trace Listener"
type="Logging.FormattedDatabaseTraceListener, Logging, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"
istenerDataType="Logging.FormattedDatabaseTraceListenerData, Logging, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"
databaseInstanceName="Logging"
writeLogStoredProcName="WriteLog"
addCategoryStoredProcName="AddCategory" />
</listeners>
<formatters>
<add type="Microsoft.Practices.EnterpriseLibrary.Logging.Formatters.TextFormatter, Microsoft.Practices.EnterpriseLibrary.Logging, Version=5.0.414.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
template="Timestamp: {timestamp}{newline}
Message: {message}{newline}
Category: {category}{newline}
Priority: {priority}{newline}
EventId: {eventid}{newline}
Severity: {severity}{newline}
Title:{title}{newline}
Machine: {localMachine}{newline}
App Domain: {localAppDomain}{newline}
ProcessId: {localProcessId}{newline}
Process Name: {localProcessName}{newline}
Thread Name: {threadName}{newline}
Win32 ThreadId:{win32ThreadId}{newline}
Extended Properties: {dictionary({key} – {value}{newline})}"
name="Text Formatter" />
</formatters>
<categorySources>
<add switchValue="All" name="General">
<listeners>
<add name="Database Trace Listener" />
</listeners>
</add>
</categorySources>
<specialSources>
<allEvents switchValue="All" name="All Events" />
<notProcessed switchValue="All" name="Unprocessed Category" />
<errors switchValue="All" name="Logging Errors & Warnings">
<listeners>
<add name="Database Trace Listener" />
</listeners>
</errors>
</specialSources>
</loggingConfiguration>
<dataConfiguration defaultDatabase="Logging" />
<connectionStrings>
<add name="Logging" connectionString="Logging" providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
That is all there is to using Enterprise Library database logging within existing transaction scope. You can download the project here. You will need to download and install Enterprise Library to build it.
Really helpful!! Thanks alot
Hi, I’m new to Unity DI, but assuming the UnityContainer is used. Can you provide an example of how this code is utilized? Thanks much!
Hi ,I need your help
I’m new to this type of technology you could send me to my running the example code mauro8k@hotmail.com funcionanda thank you very much
Hey, Mauro.
All the code you need is in the post. The rest just follows standard logging and error handling techniques. You can look up those on MSDN enterprise library site. http://msdn.microsoft.com/en-us/library/ff648951.aspx
Thanks.