How to setup Semantic Logging Application Block (SLAB)

Event Logging

Logging errors and information is a useful, if not absolutely necessary tool for developers to build and manage applications. Microsoft’s Enterprise Library contains the Semantic Logging Application Block (SLAB), which provides a consistently formatted output of log files to your SQL database. This article will walk you through the setup process for SLAB, which will log information to your SQL Server database

Installing Semantic Logging Application Block:
Install the Nuget package for ‘Semantic Logging Application Block – SQL Server Sink’ . (it will include the core Semantic Logging)
Adding an EventSource class to your application:
Now you need to override the EventSource class, which I have done in the ‘LogWriter’ class below. I’ve also created a ‘Logger’ class to make it easier to deal with LogWriter. It takes care of some repetitive work for me. I can just pass in a raw Exception to Logger, and it will utilize LogWriter to write it to the Traces table in my database. Nice and easy.

You can add this to your Lib folder in your UI (main process project):

using System;
using System.Diagnostics;
using System.Diagnostics.Tracing;

namespace MySolution.WebApi.Lib
{
    public class Logger
    {
        public static readonly Logger Log = new Logger();
        public void Error(Exception ex)
        {
            string message = ex.Message;
            string innerMessage = "";
            if (ex.InnerException != null)
                innerMessage = ex.InnerException.Message;

            StackTrace st = new StackTrace();
            string methName = st.GetFrame(1).GetMethod().Name;
            string stack = st.ToString();
            LogWriter.Log.Error(message, innerMessage, methName, stack);

        }
        public void Error(Exception ex, string addedMessage)
        {
            string message = addedMessage + " :: " + ex.Message;
            string innerMessage = "";
            if (ex.InnerException != null)
                innerMessage = ex.InnerException.Message;

            StackTrace st = new StackTrace();
            string methName = st.GetFrame(1).GetMethod().Name;
            string stack = st.ToString();
            LogWriter.Log.Error(message, innerMessage, methName, stack);

        }
        public void Error(string message)
        {
            StackTrace st = new StackTrace();
            string methName = st.GetFrame(1).GetMethod().Name;
            string stack = st.ToString();
            LogWriter.Log.Error(message, "", methName, stack);
        }

        public void Critical(string message)
        {
            StackTrace st = new StackTrace();
            string methName = st.GetFrame(1).GetMethod().Name;
            string stack = st.ToString();
            LogWriter.Log.Critical(message, methName, stack);
        }

        public void Warning(string message)
        {
            StackTrace st = new StackTrace();
            string methName = st.GetFrame(1).GetMethod().Name;
            string stack = st.ToString();
            LogWriter.Log.Warning(message, methName, stack);
        }
        public void Information(string message)
        {
            StackTrace st = new StackTrace();
            string methName = st.GetFrame(1).GetMethod().Name;
            string stack = st.ToString();
            LogWriter.Log.Information(message, methName, stack);
        }
    }

    public class LogWriter : EventSource
    {
        public static readonly LogWriter Log = new LogWriter();

        [Event(1000, Message = "{0}", Level = EventLevel.Critical)]
        public void Critical(string message, string method, string stack)
        {
            object[] parms = new object[] { message, method, stack };
            if (IsEnabled()) WriteEvent(1000, parms);
        }
        [Event(1001, Message = "{0}", Level = EventLevel.Error)]
        public void Error(string message, string innerExceptionMessage, string method, string stack)
        {
            object[] parms = new object[] { message, innerExceptionMessage, method, stack };
            if (IsEnabled()) WriteEvent(1001, parms);
        }

        [Event(1002, Message = "{0}", Level = EventLevel.Warning)]
        public void Warning(string message, string method, string stack)
        {
            object[] parms = new object[] { message, method, stack };
            if (IsEnabled()) WriteEvent(1002, parms);
        }

        [Event(1003, Message = "{0}", Level = EventLevel.Informational)]
        public void Information(string message, string method, string stack)
        {
            object[] parms = new object[] { message, method, stack };
            if (IsEnabled()) WriteEvent(1003, parms);
        }
    }
}

Wiring your app to use SLAB:
Now that you have your EventSource classes setup. You need to wire up your EventSource classes when your application starts.
In Global.asax add the following ‘using’ statements:

using Microsoft.Practices.EnterpriseLibrary.SemanticLogging;
using System.Diagnostics.Tracing;

