Logging in MVC Part 5 – The Model and Data Layer

This is part 5 of the MVC Logging series. Other articles in the series are:

• MVC Logging Part 1 – Elmah
• MVC Logging Part 2 – Health Monitoring
• MVC Logging Part 3 – NLog
• MVC Logging Part 4 – Log4Net
• MVC Logging Part 5 – Model and Data Layer

Introduction

This is the 5th article in a series on MVC logging.

Our sample website now has Elmah, NLog, Log4Net and Health monitoring set up and working. Now it is time to start work on our log reporting website by tying them all together.

Preparing the database

ASP.NET Health Monitoring logs a lot of different types of messages but there is no way to differentiate whether a message is just for information purposes or whether it is an error message that may need attention. So to address this issue, let’s create a new table called “aspnet_WebEvent_ErrorCodes” and introduce a column called “Level” which will map each message eventcode to either “Info”, “Error”.

The reason for doing this is so that we have a common “Level” attribute that we can use for all of our logging providers and this will allow us to later on filter all messages by their Log Level. Elmah for example will only be used to log unhandled exceptions so the LogLevel for our Elmah messages will always be “Error”.

Here is the database script necessary to add the new table to our database:

/****** Object:  Table [dbo].[aspnet_WebEvent_ErrorCodes]    Script Date: 07/29/2010 09:56:45 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_WebEvent_ErrorCodes]') AND type in (N'U'))
DROP TABLE [dbo].[aspnet_WebEvent_ErrorCodes]
GO
/****** Object:  Default [DF_aspnet_WebEvent_ErrorCodes_Level]    Script Date: 07/29/2010 09:56:45 ******/
IF  EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_aspnet_WebEvent_ErrorCodes_Level]') AND parent_object_id = OBJECT_ID(N'[dbo].[aspnet_WebEvent_ErrorCodes]'))
Begin
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_aspnet_WebEvent_ErrorCodes_Level]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[aspnet_WebEvent_ErrorCodes] DROP CONSTRAINT [DF_aspnet_WebEvent_ErrorCodes_Level]
END

End
GO
/****** Object:  Table [dbo].[aspnet_WebEvent_ErrorCodes]    Script Date: 07/29/2010 09:56:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_WebEvent_ErrorCodes]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[aspnet_WebEvent_ErrorCodes](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Name] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
 [EventCode] [int] NOT NULL,
 [Level] [nvarchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
 CONSTRAINT [PK_aspnet_WebEvent_ErrorCodes] PRIMARY KEY CLUSTERED
(
 [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
SET IDENTITY_INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ON
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (1, N'InvalidEventCode', -1, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (2, N'UndefinedEventCode/UndefinedEventDetailCode', 0, N'Error')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (3, N'Not used', -9999, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (4, N'ApplicationCodeBase', 1000, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (5, N'ApplicationStart', 1001, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (6, N'ApplicationShutdown', 1002, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (7, N'ApplicationCompilationStart', 1003, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (8, N'ApplicationCompilationEnd', 1004, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (9, N'ApplicationHeartbeat', 1005, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (10, N'RequestCodeBase', 2000, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (11, N'RequestTransactionComplete', 2001, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (12, N'RequestTransactionAbort', 2002, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (13, N'ErrorCodeBase', 3000, N'Error')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (14, N'RuntimeErrorRequestAbort', 3001, N'Error')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (15, N'RuntimeErrorViewStateFailure', 3002, N'Error')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (16, N'RuntimeErrorValidationFailure', 3003, N'Error')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (17, N'RuntimeErrorPostTooLarge', 3004, N'Error')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (18, N'RuntimeErrorUnhandledException', 3005, N'Error')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (19, N'WebErrorParserError', 3006, N'Error')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (20, N'WebErrorCompilationError', 3007, N'Error')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (21, N'WebErrorConfigurationError', 3008, N'Error')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (22, N'WebErrorOtherError', 3009, N'Error')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (23, N'WebErrorPropertyDeserializationError', 3010, N'Error')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (24, N'WebErrorObjectStateFormatterDeserializationError', 3011, N'Error')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (25, N'AuditCodeBase', 4000, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (26, N'AuditFormsAuthenticationSuccess', 4001, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (27, N'AuditMembershipAuthenticationSuccess', 4002, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (28, N'AuditUrlAuthorizationSuccess', 4003, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (29, N'AuditFileAuthorizationSuccess', 4004, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (30, N'AuditFormsAuthenticationFailure', 4005, N'Error')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (31, N'AuditMembershipAuthenticationFailure', 4006, N'Error')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (32, N'AuditUrlAuthorizationFailure', 4007, N'Error')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (33, N'AuditFileAuthorizationFailure', 4008, N'Error')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (34, N'AuditInvalidViewStateFailure', 4009, N'Error')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (35, N'AuditUnhandledSecurityException', 4010, N'Error')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (36, N'AuditUnhandledAccessException', 4011, N'Error')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (37, N'MiscCodeBase', 6000, N'Error')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (38, N'WebEventProviderInformation', 6001, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (39, N'ApplicationDetailCodeBase', 50000, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (40, N'ApplicationShutdownUnknown', 50001, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (41, N'ApplicationShutdownHostingEnvironment', 50002, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (42, N'ApplicationShutdownChangeInGlobalAsax', 50003, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (43, N'ApplicationShutdownConfigurationChange', 50004, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (44, N'ApplicationShutdownUnloadAppDomainCalled', 50005, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (45, N'ApplicationShutdownChangeInSecurityPolicyFile', 50006, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (46, N'ApplicationShutdownBinDirChangeOrDirectoryRename', 50007, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (47, N'ApplicationShutdownBrowsersDirChangeOrDirectoryRename', 50008, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (48, N'ApplicationShutdownCodeDirChangeOrDirectoryRename', 50009, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (49, N'ApplicationShutdownResourcesDirChangeOrDirectoryRename', 50010, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (50, N'ApplicationShutdownIdleTimeout', 50011, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (51, N'ApplicationShutdownPhysicalApplicationPathChanged', 50012, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (52, N'ApplicationShutdownHttpRuntimeClose', 50013, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (53, N'ApplicationShutdownInitializationError', 50014, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (54, N'ApplicationShutdownMaxRecompilationsReached', 50015, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (55, N'StateServerConnectionError', 50016, N'Error')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (56, N'AuditDetailCodeBase', 50200, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (57, N'InvalidTicketFailure', 50201, N'Error')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (58, N'ExpiredTicketFailure', 50202, N'Error')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (59, N'InvalidViewStateMac', 50203, N'Error')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (60, N'InvalidViewState', 50204, N'Error')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (61, N'WebEventDetailCodeBase', 50300, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (62, N'SqlProviderEventsDropped', 50301, N'Info')
INSERT [dbo].[aspnet_WebEvent_ErrorCodes] ([Id], [Name], [EventCode], [Level]) VALUES (63, N'WebExtendedBase', 100000, N'Info')
SET IDENTITY_INSERT [dbo].[aspnet_WebEvent_ErrorCodes] OFF
/****** Object:  Default [DF_aspnet_WebEvent_ErrorCodes_Level]    Script Date: 07/29/2010 09:56:45 ******/
IF Not EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_aspnet_WebEvent_ErrorCodes_Level]') AND parent_object_id = OBJECT_ID(N'[dbo].[aspnet_WebEvent_ErrorCodes]'))
Begin
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_aspnet_WebEvent_ErrorCodes_Level]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[aspnet_WebEvent_ErrorCodes] ADD  CONSTRAINT [DF_aspnet_WebEvent_ErrorCodes_Level]  DEFAULT ('Info') FOR [Level]
END

