Logging Application block using database

Topics: Logging Application Block
Nov 29, 2010 at 2:41 AM

Hi,

I need to use logging application block to insert the audit trail information to the database.

I am bit confusing whie going through the configuration section, can any one pleae explain what are the configurations needed

for my requiremnt and how can I use this block in my application.

Thanks in advance

Nov 30, 2010 at 12:37 AM

Enterprise Library comes with a script which creates the necessary database objects necessary for logging to database.  Make sure to run it first.  It's located in EntLib50Src\Blocks\Logging\Src\DatabaseTraceListener\Scripts folder.

Here are the steps to configure logging to the database:

1. Add a connection string in your config pointing to the database for logging.

2. Add the Logging Application Block section to your config.

3. Add the Formatted Database Trace Listener and configure the necessary properties.  You only need to set the value of the Database Instance Name property to the name of the connection string you created in step number 1 if you didn't changed the names of the stored procedures in the script you run for creating the related database objects.

4. Add a logging category and associate the database trace listener to it.

That's it, you can now add that category to any LogEntry you create so as to be able to log to the database.

 

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

Nov 30, 2010 at 3:49 AM

Hi I am getting an error while using the following config file and class file, please suggest me whtat's in the following code

config file

<?xml version="1.0"?>
<configuration>
  <configSections>
    <section name="loggingConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Logging.Configuration.LoggingSettings, Microsoft.Practices.EnterpriseLibrary.Logging, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
    <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
    <sectionGroup name="system.web.extensions" type="System.Web.Configuration.SystemWebExtensionsSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
      <sectionGroup name="scripting" type="System.Web.Configuration.ScriptingSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
        <section name="scriptResourceHandler" type="System.Web.Configuration.ScriptingScriptResourceHandlerSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication" />
        <sectionGroup name="webServices" type="System.Web.Configuration.ScriptingWebServicesSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
          <section name="jsonSerialization" type="System.Web.Configuration.ScriptingJsonSerializationSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="Everywhere" />
          <section name="profileService" type="System.Web.Configuration.ScriptingProfileServiceSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication" />
          <section name="authenticationService" type="System.Web.Configuration.ScriptingAuthenticationServiceSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication" />
          <section name="roleService" type="System.Web.Configuration.ScriptingRoleServiceSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication" />
        </sectionGroup>
      </sectionGroup>
    </sectionGroup>
  </configSections>
  <loggingConfiguration name="Logging Application Block" tracingEnabled="true"
    defaultCategory="General" logWarningsWhenNoCategoriesMatch="true">
    <listeners>
      <add databaseInstanceName="Connection String" writeLogStoredProcName="WriteLog"
        addCategoryStoredProcName="AddCategory" formatter="" listenerDataType="Microsoft.Practices.EnterpriseLibrary.Logging.Database.Configuration.FormattedDatabaseTraceListenerData, Microsoft.Practices.EnterpriseLibrary.Logging.Database, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
        traceOutputOptions="None" filter="All" type="Microsoft.Practices.EnterpriseLibrary.Logging.Database.FormattedDatabaseTraceListener, Microsoft.Practices.EnterpriseLibrary.Logging.Database, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
        name="CustomDBTraceListener" />
      <add source="Enterprise Library Logging" formatter="Text Formatter"
        log="Application" machineName="" listenerDataType="Microsoft.Practices.EnterpriseLibrary.Logging.Configuration.FormattedEventLogTraceListenerData, Microsoft.Practices.EnterpriseLibrary.Logging, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
        traceOutputOptions="None" filter="All" type="Microsoft.Practices.EnterpriseLibrary.Logging.TraceListeners.FormattedEventLogTraceListener, Microsoft.Practices.EnterpriseLibrary.Logging, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
        name="Formatted EventLog TraceListener" />
    </listeners>
    <formatters>
      <add template="Timestamp: {timestamp}&#xD;&#xA;Message: {message}&#xD;&#xA;Category: {category}&#xD;&#xA;Priority: {priority}&#xD;&#xA;EventId: {eventid}&#xD;&#xA;Severity: {severity}&#xD;&#xA;Title:{title}&#xD;&#xA;Machine: {machine}&#xD;&#xA;Application Domain: {appDomain}&#xD;&#xA;Process Id: {processId}&#xD;&#xA;Process Name: {processName}&#xD;&#xA;Win32 Thread Id: {win32ThreadId}&#xD;&#xA;Thread Name: {threadName}&#xD;&#xA;Extended Properties: {dictionary({key} - {value}&#xD;&#xA;)}"
        type="Microsoft.Practices.EnterpriseLibrary.Logging.Formatters.TextFormatter, Microsoft.Practices.EnterpriseLibrary.Logging, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
        name="Text Formatter" />
    </formatters>
    <categorySources>
      <add switchValue="All" name="General">
        <listeners>
          <add name="Formatted EventLog TraceListener" />
        </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="Formatted EventLog TraceListener" />
        </listeners>
      </errors>
    </specialSources>
  </loggingConfiguration>
  <connectionStrings>
    <add name="Connection String" connectionString="Data Source=local;Initial Catalog=Logging;Integrated Security=True"
      providerName="System.Data.SqlClient" />
  </connectionStrings>
  <appSettings />
  <system.web>
    <!--
            Set compilation debug="true" to insert debugging
            symbols into the compiled page. Because this
            affects performance, set this value to true only
            during development.

            Visual Basic options:
            Set strict="true" to disallow all data type conversions
            where data loss can occur.
            Set explicit="true" to force declaration of all variables.
        -->
    <compilation debug="true" strict="false" explicit="true">
      <assemblies>
        <add assembly="System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089" />
        <add assembly="System.Data.DataSetExtensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089" />
        <add assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
        <add assembly="System.Xml.Linq, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089" />
      </assemblies>
    </compilation>
    <pages>
      <namespaces>
        <clear />
        <add namespace="System" />
        <add namespace="System.Collections" />
        <add namespace="System.Collections.Generic" />
        <add namespace="System.Collections.Specialized" />
        <add namespace="System.Configuration" />
        <add namespace="System.Text" />
        <add namespace="System.Text.RegularExpressions" />
        <add namespace="System.Linq" />
        <add namespace="System.Xml.Linq" />
        <add namespace="System.Web" />
        <add namespace="System.Web.Caching" />
        <add namespace="System.Web.SessionState" />
        <add namespace="System.Web.Security" />
        <add namespace="System.Web.Profile" />
        <add namespace="System.Web.UI" />
        <add namespace="System.Web.UI.WebControls" />
        <add namespace="System.Web.UI.WebControls.WebParts" />
        <add namespace="System.Web.UI.HtmlControls" />
      </namespaces>
      <controls>
        <add tagPrefix="asp" namespace="System.Web.UI" assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
        <add tagPrefix="asp" namespace="System.Web.UI.WebControls" assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
      </controls>
    </pages>
    <!--
            The <authentication> section enables configuration
            of the security authentication mode used by
            ASP.NET to identify an incoming user.
        -->
    <authentication mode="Windows" />
    <!--
            The <customErrors> section enables configuration
            of what to do if/when an unhandled error occurs
            during the execution of a request. Specifically,
            it enables developers to configure html error pages
            to be displayed in place of a error stack trace.

        <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
            <error statusCode="403" redirect="NoAccess.htm" />
            <error statusCode="404" redirect="FileNotFound.htm" />
        </customErrors>
        -->
    <httpHandlers>
      <remove verb="*" path="*.asmx" />
      <add verb="*" path="*.asmx" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
      <add verb="*" path="*_AppService.axd" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
      <add verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" validate="false" />
    </httpHandlers>
    <httpModules>
      <add name="ScriptModule" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
    </httpModules>
  </system.web>
  <system.codedom>
    <compilers>
      <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb" warningLevel="4" type="Microsoft.VisualBasic.VBCodeProvider, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
        <providerOption name="CompilerVersion" value="v3.5" />
        <providerOption name="OptionInfer" value="true" />
        <providerOption name="WarnAsError" value="false" />
      </compiler>
    </compilers>
  </system.codedom>
  <!--
        The system.webServer section is required for running ASP.NET AJAX under Internet
        Information Services 7.0.  It is not necessary for previous version of IIS.
    -->
  <system.webServer>
    <validation validateIntegratedModeConfiguration="false" />
    <modules>
      <remove name="ScriptModule" />
      <add name="ScriptModule" preCondition="managedHandler" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
    </modules>
    <handlers>
      <remove name="WebServiceHandlerFactory-Integrated" />
      <remove name="ScriptHandlerFactory" />
      <remove name="ScriptHandlerFactoryAppServices" />
      <remove name="ScriptResource" />
      <add name="ScriptHandlerFactory" verb="*" path="*.asmx" preCondition="integratedMode" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
      <add name="ScriptHandlerFactoryAppServices" verb="*" path="*_AppService.axd" preCondition="integratedMode" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
      <add name="ScriptResource" preCondition="integratedMode" verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
    </handlers>
  </system.webServer>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Extensions" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="3.5.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Extensions.Design" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="3.5.0.0" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