and add the bold line below to your Application_Start function (in Global.asax):

protected void Application_Start()
        {
            AreaRegistration.RegisterAllAreas();
            GlobalConfiguration.Configure(WebApiConfig.Register);
            FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
            RouteConfig.RegisterRoutes(RouteTable.Routes);
            BundleConfig.RegisterBundles(BundleTable.Bundles);

            SetupSemanticLoggingApplicationBlock();

        }

Then add this function to your Global.asax:

protected void SetupSemanticLoggingApplicationBlock()
        {
            //EventTracing setup
            string logConnString =
                System.Configuration.ConfigurationManager.ConnectionStrings["LoggingConnString"].ToString();

            var sqlListener1 = SqlDatabaseLog.CreateListener("UI", logConnString);
            var sqlListener2 = SqlDatabaseLog.CreateListener("Infrastructure", logConnString);
            var sqlListener3 = SqlDatabaseLog.CreateListener("Service", logConnString);

            //get web.config value for logging level
            bool critcal = System.Configuration.ConfigurationManager.AppSettings["Logging_LogCritical"].ToLower() ==
                           "true";
            bool error = System.Configuration.ConfigurationManager.AppSettings["Logging_LogError"].ToLower() ==
                           "true";
            bool warning = System.Configuration.ConfigurationManager.AppSettings["Logging_LogWarning"].ToLower() ==
                           "true";
            bool info = System.Configuration.ConfigurationManager.AppSettings["Logging_LogInformation"].ToLower() ==
                          "true";

            //Enable the level of logging based on settings in web.config
            if (critcal)
            {
                sqlListener1.EnableEvents(LogWriter.Log, EventLevel.Critical);
                //uncomment if you add logging to your sub projects
                //sqlListener2.EnableEvents(MySolution.Infrastructure.InfrLogger.Log, EventLevel.Critical);
                //sqlListener3.EnableEvents(MySolution.Service.SvcLogger.Log, EventLevel.Critical);
            }
            if (error)
            {
                sqlListener1.EnableEvents(LogWriter.Log, EventLevel.Error);
                //uncomment if you add logging to your sub projects  
                //sqlListener2.EnableEvents(MySolution.Infrastructure.InfrLogger.Log, EventLevel.Error);
                //sqlListener3.EnableEvents(MySolution.Service.SvcLogger.Log, EventLevel.Error);
            }
            if (warning)
            {
                sqlListener1.EnableEvents(LogWriter.Log, EventLevel.Warning);
                //uncomment if you add logging to your sub projects                
                //sqlListener2.EnableEvents(MySolution.Infrastructure.InfrLogger.Log, EventLevel.Warning);
                //sqlListener3.EnableEvents(MySolution.Service.SvcLogger.Log, EventLevel.Warning);
            }
            if (info)
            {
                sqlListener1.EnableEvents(LogWriter.Log, EventLevel.Informational);
                //sqlListener2.EnableEvents(MySolution.Infrastructure.InfrLogger.Log, EventLevel.Informational);
                //sqlListener3.EnableEvents(MySolution.Service.SvcLogger.Log, EventLevel.Informational);
            }
        }