End
GO

The last step in preparing our database is to create a new view that will return all of the ASP.NET Health Monitoring messages with the LogLevel included.

Here is the database script to create the view:

CREATE VIEW vw_aspnet_WebEvents_extended
AS

SELECT
 webEvent.EventId
 , webEvent.EventTimeUtc
 , webEvent.EventTime
 , webEvent.EventType
 , webEvent.EventSequence
 , webEvent.EventOccurrence
 , webEvent.EventCode
 , webEvent.EventDetailCode
 , webEvent.Message
 , webEvent.ApplicationPath
 , webEvent.ApplicationVirtualPath
 , webEvent.MachineName
 , webEvent.RequestUrl
 , webEvent.ExceptionType
 , webEvent.Details
 , webEventCodes.Level
FROM
 dbo.aspnet_WebEvent_Events AS webEvent
INNER JOIN
 dbo.aspnet_WebEvent_ErrorCodes AS webEventCodes ON webEvent.EventCode = webEventCodes.EventCode

Before we get started on our model and data access layer I want to introduce some helper classes that we will be using along the way.

Paging Service

Every database application needs to page results from the database and display them to the end user.I looked at several paging implementations on the web but finally settled on Martin Bolands paging mechanism.

In our sample website I have created a new folder called “Paging” under the “Services” folder and added the following 4 classes (code is provided in the downloadable code at the end of this article) :

IPagedList.cs
PagedList.cs
Pager.cs
PagingExtensions.cs

We will be making use of the PagedList class in our repository class below.

Creating the Model

For our sample website we will be using Linq to Entities.

1. Create a new folder underneath Models and name it “Entities”.
2. Right-click the folder and select “Add -> New Item”.
3. Select the “ADO.NET Entity Data Model” from the Data category. In the name field, type “MvcLoggingDemo.edmx” and click on “Add”.
4. Select the “Create from Database” option and the click on “Next”.
5. The wizard should automatically find the SampleDatabase.mdf in the app_data folder. Ensure that the checkbox at the bottom of the dialog is “checked” and provide the connection string name of  “MvcLoggingDemoContainer”. Click “Next”.
6. Expand the tables node and select the following tables : Elmah_Error, NLog_Error, Log4Net_Error

