basquang™ on clouds

August 4, 2014

[Quartz.NET]: Configure NLog for logging

Filed under: Logging,Quartz,Quartz.NET — basquang @ 5:47 PM
Tags: ,

In previous article Configure Logging I described how to configure Logging in configuration file using Common.Logging.Simple.ConsoleOutLoggerFactoryAdapter.

In this article, I will describe how to configure NLog in Quartz application.

Because Quartz.NET using Common.Logging frameworks, so there are two implementations dependency when you want to use NLogLoggerFactoryAdapter:

  • Common.Logging.NLog20 is linked against NLog 2.0.0.2
  • Common.Logging.NLog10 is linked against NLog 1.0.0.505

So you need to install correct version of Common.Logging.NLog and NLog.

  1. Create Console Application
  2. Install Quartz.NET from NutGet. By default at this time NuGet will install following packages. By default Quartz use Common.Logging v2.1.2

    <packages>
      <package id="Common.Logging" version="2.1.2" targetFramework="net45" />
      <package id="Quartz" version="2.2.4" targetFramework="net45" />
    </packages>
    

  3. Add the configuration code below to configure NLog in the App.config file

    <?xml version="1.0" encoding="utf-8"?>
    <configuration>
    
      <configSections>
        <sectionGroup name="common">
          <section name="logging" type="Common.Logging.ConfigurationSectionHandler, Common.Logging" />
        </sectionGroup>
        <section name="nlog" type="NLog.Config.ConfigSectionHandler, NLog" />
      </configSections>
    
      <common>
        <logging>
          <factoryAdapter type="Common.Logging.NLog.NLogLoggerFactoryAdapter, Common.Logging.NLog20">
            <arg key="configType" value="INLINE" />
          </factoryAdapter>
        </logging>
      </common>
    
      <nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
        <targets>
          <target name="file" xsi:type="File" layout="${date:format=HH\:MM\:ss} ${logger} ${message}" fileName="${basedir}/logs/logfile.txt" />
        </targets>
        <rules>
          <logger name="*" minlevel="Debug" writeTo="file" />
        </rules>
      </nlog>
    
      <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
      </startup>       
    </configuration>
    

  4. Create simple Job like this

    public class SimpleJob : IJob
        {
            private static ILog logging = LogManager.GetLogger(typeof(SimpleJob));
    
            public void Execute(IJobExecutionContext context)
            {
                logging.InfoFormat("Hello from job");
            }
        }
    

  5. Using the job in Program

    ILog log = LogManager.GetLogger(typeof(Program));
    
    // First we must get a reference to a scheduler
    ISchedulerFactory sf = new StdSchedulerFactory();
    IScheduler sched = sf.GetScheduler();
    
    //////////////////
    try
    {
    	var startTime = DateTimeOffset.Now.AddSeconds(5);
    
    	var job = JobBuilder.Create<SimpleJob>()
    						.WithIdentity("job1", "group1")
    						.Build();
    
    	var trigger = TriggerBuilder.Create()
    		.WithIdentity("trigger1", "group1")
    		.StartAt(startTime)
    		.WithSimpleSchedule(x => x.WithIntervalInSeconds(10).WithRepeatCount(4))
    		.Build();
    
    	sched.ScheduleJob(job, trigger);       
    
    	sched.Start();
    	Thread.Sleep(TimeSpan.FromSeconds(30));
    }
    finally
    {
    	sched.Shutdown(true);
    }
    

  6. When press F5 to run the application following error will appear

    Unable to create type ‘Common.Logging.NLog.NLogLoggerFactoryAdapter, Common.Logging.NLog20’

  7. To resolve this issue. Install Common.Logging.NLog20 from NuGet by running following command in order.NOTE: To use NLong we need to update Common.Logging to version 2.2.0

    Install-Package Common.Logging.NLog20

    Install-Package Common.Logging.Core

    Install-Package Common.Logging -Version 2.2.0

    This command will install following packages

    <packages>
      <package id="Common.Logging" version="2.2.0" targetFramework="net45" />
      <package id="Common.Logging.Core" version="2.2.0" targetFramework="net45" />
      <package id="Common.Logging.NLog20" version="2.2.0" targetFramework="net45" />
      <package id="NLog" version="2.0.0.2000" targetFramework="net45" />
      <package id="Quartz" version="2.2.4" targetFramework="net45" />
    </packages>
    

  8. Now run application again and see the output of logged file in the path bin\debug\logs\logfile.txt

