How to create a custom database trace listener and use it?

Topics: General discussion, Logging Application Block
Oct 25, 2009 at 9:11 PM
Edited Oct 26, 2009 at 2:23 AM

Hello All,

I've used EL 4.1 LAB before but now have a need to customize the data captured and where it goes.  So we essentially have the need to for LogEntry classes.  The first is the original EL 4.1 LogEntry and the new one is STPLogEntry that implements LogEntry.  I have a couple of additional string properties in there for this sample called FUserName and LUserName, that's it.  LogEntry must still go to the Logging DB and use the Write StoredProc which writes to the Log table.  STPLogEntry should also go to the Logging DB but use the WriteAction storedproc which will write to a table called Actions.   For the STPDatabaseTraceListener, I've included the code below.  In a nutshell, I'm implementing the CustomTraceListener while using the FormattedDatabaseTraceListener from the EL Src code as a reference.  I think I've got custom database trace listener configured correctly but I don't know what to do from here.  How do I set the app.config file up to use it?  How do I use it in my console app and get it configured to use the database instance and different stored proc?  The STPLogEntry and STPDatabaseTraceListener are in a seperate class library project.  I've added a reference to the dll file in the console app program.  Thanks for any help!

 

Console App

using System;
using System.Diagnostics;
using Microsoft.Practices.EnterpriseLibrary.Logging;

namespace CustomLoggingSample
{
    class Program
    {
        static void Main(string[] args)
        {
            Logger.Write("Test","General",0,0,TraceEventType.Error);
            Console.Write("Ready to end...");
            Console.ReadKey();
        }
    }
}

 

STPDatabaseTraceListener

using System;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Logging.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Logging.Formatters;
using Microsoft.Practices.EnterpriseLibrary.Logging.TraceListeners;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using System.Globalization;

namespace STPLogging
{
    [ConfigurationElementType(typeof(CustomTraceListenerData))]
    class STPDatabaseTraceListener : CustomTraceListener
    {

        string writeLogStoredProcName = String.Empty;
        string addCategoryStoredProcName = String.Empty;
        Database database;

        /// <summary>
        /// Initializes a new instance of <see cref="STPDatabaseTraceListener"/>.
        /// </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="formatter">The formatter.</param>  
        ///       
        public STPDatabaseTraceListener(Database database, string writeLogStoredProcName, ILogFormatter formatter)
        {
            this.writeLogStoredProcName = writeLogStoredProcName;
            this.database = database;
        }

        /// <summary>
        /// The Write method
        /// </summary>
        /// <param name="message">The message to log</param>
        public override void Write(string message)
        {
            ExecuteWriteLogStoredProcedure( string.Empty, string.Empty, database);
        }

        /// <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 STPLogEntry)
                {
                    STPLogEntry logEntry = data as STPLogEntry;
                    if (ValidateParameters(logEntry))
                        ExecuteStoredProcedure(logEntry);
                    // FireTraceListenerEntryWrittenEvent();
                }
                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="STPDatabaseTraceListener"/>
        /// </summary>
        protected override string[] GetSupportedAttributes()
        {
            return new string[3] { "formatter", "writeLogStoredProcName", "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(STPLogEntry 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(STPLogEntry logEntry)
        {
            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();
                }
            }
        }

        /// <summary>
        /// Executes the WriteLog stored procedure
        /// </summary>
        /// <param name="fUserName">The first name of the user</param>
        /// <param name="lUserName">The last name of the user</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(string fUserName,
                                                    string lUserName, Database db)
        {
            DbCommand cmd = db.GetStoredProcCommand(writeLogStoredProcName);
            db.AddParameter(cmd, "FUserName", DbType.String, 50, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, fUserName);
            db.AddParameter(cmd, "LUserName", DbType.String, 50, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, lUserName);

            db.AddOutParameter(cmd, "LogId", DbType.Int32, 4);

            db.ExecuteNonQuery(cmd);
            int logId = Convert.ToInt32(cmd.Parameters[cmd.Parameters.Count - 1].Value, CultureInfo.InvariantCulture);
            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(STPLogEntry logEntry, Database db, DbTransaction transaction)
        {
            DbCommand cmd = db.GetStoredProcCommand(writeLogStoredProcName);
            db.AddParameter(cmd, "FUserName", DbType.String, 50, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, logEntry.FUserName);
            db.AddParameter(cmd, "LUserName", DbType.String, 50, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, logEntry.LUserName);

            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);
            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(STPLogEntry logEntry, int logID, Database db, DbTransaction transaction)
        {
            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);
            }
        }

    }
}

 

Oct 26, 2009 at 1:52 AM

You need to replace your constructor to a constructor that takes the NameValueCollection parameter.  That parameter will be populated based on the list of strings you added in the Attributes collection property of the custom trace listener in your config. 

 public STPDatabaseTraceListener(NameValueCollection attributes)
{
            this.writeLogStoredProcName = attributes["writeSp"];
            this.connectionString = attributes["connectionString"];
}