7. Expand the views node and select the following view : vw_aspnet_WebEvents_extended
8. Click OK and the tables and the view should now be added to the Entities diagram.

At this point Linq to Entities will have created 4 entities that we can use to retrieve information from our database but we need a new Entity that we can use to store the common information from all of our tables.

So underneath our Models folder, create a new class and name it “LogEvent.cs”

Replace the file contents with the code below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MvcLoggingDemo.Models
{

 /// <summary>
 /// This represents a generic log message that can store log information about
 /// any logger implemented. Eg: Log4Net, NLog, Health Monitoring, Elmah
 /// </summary>
 public class LogEvent
 {
 private string _Id = string.Empty;

 /// <summary>
 /// String representation of the event log id
 /// </summary>
 public string Id
 {
 get
 {
 switch (IdType)
 {
 case "number":
 return IdAsInteger.ToString();

 case "guid":
 return IdAsGuid.ToString();

 default:
 return _Id;
 }
 }

 set
 {
 _Id = value;
 }
 }

 /// <summary>
 /// Stores the Id of the log event as a GUID
 /// </summary>
 internal Guid IdAsGuid { get; set; }

 /// <summary>
 /// Stores the Id of the log event as an integer
 /// </summary>
 internal int IdAsInteger { get; set; }

 /// <summary>
 /// Stores the base type of the id
 /// Valid values are : number, guid, string
 /// </summary>
 internal string IdType { get; set; }

 /// <summary>
 /// The date of the log event
 /// </summary>
 public DateTime LogDate { get; set; }

 /// <summary>
 /// The name of the log provider
 /// Example values are NLog, Log4Net, Elmah, Health Monitoring
 /// </summary>
 public string LoggerProviderName { get; set; }

 /// <summary>
 /// Information about where the error occurred
 /// </summary>
 public string Source { get; set; }

 /// <summary>
 /// The machine where the error occured
 /// </summary>
 public string MachineName { get; set; }

 /// <summary>
 /// The Type name of the class that logged the error
 /// </summary>
 public string Type { get; set; }

 /// <summary>
 /// The level of the message logged
 /// Valid values are : Debug, Info, Warning, Error, Fatal
 /// </summary>
 public string Level { get; set; }

 /// <summary>
 /// The message that was logged
 /// </summary>
 public string Message { get; set; }

 /// <summary>
 /// If the message was from an error this value will contain details of the stack trace.
 /// Otherwise it will be empty
 /// </summary>
 public string StackTrace { get; set; }

 /// <summary>
 /// If the message was from an error this value will contain details of the HTTP Server variables and Cookies.
 /// Otherwise it will be empty
 /// </summary>
 public string AllXml { get; set; }
 }
}

Our various logging providers all use a different kind of primary key. Some use a unique identifier, some use an integer. So our LogEvent class has a few properties that can store the Id in various datatypes internally within our assembly but we have one public Id property available to all the consumers of the class. To accommodate this we also introduce another property called IdType that stores the type of datatype used by the logging provider. At first I tried to use a generic “Object” datatype for the Id property but I could not get the Linq to Entities query working when doing a UNION on all of the IQueryable results returned from the providers.

All of the other public properties are self-explanatory.

Creating the Data Access Layer

For our Data Access Layer we will be using the Repository pattern and the first thing we need to do is create an interface that our repository will implement.

1. Create a new folder called Repository underneath Models
2. Create a new folder underneath Repository called Interfaces
3. In the Interfaces directory, create a new class file and name it, “ILogReportingRepository”
4. Replace the contents of the newly created file with the code below :

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

using MvcLoggingDemo.Services.Paging;

namespace MvcLoggingDemo.Models.Repository
{
 /// <summary>
 /// This interface provides the methods that we need to so that we can report on log messages stored by the various
 /// logging tools used in our website
 /// </summary>
 public interface ILogReportingRepository
 {
 /// <summary>
 /// Gets a filtered list of log events
 /// </summary>
 /// <param name="pageIndex">0 based page index</param>
 /// <param name="pageSize">max number of records to return</param>
 /// <param name="start">start date</param>
 /// <param name="end">end date</param>
 /// <param name="logLevel">The level of the log messages</param>
 /// <returns>A filtered list of log events</returns>
 IQueryable<LogEvent> GetByDateRangeAndType(int pageIndex, int pageSize, DateTime start, DateTime end, string logLevel);

 /// <summary>
 /// Returns a single Log event
 /// </summary>
 /// <param name="id">Id of the log event as a string</param>
 /// <returns>A single Log event</returns>
 LogEvent GetById(string id);

 /// <summary>
 /// Clears log messages between a date range and for specified log levels
 /// </summary>
 /// <param name="start">start date</param>
 /// <param name="end">end date</param>
 /// <param name="logLevels">string array of log levels</param>
 void ClearLog(DateTime start, DateTime end, string[] logLevels);
 }
}

5. In the Repository directory, create a new class file and name it, “ElmahRepository”
6. Replace the contents of the newly created file with the code below :

