EntLib5 Logging to Database

Topics: Logging Application Block
Jan 20, 2014 at 1:04 PM
Edited Jan 20, 2014 at 1:06 PM
Hi, I am trying to log to database using EntLib5.

I configured the configuration to the following:
<configuration>
  <configSections>
    <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=5.0.505.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="true" />
    <section name="loggingConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Logging.Configuration.LoggingSettings, Microsoft.Practices.EnterpriseLibrary.Logging, Version=5.0.505.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="true" />
  </configSections>
  <dataConfiguration defaultDatabase="Connection String" />
  <connectionStrings>
    <add name="Connection String" connectionString="Data Source=SQLServerName;Initial Catalog=Logging;Integrated Security=True;"
        providerName="System.Data.SqlClient" />
  </connectionStrings>
  <loggingConfiguration name="" tracingEnabled="true" defaultCategory="General">
    <listeners>
      <add name="Database Trace Listener" type="Microsoft.Practices.EnterpriseLibrary.Logging.Database.FormattedDatabaseTraceListener, Microsoft.Practices.EnterpriseLibrary.Logging.Database, Version=5.0.505.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
          listenerDataType="Microsoft.Practices.EnterpriseLibrary.Logging.Database.Configuration.FormattedDatabaseTraceListenerData, Microsoft.Practices.EnterpriseLibrary.Logging.Database, Version=5.0.505.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
          databaseInstanceName="Connection String" writeLogStoredProcName="WriteLog"
          addCategoryStoredProcName="AddCategory" formatter="Text Formatter" />
    </listeners>
    <formatters>
      <add type="Microsoft.Practices.EnterpriseLibrary.Logging.Formatters.TextFormatter, Microsoft.Practices.EnterpriseLibrary.Logging, Version=5.0.505.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
          template="Timestamp: {timestamp}{newline}&#xA;Message: {message}{newline}&#xA;Category: {category}{newline}&#xA;Priority: {priority}{newline}&#xA;EventId: {eventid}{newline}&#xA;Severity: {severity}{newline}&#xA;Title:{title}{newline}&#xA;Machine: {localMachine}{newline}&#xA;App Domain: {localAppDomain}{newline}&#xA;ProcessId: {localProcessId}{newline}&#xA;Process Name: {localProcessName}{newline}&#xA;Thread Name: {threadName}{newline}&#xA;Win32 ThreadId:{win32ThreadId}{newline}&#xA;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 &amp; Warnings">
        <listeners>
          <add name="Database Trace Listener" />
        </listeners>
      </errors>
    </specialSources>
  </loggingConfiguration>
</configuration>
But when I try to log as follows:
            LogEntry logEntry = new LogEntry { Message = "Error Message", Severity = TraceEventType.Error , };
            logEntry.ExtendedProperties = new Dictionary<string, object>()
            {
                {"PrintedOn", DateTime.Now.AddDays(-1).ToString()},
                {"RequestedOn", DateTime.Now.AddDays(-2).ToString()},
                {"Status_ErrorMessage", "None"},
                {"Status_Success", "True"},
                {"TemplateName", "template name"},
                {"Printer_ID", "10.10.21.98"},
            };
            Logger.Write(logEntry);
And the WriteLog stored proc is as follows:
USE [Logging]
GO
/****** Object:  StoredProcedure [dbo].[WriteLog]    Script Date: 20/01/2014 13:03:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[WriteLog]') AND type in (N'P', N'PC'))
--BEGIN
ALTER PROCEDURE [dbo].[WriteLog]
(
    @EventID int, 
    @Priority int, 
    @Severity nvarchar(32), 
    @Title nvarchar(256), 
    @Timestamp datetime,
    @MachineName nvarchar(32), 
    @AppDomainName nvarchar(512),
    @ProcessID nvarchar(256),
    @ProcessName nvarchar(512),
    @ThreadName nvarchar(512),
    @Win32ThreadId nvarchar(128),
    @Message nvarchar(1500),
    @FormattedMessage ntext,
    @extendedProperties nvarchar(max),  
    @LogId int OUTPUT
)
AS 
BEGIN
    INSERT INTO [Log] (
        EventID,
        [Priority],
        Severity,
        Title,
        [Timestamp],
        MachineName,
        AppDomainName,
        ProcessID,
        ProcessName,
        ThreadName,
        Win32ThreadId,
        [Message],
        FormattedMessage
    )
    VALUES (
        @EventID, 
        @Priority, 
        @Severity, 
        @Title, 
        @Timestamp,
        @MachineName, 
        @AppDomainName,
        @ProcessID,
        @ProcessName,
        @ThreadName,
        @Win32ThreadId,
        @Message,
        @FormattedMessage)

    SET @LogID = @@IDENTITY

    if @extendedProperties is not null and len(@extendedProperties) > 0 
    BEGIN
        declare @x XML
        set @x = convert(XML, @extendedProperties)
        INSERT INTO ExtendedProperties([Key], [Value], [LogID])
        SELECT 
            replace(replace(replace(replace(replace(cast (T.c.query('./Key/text()')   as nvarchar(max)),'&quot;' , '"'), '&lt;', '<'), '&gt;', '>'), '&amp;', '&'), '&apos;', '''') as [Key],
            replace(replace(replace(replace(replace(cast (T.c.query('./Value/text()') as nvarchar(max)),'&quot;' , '"'), '&lt;', '<'), '&gt;', '>'), '&amp;', '&'), '&apos;', '''') as [Value],
            @LogId
        FROM   
            @x.nodes('/ExtendedProperties/ExtendedProperty') T(c) 
    END
    
    RETURN @LogID

END
But I get the following error in SQL Profiler:

Msg 201, Level 16, State 4, Procedure WriteLog, Line 0
Procedure or function 'WriteLog' expects parameter '@extendedProperties', which was not supplied.
Jan 20, 2014 at 1:21 PM
It looks like you are using a custom WriteLog stored procedure (from one of the sample projects) with the default FormattedDatabaseTraceListener. Either revert to using the original WriteLog stored procedure or modify the stored procedure to give the @extendedProperties parameter a default value (which should avoid the error):
ALTER PROCEDURE [dbo].[WriteLog]
(
    @EventID int, 
    @Priority int, 
    @Severity nvarchar(32), 
    @Title nvarchar(256), 
    @Timestamp datetime,
    @MachineName nvarchar(32), 
    @AppDomainName nvarchar(512),
    @ProcessID nvarchar(256),
    @ProcessName nvarchar(512),
    @ThreadName nvarchar(512),
    @Win32ThreadId nvarchar(128),
    @Message nvarchar(1500),
    @FormattedMessage ntext,
    @extendedProperties nvarchar(max) = NULL,  
    @LogId int OUTPUT
)
AS
... 
~~
Randy Levy
entlib.support@live.com
Enterprise Library support engineer
Support How-to