This code assumes that you added two items in the Attributes property, one having the "writeSp" key where its value corresponds to the name of the stored procedure you want to use for writing log entries to the database and another item with "connectionString" key having a value that corresponds to one of the connection string name you defined in the Data Access Application block.

To set your custom tracelistner in your config, Right click on the Trace Listeners node and select Custom Trace Listener.  Go to its Properties window and click on the ellipsis button on the Type property.  You will be presented with a Type Selector window, browse to the location of your class library assembly where your custom trace listener is and select it. 

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Oct 26, 2009 at 3:01 AM

Sarah,

Thanks for responding.  I've made the changes you mentioned and also created the keys in the Attributes collection of the Custom Trace Listener.  However,  but I can't seem to select the STPDatabaseTraceListener as the type for the Custom Trace Listener in the config.  When I attempt to load the Type from the class library (dll), I get a "No Types Found in Assembly" dialog box that has a message stating "There were no types found in the assembly 'STPLoggin' that implement or inherit from the base type 'Microsoft.Practices.EnterpriseLibrary.Logging.TraceListeners.CustomTraceListener'.  Any ideas?  Thanks again for responding. 

 

Oct 26, 2009 at 3:06 AM

Is your STPDatabaseTraceListener class public?  The code you posted above indicates that it is not.  Have it public, build your project, and then try again. 

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Oct 26, 2009 at 3:21 AM

Good catch.  I modified the STPDatabaseTraceListener so that it's public but still encountered the same error.

[ConfigurationElementType(typeof(CustomTraceListenerData))]
    public class STPDatabaseTraceListener : CustomTraceListener
    {.......etc

My class library that contains the STPDatabaseTraceListener class and the custom STPLogEntry class are the only two classes in the project.  I have the appropriate references to the EL.Common, Data, Logging and Logging.Database libraries.  The class library builds successfully.  I've got it as  a reference within my sample console program.  Intellisense picks it up when I type the namespace (STPLogging.).  It shows my my STPDatabaseTraceListener and STPLogEntry.  Any ideas?  Do I have to move it somewhere for EL 4.1 to pick it up?

 

Oct 26, 2009 at 3:29 AM

Try closing Visual Studio and reopen it.

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Oct 26, 2009 at 10:46 AM

No affect.  I still get the same error.  Do I need to do anything with signing the class library or anything assembly attributes?

 

Oct 26, 2009 at 12:38 PM

Sarah,

I got it.  In my attempts to previously getting this to work, I had copied the dll into the \BIN directory of ENTLIB within the Program Files structure.  It looks like ENTLIB will look there first instead of loading from the 'Load File'.  The version in the \BIN location was an older incorrect copy.  When I removed it and did the 'Load from File', the STPDatabseTraceListener showed up and was selectable.  Now that I have that, I noticed in the code for STPDatabaseTraceListener that the connectionString is set via the attribute but is never assigned.  Where would I actually use this connectionString in the code?  Would I change my constructor to do:

public STPDatabaseTraceListener(NameValueCollection attributes) {
            this.writeLogStoredProcName = attributes["writeSp"];
            this.connectionString = attributes["connectionString"];
            this.database.CreateConnection().ConnectionString = connectionString;
        }

or should I alter another method within the tracelistener?

Thanks Sarah for hanging in there with this.

Oct 26, 2009 at 2:31 PM

Sarah,

I went ahead and modified my constructor as indicated above.  When I try to write out using the console app, I receive the following error:

The current build operation (build key Build Key[Microsoft.Practices.EnterpriseLibrary.Logging.LogWriter, null]) failed: The type 'STPLogging.STPDatabaseTraceListener' specified for custom trace listener named 'Custom Trace Listener' does not a default constructor, which is required when no InitData is specified in the configuration. (Strategy type ConfiguredObjectStrategy, index 2)

 

The quick code for the console app is:

STPLogEntry log = new STPLogEntry();
                log.Categories.Add("STPActions");
                log.FUserName = "Michael";
                log.LUserName = "Richards";
                Logger.Write(log);

 

 

Oct 27, 2009 at 11:03 AM

Sorry on that one, I forgot that it uses the parameterless constructor.  Please add one.  You can access the values in the Attributes collection property(this.Attributes) after the instantiation, thus anywhere except in the constructor. 

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Oct 28, 2009 at 3:39 AM

Sarah,

I'm not sure I understand the usage of the Attributes collection when using a parameterless constructor.  Where would I reference that call in my STPDatabaseTraceListener?  Using the above code, could you give me an example of where I could place and use?  Thanks!

Oct 28, 2009 at 4:12 AM

Would you like me to just send you the sample code?  If so, just email us (entlib.support@avanade.com)

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Oct 28, 2009 at 12:44 PM

Sarah,

I sent an email to the address you listed asking for the sample code.  Thank you very much for your help with this.

Michael

Oct 28, 2009 at 1:05 PM
Edited Oct 28, 2009 at 1:06 PM

I've already sent it.  Let me know if you still have questions.

Nov 9, 2009 at 2:42 AM

can you please sent that code to me also.........I am struggling to display CustomLogEntry property using CustomTraceFormatter . Finally I also need to store log in database.....Thank You