Logging Information Into a Database¶

In order to adjust log4net to write to a particular database, as opposed to the flat .log file there are a few steps that you need to take.

Note

Depending on whether you are using the .NET Framework or .NET Core API resources, the configuration steps may vary. Please refer to the appropriate section below based on your setup.

Using .NET Framework API¶

Creating the Logging Database

On your database sever, create an empty database with a meaningful name to easily identify its contents (ex. Izenda Logs)

Within this table, create one table, entitled Log, with the query below:

CREATE TABLE [dbo].[Log] (
    [Id] [int] IDENTITY (1, 1) NOT NULL,
    [Date] [datetime] NOT NULL,
    [Thread] [varchar] (255) NOT NULL,
    [Level] [varchar] (50) NOT NULL,
    [Logger] [varchar] (255) NOT NULL,
    [Message] [varchar] (4000) NOT NULL,
    [Exception] [varchar] (2000) NULL
)

Open the web.config file in your API folder

in <configSections> replace the current <section name="log4net"> with the following:

<section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net, Version=2.0.8.0, Culture=neutral, PublicKeyToken=669e0ddf0bb1aa2a" />

Lower on the same web.config file, there is a element (line 14). Remove this and all of its contents (Lines 14 through 52)

Replace the removed section with the following, noting to add your connection string to the log database:

<log4net> <root> <level value="DEBUG" />
  <appender-ref ref="ADONetAppender" /> </root>
  <appender name="ADONetAppender" type="log4net.Appender.ADONetAppender"> <bufferSize value="1" /> <connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=2.0.8.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />

    <connectionString value="[YOUR CONNECTION STRING TO THE IZENDA LOGS DATABASE HERE]" />
    <commandText value="INSERT INTO Log ([Date],[Thread],[Level],[Logger],[Message],[Exception]) VALUES (@log_date, @thread, @log_level, @logger, @message, @exception)" />

    <parameter>
         <parameterName value="@log_date"/> <dbType value="DateTime"/> <layout type="log4net.Layout.RawTimeStampLayout"/>
    </parameter>
    <parameter>
         <parameterName value="@thread"/> <dbType value="String"/> <size value="255"/> <layout type="log4net.Layout.PatternLayout"> <conversionPattern value="%thread"/> </layout>
    </parameter>
    <parameter>
         <parameterName value="@log_level"/> <dbType value="String"/> <size value="50"/> <layout type="log4net.Layout.PatternLayout"> <conversionPattern value="%level"/> </layout>
    </parameter>
    <parameter>
         <parameterName value="@logger"/> <dbType value="String"/> <size value="255"/> <layout type="log4net.Layout.PatternLayout"> <conversionPattern value="%logger"/> </layout>
    </parameter>
    <parameter>
         <parameterName value="@message"/> <dbType value="String"/> <size value="4000"/> <layout type="log4net.Layout.PatternLayout"> <conversionPattern value="%message"/> </layout>
    </parameter>
    <parameter>
         <parameterName value="@exception"/> <dbType value="String"/> <size value="2000"/> <layout type="log4net.Layout.ExceptionLayout"/>
    </parameter>
  </appender>
</log4net>

Note

The bufferSize value is currently set to 1. You can adjust this value to change the frequency with which the logs get pushed into the database.

Using .NET Core API¶

Creating the Logging Database

On your database sever, create an empty database with a meaningful name to easily identify its contents (ex. Izenda Logs)

Within this table, create one table, entitled Log, with the query below:

CREATE TABLE [dbo].[Log] (
    [Id] [int] IDENTITY (1, 1) NOT NULL,
    [Date] [datetime] NOT NULL,
    [Thread] [varchar] (255) NOT NULL,
    [Level] [varchar] (50) NOT NULL,
    [Logger] [varchar] (255) NOT NULL,
    [Message] [varchar] (4000) NOT NULL,
    [Exception] [varchar] (2000) NULL
)

Open the log4net.config file in your API folder
- In <root> replace the current <appender-ref> elements with the ADONetAppender. Please refer the following as example:
```
<root>
     <appender-ref ref="ADONetAppender" />
</root>
```

Lower on the same log4net.config file, there are two appender elements. Remove this and all of its contents (Lines 8 through 38)

Replace the removed section with the following, noting the reference to appsetting.json for the connectionStringFile:

<appender name="ADONetAppender" type="MicroKnights.Logging.AdoNetAppender, MicroKnights.Log4NetAdoNetAppender">
     <bufferSize value="1" />
     <connectionType value="System.Data.SqlClient.SqlConnection,System.Data,Version=4.0.0.0,Culture=neutral,PublicKeyToken=b77a5c561934e089" />
     <connectionStringName value="log4net" />
     <connectionStringFile value="appsettings.json" />
     <commandText value="INSERT INTO Log ([Date],[Thread],[Level],[Logger],[Message],[Exception]) VALUES (@log_date, @thread, @log_level, @logger, @message, @exception)" />
     <parameter>
          <parameterName value="@log_date" />
          <dbType value="DateTime" />
          <layout type="log4net.Layout.RawTimeStampLayout" />
     </parameter>
     <parameter>
          <parameterName value="@thread" />
          <dbType value="String" />
          <size value="255" />
          <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%thread" />
          </layout>
     </parameter>
     <parameter>
          <parameterName value="@log_level" />
          <dbType value="String" />
          <size value="50" />
          <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%level" />
          </layout>
     </parameter>
     <parameter>
          <parameterName value="@logger" />
          <dbType value="String" />
          <size value="255" />
          <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%logger" />
          </layout>
     </parameter>
     <parameter>
          <parameterName value="@message" />
          <dbType value="String" />
          <size value="4000" />
          <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%message" />
          </layout>
     </parameter>
     <parameter>
          <parameterName value="@exception" />
          <dbType value="String" />
          <size value="2000" />
          <layout type="log4net.Layout.ExceptionLayout" />
     </parameter>
</appender>

Note

The bufferSize value is currently set to 1. You can adjust this value to change the frequency with which the logs get pushed into the database.

Open the appsettings.json file in your API folder

Add a “connectionStrings” parameter that points to your Log database similar to the below:

{
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "httpHandlers": {

  },
  "AllowedHosts": "*",
  "AppSettings": {
    "Settings": {
      "defaultlandingpage": "Views/DefaultLandingPage.html",
      "izendaapiprefix": "api",
      "izendapassphrase": "vqL7SF+9c9FIQEKUOhSZapacQgUQh",
      "responseserverpath": "rs",
      "izenda.mssql.trimcharacters": "",
      "izenda.mysql.trimcharacters": "",
      "izenda.oracle.trimcharacters": "",
      "izenda.postgre.trimcharacters": ";",
      "izenda.redshift.trimcharacters": "",
      "izenda.elasticsearch.trimcharacters": "",
      "izenda.mongo.trimcharacters": ""
    }
  },
  "connectionStrings": {
    "log4net": "[YOUR CONNECTION STRING TO THE IZENDA LOGS DATABASE HERE]"
  }
}

Download the MicroKnights.Log4NetAdoNetAppender.dll file from our Utilities page from the Upgrade tab in the Customer Portal.
- Add this assembly file to the root of your API directory.