The logged file created by NLog in the configuration you have defined above.

Hope this help!

 

Advertisements

[Quartz.NET]: Configure Logging

Filed under: Logging,Quartz,Quartz.NET — basquang @ 5:02 PM
Tags: ,

Common.Logging can be configured to use different logging frameworks under the hood; namely Enterprise Library, Log4Net and NLog.

However, to keep things simple in last post Get Started we take the simple route and configure logging using code to just log to the console using Common.Logging basic logging mechanism.

Common.Logging.LogManager.Adapter = new Common.Logging.Simple.ConsoleOutLoggerFactoryAdapter { Level = Common.Logging.LogLevel.Info};

This blog post describe how to configure Logging in application configuration file.

  1. Create Console Application
  2. Install Quartz.NET from NuGet
  3. Add following code in the App.config to configure Logging for Quart. In this logging configuration we use ConsoleOutLoggerFactoryAdapter to display log to the Console.Out screen.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  
  <configSections>   
    <sectionGroup name="common">
      <section name="logging" type="Common.Logging.ConfigurationSectionHandler, Common.Logging"/>
    </sectionGroup>
  </configSections>

  <common>
    <logging>
      <factoryAdapter type="Common.Logging.Simple.ConsoleOutLoggerFactoryAdapter, Common.Logging">
        <arg key="showLogName" value="true"/>
        <arg key="showDataTime" value="true"/>
        <arg key="level" value="INFO"/>
        <arg key="dateTimeFormat" value="HH:mm:ss:fff"/>
      </factoryAdapter>
    </logging>
  </common>
  
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
</configuration>

  1. Add a simple Job like this. In this Job we use log the execution of the Job by writing the message “Hello World…”

    public class HelloJob : IJob
        {
            //Get logging configuration
            private static ILog _log = LogManager.GetLogger(typeof(HelloJob));
    
            /// <summary> 
            /// Called by the <see cref="IScheduler" /> when a
            /// <see cref="ITrigger" /> fires that is associated with
            /// the <see cref="IJob" />.
            /// </summary>
            public virtual void Execute(IJobExecutionContext context)
            {
    
                // Say Hello to the World and display the date/time
                _log.Info(string.Format("Hello World! - {0}", System.DateTime.Now.ToString()));
            }
    
        }
    

  2. Using the Job in Program class as below
  3. //Get Logging configuration
    ILog log = LogManager.GetLogger(typeof(Program));
    
    // First we must get a reference to a scheduler
    ISchedulerFactory sf = new StdSchedulerFactory();
    IScheduler sched = sf.GetScheduler();
    
    log.Info("------- Scheduling Job  -------------------");
    
    // define the job and tie it to our HelloJob class
    IJobDetail job = JobBuilder.Create<HelloJob>()
    	.WithIdentity("job1", "group1")
    	.Build();
    
    // Trigger the job to run on the next round minute
    ITrigger trigger = TriggerBuilder.Create()
    	.WithIdentity("trigger1", "group1")
    	.StartNow()
    	.WithSimpleSchedule(x => x
    	.WithIntervalInSeconds(10)
    	.RepeatForever())
    	.Build();
    
    // Tell quartz to schedule the job using our trigger
    sched.ScheduleJob(job, trigger);           
    
    // Start up the scheduler (nothing can actually run until the 
    // scheduler has been started)
    sched.Start();
    
    // wait long enough so that the scheduler as an opportunity to 
    // run the job!
    
    // wait 65 seconds to show jobs
    Thread.Sleep(TimeSpan.FromSeconds(60));
    
    // shut down the scheduler
    sched.Shutdown(true);
    

  4. The Console screen output will display the message (execute the job) you have been defined each 10 seconds 6 time (within 60 seconds)

