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

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" type = "codeph" text = "/codeph"/>
<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 = "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 ();

Note:

You canProgramSet assemblyinfo. CS file to read log4net Configuration
For winform applications, you can add
[Assembly: log4net. config. domconfigurator ()] or [Assembly: log4net. config. xmlconfigurator ()]
For webform, you can add
[Assembly: log4net. config. domconfigurator (configfile = "Web. config", watch = true)]

Note: If you are using a nunit test friend, use the generated event, 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 = "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