Adding EventSource classes in sub projects:
In all of your other projects that you include in this main project you can also have logging by adding the following file into the Lib folder (or any folder really) of those projects. You just have to make sure the name of the class is unique in each project so I will name mine SvcLogger, InfrLogger, etc, and you need to give unique codes on the Events of each (where above they are 100x, these are 200x, and if added to another project you would need to make those 300x.

So here is the sample from my Service project logger. This is all I have to add to the project to do logging in it:

using System;
using System.Diagnostics;
using System.Diagnostics.Tracing;

namespace MySolution.Service
{
    public class SvcLogger : EventSource
    {
        public static readonly SvcLogger Log = new SvcLogger();

        public void Error(string message)
        {
            StackTrace st = new StackTrace();
            string methName = st.GetFrame(1).GetMethod().Name;
            string stack = st.ToString();
            DoError(message, methName, stack);
        }

        public void Critical(string message)
        {
            StackTrace st = new StackTrace();
            string methName = st.GetFrame(1).GetMethod().Name;
            string stack = st.ToString();
            DoCritical(message, methName, stack);
        }

        public void Warning(string message)
        {
            StackTrace st = new StackTrace();
            string methName = st.GetFrame(1).GetMethod().Name;
            string stack = st.ToString();
            DoWarning(message, methName, stack);
        }

        public void Information(string message)
        {
            StackTrace st = new StackTrace();
            string methName = st.GetFrame(1).GetMethod().Name;
            string stack = st.ToString();
            DoInformation(message, methName, stack);
        }

        [Event(2000, Message = "{0}", Level = EventLevel.Critical)]
        private void DoCritical(string message, string method, string stack)
        {
            object[] parms = new object[] { message, method, stack };
            if (IsEnabled()) WriteEvent(2000, parms);
        }
        [Event(2001, Message = "{0}", Level = EventLevel.Error)]
        private void DoError(string message, string method, string stack)
        {
            object[] parms = new object[] { message, method, stack };
            if (IsEnabled()) WriteEvent(2001, parms);
        }

        [Event(2002, Message = "{0}", Level = EventLevel.Warning)]
        private void DoWarning(string message, string method, string stack)
        {
            object[] parms = new object[] { message, method, stack };
            if (IsEnabled()) WriteEvent(2002, parms);
        }

        [Event(2003, Message = "{0}", Level = EventLevel.Informational)]
        private void DoInformation(string message, string method, string stack)
        {
            object[] parms = new object[] { message, method, stack };
            if (IsEnabled()) WriteEvent(2003, parms);
        }

    }
}

Setup your database:
You will need to add the Traces table to your database. You will find a script in your [Solution Folder]\packages\EnterpriseLibrary.SemanticLogging.Database.2.0.1406.1\scripts folder called ‘CreateSemanticLoggingDatabaseObjects.sql’. Run this script against the database you wish to record your logs in.

Setup your app to point to your database:
In the function we put in global.asax, we specified our connection string as

 string logConnString =
                System.Configuration.ConfigurationManager.ConnectionStrings["LoggingConnString"].ToString();

Now you need to add that connection string to your web.config in the connection string section. Add:

 
    add name="LoggingConnString" connectionString="Data Source=(local);initial catalog=MyDatabase;Integrated Security=True" providerName="System.Data.SqlClient"
   

A note about error handling:
In every project except the UI (top level), I normally will want to log the error then have that error thrown again up to the calling function. To make sure that I send the call stack history, I will do it as follows:

            catch (Exception ex)
            {
                SvcLogger.Log.Error(ex.Message);
                throw;  //NOT throw ex; 
            }

instead of the following because the following won’t pass the existing stack information.

            catch (Exception ex)
            {
                SvcLogger.Log.Error(ex.Message);
                throw ex;  //this throws as if the error starts now.
            }

In the UI level I would do the following in order to make sure that sensitive info is never shown on the UI. If you are debugging this isn’t very helpful, but is also not helpful for the hacker trying to gain insight into your code.

            catch (Exception ex)
            {
                Logger.Log.Error(ex.Message);
                throw new Exception("Error deleting item. Review logs for info."); //don't pass sensitive info
            }

You will also need to set the level of errors to log in your web.config file app settings, if you set any item, then that item and the ones more critical will all be logged. For instance if I set true on ‘Warning’, then Warning, Error and Critical would be logged (as it is set in sample below). Information level items would not be logged.

 <!--######### Semantic Logging settings #########-->
    <!-- If you enable any level of Logging, it will enable that level and any level more severe. Enabling 'Warning' would enable 'Critical' and 'Error' automatically. -->
    <add key="Logging_LogCritical" value="False" />
    <add key="Logging_LogError" value="False" />
    <add key="Logging_LogWarning" value="True" /> 
    <add key="Logging_LogInformation" value="False" />
    <!--######### end Semantic Logging settings #########-->

That should get you going with an easy to setup and manage Logging system in your application. Enjoy.

Developing SSRS reports without buying Visual Studio

I have been trying to find a way to develop SSRS reports without requiring a Visual Studio license. This is because we have a group of people that I work with that don’t need Visual Studio for anything else, but have been trained to create SSRS reports and have SQL experience.  I had expected that Visual Studio Express would most likely work, but I wasn’t able to find any success with that.

In the last 2 weeks, Microsoft has released  the new Visual Studio 2013 Community. This is a free version that is basically the equivalent of Visual Studio 2013 Professional. If you qualify for it, and most people probably do, then I highly suggest it. You can get it at http://www.visualstudio.com/en-us/downloads/download-visual-studio-vs#d-community. VS Express is being replaced by Community edition.  This will be a great option if you are an individual user, or in a business situation where there are 5 or less developers. The limitations set on businesses for using this product are:

  1. Can be used by 5 or less developers without restrictions if you have less than 250 PC’s and make less than $1M annual revenue.
  2. Can be used by unlimited users for classroom learning or open source development even if you have more than 250 PC and make more than $1M.

Those restrictions make it impossible for us to use Visual Studio 2013 Community. If you are in a business that exceeds 250 PCs  or has more than $1M annual revenue and you still need a free development environment for SSRS development, I’ve found a solution by using Visual Studio 2013 Integrated Shell and installing BIDS.

This is completely free and easy to setup.

1) Download and install Visual Studio 2013 Isolated Shell (is required before installing integrated shell) –  http://www.microsoft.com/en-us/download/details.aspx?id=40764