Hope this help

August 26, 2011

Logging: Using Log4Net in ASP.NET MVC 3

Filed under: Logging,MVC — basquang @ 9:08 AM

This article will describe how to configure logging with Log4Net in ASP.NET MVC 3 application.

Firstly, Create a new ASP.NET MVC 3 Internet Application.

From visual studio Tools –> Library Package Manager –> Manage NuGet Packages… In Manage NuGet Packages… windows, search Log4Net then install log4net package to your project.

image

1. Logging to SQL Server database.

1.1  you need create a table as script bellow to store your logging to SQL Server database.

CREATE TABLE [dbo].[Log](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Date] [datetime] NOT NULL,
	[Thread] [varchar](255) NOT NULL,
	[Level] [varchar](20) NOT NULL,
	[Logger] [varchar](255) NOT NULL,
	[Message] [varchar](4000) NOT NULL
) ON [PRIMARY]

1.2 Open your web.config configuration file and add following log4net configuration sections

<configSections>
		<section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net" />
	</configSections>
	<log4net>
		<appender name="AdoNetAppender_SqlServer" type="log4net.Appender.AdoNetAppender">
			<connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
			<connectionString value="Data Source=BASQUANG;Initial Catalog=LoggingDB;Integrated Security=True" />
			<commandText value="INSERT INTO Log ([Date],[Thread],[Level],[Logger],[Message]) VALUES (@log_date, @thread, @log_level, @logger, @message)" />
			<parameter>
				<parameterName value="@log_date" />
				<dbType value="DateTime" />
				<layout type="log4net.Layout.PatternLayout" value="%date{yyyy'-'MM'-'dd HH':'mm':'ss'.'fff}" />
			</parameter>
			<parameter>
				<parameterName value="@thread" />
				<dbType value="String" />
				<size value="255" />
				<layout type="log4net.Layout.PatternLayout" value="%thread" />
			</parameter>
			<parameter>
				<parameterName value="@log_level" />
				<dbType value="String" />
				<size value="50" />
				<layout type="log4net.Layout.PatternLayout" value="%level" />
			</parameter>
			<parameter>
				<parameterName value="@logger" />
				<dbType value="String" />
				<size value="255" />
				<layout type="log4net.Layout.PatternLayout" value="%logger" />
			</parameter>
			<parameter>
				<parameterName value="@message" />
				<dbType value="String" />
				<size value="4000" />
				<layout type="log4net.Layout.PatternLayout" value="%message" />
			</parameter>
		</appender>		
		<root>
			<level value="All"/>
			<appender-ref ref="AdoNetAppender_SqlServer"/>
		</root>
	</log4net>

Note: Make sure you have already added the correctly version (4.0.0.0) of System.Data.dll to your project

2. Logging to Oracle database

2.1 you need create a table as script bellow to store your logging to Oracle database.

CREATE TABLE "LOG" (
  "DATETIME" TIMESTAMP(3) NULL,
  "THREAD" VARCHAR2(255 BYTE) NULL,
  "LOG_LEVEL" VARCHAR2(255 BYTE) NULL,
  "LOGGER" VARCHAR2(255 BYTE) NULL,
  "MESSAGE" VARCHAR2(4000 BYTE) NULL)
  STORAGE ( 
    NEXT 1048576 ) 
/

2.2 Open your web.config configuration file and add following log4net configuration sections