</configuration>

class file

Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports Microsoft.Practices.EnterpriseLibrary.Logging
Imports Microsoft.Practices.EnterpriseLibrary.Logging.LogEntry
Imports Microsoft.Practices.EnterpriseLibrary.Data.ConnectionString
Imports Microsoft.Practices.EnterpriseLibrary.Data.DatabaseFactory
Imports Microsoft.Practices.EnterpriseLibrary.Logging.LoggingException
Imports Microsoft.Practices.EnterpriseLibrary.Logging.TraceListeners
Imports Microsoft.Practices.EnterpriseLibrary.Logging.Configuration
Imports System.Data.Common

<ConfigurationCollectionAttribute(GetType(CustomTraceListenerData))> _
Public Class CustomDBTraceListener
    Inherits CustomTraceListener

    Public Overrides Sub TraceData(ByVal eventCache As TraceEventCache, ByVal source As String, ByVal eventType As TraceEventType, ByVal id As Integer, ByVal data As Object)
        If TypeOf data Is LogEntry AndAlso data IsNot Nothing Then
         

      Dim db As Database = DatabaseFactory.CreateDatabase(Attributes("CustomDBTraceListener")) //error part
            Dim cmd As DbCommand = db.GetStoredProcCommand(Attributes("WriteLog"))//error part

            Dim le As LogEntry = DirectCast(data, LogEntry)
            db.AddParameter(cmd, "severity", DbType.[String], 32, ParameterDirection.Input, False, _
             0, 0, Nothing, DataRowVersion.[Default], le.Severity.ToString())
            db.AddInParameter(cmd, "timestamp", DbType.DateTime, le.TimeStamp)
            db.AddParameter(cmd, "message", DbType.[String], 1500, ParameterDirection.Input, False, _
             0, 0, Nothing, DataRowVersion.[Default], le.Message)
            db.AddInParameter(cmd, "ExtendedProperty", DbType.[String], System.Security.Principal.WindowsIdentity.GetCurrent().Name)
            db.AddOutParameter(cmd, "LogId", DbType.Int32, 4)
            db.ExecuteNonQuery(cmd)
        End If
    End Sub

    Public Overrides Sub Write(ByVal message As String)
        Throw New NotImplementedException()
    End Sub

    Public Overrides Sub WriteLine(ByVal message As String)
        Throw New NotImplementedException()
    End Sub