using System;
using System.Collections.Generic;
using System.Data.Objects;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

using MvcLoggingDemo.Models.Entities;
using MvcLoggingDemo.Services.Paging;
using MvcLoggingDemo.Helpers;

namespace MvcLoggingDemo.Models.Repository
{
 /// <summary>
 /// This class extracts information that Elmah stores so that we can report on it
 /// </summary>
 public class ElmahRepository : ILogReportingRepository
 {
 MvcLoggingDemoContainer _context = null;

 /// <summary>
 /// Default Constructor uses the default Entity Container
 /// </summary>
 public ElmahRepository()
 {
 _context = new MvcLoggingDemoContainer();
 }

 /// <summary>
 /// Overloaded constructor that can take an EntityContainer as a parameter so that it can be mocked out by our tests
 /// </summary>
 /// <param name="context">The Entity context</param>
 public ElmahRepository(MvcLoggingDemoContainer context)
 {
 _context = context;
 }

 /// <summary>
 /// Gets a filtered list of log events
 /// </summary>
 /// <param name="pageIndex">0 based page index</param>
 /// <param name="pageSize">max number of records to return</param>
 /// <param name="start">start date</param>
 /// <param name="end">end date</param>
 /// <param name="logLevel">The level of the log messages</param>
 /// <returns>A filtered list of log events</returns>
 public IQueryable<LogEvent> GetByDateRangeAndType(int pageIndex, int pageSize, DateTime start, DateTime end, string logLevel)
 {
 IQueryable<LogEvent> list = (from a in _context.ELMAH_Error
 where a.TimeUtc >= start && a.TimeUtc <= end
 && (logLevel == "All" || logLevel == "Error")
 select new LogEvent { IdType = "guid"
 , Id = ""
 , IdAsInteger = 0
 , IdAsGuid = a.ErrorId
 , LoggerProviderName = "Elmah"
 , LogDate = a.TimeUtc
 , MachineName = a.Host
 , Message = a.Message
 , Type = a.Type
 , Level = "Error"
 , Source = a.Source, StackTrace = "" });

 return list;
 }

 /// <summary>
 /// Returns a single Log event
 /// </summary>
 /// <param name="id">Id of the log event as a string</param>
 /// <returns>A single Log event</returns>
 public LogEvent GetById(string id)
 {
 Guid guid = new Guid(id);
 LogEvent logEvent = (from b in _context.ELMAH_Error
 where b.ErrorId == guid
 select new LogEvent { IdType = "guid"
 , IdAsGuid = b.ErrorId
 , LoggerProviderName = "Elmah"
 , LogDate = b.TimeUtc
 , MachineName = b.Host
 , Message = b.Message
 , Type = b.Type
 , Level = "Error"
 , Source = b.Source
 , StackTrace = ""
 , AllXml = b.AllXml })
 .SingleOrDefault();

 return logEvent;
 }

 /// <summary>
 /// Clears log messages between a date range and for specified log levels
 /// </summary>
 /// <param name="start">start date</param>
 /// <param name="end">end date</param>
 /// <param name="logLevels">string array of log levels</param>
 public void ClearLog(DateTime start, DateTime end, string[] logLevels)
 {
 string commandText = "delete from Elmah_Error WHERE TimeUtc >= @p0 and TimeUtc <= @p1";

 SqlParameter paramStartDate = new SqlParameter { ParameterName = "p0", Value = start.ToUniversalTime(), DbType = System.Data.DbType.DateTime };
 SqlParameter paramEndDate = new SqlParameter { ParameterName = "p1", Value = end.ToUniversalTime(), DbType = System.Data.DbType.DateTime };

 _context.ExecuteStoreCommand(commandText, paramStartDate, paramEndDate);
 }

 }
}
 

7. In the Repository directory, create a new class file and name it, “NLogRepository”
8. Replace the contents of the newly created file with the code below :

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

using MvcLoggingDemo.Models.Entities;
using MvcLoggingDemo.Services.Paging;
using MvcLoggingDemo.Helpers;
using System.Data.SqlClient;

namespace MvcLoggingDemo.Models.Repository
{
 /// <summary>
 /// This class extracts information that NLog stores so that we can report on it
 /// </summary>
 public class NLogRepository : ILogReportingRepository
 {
 MvcLoggingDemoContainer _context = null;

 /// <summary>
 /// Default Constructor uses the default Entity Container
 /// </summary>
 public NLogRepository()
 {
 _context = new MvcLoggingDemoContainer();
 }

 /// <summary>
 /// Overloaded constructor that can take an EntityContainer as a parameter so that it can be mocked out by our tests
 /// </summary>
 /// <param name="context">The Entity context</param>
 public NLogRepository(MvcLoggingDemoContainer context)
 {
 _context = context;
 }