<configSections>
		<section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net" />
	</configSections>
	<log4net>		
		<appender name="AdoNetAppender_Oracle" type="log4net.Appender.AdoNetAppender">
			<connectionType value="System.Data.OracleClient.OracleConnection, System.Data.OracleClient, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
			<connectionString value="data source=XE;User ID=LoggingDB;Password=abcd1234-" />
			<commandText value="INSERT INTO Log (Datetime,Thread,Log_Level,Logger,Message) VALUES (:log_date, :thread, :log_level, :logger, :message)" />
			<bufferSize value="128" />
			<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>
		</appender>
		<root>
			<level value="All"/>
			<appender-ref ref="AdoNetAppender_Oracle"/>
		</root>
	</log4net>

Note: Make sure you have already added the correctly version (4.0.0.0) of System.Data.Oracle.dll to your project

3. Add this line of code to  Application_Start on Global.asax.cs file

log4net.Config.XmlConfigurator.Configure(); 

4. Add these lines of code on HomeController to testing your logging configuration

public ActionResult About()
        {
            log4net.ILog log = log4net.LogManager.GetLogger(this.GetType());
            log.Info("Test message for Log4Net"); 
            return View();
        }

5. Test your configuration. Run your website, from home page click on About then looking for a new record added on database table.

August 25, 2011

Logging: Using Elmah, Nuget in ASP.NET MVC 3 and Oracle database

Filed under: Logging,MVC,Oracle — basquang @ 3:00 PM

Logging is one of the most important thing you must to thinking about when intend to building a production website. There are many many way to implement logging in a website, and it was not able to easily.

So in this article, I will simplify the way to implement logging in ASP.NET MVC 3 using Elmah!

Configure Elmah with NuGet

1. Create a ASP.NET MVC 3 Internet Application

imageimage

2. From Visual Studio select Tools –> Library Package Manager –> Manage NuGet Packages…

In Manage NuGet Packages…Windows, seach Elmah then install this package for your project as bellow when finished.

imageimage

3. NuGet will automatically add reference elmah.dll to your project and the appropriate element of elmah on web.config file.

Now you can ready to test the Elmah logging by navigate your website to http://[yourwebsite]/elmah.axd as bellow

image

Logging your exception with Elmah.

But because we would like to use Elmah to handle our exceptions instead of MVC, we will replace the standard MVC [HandleError] attribute with our own custom attribute called [HandleErrorWithElmah].

So you must to create those class bellow

a. the HandleErrorWithELMAHAttribute.cs class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Elmah;

namespace MvcApplicationElmah.Logging
{
    public class HandleErrorWithELMAHAttribute : HandleErrorAttribute
    {
        public override void OnException(ExceptionContext context)
        {
            base.OnException(context);

            var e = context.Exception;
            if (!context.ExceptionHandled   // if unhandled, will be logged anyhow
                    || RaiseErrorSignal(e)      // prefer signaling, if possible
                    || IsFiltered(context))     // filtered?
                return;

            LogException(e);
        }

        private static bool RaiseErrorSignal(Exception e)
        {
            var context = HttpContext.Current;
            if (context == null)
                return false;
            var signal = ErrorSignal.FromContext(context);
            if (signal == null)
                return false;
            signal.Raise(e, context);
            return true;
        }

        private static bool IsFiltered(ExceptionContext context)
        {
            var config = context.HttpContext.GetSection("elmah/errorFilter")
                                     as ErrorFilterConfiguration;

            if (config == null)
                return false;

            var testContext = new ErrorFilterModule.AssertionHelperContext(
                                                                context.Exception, HttpContext.Current);

            return config.Assertion.Test(testContext);
        }

        private static void LogException(Exception e)
        {
            var context = HttpContext.Current;
            ErrorLog.GetDefault(context).Log(new Error(e, context));
        }
    }
}

b. The ErrorHandlingControllerFactory.cs class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Routing;

namespace MvcApplicationElmah.Logging
{
    public class ErrorHandlingControllerFactory : DefaultControllerFactory
    {
        /// <summary>
        /// Injects a custom attribute 
        /// on every action that is invoked by the controller
        /// </summary>
        /// <param name="requestContext">The request context</param>
        /// <param name="controllerName">The name of the controller</param>
        /// <returns>An instance of a controller</returns>
        public override IController CreateController(
            RequestContext requestContext,
            string controllerName)
        {
            var controller =
                base.CreateController(requestContext,
                controllerName);

            var c = controller as Controller;

            if (c != null)
            {
                c.ActionInvoker =
                    new ErrorHandlingActionInvoker(
                        new HandleErrorWithELMAHAttribute());
            }

            return controller;
        }
    }
}