End Class

calling in application

Public Sub Logger(ByVal le As LogEntry)
        Dim trace As New CustomDBTraceListener
        Trace.TraceData(New TraceEventCache(), le.Message, New TraceEventType(), 7, le)
    End Sub 

Nov 30, 2010 at 5:33 AM
The string that you should use to access an attribute should correspond to the key you entered in the config.  Thus, the first one should be Attributes("databaseInstanceName") while the second one should be Attributes("writeLogStoredProcName")

<add databaseInstanceName="Connection String" writeLogStoredProcName="WriteLog"
        addCategoryStoredProcName="AddCategory" formatter="" listenerDataType="Microsoft.Practices.EnterpriseLibrary.Logging.Database.Configuration.FormattedDatabaseTraceListenerData, Microsoft.Practices.EnterpriseLibrary.Logging.Database, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
        traceOutputOptions="None" filter="All" type="Microsoft.Practices.EnterpriseLibrary.Logging.Database.FormattedDatabaseTraceListener, Microsoft.Practices.EnterpriseLibrary.Logging.Database, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
        name="CustomDBTraceListener" />

I also noticed something here.  Are you sure this is the correct config and it triggers your custom database trace listener?  Because the specified listenerDataType and type attribute still points to EntLib's out of the box formatted database trace listener rather than to your custom database trace listener.   What you put in the name attribute doesn't dictate which trace listener to use, it's just for the identity of each trace listener.

Assuming your CustomDBTraceListener is defined in the assembly named CustomProvider, the entry in the config should be something like:

<add databaseInstance="Connection String" writeLogStoredProcName="WriteLog"
        addCategoryStoredProcName="AddCategory" listenerDataType="Microsoft.Practices.EnterpriseLibrary.Logging.Configuration.CustomTraceListenerData, Microsoft.Practices.EnterpriseLibrary.Logging, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
        traceOutputOptions="None" filter="All" type="CustomProvider.MyDatabaseTraceListener, CustomProvider, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"
        name="CustomDBTraceListener" formatter="Text Formatter" />

 

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

Nov 30, 2010 at 7:09 AM

Hi

for testing purpose I am using the logger db itself.

I am getting the argument exception error saying that   The value can not be null or string or empty. Parameter name: id

what might be the cause for it.

config code is

