[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Cannot write to database sql server using log4net.

Posted on 2010-03-30
13
Medium Priority
?
2,723 Views
Last Modified: 2013-12-17
http://weblogs.asp.net/drnetjes/archive/2005/02/16/374780.aspx
I created a file called log4net.config.
--------------------------------------------
<?xml version="1.0"?>
<log4net>

  <appender name="ADONetAppender" type="log4net.Appender.ADONetAppender">
    <bufferSize value="1" />
    <connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
    <connectionString value="Data Source=72.18.146.251,1533;Initial Catalog=omegalove;Persist Security Info=True;User ID=administrator;Password=...." providerName="System.Data.SqlClient" />
    <commandText value="INSERT INTO Log ([Date],[Level],[Logger],[User],[Message],[Exception]) VALUES (@log_date, @log_level, @logger, @user, @message, @exception)" />
    <parameter>
      <parameterName value="@log_date" />
      <dbType value="DateTime" />
      <layout type="log4net.Layout.RawTimeStampLayout" />
    </parameter>
    <parameter>
      <parameterName value="@log_level" />
      <dbType value="String" />
      <size value="50" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%p" />
      </layout>
    </parameter>
    <parameter>
      <parameterName value="@logger" />
      <dbType value="String" />
      <size value="255" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%c" />
      </layout>
    </parameter>
    <parameter>
      <parameterName value="@user" />
      <dbType value="String" />
      <size value="50" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%X{user}" />
      </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="ALL" />
    <appender-ref ref="ADONetAppender" />
  </root>
</log4net>

1 st question is how to I reference this, I do not have an app_code directory.
2 nd is how can I test this.
I am confused by the article. Where the hell does this go?
public void LogError (string message, Exception e){  //get logger  ILog logger = LogManager.GetLogger("MyProject");    //set user to log4net context, so we can use %X{user} in the appenders  if (HttpContext.Current.User != null && HttpContext.Current.User.Identity.IsAuthenticated)    MDC.Set("user", HttpContext.Current.User.Identity.Name);   if (logger.IsErrorEnabled)    logger.Error(message, e); //now log error}


<configSections>
    <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler,log4net" />
    <section name="OmegaLove" type="OmegaLove.OmegaLoveSection, __code" />

    <sectionGroup name="system.web.extensions" type="System.Web.Configuration.SystemWebExtensionsSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
      <sectionGroup name="scripting" type="System.Web.Configuration.ScriptingSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
        <section name="scriptResourceHandler" type="System.Web.Configuration.ScriptingScriptResourceHandlerSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication" />
        <sectionGroup name="webServices" type="System.Web.Configuration.ScriptingWebServicesSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
          <section name="jsonSerialization" type="System.Web.Configuration.ScriptingJsonSerializationSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="Everywhere" />
          <section name="profileService" type="System.Web.Configuration.ScriptingProfileServiceSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication" />
          <section name="authenticationService" type="System.Web.Configuration.ScriptingAuthenticationServiceSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication" />
          <section name="roleService" type="System.Web.Configuration.ScriptingRoleServiceSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication" />
        </sectionGroup>
      </sectionGroup>
    </sectionGroup>
  </configSections>

---------------------------------------
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Log]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
 BEGIN 
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, [User] [varchar] (50) NULL, [Message] [varchar] (4000) NOT NULL, [Exception] [varchar] (2000) NULL)

Open in new window

0
Comment
Question by:omegalove
  • 8
  • 5
13 Comments
 
LVL 3

Expert Comment

by:Mike_Mozhaev
ID: 29169714
Not sure I understand correctly what the probem is but here are some thoughts:

1. To configure log4net using this config you may call

  XmlConfigurator.ConfigureAndWatch("log4net.config");

probably with the path to log4net.config.
There are other ways, e.g. through app.config file, but I prefer this.

2. If you'd like to test the appender then you probably should obtain this appender from code or instantiate and configure it directly and test.
If you need to test that your app is logging then you can mock the ILog object you use in your application and check anything you like.

I usualy do the following:
1. Configure log4net using XmlConfigurator()
2. In every class where I need to log something I put the following line:
private static readonly ILog Log = LogManager.GetLogger("MyClassName");
3. Use Log object to write log:
Log.Debug("Something");

In the article author adds one property to logging context (user). Since user is not constant we need to determine it every time. To do that author creates a wrapper function LogError() which determines the current user and sets it to context. Then it can be references in formatting pattern as %X{user}
0
 

Author Comment

by:omegalove
ID: 29250650
i can right to a file.
but not to a db
0
 
LVL 3

Expert Comment