 /// <summary>
 /// Gets a filtered list of log events
 /// </summary>
 /// <param name="pageIndex">0 based page index</param>
 /// <param name="pageSize">max number of records to return</param>
 /// <param name="start">start date</param>
 /// <param name="end">end date</param>
 /// <param name="logLevel">The level of the log messages</param>
 /// <returns>A filtered list of log events</returns>
 public IQueryable<LogEvent> GetByDateRangeAndType(int pageIndex, int pageSize, DateTime start, DateTime end, string logLevel)
 {
 IQueryable<LogEvent> list = (from b in _context.NLog_Error
 where b.time_stamp >= start && b.time_stamp <= end
 && (b.level == logLevel || logLevel == "All")
 select new LogEvent { IdType = "number"
 , Id = ""
 , IdAsInteger = b.Id
 , IdAsGuid = Guid.NewGuid()
 , LoggerProviderName = "NLog"
 , LogDate = b.time_stamp
 , MachineName = b.host
 , Message = b.message
 , Type = b.type
 , Level = b.level
 , Source = b.source
 , StackTrace = b.stacktrace });

 return list;
 }

 /// <summary>
 /// Returns a single Log event
 /// </summary>
 /// <param name="id">Id of the log event as a string</param>
 /// <returns>A single Log event</returns>
 public LogEvent GetById(string id)
 {
 int logEventId = Convert.ToInt32(id);

 LogEvent logEvent = (from b in _context.NLog_Error
 where b.Id == logEventId
 select new LogEvent { IdType = "number"
 , IdAsInteger = b.Id
 , LoggerProviderName = "NLog"
 , LogDate = b.time_stamp
 , MachineName = b.host
 , Message = b.message
 , Type = b.type
 , Level = b.level
 , Source = b.source
 , StackTrace = b.stacktrace
 , AllXml = b.allxml })
 .SingleOrDefault();

 return logEvent;

 }

 /// <summary>
 /// Clears log messages between a date range and for specified log levels
 /// </summary>
 /// <param name="start">start date</param>
 /// <param name="end">end date</param>
 /// <param name="logLevels">string array of log levels</param>
 public void ClearLog(DateTime start, DateTime end, string[] logLevels)
 {
 string logLevelList = "";
 foreach (string logLevel in logLevels)
 {
 logLevelList += ",'" + logLevel + "'";
 }
 if (logLevelList.Length > 0)
 {
 logLevelList = logLevelList.Substring(1);
 }

 string commandText = "delete from NLog_Error WHERE time_stamp >= @p0 and time_stamp <= @p1 and level in (@p2)";

 SqlParameter paramStartDate = new SqlParameter { ParameterName = "p0", Value = start.ToUniversalTime(), DbType = System.Data.DbType.DateTime };
 SqlParameter paramEndDate = new SqlParameter { ParameterName = "p1", Value = end.ToUniversalTime(), DbType = System.Data.DbType.DateTime };
 SqlParameter paramLogLevelList = new SqlParameter { ParameterName = "p2", Value = logLevelList };

 _context.ExecuteStoreCommand(commandText, paramStartDate, paramEndDate, paramLogLevelList);
 }

 }
}
 

9. In the Repository directory, create a new class file and name it, “Log4NetRepository”
10. Replace the contents of the newly created file with the code below :

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

using MvcLoggingDemo.Models.Entities;
using MvcLoggingDemo.Services.Paging;
using MvcLoggingDemo.Helpers;
using System.Data.SqlClient;

namespace MvcLoggingDemo.Models.Repository
{
 /// <summary>
 /// This class extracts information that Log4Net stores so that we can report on it
 /// </summary>
 public class Log4NetRepository : ILogReportingRepository
 {
 MvcLoggingDemoContainer _context = null;

 /// <summary>
 /// Default Constructor uses the default Entity Container
 /// </summary>
 public Log4NetRepository()
 {
 _context = new MvcLoggingDemoContainer();
 }

 /// <summary>
 /// Overloaded constructor that can take an EntityContainer as a parameter so that it can be mocked out by our tests
 /// </summary>
 /// <param name="context">The Entity context</param>
 public Log4NetRepository(MvcLoggingDemoContainer context)
 {
 _context = context;
 }

 /// <summary>
 /// Gets a filtered list of log events
 /// </summary>
 /// <param name="pageIndex">0 based page index</param>
 /// <param name="pageSize">max number of records to return</param>
 /// <param name="start">start date</param>
 /// <param name="end">end date</param>
 /// <param name="logLevel">The level of the log messages</param>
 /// <returns>A filtered list of log events</returns>
 public IQueryable<LogEvent> GetByDateRangeAndType(int pageIndex, int pageSize, DateTime start, DateTime end, string logLevel)
 {
 IQueryable<LogEvent> list = (from b in _context.Log4Net_Error
 where b.Date >= start && b.Date <= end
 && (b.Level == logLevel || logLevel == "All")
 select new LogEvent { IdType = "number"
 , Id = ""
 , IdAsInteger = b.Id
 , IdAsGuid = Guid.NewGuid()
 , LoggerProviderName = "Log4Net"
 , LogDate = b.Date
 , MachineName = b.Thread
 , Message = b.Message
 , Type = ""
 , Level = b.Level
 , Source = b.Thread
 , StackTrace = "" });

 return list;
 }