<add databaseInstanceName="ConnectionString" writeLogStoredProcName="WriteLog"
        addCategoryStoredProcName="AddCategory" formatter="Text Formatter"
        listenerDataType="Microsoft.Practices.EnterpriseLibrary.Logging.Database.Configuration.FormattedDatabaseTraceListenerData, Microsoft.Practices.EnterpriseLibrary.Logging.Database, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
        traceOutputOptions="LogicalOperationStack" filter="All" type="Microsoft.Practices.EnterpriseLibrary.Logging.Database.FormattedDatabaseTraceListener, Microsoft.Practices.EnterpriseLibrary.Logging.Database, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
        name="CustomDBTraceListener" /> 

<connectionStrings>
 <add name="ConnectionString" connectionString="Data Source=WPMYD0000627;Initial Catalog=Logging;User ID=sa;Password=sa"
      providerName="System.Data.SqlClient" />
  </connectionStrings>

Nov 30, 2010 at 7:26 AM

What line of code produces this error?

 

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

Nov 30, 2010 at 7:31 AM

Hi,

error line is highlighted in red color

Public Overrides Sub TraceData(ByVal eventCache As TraceEventCache, ByVal source As String, ByVal eventType As TraceEventType, ByVal id As Integer, ByVal data As Object)
        If TypeOf data Is LogEntry AndAlso data IsNot Nothing Then
          Dim db As Database = DatabaseFactory.CreateDatabase(Me.Attributes("ConnectionString"))
Dim cmd As DbCommand = db.GetStoredProcCommand(Me.Attributes("WriteLog"))

            Dim le As LogEntry = DirectCast(data, LogEntry)
            db.AddParameter(cmd, "severity", DbType.[String], 32, ParameterDirection.Input, False, _
             0, 0, Nothing, DataRowVersion.[Default], le.Severity.ToString())
            db.AddInParameter(cmd, "timestamp", DbType.DateTime, le.TimeStamp)
            db.AddParameter(cmd, "message", DbType.[String], 1500, ParameterDirection.Input, False, _
             0, 0, Nothing, DataRowVersion.[Default], le.Message)
            db.AddInParameter(cmd, "ExtendedProperty", DbType.[String], System.Security.Principal.WindowsIdentity.GetCurrent().Name)
            db.AddOutParameter(cmd, "LogId", DbType.Int32, 4)
            db.ExecuteNonQuery(cmd)
        End If
    End Sub

    Public Overrides Sub Write(ByVal message As String)
        Throw New NotImplementedException()
    End Sub

    Public Overrides Sub WriteLine(ByVal message As String)
        Throw New NotImplementedException()
    End Sub
End Class

Nov 30, 2010 at 7:52 AM

As I've said before, it should be Attributes("databaseInstanceName") instead of Attributes("ConnectionString") and

Attributes("writeLogStoredProcName") instead of Attributes("WriteLog")

Dim db As Database = DatabaseFactory.CreateDatabase(Me.Attributes("databaseInstanceName"))

The index you should use should correspond to the attribute name, not the value.  See the highlighted attribute below:

<add databaseInstanceName="ConnectionString" writeLogStoredProcName="WriteLog"
        addCategoryStoredProcName="AddCategory" formatter="Text Formatter"
        listenerDataType="Microsoft.Practices.EnterpriseLibrary.Logging.Database.Configuration.FormattedDatabaseTraceListenerData, Microsoft.Practices.EnterpriseLibrary.Logging.Database, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
        traceOutputOptions="LogicalOperationStack" filter="All" type="Microsoft.Practices.EnterpriseLibrary.Logging.Database.FormattedDatabaseTraceListener, Microsoft.Practices.EnterpriseLibrary.Logging.Database, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
        name="CustomDBTraceListener" /> 

 

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

Nov 30, 2010 at 8:12 AM

Hi, getting the same error even after I changed

Nov 30, 2010 at 8:17 AM

Did you debug through the TraceData method and verified that it is throwing the same exception in the same line?  Are you referencing the assembly for the custom database trace listener as a project reference?  If you're sure you're using the correct assembly, please send a small repro of the project so I could take a look.

 

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

Dec 1, 2010 at 2:23 AM

Hi Sarah,

I managed to execute the code with out any errors with custom db listener.

Now I have one more problem that is.

1.I wrote theentire code in a class library

2.I need to use the class library dll in other application like winforms and web forms.

3.I added the dll as reference and when trying to execute, it showing the error as config settings not found in the assembly,

how can I resolve this issue. Please suggest

Thanks in advance

Dec 1, 2010 at 2:38 AM

I think you have the same issue as with the topic in this thread.   Please refer to that discussion for the explanation and solution.

 

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