by:Mike_Mozhaev
ID: 29309034
It fails because Thread column in database is not allowing NULLs and the query doesn't specify value for this column.

Add the following parameter:
   
     
     
     
     
       
     
   

and change command text to

   

It works for me.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

Author Comment

by:omegalove
ID: 29313496
It is still not working..

using System.IO;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.SessionState;
using log4net;

namespace OmegaLove.Web
{
    public class Global : System.Web.HttpApplication
    {
        private static readonly ILog log = LogManager.GetLogger(typeof(Global));

        protected void Application_Start(object sender, EventArgs e)
        {
            // Code that runs on application startup

            Application["StartTime"] = DateTime.Now;
            log4net.Config.XmlConfigurator.Configure();
            log.Info("Application Start");
            log.Error("HELLO");
            log.Debug("Exiting application.");
        }
nothing in the db?
 <log4net>

    <appender name="AdoNetAppender_SqlServer" type="log4net.Appender.AdoNetAppender">
      <connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
      <connectionString value="Data Source=72.18.146.251,1533;Initial Catalog=omegalove;Persist Security Info=True;User ID=administrator;Password=*****" providerName="System.Data.SqlClient" />
      <commandText value="INSERT INTO Log ([Date],[Level],[Logger],[User],[Message],[Exception],[Thread]) VALUES (@log_date, @log_level, @logger, @user, @message, @exception, @thread)" />
      <parameter>
        <parameterName value="@log_date" />
        <dbType value="DateTime" />
        <layout type="log4net.Layout.RawTimeStampLayout" />
      </parameter>
      <parameter>
        <parameterName value="@log_level" />
        <dbType value="String" />
        <size value="50" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%p" />
        </layout>
      </parameter>
      <parameter>
        <parameterName value="@logger" />
        <dbType value="String" />
        <size value="255" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%c" />
        </layout>
      </parameter>
      <parameter>
        <parameterName value="@user" />
        <dbType value="String" />
        <size value="50" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%X{user}" />
        </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>
      <parameter>
        <parameterName value="@thread" />
        <dbType value="String" />
        <size value="255" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%t" />
        </layout>
      </parameter>
    </appender>
    <root>
      <level value="ALL" />
      <appender-ref ref="AdoNetAppender_SqlServer" />
    </root>


  </log4net>

Open in new window

0
 

Author Comment

by:omegalove
ID: 29313879
it is working now.

How should I populate the username?
0
 
LVL 3

Expert Comment

by:Mike_Mozhaev
ID: 29314231
Put it into MDC before calling log functions:

public void LogError (string message, Exception e)
{
    //get logger  ILog logger = LogManager.GetLogger("MyProject");
    //set user to log4net context, so we can use %X{user} in the appenders
    if (HttpContext.Current.User != null && HttpContext.Current.User.Identity.IsAuthenticated)
        MDC.Set("user", HttpContext.Current.User.Identity.Name);

   if (logger.IsErrorEnabled)
       logger.Error(message, e);

    //now log error
}
0
 

Author Comment

by:omegalove
ID: 29314683
MDC ?
0
 
LVL 3

Expert Comment

by:Mike_Mozhaev
ID: 29314860
Well, MDC is obsolete.
Better
  ThreadContext.Properties["user"] = ...;
0
 

Author Comment

by:omegalove
ID: 29315184
  if (HttpContext.Current.User != null && HttpContext.Current.User.Identity.IsAuthenticated)
                MDC.Set("user", HttpContext.Current.User.Identity.Name);

should this go into session s start.
33      2010-04-01 02:37:43.530      5608      INFO      OmegaLove.Web.Global      (null)      Session r0st5n55pdwp5n55h2eszwn1 is starting.      
34      2010-04-01 02:39:07.943      4488      INFO      OmegaLove.Web.Global      (null)      Session r0st5n55pdwp5n55h2eszwn1 has ended.      
35      2010-04-01 02:39:07.947      5688      INFO      OmegaLove.Web.Global      (null)      Session r0st5n55pdwp5n55h2eszwn1 is starting.      
36      2010-04-01 02:39:24.387      5608      INFO      OmegaLove.Web.Global      (null)      Session r0st5n55pdwp5n55h2eszwn1 has ended.      
0
 
LVL 3

Accepted Solution

by:
Mike_Mozhaev earned 2000 total points
ID: 29315434
That means there is no user name in HttpContext. It's the specifics of your application. Probably no authentication.
0
 

Author Comment

by:omegalove
ID: 29315625
there is a login but something I am not sure what I am missing in the global.asax
0
 

Author Closing Comment

by:omegalove
ID: 31709144
k
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Suggested Courses
Course of the Month9 days, 11 hours left to enroll

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question