c. The ErrorHandlingActionInvoker.cs class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace MvcApplicationElmah.Logging
{
    public class ErrorHandlingActionInvoker : ControllerActionInvoker
    {
        private readonly IExceptionFilter filter;

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="filter">The exception filter to inject</param>
        public ErrorHandlingActionInvoker(IExceptionFilter filter)
        {
            if (filter == null)
            {
                throw new ArgumentNullException("filter");
            }

            this.filter = filter;
        }

        /// <summary>
        /// This methods returns all of the normal filters used
        /// PLUS it appends our custom filter to the end of the list 
        /// </summary>
        /// <param name="controllerContext">The context of the controller</param>
        /// <param name="actionDescriptor">The action descriptor</param>
        /// <returns>All of the action filters</returns>
        protected override FilterInfo GetFilters(
            ControllerContext controllerContext,
            ActionDescriptor actionDescriptor)
        {
            var filterInfo =
                base.GetFilters(controllerContext,
                actionDescriptor);

            filterInfo.ExceptionFilters.Add(this.filter);

            return filterInfo;
        }
    }
}

Now throw a new Exception in About action on your HomeController

public ActionResult About()
        {
            throw new Exception("A test exception for ELMAH");
            return View();
        }

Enable custom error in web.config

<customErrors mode="On" defaultRedirect="/Home/Error"></customErrors>

Register your custom error handler on Global.asax

protected void Application_Start()
        {
            AreaRegistration.RegisterAllAreas();

            RegisterGlobalFilters(GlobalFilters.Filters);
            RegisterRoutes(RouteTable.Routes);

            ControllerBuilder.Current.SetControllerFactory(new MvcApplicationElmah.Logging.ErrorHandlingControllerFactory());
        }

Now, It’s time to test your custom exception that logged in Elmah.

imageimage

Logging to Oracle database

With a big website we need to log all exception to database. Elmah supports both of SQL Server and Oracle and more kind of databases. Is this section I will describe how to log the errors in Oracle database.

Firstly, we need to run the Oracle.sql script that provided from Elmah package that you has already downloaded.

image

/*
  
   ELMAH - Error Logging Modules and Handlers for ASP.NET
   Copyright (c) 2004-9 Atif Aziz. All rights reserved.
  
    Author(s):
  
      James Driscoll, mailto:jamesdriscoll@btinternet.com
  
   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at
  
      http://www.apache.org/licenses/LICENSE-2.0
  
   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
  
*/

-- $Id: Oracle.sql 568 2009-05-11 14:18:34Z azizatif $

-- NB This script assumes you have logged on in the schema where you want to create the ELMAH objects

-- create a sequence for the errors (user to simulate an identity in SQL Server)
CREATE SEQUENCE elmah$error_seq START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE NOCACHE NOORDER;

-- create the table to store the data
-- you can optionally specify tablespaces here too!
CREATE TABLE elmah$error
(
    -- if using Oracle 10g and above you can add DEFAULT SYS_GUID() 
    -- to the errorid definition.
    -- Oracle 8i doesn't like it with an NVARCHAR2
    -- haven't tested it against 9i
    errorid         NVARCHAR2(32) NOT NULL,
    application     NVARCHAR2(60) NOT NULL,
    host            NVARCHAR2(30) NOT NULL,
    type            NVARCHAR2(100) NOT NULL,
    source          NVARCHAR2(60),
    message         NVARCHAR2(500) NOT NULL,
    username        NVARCHAR2(50),
    statuscode      NUMBER NOT NULL,
    timeutc         DATE NOT NULL,
    sequencenumber  NUMBER NOT NULL,
    allxml          NCLOB NOT NULL,
    CONSTRAINT idx_elmah$error_pk 
        PRIMARY KEY (errorid) 
        USING INDEX -- TABLESPACE "TABLESPACE FOR INDEX"
) -- TABLESPACE "TABLESPACE FOR DATA"
/