 /// <summary>
 /// Returns a single Log event
 /// </summary>
 /// <param name="id">Id of the log event as a string</param>
 /// <returns>A single Log event</returns>
 public LogEvent GetById(string id)
 {
 int logEventId = Convert.ToInt32(id);

 LogEvent logEvent = (from b in _context.Log4Net_Error
 where b.Id == logEventId
 select new LogEvent { IdType = "number"
 , IdAsInteger = b.Id
 , LoggerProviderName = "Log4Net"
 , LogDate = b.Date
 , MachineName = b.Thread
 , Message = b.Message
 , Type = ""
 , Level = b.Level
 , Source = b.Thread
 , StackTrace = ""
 , AllXml = "" })
 .SingleOrDefault();

 return logEvent;
 }

 /// <summary>
 /// Clears log messages between a date range and for specified log levels
 /// </summary>
 /// <param name="start">start date</param>
 /// <param name="end">end date</param>
 /// <param name="logLevels">string array of log levels</param>
 public void ClearLog(DateTime start, DateTime end, string[] logLevels)
 {
 string logLevelList = "";
 foreach (string logLevel in logLevels)
 {
 logLevelList += ",'" + logLevel + "'";
 }
 if (logLevelList.Length > 0)
 {
 logLevelList = logLevelList.Substring(1);
 }

 string commandText = "delete from Log4Net_Error WHERE [Date] >= @p0 and [Date] <= @p1 and Level in (@p2)";

 SqlParameter paramStartDate = new SqlParameter { ParameterName = "p0", Value = start.ToUniversalTime(), DbType = System.Data.DbType.DateTime };
 SqlParameter paramEndDate = new SqlParameter { ParameterName = "p1", Value = end.ToUniversalTime(), DbType = System.Data.DbType.DateTime };
 SqlParameter paramLogLevelList = new SqlParameter { ParameterName = "p2", Value = logLevelList };

 _context.ExecuteStoreCommand(commandText, paramStartDate, paramEndDate, paramLogLevelList);
 }

 }
}
 

11. In the Repository directory, create a new class file and name it, “HealthMonitoringRepository”
12. Replace the contents of the newly created file with the code below :

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

using MvcLoggingDemo.Models.Entities;
using MvcLoggingDemo.Services.Paging;
using MvcLoggingDemo.Helpers;
using System.Data.SqlClient;

namespace MvcLoggingDemo.Models.Repository
{
 /// <summary>
 /// This class extracts information that ASP.NET Health Monitoring stores so that we can report on it
 /// </summary>
 public class HealthMonitoringRepository : ILogReportingRepository
 {
 MvcLoggingDemoContainer _context = null;

 /// <summary>
 /// Default Constructor uses the default Entity Container
 /// </summary>
 public HealthMonitoringRepository()
 {
 _context = new MvcLoggingDemoContainer();
 }

 /// <summary>
 /// Overloaded constructor that can take an EntityContainer as a parameter so that it can be mocked out by our tests
 /// </summary>
 /// <param name="context">The Entity context</param>
 public HealthMonitoringRepository(MvcLoggingDemoContainer context)
 {
 _context = context;
 }

 /// <summary>
 /// Gets a filtered list of log events
 /// </summary>
 /// <param name="pageIndex">0 based page index</param>
 /// <param name="pageSize">max number of records to return</param>
 /// <param name="start">start date</param>
 /// <param name="end">end date</param>
 /// <param name="logLevel">The level of the log messages</param>
 /// <returns>A filtered list of log events</returns>
 public IQueryable<LogEvent> GetByDateRangeAndType(int pageIndex, int pageSize, DateTime start, DateTime end, string logLevel)
 {
 IQueryable<LogEvent> list = (from h in _context.vw_aspnet_WebEvents_extended
 where h.EventTimeUtc >= start && h.EventTimeUtc <= end
 && (h.Level == logLevel || logLevel == "All")
 select new LogEvent { IdType = "string"
 , Id = h.EventId
 , IdAsInteger = 0
 , IdAsGuid = Guid.NewGuid()
 , LoggerProviderName = "Health Monitoring"
 , LogDate = h.EventTimeUtc
 , MachineName = h.MachineName
 , Message = h.Message
 , Type = h.EventType
 , Level = h.Level
 , Source = h.RequestUrl
 , StackTrace = "" });

 return list;
 }

 /// <summary>
 /// Returns a single Log event
 /// </summary>
 /// <param name="id">Id of the log event as a string</param>
 /// <returns>A single Log event</returns>
 public LogEvent GetById(string id)
 {
 LogEvent logEvent = logEvent = (from b in _context.vw_aspnet_WebEvents_extended
 where b.EventId == id
 select new LogEvent { IdType = "string"
 , Id = b.EventId
 , LoggerProviderName = "Health Monitoring"
 , LogDate = b.EventTimeUtc
 , MachineName = b.MachineName
 , Message = b.Message
 , Type = b.EventType
 , Level = b.Level
 , Source = b.RequestUrl
 , StackTrace = ""
 , AllXml = "" })
 .SingleOrDefault();

 return logEvent;

 }

