Log4net: use Sql Server to log your application events-摩杜云开发者社区

Log4net: use Sql Server to log your application events

by Emanuele 2/23/2008 2:26:00 PM

In the previous article on the Log4net configuration, I explain how to configure Log4net with the file appender.
In this article I explain how to configure Log4net with the Sql Server appender.
It is very similar, but we see in detail the new configuration.

Download
You can download the latest version of Log4net from this location.

Add reference to your solution
In Visual Studio 2005 select Project -> Add Reference.
In the tab Browse, find and select the dll Log4net.dll in your local folder.

Modify web.config
Into web.config file add this code into the section Configuration->Configsections:

<configSections> 

    <section name="log4net" 

      type="log4net.Config.Log4NetConfigurationSectionHandler, log4net" /> 

</configSections> <log4net> 

  <appender name="ADONetAppender" type="log4net.Appender.ADONetAppender"> 

    <bufferSize value="100" /> 

    <connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> 

    <connectionString value="server=localhost; uid=; pwd=; database=" /> 

    <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="32" /> 

      <layout type="log4net.Layout.PatternLayout"> 

        <conversionPattern value="%t" /> 

      </layout> 

    </parameter> 

    <parameter> 

      <parameterName value="@log_level" /> 

      <dbType value="String" /> 

      <size value="512" /> 

      <layout type="log4net.Layout.PatternLayout"> 

        <conversionPattern value="%p" /> 

      </layout> 

    </parameter> 

    <parameter> 

      <parameterName value="@logger" /> 

      <dbType value="String" /> 

      <size value="512" /> 

      <layout type="log4net.Layout.PatternLayout"> 

        <conversionPattern value="%c" /> 

      </layout> 

    </parameter> 

    <parameter> 

      <parameterName value="@message" /> 

      <dbType value="String" /> 

      <size value="4000" /> 

      <layout type="log4net.Layout.PatternLayout"> 

        <conversionPattern value="%m" /> 

      </layout> 

    </parameter> 

    <parameter> 

      <parameterName value="@exception" /> 

      <dbType value="String" /> 

      <size value="2000" /> 

      <layout type="log4net.Layout.ExceptionLayout" /> 

    </parameter> 

  </appender> 

  <root> 

    <level value="DEBUG" /> 

    <appender-ref ref="ADONetAppender" /> 

  </root> 

</log4net>

Change the connectionstring parameters to connect to your database.

Create the table

In your database, create the table to use Log4net.  
CREATE TABLE [dbo].[Log]( 
    [Id] [int] IDENTITY(1,1) NOT NULL, 
    [Date] [datetime] NULL, 
    [Thread] [varchar](255) NULL, 
    [Level] [varchar](50) NULL, 
    [Logger] [varchar](255) NULL, 
    [Message] [varchar](4000) NULL, 
    [Exception] [varchar](2000) NULL 
) ON [PRIMARY]

Edit the Global.asax file

In the event "Application_Start" of the file Global.asax add this line:

log4net.Config.XmlConfigurator.Configure();

【注，此步的替代方案：

你可以在应用程序集的assemblyInfo.cs文件中读取log4net配置
对于WINFORM应用程序,你可以加入
[assembly:log4net.Config.DOMConfigurator()]或 [assembly:log4net.Config.XmlConfigurator()]
对于WEBFORM你可以加入
[assembly:log4net.Config.DOMConfigurator(ConfigFile="web.config",Watch=true)]

注意:如果使用NUNIT测试的朋友,要用生成后事件,copy "$(ProjectDir)app.config" "$(TargetPath).config"

ref: Moving your Log4Net configuration out of web.config with ASP.NET 2.0 web sites. http://geekswithblogs.net/bsherwin/archive/2008/02/15/119657.aspx 】

Begin to log

In every page you want to log something, add the static variable like below:
private static readonly ILog log = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

This class has 5 levels of severity to log the operations:

log.Debug("log something at this level")
log.Info("log something at this level")
log.Warn("log something at this level");
log.Error("log something at this level");
log.Fatal("log something at this level");

You find the official documentation at this link.

from: http://blog.emanuelebartolesi.com/post/2008/02/Log4net-use-Sql-Server-to-log-your-application-events.aspx

==============================

Global.asax

------------------------

Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)

        ' Code that runs on application startup


        'log4net

        Dim fi As System.IO.FileInfo = New System.IO.FileInfo(Server.MapPath(".") + ""log4net.config")

        log4net.Config.XmlConfigurator.ConfigureAndWatch(fi)            End Sub

------------------------

log4net.config

------------------------ 
<?xml version="1.0"?>

<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">

  <log4net>

    <appender name="ADONetAppender" type="log4net.Appender.ADONetAppender">

      <connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />

      <connectionString value="server=xxxx;database=xxxxx;Integrated Security=True;Persist Security Info=False;" />

      <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="32" />

        <layout type="log4net.Layout.PatternLayout">

          <conversionPattern value="%t" />

        </layout>

      </parameter>

      <parameter>

        <parameterName value="@log_level" />

        <dbType value="String" />

        <size value="512" />

        <layout type="log4net.Layout.PatternLayout">

          <conversionPattern value="%p" />

        </layout>

      </parameter>

      <parameter>

        <parameterName value="@logger" />

        <dbType value="String" />

        <size value="512" />

        <layout type="log4net.Layout.PatternLayout">

          <conversionPattern value="%c" />

        </layout>

      </parameter>

      <parameter>

        <parameterName value="@message" />

        <dbType value="String" />

        <size value="4000" />

        <layout type="log4net.Layout.PatternLayout">

          <conversionPattern value="%m" />

        </layout>

      </parameter>

      <parameter>

        <parameterName value="@exception" />

        <dbType value="String" />

        <size value="2000" />

        <layout type="log4net.Layout.ExceptionLayout" />

      </parameter>

    </appender>

    <root>

      <level value="DEBUG" />

      <appender-ref ref="ADONetAppender" />

    </root>

  </log4net>

</configuration>

----------------------

Use log4net

----------------------

Partial Class xxxxxx

    Inherits System.Web.UI.Page


    Private Shared ReadOnly log As ILog = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType) 
   Protected Sub btnEMail_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles        btnEMail.Click        
        Try

            xxxxxx

        Catch ex As Exception            

            log.Error(ex.Message)

            If Not ex.InnerException Is Nothing Then

               log.Error(ex.InnerException.Message)

            End If

        End Try

    End Sub

End Class