Cannot write to database sql server using log4net.

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

omegaloveAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike_MozhaevCommented:
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
omegaloveAuthor Commented:
i can right to a file.
but not to a db
0
Mike_MozhaevCommented:
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

omegaloveAuthor Commented:
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
omegaloveAuthor Commented:
it is working now.

How should I populate the username?
0
Mike_MozhaevCommented:
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
omegaloveAuthor Commented:
MDC ?
0
Mike_MozhaevCommented:
Well, MDC is obsolete.
Better
  ThreadContext.Properties["user"] = ...;
0
omegaloveAuthor Commented:
  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
Mike_MozhaevCommented:
That means there is no user name in HttpContext. It's the specifics of your application. Probably no authentication.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
omegaloveAuthor Commented:
there is a login but something I am not sure what I am missing in the global.asax
0
omegaloveAuthor Commented:
k
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.