How to configure Enterpise Library 6.0 logging to database?

Question

In my App.config I have a second things:

  <configSections>
    <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=6.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="true" />
    <section name="loggingConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Logging.Configuration.LoggingSettings, Microsoft.Practices.EnterpriseLibrary.Logging, Version=6.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="true" />
  </configSections>
<dataConfiguration defaultDatabase="Core" />
  <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=6.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
           listenerDataType="Microsoft.Practices.EnterpriseLibrary.Logging.Database.Configuration.FormattedDatabaseTraceListenerData, Microsoft.Practices.EnterpriseLibrary.Logging.Database, Version=6.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
           databaseInstanceName="Core" 
           writeLogStoredProcName="WriteLog"
           addCategoryStoredProcName="AddCategory" />
      <add name="Rolling Flat File Trace Listener" 
           type="Microsoft.Practices.EnterpriseLibrary.Logging.TraceListeners.RollingFlatFileTraceListener, Microsoft.Practices.EnterpriseLibrary.Logging, Version=6.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" 
           listenerDataType="Microsoft.Practices.EnterpriseLibrary.Logging.Configuration.RollingFlatFileTraceListenerData, Microsoft.Practices.EnterpriseLibrary.Logging, Version=6.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" 
           fileName="rolling.log" 
           formatter="Text Formatter" 
           rollInterval="Hour" 
           rollSizeKB="10000" 
           traceOutputOptions="DateTime, Callstack" />
    </listeners>
    <formatters>
      <add type="Microsoft.Practices.EnterpriseLibrary.Logging.Formatters.TextFormatter, Microsoft.Practices.EnterpriseLibrary.Logging, Version=6.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" 
           template="Application: {property(ApplicationName)}{newline}&#xA;Guid: {property(HandlingInstanceId)}{newline}&#xA;Timestamp: {timestamp}{newline}&#xA;Message: {message}{newline}&#xA;Exception type: {property(ExceptionType)}{newline}&#xA;Category: {category}{newline}&#xA;Severity: {severity}{newline}&#xA;Machine: {localMachine}{newline}&#xA;App Domain: {localAppDomain}{newline}&#xA;ProcessId: {localProcessId}{newline}&#xA;Process Name: {localProcessName}{newline}&#xA;Stack trace: {property(StackTrace)}{newline}&#xA;File: {property(FileName)}{newline}&#xA;Line: {property(LineNumber)}{newline}" 
           name="Text Formatter" />
    </formatters>
    <categorySources>
      <add switchValue="All" name="General">
        <listeners>
          <add name="Database Trace Listener" />
          <add name="Rolling Flat File 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" />
          <add name="Rolling Flat File Trace Listener" />
        </listeners>
      </errors>
    </specialSources>
  </loggingConfiguration>

I also added this part into initialisation:

DatabaseFactory.SetDatabaseProviderFactory(new DatabaseProviderFactory());
LogWriterFactory logWriterFactory = new LogWriterFactory();

And, Exception write:

private static void LogExceptionDetails(Exception ex)
    {
        LogEntry logEntry = new LogEntry
        {
            Message = ex.Message
        };
        Logger.Write(logEntry);
    }

The thing I cant get so far is, how I suppose to detect, in which table exception will be logged. I mean, to make Enterprise see Core, I need to add:

<connectionStrings>
   <add name="Core" connectionString="blablabla" />
</connectionStrings>

But this still doest explain how to select propher table and format it (the only way I know is creating procedure and putting it in writeLogStoredProcName= but I used this in 4.1, I never touched 6.0 before).

Also, the problem is that all my confirugations are stored in another database, and this is the reason why I want to get rid of connectionString from App.config).

So, to summarize, my question is:

Is there a way to set database, table and table columns outside the App.config (and procedure) for Enterprise Library Logging to database? Maybe it is done somewhere in DatabaseFactory?

P.S Local logging into rolling.log file works fine.

EDIT: Looks like I have no choise and I need to use procedure to write in needed table. Still trying to find a way to set connectionstring outside the App.config

Answer 1

As you found out the out of the box FormattedDatabaseTraceListener writes to the database using a stored procedure. You can configure the name of the stored procedure but a stored procedure contains the database specific logic. You can modify or create a new stored procedure to do whatever you want as long as it conforms to the stored procedure interfaces:

CREATE 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,
    @LogId int OUTPUT
)

CREATE PROCEDURE [dbo].[AddCategory]
    @CategoryName nvarchar(64),
    @LogID int

The other alternative is to create a custom trace listener to do exactly what you want.

In terms of placing the connectionStrings in an external configuration file you can use the built-in .NET configSource attribute:

<connectionStrings configSource="connections.config" />

Or the Enterprise Library redirectSections:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="enterpriseLibrary.ConfigurationSource" type="Microsoft.Practices.EnterpriseLibrary.Common.Configuration.ConfigurationSourceSection, Microsoft.Practices.EnterpriseLibrary.Common, Version=6.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="true" />
  </configSections>
  <enterpriseLibrary.ConfigurationSource selectedSource="System Configuration Source">
    <sources>
      <add name="System Configuration Source" type="Microsoft.Practices.EnterpriseLibrary.Common.Configuration.SystemConfigurationSource, Microsoft.Practices.EnterpriseLibrary.Common, Version=6.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
      <add name="File-based Configuration Source" type="Microsoft.Practices.EnterpriseLibrary.Common.Configuration.FileConfigurationSource, Microsoft.Practices.EnterpriseLibrary.Common, Version=6.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
        filePath="data.config" />
    </sources>
    <redirectSections>
      <add sourceName="File-based Configuration Source" name="dataConfiguration" />
      <add sourceName="File-based Configuration Source" name="connectionStrings" />
    </redirectSections>
  </enterpriseLibrary.ConfigurationSource>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>
</configuration>

If you want to be completely app.config "independent" then you can place all configuration in an external configuration file and then load that configuration programmatically using a FileConfigurationSource.

See my answer to EnterpriseLibrary Data Access Application Block redirect Section can't find default database for more details.

If you want to mix and match declarative XML logging configuration with runtime programmatic database configuration then you can do it using something similar to:

class Program
{
    static void Main(string[] args)
    {
        // Configure databases from some external source but ensure that 
        // database name matches XML config
        DatabaseFactory.SetDatabases(
            () => CreateDatabaseFromExternalSource(null),
            name => CreateDatabaseFromExternalSource(name));

        // Configure logging from XML config 
        LogWriterFactory factory = new LogWriterFactory();
        Logger.SetLogWriter(factory.Create());

        Logger.Write("Test", "General");
    }

    private static Database CreateDatabaseFromExternalSource(string name)
    {
        // SqlDatabase assumes SQL Server database
        return new SqlDatabase(GetConnectionString(name));
    }

    private static string GetConnectionString(string name)
    {
        // do whatever you need to get the database connection
        return @"Server=.\SQLEXPRESS;Database=myDataBase;User Id=myUsername;Password=myPassword;";
    }
}