-- trigger to make sure we get our sequence number in the table
CREATE TRIGGER trg_elmah$error_bi
BEFORE INSERT ON elmah$error
FOR EACH ROW
BEGIN
    SELECT elmah$error_seq.NEXTVAL INTO :new.sequencenumber FROM dual;
END trg_elmah$error_bi;
/

-- create the index on the table
CREATE INDEX idx_elmah$error_app_time_seq ON elmah$error(application, timeutc DESC, sequencenumber DESC)
/

-- package containing the procedure we need for ELMAH to log errors
CREATE OR REPLACE PACKAGE pkg_elmah$log_error
IS
    PROCEDURE LogError
    (
        v_ErrorId IN elmah$error.errorid%TYPE,
        v_Application IN elmah$error.application%TYPE,
        v_Host IN elmah$error.host%TYPE,
        v_Type IN elmah$error.type%TYPE,
        v_Source IN elmah$error.source%TYPE,
        v_Message IN elmah$error.message%TYPE,
        v_User IN elmah$error.username%TYPE,
        v_AllXml IN elmah$error.allxml%TYPE,
        v_StatusCode IN elmah$error.statuscode%TYPE,
        v_TimeUtc IN elmah$error.timeutc%TYPE
    );

END pkg_elmah$log_error;
/

CREATE OR REPLACE PACKAGE BODY pkg_elmah$log_error
IS
    PROCEDURE LogError
    (
        v_ErrorId IN elmah$error.errorid%TYPE,
        v_Application IN elmah$error.application%TYPE,
        v_Host IN elmah$error.host%TYPE,
        v_Type IN elmah$error.type%TYPE,
        v_Source IN elmah$error.source%TYPE,
        v_Message IN elmah$error.message%TYPE,
        v_User IN elmah$error.username%TYPE,
        v_AllXml IN elmah$error.allxml%TYPE,
        v_StatusCode IN elmah$error.statuscode%TYPE,
        v_TimeUtc IN elmah$error.timeutc%TYPE
    )
    IS
    BEGIN
        INSERT INTO elmah$error
            (
                errorid,
                application,
                host,
                type,
                source,
                message,
                username,
                allxml,
                statuscode,
                timeutc
            )
        VALUES
            (
                UPPER(v_ErrorId),
                v_Application,
                v_Host,
                v_Type,
                v_Source,
                v_Message,
                v_User,
                v_AllXml,
                v_StatusCode,
                v_TimeUtc
            );

    END LogError;   

END pkg_elmah$log_error;
/


-- package containing the procedure we need for ELMAH to retrieve errors
CREATE OR REPLACE PACKAGE pkg_elmah$get_error
IS
	-- NB this is for backwards compatibility with Oracle 8i
    TYPE t_cursor IS REF CURSOR;
    
    PROCEDURE GetErrorXml
    (
        v_Application IN elmah$error.application%TYPE,
        v_ErrorId IN elmah$error.errorid%TYPE,
        v_AllXml OUT elmah$error.allxml%TYPE
    );

    PROCEDURE GetErrorsXml
    (
        v_Application IN elmah$error.application%TYPE,
        v_PageIndex IN NUMBER DEFAULT 0,
        v_PageSize IN NUMBER DEFAULT 15,
        v_TotalCount OUT NUMBER,
        v_Results OUT t_cursor
    );
    
END pkg_elmah$get_error;
/