2) Download and install Visual Studio 2103 Integrated Shell – http://www.microsoft.com/en-us/download/details.aspx?id=40777

3) Download and install MSST – BIDS for VS 2013 – http://www.microsoft.com/en-us/download/details.aspx?id=42313

Cheers!

SQL Query to find tables by name and column

This script will find tables where the table name is like a value and it contains a column name that is like some value

SELECT TABLE_NAME as TableName , c.Name as ColumnName
FROM INFORMATION_SCHEMA.TABLES ist 
join sys.tables st on 
ist.TABLE_NAME = st.name 
join sys.columns c 
on c.object_id = st.object_id 
WHERE (1=1)  
and ist.TABLE_TYPE = 'BASE TABLE' 
and  ist.Table_Name like '%account%' 
and c.name like '%agent%'

SQL Split string

This method involves adding a table function to your db which is given below, then you can call it like this:

declare @list varchar(8000)
set @list = 'bus order closing, that, some, other, thing'

Select item into #myTemp
from NS_Reporting.dbo.DelimitedSplit8K(@list, ‘,’)

select rtrim(ltrim(Item)) as Item from #myTemp

Here is the Table function script:

USE [NS_Reporting]
GO
/****** Object: UserDefinedFunction [dbo].[DelimitedSplit8K] Script Date: 06/06/2014 12:45:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[DelimitedSplit8K]
–===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
–===== “Inline” CTE Driven “Tally Table” produces values from 0 up to 10,000…
– enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), –10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), –10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), –10E+4 or 10,000 rows max
cteTally(N) AS (–==== This provides the “zero base” and limits the number of rows right up front
– for both a performance gain and prevention of accidental “overruns”
SELECT 0 UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (–==== This returns N+1 (starting position of each “element” just once for each delimiter)
SELECT t.N+1
FROM cteTally t
WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)
)
–===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000))
FROM cteStart s
;

SQL with a JOIN to the TOP 1 of another table

So I needed to do a join where a table would join only to the top row returned from the second table. In fact in this one I needed to have 2 joins that do that, so here is how I created it:


SELECT d.TableName,d.GroupName,d.CircuitID,d.ServiceIndicator,d.ErrorPriorityCode,d.OmniaAddressID,d.LegacyKeyID,d.ServiceIndicatorSource,
d.ServiceIndicatorOriginal,d.RowID,
assignment.AssignedBy_UserId as LastAssignedBy,
assignment.AssignedTo_UserId as LastAssignedTo,
assignment.Comment as LastAssignmentComment,
assignment.DateAssigned as LastDateAssigned,
checkout.UserId as LastCheckedOutBy,
checkout.ResolutionId as LastCheckoutResolutionId,
checkout.Comments as LastCheckoutComment,
checkout.Closed as LastCheckoutClosed,
checkout.CheckoutDate as LastCheckoutDate,
checkout.CheckinDate as LastCheckInDate

FROM dbo.Details AS d
LEFT JOIN circuitjoin AS cmCircuit
ON d.CircuitID = cmCircuit.Id
Left Join dbo.DetailAssignment assignment
ON assignment.RowId =
(select top 1 a.RowId from DetailAssignment a
where a.DetailServiceIndicator = d.ServiceIndicator
order by a.DateAssigned desc)

LEFT JOIN dbo.DetailCheckout AS checkout
ON checkout.RowId =
(Select top 1 c.RowId from dbo.DetailCheckout c
where c.DetailServiceIndicator = d.ServiceIndicator
order by c.CheckoutDate desc)