How to store log in database using log4net

One of the best ways to store log in the database using log4net coz it is easy to use and its world wide popularity. For storing log in the database all task are manage by log4net internally.

At first you need to create a table for Storing log in the SqlServer database

The table should be like as

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

)

1.    Download log4net from http://logging.apache.org/log4net/download.html

2.    Open visual studio and create an application.

3.    Add to the project a reference to the \bin\net\2.0\release\log4net.dll assembly in the log4net distribution.

4.    Now put this web.config/app.config file in configuration tag.

<configSections> <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler,Log4net"/> </configSections> <log4net> <root> <level value="DEBUG" /> <appender-ref ref="ADONetAppender" /> </root> <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=servername; uid=Lion; pwd=Lionman; database=databasename" /> <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> 

      In the connection string tag you need to change server name and database. You also can decide how to define the security part of the connection string.

There are two way to define the security part

·         Use integrated Security

·         State the username and the password in the connection string.

In both cases you need to make sure that the user has access to the, SQL server, the database and the databasetable that Log4Net is going to use.

If you use integrated security then the connection string should be like as

<connectionString value=”Data Source=servername;initial Catalog=databasename; Integrated Security=True;”/> 

 5.    To use log4net put this as a local class variable:    private static readonly log4net.ILog log =log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

6.    And do this to write messages in the log file.   log.Debug(”this text will be in log file”); 

For Example,

 
using System; 
using System.Collections.Generic; 
using System.Text; using log4net; 
using log4net.Config; using log4net.Core; 
using log4net.Repository.Hierarchy; 
using log4net.Appender;
 
namespace LogPractice
{   
    class Program 
    { 
        private static readonly log4net.ILog log =log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        static void Main(string[] args) 
        {
            log4net.Config.XmlConfigurator.Configure(); 
            log.Debug("log Debug"); 
            log.Info("log Info"); 
            log.Warn("log Warn"); 
            log.Error("log Error"); 
            log.Fatal("log Fatal"); 
        } 
    } 
}

Now run the code then you can see the log stored in the database table.

 

 

 
 
 
 
 
 
 
 

 

 

About these ads

About Md Shaik Sadi

I am Md. Shaik Sadi, got my B.Sc.(Engg) degree in Computer Science and Engineering and started my carrer as a trainee developer. I have worked on different phases of software development life cycle. Recently, I am working on asp.net/2 and want to stablish my career on this platfrom.
This entry was posted in Computer Science and tagged , , , , . Bookmark the permalink.

31 Responses to How to store log in database using log4net

  1. Georg says:

    Just want to mention that Log4Net has the possibility to reload configuration dynamically when the config is changed, typical used if you change the log level when the application is running.

    To activate this you need to use the XmlConfigurator.ConfigureAndWatch instead of XmlConfigurator.Configure()

    The ConfigureAndWatch can be invoked using several methods my personal favorite is using an assembly-level attribute:
    [assembly: log4net.Config.XmlConfigurator(Watch=true)]

    Best regards
    Georg Jansen
    http://www.l4ndash.com – Log analysis and monitoring made easy

  2. Md. Shaik Sadi says:

    thank for the comments …
    it enriched my blog …

    /author

  3. satya says:

    Hi friend,

    I used Log4net as above said.It is very good.
    But i faced one problem is data which we entered in to database is not commited immediatly.after closing app/dadaserver it is coming.
    plase help on this waiting for your reply.

    Thanks regards
    satya

  4. Roger says:

    Hello,

    I’m facing with the same problem.. data which we entered in to database is not commited immediatly.after closing app/dadaserver it is coming.
    Why happens this? How can data be commited without shutting down the server? I’m using C#, not VB.

    Thanks

  5. Mac says:

    Great article. How do i get the identity of the log i have just inserted.

    For eg. i want to display the id to user so that they can contact support team in case of exception.

    So once i call Log.Fatal it should return me the id. Can i override Fatal method?

  6. Rafael says:

    Hi, I`m trying to store my logs in my database(SQL Server 2005), I tried to use this code but nothing happened, Do I need to do anything else??
    Maybe write another code in another class…
    thanks..

  7. baushaug says:

    Did any of you guys run into the error of “Unrecognized configuration section log4net” when adding the log4net section to the web.config ?

  8. sini jose says:

    Hi,

    I am using sqlserver2008 and i hav done all the steps mentioned above but the log is not updating in the database i am not getting any error msg

  9. Mel Blanc says:

    what a shitty information blog. If you dont know how something works entirely, dont make a blog about it.

  10. I am getting the same error,

    mcupryk@shaw.ca

    I am using sqlserver2008 and i hav done all the steps mentioned above but the log is not updating in the database i am not getting any error msg

  11. Bora says:

    How can we separete log types?For example; Warning logs will put warninngLogTbl or error logs will put errorLogTbl.

  12. Bora says:

    Also I have another question. I have a project. There are three types of users.Users,Administrator etc. I want to separete their own log. I created UserLogTable and AdminLog also I want to also create an order table which is keep orderLog. How can I do this?

  13. Rishab says:

    i was searching for this only… thanks sharing .. its helped me a lot …:)

  14. Rakesh Reddy K says:

    Hi,
    I am using sqlserver2008 and i have done all the steps mentioned above but the log is not updating in the database i am not getting any error msg.
    Please help me out.
    rakesh.karra@bankofamerica.com
    Thanks.

  15. Izaac Asimov says:

    for all of you who don’t get update .
    got some suggestion from Simon :
    Set the buffersize value =1 to reflect the value in DB for each record

    • oliver says:

      Hi, i set buffersize value r2 =1 but no luck i configure exactly that is explained before any ideas? using sql server

  16. surensaluka says:

    ref=”ADONetAppender” should change to ref=”AdoNetAppender”

  17. ROWEN says:

    To get this working in the App.Config you need to correct a small error which is to correct the case as shown below. Note that Log4net has an upper case L after Log4NetConfigurationSectionHandler,Log4net when it appears that it should be a lower case l.
    INCORRECT (Note upper case L after the comma, change this to a lower case) :

    CORRECT (lower case l after the comma, this work for me.):

  18. Sunil says:

    Can we save logs in database as well as in file simultaneously.
    If yes,how?

  19. vijay says:

    Thanks for this concept………,,,,,,,,,,

  20. Vu Quang Cuong says:

    You need run this line of code: log4net.Config.XmlConfigurator.Configure(); in Application_Start of your Global.asax when using Ado.net with Asp.net. It’s true in my case, I have waste long time because log4net doesn’t running until I add this code.

  21. vin says:

    It is not logging to database. I am not seeing errors during execution .

  22. 1. Change all relevant references in web.config to [case sensitive] “AdoNetAppender” and 2. set if you want immediate update to database while testing this. Thanks for this blog.

  23. 1. Change all relevant references in web.config to [case sensitive] “AdoNetAppender” and 2. set bufferSize value=”1″ if you want immediate update to database while testing this. Thanks for this blog.

  24. Hey guys,
    For thos of you having a hard time trying to make this work, in my case, I found my solution issuing this within VS.NET immediate window:

    log4net.LogManager.GetLogger(this.GetType()).Error(“test”);

    It thrown the Sql exception below. I fixed the null issue and it worked like a charm !

    log4net:ERROR [AdoNetAppender] Exception while writing to database
    System.Data.SqlClient.SqlException (0×80131904): Cannot insert the value NULL into column ‘event_type_id’, table ‘reduxssp_test.dbo.eventlog’; column does not allow nulls. INSERT fails.
    The statement has been terminated.

    hth!

    Sylvain Audet
    MyDevPartner.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s