CREATE OR REPLACE PACKAGE BODY pkg_elmah$get_error
IS
    PROCEDURE GetErrorXml
    (
        v_Application IN elmah$error.application%TYPE,
        v_ErrorId IN elmah$error.errorid%TYPE,
        v_AllXml OUT elmah$error.allxml%TYPE
    )
    IS
    BEGIN
        SELECT  allxml
        INTO    v_AllXml
        FROM    elmah$error
        WHERE   errorid = UPPER(v_ErrorId)
        AND     application = v_Application;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            v_AllXml := NULL;
    END GetErrorXml;

    PROCEDURE GetErrorsXml
    (
        v_Application IN elmah$error.application%TYPE,
        v_PageIndex IN NUMBER DEFAULT 0,
        v_PageSize IN NUMBER DEFAULT 15,
        v_TotalCount OUT NUMBER,
        v_Results OUT t_cursor
    )
    IS
        l_StartRowIndex NUMBER;
        l_EndRowIndex   NUMBER;
    BEGIN
        -- Get the ID of the first error for the requested page
        l_StartRowIndex := v_PageIndex * v_PageSize + 1;
        l_EndRowIndex := l_StartRowIndex + v_PageSize - 1;
        
        -- find out how many rows we've got in total
        SELECT  COUNT(*)
        INTO    v_TotalCount
        FROM    elmah$error
        WHERE   application = v_Application;

        OPEN v_Results FOR
            SELECT  *
            FROM
            (
                SELECT  e.*,
                        ROWNUM row_number
                FROM
                (
                    SELECT  /*+ INDEX(elmah$error, idx_elmah$error_app_time_seq) */
                            errorid,
                            application,
                            host,
                            type,
                            source,
                            message,
                            username,
                            statuscode,
                            timeutc
                    FROM    elmah$error
                    WHERE   application = v_Application
                    ORDER BY
                            timeutc DESC, 
                            sequencenumber DESC
                ) e
                WHERE ROWNUM <= l_EndRowIndex
            )
            WHERE   row_number >= l_StartRowIndex;
            
    END GetErrorsXml;

END pkg_elmah$get_error;
/

/* 
-- If you are securing the packages above, you will need to grant execute
-- privileges on them so that they can be called by the user connecting to the database.
-- NB As long as you use the schema owner for the connection string, this is not necessary,
-- although this is generally discouraged by Best Practices.

-- Option 1) Allow any user to execute the package (not recommended)
-- replace OWNER for the schema owner in the following statement
GRANT EXECUTE ON OWNER.pkg_elmah$log_error TO PUBLIC;
GRANT EXECUTE ON OWNER.pkg_elmah$get_error TO PUBLIC;

-- Option 2) Allow a single user to execute the package (better)
-- replace OWNER for the schema owner in the following statement
GRANT EXECUTE ON OWNER.pkg_elmah$log_error TO USER_NAME;
GRANT EXECUTE ON OWNER.pkg_elmah$get_error TO USER_NAME;

-- Option 3) Lock things down so that one user can only log errors, while another user can read and log errors (most secure)
-- replace OWNER for the schema owner in the following statement
-- LOGGING_USER_NAME will be used to connect to the database in all sites which log errors to the database
GRANT EXECUTE ON OWNER.pkg_elmah$log_error TO LOGGING_USER_NAME;
-- ADMIN_USER_NAME will be used to connect to the database in an admin portal which allows users to read errors
GRANT EXECUTE ON OWNER.pkg_elmah$log_error TO ADMIN_USER_NAME;
GRANT EXECUTE ON OWNER.pkg_elmah$get_error TO ADMIN_USER_NAME;

-- NB if you do take this approach, be sure to set the schemaOwner parameter in your web.config
*/

Then you need to install “Elmah on Oracle” package from NuGet. It will automatically add appropriate elements in web.config to support logging to Oracle database

image

Finally, you have to manual change the connection string to your oracle database that store the Elmah Logging table as bellow

<add name="elmah-oracle" connectionString="Data Source=XE;User ID=loggingdb;Password=*****;" />

You could be able to  test your configuration now. Starting the website, click on About then looking for a new record has already added to ELMAH$ERROR table in Oracle database.

image

Hope this help!

Blog at WordPress.com.