 /// <summary>
 /// Clears log messages between a date range and for specified log levels
 /// </summary>
 /// <param name="start">start date</param>
 /// <param name="end">end date</param>
 /// <param name="logLevels">string array of log levels</param>
 public void ClearLog(DateTime start, DateTime end, string[] logLevels)
 {
 string logLevelList = "";
 foreach (string logLevel in logLevels)
 {
 logLevelList += ",'" + logLevel + "'";
 }
 if (logLevelList.Length > 0)
 {
 logLevelList = logLevelList.Substring(1);
 }

 string commandText = "";
 commandText += "DELETE ";
 commandText += "FROM ";
 commandText += "    aspnet_WebEvent_Events ";
 commandText += "WHERE";
 commandText += "    EventId IN    ";
 commandText += "(SELECT EventId    ";
 commandText += " FROM vw_aspnet_WebEvents_extended ";
 commandText += " WHERE ";
 commandText += "    [EventTimeUtc] >= @p0";
 commandText += " AND [EventTimeUtc] <= @p1";
 commandText += " AND [Level] IN (@p2)"; // eg:  AND [Level] IN ('Info','Debug')
 commandText += " )";

 SqlParameter paramStartDate = new SqlParameter { ParameterName = "p0", Value = start.ToUniversalTime(), DbType = System.Data.DbType.DateTime };
 SqlParameter paramEndDate = new SqlParameter { ParameterName = "p1", Value = end.ToUniversalTime(), DbType = System.Data.DbType.DateTime };
 SqlParameter paramLogLevelList = new SqlParameter { ParameterName = "p2", Value = logLevelList };

 _context.ExecuteStoreCommand(commandText, paramStartDate, paramEndDate, paramLogLevelList);
 }

 }
}
 

At this point we have 4 stand alone repository classes that can each retrieve information from their own data store (table).

To build our log reporting tool we will need to create a new class that will be able to pull data out of one or all of the repositories that we have just created.

13. In our “Interfaces” folder, create a new file and name it, “ILogReportingFacade.cs”

14. Replace the contents of the newly created file with the code below :

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

using MvcLoggingDemo.Services.Paging;

namespace MvcLoggingDemo.Models.Repository
{
 /// <summary>
 /// This interface provides a facade over all of our LogReport repositories
 /// </summary>
 public interface ILogReportingFacade
 {
 /// <summary>
 /// Gets a filtered list of log events
 /// </summary>
 /// <param name="pageIndex">0 based page index</param>
 /// <param name="pageSize">max number of records to return</param>
 /// <param name="start">start date</param>
 /// <param name="end">end date</param>
 /// <param name="logProviderName">If empty all log providers used, otherwise it will be filtered by the specified log provider</param>
 /// <param name="logLevel">The level of the log messages</param>
 /// <returns>A filtered list of log events</returns>
 IPagedList<LogEvent> GetByDateRangeAndType(int pageIndex, int pageSize, DateTime start, DateTime end, string logProviderName, string logLevel);

 /// <summary>
 /// Returns a single Log event
 /// </summary>
 /// <param name="logProviderName">Name of the log provider</param>
 /// <param name="id">Id of the log event as a string</param>
 /// <returns>A single Log event</returns>
 LogEvent GetById(string logProviderName, string id);

 /// <summary>
 /// Clears log messages for a given date range and log level
 /// </summary>
 /// <param name="logProviderName">Name of the log provider</param>
 /// <param name="start">start date</param>
 /// <param name="end">end date</param>
 /// <param name="logLevels">The level of the log messages</param>
 void ClearLog(string logProviderName, DateTime start, DateTime end, string[] logLevels);

 /// <summary>
 /// Get's a list of all log providers registered in the web.config file
 /// </summary>
 /// <returns>A list of all log providers registered</returns>
 Dictionary<string, string> GetLogProviders();
 }
}
 

15. In the “Repository” folder, create a new file and name it, “LogReportingFacade.cs”

16. Replace the contents of the newly created file with the code below :

using System;
using System.Configuration;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Linq;
using System.Web;

using MvcLoggingDemo.Models.Entities;
using MvcLoggingDemo.Services.Logging;
using MvcLoggingDemo.Services.Paging;
using MvcLoggingDemo.Helpers;

namespace MvcLoggingDemo.Models.Repository
{
 /// <summary>
 /// This class provides a facade over all of our LogReport repositories
 /// </summary>
 public class LogReportingFacade : ILogReportingFacade
 {
 MvcLoggingDemoContainer _context = new MvcLoggingDemoContainer();

 private Dictionary<string, string> logProviders = null;

 /// <summary>
 /// Default constructor
 /// </summary>
 public LogReportingFacade()
 {
 Init();
 }

 /// <summary>
 /// Overloaded constructor that can take an EntityContainer as a parameter so that it can be mocked out by our tests
 /// </summary>
 /// <param name="context">The Entity context</param>
 public LogReportingFacade(MvcLoggingDemoContainer context)
 {
 _context = context;

 Init();
 }

 /// <summary>
 ///
 /// </summary>
 private void Init()
 {
 logProviders = new Dictionary<string, string>();

 // Call ConfigurationManager to read the custom logConfiguration
 // of the web.config file and put its contents into an
 // instance of the custom class created for it.
 LogConfigurationSection configSection = ConfigurationManager.GetSection("logConfiguration") as LogConfigurationSection;

 if (configSection == null)
 throw new ApplicationException("Failed to load the Log Configuration section.");
 else
 {
 for (int i = 0; i < configSection.LogProviders.Count; i++)
 {
 logProviders.Add(configSection.LogProviders[i].Name, configSection.LogProviders[i].Type);
 }
 }
 }

 /// <summary>
 /// Creates and returns an instance of a log provider
 /// </summary>
 /// <param name="logProviderName">The type name of the log provider</param>
 /// <returns>An instance of a log provider</returns>
 private ILogReportingRepository GetProvider(string logProviderName)
 {
 string logSourceType = logProviders[logProviderName];

 Type providerType = Type.GetType(logSourceType);

 ILogReportingRepository provider = Activator.CreateInstance(providerType, _context) as ILogReportingRepository;

 return provider;
 }

 /// <summary>
 /// Gets a filtered list of log events
 /// </summary>
 /// <param name="pageIndex">0 based page index</param>
 /// <param name="pageSize">max number of records to return</param>
 /// <param name="start">start date</param>
 /// <param name="end">end date</param>
 /// <param name="logProviderName">name of the log provider</param>
 /// <param name="logLevel">The level of the log messages</param>
 /// <returns>A filtered list of log events</returns>
 public IPagedList<LogEvent> GetByDateRangeAndType(int pageIndex, int pageSize, DateTime start, DateTime end, string logProviderName, string logLevel)
 {
 IQueryable<LogEvent> list = null;

 switch (logProviderName)
 {
 case "All":
 foreach (string providerName in logProviders.Values)
 {
 IQueryable<LogEvent> logList = GetProvider(providerName).GetByDateRangeAndType(pageIndex, pageSize, start, end, logLevel);
 list = (list == null) ? logList : list.Union(logList);
 }
 break;

 default:
 list = GetProvider(logProviderName).GetByDateRangeAndType(pageIndex, pageSize, start, end, logLevel);
 break;
 }

 list = list.OrderByDescending(d => d.LogDate);

 return new PagedList<LogEvent>(list, pageIndex, pageSize);
 }

 /// <summary>
 /// Returns a single Log event
 /// </summary>
 /// <param name="logProviderName">name of the log provider</param>
 /// <param name="id">Id of the log event as a string</param>
 /// <returns>A single Log event</returns>
 public LogEvent GetById(string logProviderName, string id)
 {
 LogEvent logEvent = GetProvider(logProviderName).GetById(id);
 return logEvent;
 }

 /// <summary>
 /// Clears log messages between a date range and for specified log levels
 /// </summary>
 /// <param name="logProviderName">name of the log provider</param>
 /// <param name="start">start date</param>
 /// <param name="end">end date</param>
 /// <param name="logLevels">string array of log levels</param>
 public void ClearLog(string logProviderName, DateTime start, DateTime end, string[] logLevels)
 {
 GetProvider(logProviderName).ClearLog(start, end, logLevels);
 }

 /// <summary>
 /// Returns a list of all registered log providers
 /// </summary>
 /// <returns>A list of all registered log providers</returns>
 public Dictionary<string, string> GetLogProviders()
 {
 return logProviders;
 }

 }
}
 

The class file above makes use of a custom configuration class. The source code for it can be found in the accompanying download at the end of this article.

The interesting part is in the GetByDateRangeAndType method where we determine if we need to return the results from all of our registered log providers or whether we return the results from a single log provider. Each implemented Log Reporting provider returns an IQueryable interface so we can use the power of LINQ to just loop through each one and do a UNION on them.

After we have consolidated all of the results we order them and page the results. All of this happens server side and then the results for each page are returned back to the calling client. Although at this stage we haven’t built our controller and views yet. That will be done in the next article.

Conclusion

In this article we have created the model and the database access layer for our log reporting tool. We have seen how we can query multiple data sources using Linq to Entities UNION keyword.

In the next article we will build our controller and view to display the logs from our various data sources.

Download

The sourcecode for part 1 is on the Downloads tab of the associated Codeplex website

‹ Logging in MVC Part 4 – Log4Net

MVC Logging Part 6 – Controller / Views ›

Tagged with: asp.net, elmah, health monitoring, log4net, logging
Posted in ASP.NET MVC