Home » SQL

Category Archives: SQL

Advertisements

Write Sitecore Experience Forms Log Entries to a Custom SQL Server Database Table

Not long after I wrote the code for my last post, I continued exploring ways of changing service classes in Sitecore Experience Forms.

One thing that popped out when continuing on this quest was Sitecore.ExperienceForms.Diagnostics.ILogger. I immediately thought “I just wrote code for retrieving Forms configuration settings from a SQL Server database table, why not create a new ILogger service class for storing log entries in a custom SQL table?”

Well, that’s what I did, and the code in this post captures how I went about doing that.

You might be asking “Mike, you know you can just use a SQL appender in log4net, right?”

Well, I certainly could have but what fun would that have been?

Anyways, let’s get started.

We first need a class that represents a log entry. I created the following POCO class to serve that purpose:

using System;

namespace Sandbox.Foundation.Forms.Models.Logging
{
	public class LogEntry
	{
		public Exception Exception { get; set; }

		public string LogEntryType { get; set; }

		public string LogMessage { get; set; }

		public string Message { get; set; }

		public object Owner { get; set; }

		public DateTime CreatedDate { get; set; }
	}
}

Since I hate calling the “new” keyword when creating new instances of classes, I chose to create a factory class. The following interface will be for instances of classes that create LogEntry instances:

using System;

using Sandbox.Foundation.Forms.Models.Logging;

namespace Sandbox.Foundation.Forms.Services.Factories.Diagnostics
{
	public interface ILogEntryFactory
	{
		LogEntry CreateLogEntry(string logEntryType, string message, Exception exception, Type ownerType, DateTime createdDate);
		
		LogEntry CreateLogEntry(string logEntryType, string message, Exception exception, object owner, DateTime createdDate);

		LogEntry CreateLogEntry(string logEntryType, string message, Type ownerType, DateTime createdDate);

		LogEntry CreateLogEntry(string logEntryType, string message, object owner, DateTime createdDate);
	}
}

Well, we can’t do much with just an interface. The following class implements the interface above. It creates an instance of LogEntry with the passed parameters to all methods (assuming the required parameters are passed with the proper values on them):

using System;

using Sandbox.Foundation.Forms.Models.Logging;

namespace Sandbox.Foundation.Forms.Services.Factories.Diagnostics
{
	public class LogEntryFactory : ILogEntryFactory
	{
		public LogEntry CreateLogEntry(string logEntryType, string message, Exception exception, Type ownerType, DateTime createdDate)
		{
			return CreateLogEntry(logEntryType, message, exception, ownerType, createdDate);
		}

		public LogEntry CreateLogEntry(string logEntryType, string message, Exception exception, object owner, DateTime createdDate)
		{
			if (!CanCreateLogEntry(logEntryType, message, owner, createdDate))
			{
				return null;
			}

			return new LogEntry
			{
				LogEntryType = logEntryType,
				Message = message,
				Exception = exception,
				Owner = owner,
				CreatedDate = createdDate
			};
		}

		public LogEntry CreateLogEntry(string logEntryType, string message, Type ownerType, DateTime createdDate)
		{
			return CreateLogEntry(logEntryType, message, ownerType, createdDate);
		}

		public LogEntry CreateLogEntry(string logEntryType, string message, object owner, DateTime createdDate)
		{
			if(!CanCreateLogEntry(logEntryType, message, owner, createdDate))
			{
				return null;
			}

			return new LogEntry
			{
				LogEntryType = logEntryType,
				Message = message,
				Owner = owner,
				CreatedDate = createdDate
			};
		}

		protected virtual bool CanCreateLogEntry(string logEntryType, string message, object owner, DateTime createdDate)
		{
			return !string.IsNullOrWhiteSpace(logEntryType)
				&& !string.IsNullOrWhiteSpace(message)
				&& owner != null
				&& createdDate != DateTime.MinValue
				&& createdDate != DateTime.MaxValue;
		}
	}
}

I didn’t want to send LogEntry instances directly to a repository class instance directly, so I created the following class to represent the entities which will ultimately be stored in the database:

using System;

namespace Sandbox.Foundation.Forms.Models.Logging
{
	public class RepositoryLogEntry
	{
		public string LogEntryType { get; set; }

		public string LogMessage { get; set; }

		public DateTime Created { get; set; }
	}
}

As I had done with LogEntry, I created a factory class for it. The difference here is we will be passing an instance of LogEntry to this new factory so we can create a RepositoryLogEntry instance from it.

The following interface is for factories of RepositoryLogEntry:

using System;

using Sandbox.Foundation.Forms.Models.Logging;

namespace Sandbox.Foundation.Forms.Services.Factories.Diagnostics
{
	public interface IRepositoryLogEntryFactory
	{
		RepositoryLogEntry CreateRepositoryLogEntry(LogEntry entry);
		
		RepositoryLogEntry CreateRepositoryLogEntry(string logEntryType, string logMessage, DateTime created);
	}
}

Now that we have the interface ready to go, we need an implementation class for it. The following class does the job:

using System;

using Sandbox.Foundation.Forms.Models.Logging;

namespace Sandbox.Foundation.Forms.Services.Factories.Diagnostics
{
	public class RepositoryLogEntryFactory : IRepositoryLogEntryFactory
	{
		public RepositoryLogEntry CreateRepositoryLogEntry(LogEntry entry)
		{
			return CreateRepositoryLogEntry(entry.LogEntryType, entry.LogMessage, entry.CreatedDate);
		}

		public RepositoryLogEntry CreateRepositoryLogEntry(string logEntryType, string logMessage, DateTime created)
		{
			if (!CanCreateRepositoryLogEntry(logEntryType, logMessage, created))
			{
				return null;
			}

			return new RepositoryLogEntry
			{
				LogEntryType = logEntryType,
				LogMessage = logMessage,
				Created = created
			};
		}

		protected virtual bool CanCreateRepositoryLogEntry(string logEntryType, string logMessage, DateTime created)
		{
			return !string.IsNullOrWhiteSpace(logEntryType)
					&& !string.IsNullOrWhiteSpace(logMessage)
					&& created != DateTime.MinValue
					&& created != DateTime.MaxValue;
		}
	}
}

I’m following a similiar structure here as I had done in the LogEntryFactory class above. The CanCreateRepositoryLogEntry() method ensures required parameters are passed to methods on the class. If they are not, then a null reference is returned to the caller.

Since I hate hardcoding things, I decided to create a service class that gets the newline character. The following interface is for classes that do that:

namespace Sandbox.Foundation.Forms.Services.Environment
{
	public interface IEnvironmentService
	{
		string GetNewLine();
	}
}

This next class implements the interface above:

namespace Sandbox.Foundation.Forms.Services.Environment
{
	public class EnvironmentService : IEnvironmentService
	{
		public string GetNewLine()
		{
			return System.Environment.NewLine;
		}
	}
}

In the class above, I’m taking advantage of stuff build into the .NET library for getting the newline character.

I love when I discover things like this, albeit wish I had found something like this when trying to find an html break string for something I was working on the other day, but I digress (if you know of a way, please let me know in a comment below 😉 ).

The above interface and class might seem out of place in this post but I am using them when formatting messages for the LogEntry instances further down in another service class. Just keep an eye out for it.

Since I loathe hardcoding strings with a passion, I like to hide these away in Sitecore configuration patch files and hydrate a POCO class instance with the values from the aforementioned configuration. The following class is such a POCO settings object for a service class I will discuss further down in the post:

namespace Sandbox.Foundation.Forms.Models.Logging
{
	public class LogEntryServiceSettings
	{
		public string DebugLogEntryType { get; set; }

		public string ErrorLogEntryType { get; set; }

		public string FatalLogEntryType { get; set; }

		public string InfoLogEntryType { get; set; }

		public string WarnLogEntryType { get; set; }

		public string ExceptionPrefix { get; set; }

		public string MessagePrefix { get; set; }

		public string SourcePrefix { get; set; }

		public string NestedExceptionPrefix { get; set; }

		public string LogEntryTimeFormat { get; set; }
	}
}

Okay, so need we need to know what “type” of LogEntry we are dealing with — is it an error or a warning or what? — before sending to a repository to save in the database. I created the following interface for service classes that return back strings for the different LogEntry types, and also generate a log message from the data on properties on the LogEntry instance — this is the message that will end up in the database for the LogEntry:

using Sandbox.Foundation.Forms.Models.Logging;

namespace Sandbox.Foundation.Forms.Services.Diagnostics
{
	public interface ILogEntryService
	{
		string GetDebugLogEntryType();

		string GetErrorLogEntryType();

		string GetFatalLogEntryType();

		string GetInfoLogEntryType();

		string GetWarnLogEntryType();

		string GenerateLogMessage(LogEntry entry);
	}
}

And here is its implementation class:

using System;
using System.Text;

using Sandbox.Foundation.Forms.Models.Logging;
using Sandbox.Foundation.Forms.Services.Environment;

namespace Sandbox.Foundation.Forms.Services.Diagnostics
{
	public class LogEntryService : ILogEntryService
	{
		private readonly string _newLine;
		private readonly LogEntryServiceSettings _logEntryServiceSettings;

		public LogEntryService(IEnvironmentService environmentService, LogEntryServiceSettings logEntryServiceSettings)
		{
			_newLine = GetNewLine(environmentService);
			_logEntryServiceSettings = logEntryServiceSettings;
		}

		protected virtual string GetNewLine(IEnvironmentService environmentService)
		{
			return environmentService.GetNewLine();
		}

		public string GetDebugLogEntryType()
		{
			return _logEntryServiceSettings.DebugLogEntryType;
		}

		public string GetErrorLogEntryType()
		{
			return _logEntryServiceSettings.ErrorLogEntryType;
		}

		public string GetFatalLogEntryType()
		{
			return _logEntryServiceSettings.FatalLogEntryType;
		}

		public string GetInfoLogEntryType()
		{
			return _logEntryServiceSettings.InfoLogEntryType;
		}

		public string GetWarnLogEntryType()
		{
			return _logEntryServiceSettings.WarnLogEntryType;
		}

		public string GenerateLogMessage(LogEntry entry)
		{
			if(!CanGenerateLogMessage(entry))
			{
				return string.Empty;
			}

			string exceptionMessage = GenerateExceptionMessage(entry.Exception);
			if(string.IsNullOrWhiteSpace(exceptionMessage))
			{
				return $"{entry.Message}";
			}

			return $"{entry.Message} {exceptionMessage}";
		}

		protected virtual bool CanGenerateLogMessage(LogEntry entry)
		{
			return entry != null
					&& !string.IsNullOrWhiteSpace(entry.Message)
					&& entry.Owner != null;
		}

		protected virtual string GenerateExceptionMessage(Exception exception)
		{
			if(exception == null)
			{
				return string.Empty;
			}

			StringBuilder messageBuilder = new StringBuilder();
			messageBuilder.Append(_logEntryServiceSettings.ExceptionPrefix).Append(exception.GetType().FullName); ;
			AppendNewLine(messageBuilder);
			messageBuilder.Append(_logEntryServiceSettings.MessagePrefix).Append(exception.Message);
			AppendNewLine(messageBuilder);

			if (!string.IsNullOrWhiteSpace(exception.Source))
			{
				messageBuilder.Append(_logEntryServiceSettings.SourcePrefix).Append(exception.Source);
				AppendNewLine(messageBuilder);
			}

			if(!string.IsNullOrWhiteSpace(exception.StackTrace))
			{
				messageBuilder.Append(exception.StackTrace);
				AppendNewLine(messageBuilder);
			}
			
			if (exception.InnerException != null)
			{
				AppendNewLine(messageBuilder);
				messageBuilder.Append(_logEntryServiceSettings.NestedExceptionPrefix);
				AppendNewLine(messageBuilder, 3);
				messageBuilder.Append(GenerateExceptionMessage(exception.InnerException));
				AppendNewLine(messageBuilder);
			}
			
			return messageBuilder.ToString();
		}

		protected virtual void AppendNewLine(StringBuilder builder, int repeatCount = 1)
		{
			AppendRepeat(builder, _newLine, repeatCount);
		}

		protected virtual void AppendRepeat(StringBuilder builder, string stringToAppend, int repeatCount)
		{
			if (builder == null || string.IsNullOrWhiteSpace(stringToAppend) || repeatCount < 1)
			{
				return;
			}

			for(int i = 0; i < repeatCount; i++)
			{
				builder.Append(stringToAppend);
			}
		}
	}
}

I’m not going to discuss all the code in the above class as it should be self-explanatory.

I do want to point out GenerateLogMessage() will generate one of two strings, depending on whether an Exception was set on the LogEntry instance.

If an Exception was set, we append the Exception details — the GenerateExceptionMessage() method generates a string from the Exception — onto the end of the LogEntry message

If it was not set, we just return the LogEntry message to the caller.

Well, now we need a place to store the log entries. I used the following SQL script to create a new table for storing these:

USE [ExperienceFormsSettings]
GO

CREATE TABLE [dbo].[ExperienceFormsLog](
	[ID] [uniqueidentifier] NOT NULL,
	[LogEntryType] [nvarchar](max) NOT NULL,
	[LogMessage] [nvarchar](max) NOT NULL,
	[Created] [datetime] NOT NULL,
 CONSTRAINT [PK_ExperienceFormsLog] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[ExperienceFormsLog] ADD  DEFAULT (newsequentialid()) FOR [ID]
GO

I also sprinkled some magical database dust onto the table:

😉

Wonderful, we now can move on to the fun bit — actually writing some code to store these entries into the database table created from the SQL script above.

I wrote the following POCO class to represent a SQL command — either a query or statement (it really doesn’t matter as it will support both):

namespace Sandbox.Foundation.Forms.Models.Logging
{
	public class SqlCommand
	{
		public string Sql { get; set; }

		public object[] Parameters { get; set; }
	}
}

I’m sure I could have found something in Sitecore.Kernel.dll that does exactly what the class above does but I couldn’t find such a thing (if you know of such a class, please share in a comment below).

Now we need a settings class for the SQL Logger I am writing further down in this post. As I had done for the LogEntryService class above, this data will be coming from Sitecore configuration:

namespace Sandbox.Foundation.Forms.Models.Logging
{
	public class SqlLoggerSettings
	{
		public string LogPrefix { get; set; }

		public string LogDatabaseConnectionStringName { get; set; }

		public string InsertLogEntrySqlFormat { get; set; }

		public string ConnectionStringNameColumnName { get; set; }

		public string FieldsPrefixColumnName { get; set; }

		public string FieldsIndexNameColumnName { get; set; }

		public int NotFoundOrdinal { get; set; }

		public string LogEntryTypeParameterName { get; set; }

		public string LogMessageParameterName { get; set; }

		public string CreatedParameterName { get; set; }
	}
}

Now the fun part — creating an implementation of Sitecore.ExperienceForms.Diagnostics.ILogger:

using System;

using Sitecore.Abstractions;
using Sitecore.Data.DataProviders.Sql;
using Sitecore.ExperienceForms.Diagnostics;

using Sandbox.Foundation.Forms.Services.Factories;
using Sandbox.Foundation.Forms.Models.Logging;
using Sandbox.Foundation.Forms.Services.Factories.Diagnostics;

namespace Sandbox.Foundation.Forms.Services.Diagnostics
{
	public class SqlLogger : ILogger
	{
		private readonly SqlLoggerSettings _sqlLoggerSettings;
		private readonly BaseSettings _settings;
		private readonly BaseFactory _factory;
		private readonly SqlDataApi _sqlDataApi;
		private readonly ILogEntryFactory _logEntryFactory;
		private readonly ILogEntryService _logEntryService;
		private readonly IRepositoryLogEntryFactory _repositoryLogEntryFactory;

		public SqlLogger(SqlLoggerSettings sqlLoggerSettings, BaseSettings settings, BaseFactory factory, ISqlDataApiFactory sqlDataApiFactory, ILogEntryFactory logEntryFactory, IRepositoryLogEntryFactory repositoryLogEntryFactory, ILogEntryService logEntryService)
		{
			_sqlLoggerSettings = sqlLoggerSettings;
			_settings = settings;
			_factory = factory;
			_sqlDataApi = CreateSqlDataApi(sqlDataApiFactory);
			_logEntryFactory = logEntryFactory;
			_logEntryService = logEntryService;
			_repositoryLogEntryFactory = repositoryLogEntryFactory;
		}

		protected virtual SqlDataApi CreateSqlDataApi(ISqlDataApiFactory sqlDataApiFactory)
		{
			return sqlDataApiFactory.CreateSqlDataApi(GetLogDatabaseConnectionString());
		}

		protected virtual string GetLogDatabaseConnectionString()
		{
			return _settings.GetConnectionString(GetLogDatabaseConnectionStringName());
		}

		protected virtual string GetLogDatabaseConnectionStringName()
		{
			return _sqlLoggerSettings.LogDatabaseConnectionStringName;
		}

		public void Debug(string message)
		{
			Debug(message, GetDefaultOwner());
		}

		public void Debug(string message, object owner)
		{
			SaveLogEntry(CreateLogEntry(GetDebugLogEntryType(), message, owner, GetLogEntryDateTime()));
		}

		protected virtual string GetDebugLogEntryType()
		{
			return _logEntryService.GetDebugLogEntryType();
		}

		public void LogError(string message)
		{
			LogError(message, null, GetDefaultOwner());
		}

		public void LogError(string message, object owner)
		{
			LogError(message, null, owner);
		}

		public void LogError(string message, Exception exception, Type ownerType)
		{
			LogError(message, exception, (object)ownerType);
		}

		public void LogError(string message, Exception exception, object owner)
		{
			SaveLogEntry(CreateLogEntry(GetErrorLogEntryType(), message, exception, owner, GetLogEntryDateTime()));
		}

		protected virtual string GetErrorLogEntryType()
		{
			return _logEntryService.GetErrorLogEntryType();
		}

		public void Fatal(string message)
		{
			Fatal(message, null, GetDefaultOwner());
		}

		public void Fatal(string message, object owner)
		{
			Fatal(message, null, owner);
		}

		public void Fatal(string message, Exception exception, Type ownerType)
		{
			Fatal(message, exception, (object)ownerType);
		}

		public void Fatal(string message, Exception exception, object owner)
		{
			SaveLogEntry(CreateLogEntry(GetFatalLogEntryType(), message, exception, owner, GetLogEntryDateTime()));
		}

		protected virtual string GetFatalLogEntryType()
		{
			return _logEntryService.GetFatalLogEntryType();
		}

		public void Info(string message)
		{
			Info(message, GetDefaultOwner());
		}

		public void Info(string message, object owner)
		{
			SaveLogEntry(CreateLogEntry(GetInfoLogEntryType(), message, owner, GetLogEntryDateTime()));
		}

		protected virtual string GetInfoLogEntryType()
		{
			return _logEntryService.GetInfoLogEntryType();
		}

		public void Warn(string message)
		{
			Warn(message, GetDefaultOwner());
		}

		public void Warn(string message, object owner)
		{
			SaveLogEntry(CreateLogEntry(GetWarnLogEntryType(), message, owner, GetLogEntryDateTime()));
		}

		protected virtual string AddPrefixToMessage(string message)
		{
			return string.Concat(_sqlLoggerSettings.LogPrefix, message);
		}

		protected virtual object GetDefaultOwner()
		{
			return this;
		}

		protected virtual LogEntry CreateLogEntry(string logEntryType, string message, Exception exception, Type ownerType, DateTime createdDate)
		{
			return _logEntryFactory.CreateLogEntry(logEntryType, message, exception, ownerType, createdDate);
		}

		protected virtual LogEntry CreateLogEntry(string logEntryType, string message, Exception exception, object owner, DateTime createdDate)
		{
			return _logEntryFactory.CreateLogEntry(logEntryType, message, exception, owner, createdDate);
		}

		protected virtual LogEntry CreateLogEntry(string logEntryType, string message, Type ownerType, DateTime createdDate)
		{
			return _logEntryFactory.CreateLogEntry(logEntryType, message, ownerType, createdDate);
		}

		protected virtual LogEntry CreateLogEntry(string logEntryType, string message, object owner, DateTime createdDate)
		{
			return _logEntryFactory.CreateLogEntry(logEntryType, message, owner, createdDate);
		}

		protected virtual string GetWarnLogEntryType()
		{
			return _logEntryService.GetWarnLogEntryType();
		}

		protected virtual DateTime GetLogEntryDateTime()
		{
			return DateTime.Now.ToUniversalTime();
		}

		protected virtual void SaveLogEntry(LogEntry entry)
		{
			if (entry == null)
			{
				return;
			}

			entry.LogMessage = _logEntryService.GenerateLogMessage(entry);

			RepositoryLogEntry repositoryEntry = CreateRepositoryLogEntry(entry);
			if (repositoryEntry == null)
			{
				return;
			}

			SaveRepositoryLogEntry(repositoryEntry);
		}

		protected virtual string GenerateLogMessage(LogEntry entry)
		{
			return _logEntryService.GenerateLogMessage(entry);
		}

		protected virtual RepositoryLogEntry CreateRepositoryLogEntry(LogEntry entry)
		{
			return _repositoryLogEntryFactory.CreateRepositoryLogEntry(entry);
		}

		protected virtual void SaveRepositoryLogEntry(RepositoryLogEntry entry)
		{
			if(!CanLogEntry(entry))
			{
				return;
			}

			SqlCommand insertCommand = GetinsertCommand(entry);
			if(insertCommand == null)
			{
				return;
			}

			ExecuteNoResult(insertCommand);
		}

		protected virtual bool CanLogEntry(RepositoryLogEntry entry)
		{
			return entry != null
					&& !string.IsNullOrWhiteSpace(entry.LogEntryType)
					&& !string.IsNullOrWhiteSpace(entry.LogMessage)
					&& entry.Created > DateTime.MinValue
					&& entry.Created < DateTime.MaxValue;
		}

		protected virtual SqlCommand GetinsertCommand(RepositoryLogEntry entry)
		{
			return new SqlCommand
			{
				Sql = GetInsertLogEntrySql(),
				Parameters = GetinsertCommandParameters(entry)
			};
		}

		protected virtual object[] GetinsertCommandParameters(RepositoryLogEntry entry)
		{
			return new object[]
			{
				GetLogEntryTypeParameterName(),
				entry.LogEntryType,
				GetLogMessageParameterName(),
				entry.LogMessage,
				GetCreatedParameterName(),
				entry.Created
			};
		}

		protected virtual string GetLogEntryTypeParameterName()
		{
			return _sqlLoggerSettings.LogEntryTypeParameterName;
		}

		protected virtual string GetLogMessageParameterName()
		{
			return _sqlLoggerSettings.LogMessageParameterName;
		}

		protected virtual string GetCreatedParameterName()
		{
			return _sqlLoggerSettings.CreatedParameterName;
		}

		protected virtual string GetInsertLogEntrySql()
		{
			return _sqlLoggerSettings.InsertLogEntrySqlFormat;
		}

		protected virtual void ExecuteNoResult(SqlCommand sqlCommand)
		{
			_factory.GetRetryer().ExecuteNoResult(() => { _sqlDataApi.Execute(sqlCommand.Sql, sqlCommand.Parameters); });
		}
	}
}

Since there is a lot of code in the class above, I’m not going to talk about all of it — it should be clear on what this class is doing for the most part.

I do want to highlight that the SaveRepositoryLogEntry() method takes in a RepositoryLogEntry instance; builds up a SqlCommand instance from it as well as the insert SQL statement and parameters from the SqlLoggerSettings instance (these are coming from Sitecore configuration, and there are hooks on this class to allow for overriding these if needed); and passes the SqlCommand instance to the ExecuteNoResult() method which uses the SqlDataApi instance for saving to the database. Plus, I’m leveraging an “out of the box” “retryer” from the Sitecore.Kernel.dll to ensure it makes its way into the database table.

Moreover, I’m reusing the ISqlDataApiFactory instance above from my previous post. Have a read of it so you can see what this factory class does.

Since Experience Forms was built perfectly — 😉 — I couldn’t see any LogEntry instances being saved to my database right away. So went ahead and created some <forms.renderField> pipeline processors to capture some.

The following interface is for a <forms.renderField> pipeline processor to just throw an exception by dividing by zero:

using Sitecore.ExperienceForms.Mvc.Pipelines.RenderField;

namespace Sandbox.Foundation.Forms.Pipelines.RenderField
{
	public interface IThrowExceptionProcessor
	{
		void Process(RenderFieldEventArgs args);
	}
}

Here is its implementation class:

using System;

using Sitecore.ExperienceForms.Diagnostics;
using Sitecore.ExperienceForms.Mvc.Pipelines.RenderField;

namespace Sandbox.Foundation.Forms.Pipelines.RenderField
{
	public class ThrowExceptionProcessor : IThrowExceptionProcessor
	{
		private readonly ILogger _logger;

		public ThrowExceptionProcessor(ILogger logger)
		{
			_logger = logger;
		}

		public void Process(RenderFieldEventArgs args)
		{
			try
			{
				int i = 1 / GetZero();
			}
			catch(Exception ex)
			{
				_logger.LogError(ToString(), ex, this);
			}
		}

		private int GetZero()
		{
			return 0;
		}
	}
}

I’m sure you would never do such a thing, right? 😉

I then created the following interface for another <forms.renderField> pipeline processor to log some information on the RenderFieldEventArgs instance sent to the Process() method:

using Sitecore.ExperienceForms.Mvc.Pipelines.RenderField;

namespace Sandbox.Foundation.Forms.Pipelines.RenderField
{
	public interface ILogRenderedFieldInfo
	{
		void Process(RenderFieldEventArgs args);
	}
}

Here is the implementation class for this:

using Sitecore.ExperienceForms.Diagnostics;
using Sitecore.ExperienceForms.Mvc.Pipelines.RenderField;
using Sitecore.Mvc.Pipelines;

namespace Sandbox.Foundation.Forms.Pipelines.RenderField
{
	public class LogRenderedFieldInfo : MvcPipelineProcessor<RenderFieldEventArgs>, ILogRenderedFieldInfo
	{
		private readonly ILogger _logger;

		public LogRenderedFieldInfo(ILogger logger)
		{
			_logger = logger;
		}

		public override void Process(RenderFieldEventArgs args)
		{
			LogInfo($"ViewModel Details:\n\nName: {args.ViewModel.Name}, ItemId: {args.ViewModel.ItemId}, TemplateId: {args.ViewModel.TemplateId}, FieldTypeItemId: {args.ViewModel.FieldTypeItemId}");
			LogInfo($"RenderingSettings Details\n\nFieldTypeName: {args.RenderingSettings.FieldTypeName}, FieldTypeId: {args.RenderingSettings.FieldTypeId}, FieldTypeIcon: {args.RenderingSettings.FieldTypeIcon}, FieldTypeDisplayName: {args.RenderingSettings.FieldTypeDisplayName}, FieldTypeBackgroundColor: {args.RenderingSettings.FieldTypeBackgroundColor}");
			LogInfo($"Item Details: {args.Item.ID}, Name: {args.Item.Name} FullPath: {args.Item.Paths.FullPath}, TemplateID: {args.Item.TemplateID}");
		}

		protected virtual void LogInfo(string message)
		{
			if(string.IsNullOrWhiteSpace(message))
			{
				return;
			}

			_logger.Info(message);
		}
	}
}

I then registered everything in the Sitecore IoC container using the following configurator:

using System;

using Microsoft.Extensions.DependencyInjection;

using Sitecore.Abstractions;
using Sitecore.DependencyInjection;
using Sitecore.ExperienceForms.Diagnostics;

using Sandbox.Foundation.Forms.Services.Factories.Diagnostics;
using Sandbox.Foundation.Forms.Services.Factories;
using Sandbox.Foundation.Forms.Models.Logging;
using Sandbox.Foundation.Forms.Services.Environment;
using Sandbox.Foundation.Forms.Services.Diagnostics;
using Sandbox.Foundation.Forms.Pipelines.RenderField;

namespace Sandbox.Foundation.Forms
{
	public class SqlLoggerConfigurator : IServicesConfigurator
	{
		public void Configure(IServiceCollection serviceCollection)
		{
			ConfigureConfigObjects(serviceCollection);
			ConfigureFactories(serviceCollection);
			ConfigureServices(serviceCollection);
			ConfigurePipelineProcessors(serviceCollection);
		}

		private void ConfigureConfigObjects(IServiceCollection serviceCollection)
		{
			serviceCollection.AddSingleton(provider => GetLogEntryServiceSettings(provider));
			serviceCollection.AddSingleton(provider => GetSqlLoggerSettings(provider));
		}

		private LogEntryServiceSettings GetLogEntryServiceSettings(IServiceProvider provider)
		{
			return CreateConfigObject<LogEntryServiceSettings>(provider, "moduleSettings/foundation/forms/logEntryServiceSettings");
		}

		private SqlLoggerSettings GetSqlLoggerSettings(IServiceProvider provider)
		{
			return CreateConfigObject<SqlLoggerSettings>(provider, "moduleSettings/foundation/forms/sqlLoggerSettings");
		}

		private TConfigObject CreateConfigObject<TConfigObject>(IServiceProvider provider, string path) where TConfigObject : class
		{
			BaseFactory factory = GetService<BaseFactory>(provider);
			return factory.CreateObject(path, true) as TConfigObject;
		}

		private TService GetService<TService>(IServiceProvider provider)
		{
			return provider.GetService<TService>();
		}

		private void ConfigureFactories(IServiceCollection serviceCollection)
		{
			serviceCollection.AddSingleton<ILogEntryFactory, LogEntryFactory>();
			serviceCollection.AddSingleton<IRepositoryLogEntryFactory, RepositoryLogEntryFactory>();
			serviceCollection.AddSingleton<ISqlDataApiFactory, SqlDataApiFactory>();
		}

		private void ConfigureServices(IServiceCollection serviceCollection)
		{
			serviceCollection.AddSingleton<IEnvironmentService, EnvironmentService>();
			serviceCollection.AddSingleton<ILogEntryService, LogEntryService>();
			serviceCollection.AddSingleton<ILogger, SqlLogger>();
		}

		private void ConfigurePipelineProcessors(IServiceCollection serviceCollection)
		{
			serviceCollection.AddSingleton<ILogRenderedFieldInfo, LogRenderedFieldInfo>();
			serviceCollection.AddSingleton<IThrowExceptionProcessor, ThrowExceptionProcessor>();
		}
	}
}

Note: the GetLogEntryServiceSettings() and the GetSqlLoggerSettings() methods both create settings objects by using the Sitecore Configuration Factory. Ultimately, these settings objects are thrown into the container so they can be injected into the service classes that need them.

I then strung everything together using the following the Sitecore patch configuration file.

<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
	<sitecore>
		<pipelines>
			<forms.renderField>
				<processor type="Sandbox.Foundation.Forms.Pipelines.RenderField.LogRenderedFieldInfo, Sandbox.Foundation.Forms" resolve="true"/>
				<processor type="Sandbox.Foundation.Forms.Pipelines.RenderField.ThrowExceptionProcessor, Sandbox.Foundation.Forms" resolve="true"/>
			</forms.renderField>
		</pipelines>
		<services>
			<configurator type="Sandbox.Foundation.Forms.SqlLoggerConfigurator, Sandbox.Foundation.Forms" />
			<register serviceType="Sitecore.ExperienceForms.Diagnostics.ILogger, Sitecore.ExperienceForms">
				<patch:delete />
			</register>
		</services>

		<moduleSettings>
			<foundation>
				<forms>
					<logEntryServiceSettings type="Sandbox.Foundation.Forms.Models.Logging.LogEntryServiceSettings, Sandbox.Foundation.Forms" singleInstance="true">
						<DebugLogEntryType>DEBUG</DebugLogEntryType>
						<ErrorLogEntryType>ERROR</ErrorLogEntryType>
						<FatalLogEntryType>FATAL</FatalLogEntryType>
						<InfoLogEntryType>INFO</InfoLogEntryType>
						<WarnLogEntryType>WARN</WarnLogEntryType>
						<ExceptionPrefix>Exception: </ExceptionPrefix>
						<MessagePrefix>Message: </MessagePrefix>
						<SourcePrefix>Source: </SourcePrefix>
						<NestedExceptionPrefix>Nested Exception</NestedExceptionPrefix>
						<LogEntryTimeFormat>HH:mm:ss.ff</LogEntryTimeFormat>
					</logEntryServiceSettings>
					<sqlLoggerSettings type="Sandbox.Foundation.Forms.Models.Logging.SqlLoggerSettings, Sandbox.Foundation.Forms" singleInstance="true">
						<LogPrefix>[Experience Forms]:</LogPrefix>
						<LogDatabaseConnectionStringName>ExperienceFormsSettings</LogDatabaseConnectionStringName>
						<InsertLogEntrySqlFormat>INSERT INTO {0}ExperienceFormsLog{1}({0}LogEntryType{1},{0}LogMessage{1},{0}Created{1})VALUES({2}logEntryType{3},{2}logMessage{3},{2}created{3});</InsertLogEntrySqlFormat>
						<LogEntryTypeParameterName>logEntryType</LogEntryTypeParameterName>
						<LogMessageParameterName>logMessage</LogMessageParameterName>
						<CreatedParameterName>created</CreatedParameterName>
					</sqlLoggerSettings>
				</forms>
			</foundation>
		</moduleSettings>
	</sitecore>
</configuration> 

Ok, let’s take this for a spin.

After building and deploying everything above, I spun up my Sitecore instance:

I then navigated to a form I had created in a previous post:

After the page with my form was done loading, I ran a query on my custom log table and saw this:

As you can see, it worked.

If you have any questions or comments, don’t hesitate to drop these in a comment below.

Until next time, have yourself a Sitecoretastic day!

Advertisements

Grab Sitecore Experience Forms Configuration Settings from a Custom SQL Server Database Table

Just the other day, I poking around Sitecore Experience Forms to see what’s customisable and have pretty much concluded virtually everything is.

morpheus

How?

Just have a look at http://[instance]/sitecore/admin/showservicesconfig.aspx of your Sitecore instance and scan for “ExperienceForms”. You will also discover lots of its service class are registered in Sitecore’s IoC container — such makes it easy to swap things out with your own service implementation classes as long as they implement those service types defined in the container.

Since I love to tinkering with all things in Sitecore — most especially when it comes to customising bits of it — I decided to have a crack at replacing IFormsConfigurationSettings — more specifically Sitecore.ExperienceForms.Configuration.IFormsConfigurationSettings in Sitecore.ExperienceForms.dll which represents Sitecore configuration settings for Experience Forms — as it appeared to be something simple enough to do.

The IFormsConfigurationSettings interface represents the following configuration settings:

config-settings-ootb

So, what did I do to customise it?

I wrote a bunch of code — it’s all in this post — which pulls these settings from a custom SQL Server database table.

in-da-db.gif

Why did I do that? Well, I did it because I could. 😉

Years ago, long before my Sitecore days, I worked on ASP.NET Web Applications which had their configuration settings stored in SQL Server databases. Whether this was, or still is, a good idea is a discussion for another time though you are welcome to drop a comment below with your thoughts.

However, for the meantime, just roll with it as the point of this post is to show that you can customise virtually everything in Experience Forms, and I’m just showing one example.

I first created the following class which implements IFormsConfigurationSettings:

using Sitecore.ExperienceForms.Configuration;

namespace Sandbox.Foundation.Forms.Models.Configuration
{
	public class SandboxFormsConfigurationSettings : IFormsConfigurationSettings
	{
		public string ConnectionStringName { get; set; }

		public string FieldsPrefix { get; set; }

		public string FieldsIndexName { get; set; }
	}
}

You might be asking “Mike, why did you create an implementation class when Experience Forms already provides one ‘out of the box’?”

Well, all the properties defined in Sitecore.ExperienceForms.Configuration.IFormsConfigurationSettings — these are the same properties that you see in the implementation class above — lack mutators on them in the interface. My implementation class of IFormsConfigurationSettings adds them in — I hate baking method calls in property accessors as it doesn’t seem clean to me. ¯\_(ツ)_/¯

When I had a look at the “out of the box” implementation class of IFormsConfigurationSettings, I discovered direct calls to the GetSetting() method on the Sitecore.Configuration.Settings static class — this lives in Sitecore.Kernel.dll — but that doesn’t help me with setting those properties, hence the custom IFormsConfigurationSettings class above.

Next, I used the following SQL script to create my custom settings database table:

USE [ExperienceFormsSettings]
GO

CREATE TABLE [dbo].[FormsConfigurationSettings](
	[FieldsPrefix] [nvarchar](max) NULL,
	[FieldsIndexName] [nvarchar](max) NULL,
	[ConnectionStringName] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I then inserted the settings from the configuration file snapshot above into my new table (I’ve omitted the SQL insert statement for this):

config-database

Now we need a way to retrieve these settings from the database. The following interface will be for factory classes which create instances of Sitecore.Data.DataProviders.Sql.SqlDataApi — this lives in Sitecore.Kernel.dll — with the passed connection strings:

using Sitecore.Data.DataProviders.Sql;

namespace Sandbox.Foundation.Forms.Services.Factories
{
	public interface ISqlDataApiFactory
	{
		SqlDataApi CreateSqlDataApi(string connectionString);
	}
}

Well, we can’t do much with an interface without having some class implement it. The following class implements the interface above:

using Sitecore.Data.DataProviders.Sql;
using Sitecore.Data.SqlServer;

namespace Sandbox.Foundation.Forms.Services.Factories
{
	public class SqlDataApiFactory : ISqlDataApiFactory
	{
		public SqlDataApi CreateSqlDataApi(string connectionString)
		{
			if(string.IsNullOrWhiteSpace(connectionString))
			{
				return null;
			}

			return new SqlServerDataApi(connectionString);
		}
	}
}

It’s just creating an instance of the SqlServerDataApi class. Nothing special about it at all.

Ironically, I do have to save my own configuration settings in Sitecore Configuration — this would include the the connection string name to the database that contains my new table as well as a few other things.

An instance of the following class will contain these settings — have a look at /sitecore/moduleSettings/foundation/forms/repositorySettings in the Sitecore patch file near the bottom of this post but be sure to come back up here when you are finished 😉 — and this instance will be put into the Sitecore IoC container so it can be injected in an instance of a class I’ll talk about further down in this post:

namespace Sandbox.Foundation.Forms.Models.Configuration
{
	public class RepositorySettings
	{
		public string ConnectionStringName { get; set; }

		public string GetSettingsSql { get; set; }

		public string ConnectionStringNameColumnName { get; set; }

		public string FieldsPrefixColumnName { get; set; }

		public string FieldsIndexNameColumnName { get; set; }

		public int NotFoundOrdinal { get; set; }
	}
}

I then defined the following interface for repository classes which retrieve IFormsConfigurationSettings instances:

using Sitecore.ExperienceForms.Configuration;

namespace Sandbox.Foundation.Forms.Repositories.Configuration
{
	public interface ISettingsRepository
	{
		IFormsConfigurationSettings GetFormsConfigurationSettings();
	}
}

Here’s the implementation class for the interface above:

using System;
using System.Data;
using System.Linq;

using Sitecore.Abstractions;
using Sitecore.Data.DataProviders.Sql;
using Sitecore.ExperienceForms.Configuration;
using Sitecore.ExperienceForms.Diagnostics;

using Sandbox.Foundation.Forms.Models.Configuration;
using Sandbox.Foundation.Forms.Services.Factories;

namespace Sandbox.Foundation.Forms.Repositories.Configuration
{
	public class SettingsRepository : ISettingsRepository
	{
		private readonly RepositorySettings _repositorySettings;
		private readonly BaseSettings _settings;
		private readonly int _notFoundOrdinal;
		private readonly ILogger _logger;
		private readonly SqlDataApi _sqlDataApi;

		public SettingsRepository(RepositorySettings repositorySettings, BaseSettings settings, ILogger logger, ISqlDataApiFactory sqlDataApiFactory)
		{
			_repositorySettings = repositorySettings;
			_settings = settings;
			_notFoundOrdinal = GetNotFoundOrdinal();
			_logger = logger;
			_sqlDataApi = GetSqlDataApi(sqlDataApiFactory);
		}

		protected virtual SqlDataApi GetSqlDataApi(ISqlDataApiFactory sqlDataApiFactory)
		{
			return sqlDataApiFactory.CreateSqlDataApi(GetConnectionString());
		}

		protected virtual string GetConnectionString()
		{
			return _settings.GetConnectionString(GetConnectionStringName());
		}

		protected virtual string GetConnectionStringName()
		{
			return _repositorySettings.ConnectionStringName;
		}

		public IFormsConfigurationSettings GetFormsConfigurationSettings()
		{
			try
			{
				return _sqlDataApi.CreateObjectReader(GetSqlQuery(), GetParameters(), GetMaterializer()).FirstOrDefault();
			}
			catch (Exception ex)
			{
				LogError(ex);
				
			}

			return CreateFormsConfigurationSettingsNullObject();
		}

		protected virtual string GetSqlQuery()
		{
			return _repositorySettings.GetSettingsSql;
		}

		protected virtual object[] GetParameters()
		{
			return Enumerable.Empty<object>().ToArray();
		}

		protected virtual Func<IDataReader, IFormsConfigurationSettings> GetMaterializer()
		{
			return new Func<IDataReader, IFormsConfigurationSettings>(ParseFormsConfigurationSettings);
		}

		protected virtual IFormsConfigurationSettings ParseFormsConfigurationSettings(IDataReader dataReader)
		{
			return new SandboxFormsConfigurationSettings
			{
				ConnectionStringName = GetString(dataReader, GetConnectionStringNameColumnName()),
				FieldsPrefix = GetString(dataReader, GetFieldsPrefixColumnName()),
				FieldsIndexName = GetString(dataReader, GetFieldsIndexNameColumnName())
			};
		}

		protected virtual string GetString(IDataReader dataReader, string columnName)
		{
			if(dataReader == null || string.IsNullOrWhiteSpace(columnName))
			{
				return string.Empty;

			}

			int ordinal = GetOrdinal(dataReader, columnName);
			if(ordinal == _notFoundOrdinal)
			{
				return string.Empty;
			}

			return dataReader.GetString(ordinal);
		}

		protected virtual int GetOrdinal(IDataReader dataReader, string columnName)
		{
			if(dataReader == null || string.IsNullOrWhiteSpace(columnName))
			{
				return _notFoundOrdinal;
			}

			try
			{
				return dataReader.GetOrdinal(columnName);
			}
			catch(IndexOutOfRangeException)
			{
				return _notFoundOrdinal;
			}
		}

		protected virtual int GetNotFoundOrdinal()
		{
			return _repositorySettings.NotFoundOrdinal;
		}

		protected virtual void LogError(Exception exception)
		{
			_logger.LogError(ToString(), exception, this);
		}

		protected virtual string GetConnectionStringNameColumnName()
		{
			return _repositorySettings.ConnectionStringNameColumnName;
		}

		protected virtual string GetFieldsPrefixColumnName()
		{
			return _repositorySettings.FieldsPrefixColumnName;
		}

		protected virtual string GetFieldsIndexNameColumnName()
		{
			return _repositorySettings.FieldsIndexNameColumnName;
		}

		protected virtual IFormsConfigurationSettings CreateFormsConfigurationSettingsNullObject()
		{
			return new SandboxFormsConfigurationSettings
			{
				ConnectionStringName = string.Empty,
				FieldsIndexName = string.Empty,
				FieldsPrefix = string.Empty
			};
		}
	}
}

I’m not going to go into details of all the code above but will talk about some important pieces.

The GetFormsConfigurationSettings() method above creates an instance of the IFormsConfigurationSettings instance using the SqlDataApi instance created from the injected factory service — this was defined above — with the SQL query provided from configuration along with the GetMaterializer() method which just uses the ParseFormsConfigurationSettings() method to create an instance of the IFormsConfigurationSettings by grabbing data from the IDataReader instance.

Phew, I’m out of breath as that was a mouthful. 😉

I then registered all of my service classes above in the Sitecore IoC container using the following the configurator — aka a class that implements the IServicesConfigurator interface:

using System;

using Microsoft.Extensions.DependencyInjection;

using Sitecore.Abstractions;
using Sitecore.DependencyInjection;
using Sitecore.ExperienceForms.Configuration;

using Sandbox.Foundation.Forms.Repositories.Configuration;
using Sandbox.Foundation.Forms.Models.Configuration;

using Sandbox.Foundation.Forms.Services.Factories;

namespace Sandbox.Foundation.Forms
{
	public class SettingsConfigurator : IServicesConfigurator
	{
		public void Configure(IServiceCollection serviceCollection)
		{
			serviceCollection.AddSingleton(provider => GetRepositorySettings(provider));
			serviceCollection.AddSingleton<ISqlDataApiFactory, SqlDataApiFactory>();
			serviceCollection.AddSingleton<ISettingsRepository, SettingsRepository>();
			serviceCollection.AddSingleton(provider => GetFormsConfigurationSettings(provider));
		}

		private RepositorySettings GetRepositorySettings(IServiceProvider provider)
		{
			return CreateConfigObject<RepositorySettings>(provider, "moduleSettings/foundation/forms/repositorySettings");
		}

		private TConfigObject CreateConfigObject<TConfigObject>(IServiceProvider provider, string path) where TConfigObject : class
		{
			BaseFactory factory = GetService<BaseFactory>(provider);
			return factory.CreateObject(path, true) as TConfigObject;
		}

		private IFormsConfigurationSettings GetFormsConfigurationSettings(IServiceProvider provider)
		{
			ISettingsRepository repository = GetService<ISettingsRepository>(provider);
			return repository.GetFormsConfigurationSettings();
		}

		private TService GetService<TService>(IServiceProvider provider)
		{
			return provider.GetService<TService>();
		}
	}
}

One thing to note is the GetRepositorySettings() method above uses the Configuration Factory — this is represented by the BaseFactory abstract class which lives in the Sitecore IoC container “out of the box” — to create an instance of the RepositorySettings class, defined further up in this post, using the settings in the following Sitecore patch file:

<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
	<sitecore>
		<services>
			<configurator type="Sandbox.Foundation.Forms.SettingsConfigurator, Sandbox.Foundation.Forms" />
			<register serviceType="Sitecore.ExperienceForms.Configuration.IFormsConfigurationSettings, Sitecore.ExperienceForms">
				<patch:delete />
			</register>
		</services>

		<moduleSettings>
			<foundation>
				<forms>
					<repositorySettings type="Sandbox.Foundation.Forms.Models.Configuration.RepositorySettings, Sandbox.Foundation.Forms" singleInstance="true">
						<ConnectionStringName>ExperienceFormsSettings</ConnectionStringName>
						<GetSettingsSql>SELECT TOP (1) {0}ConnectionStringName{1},{0}FieldsPrefix{1},{0}FieldsIndexName{1} FROM {0}FormsConfigurationSettings{1}</GetSettingsSql>
						<ConnectionStringNameColumnName>ConnectionStringName</ConnectionStringNameColumnName>
						<FieldsPrefixColumnName>FieldsPrefix</FieldsPrefixColumnName>
						<FieldsIndexNameColumnName>FieldsIndexName</FieldsIndexNameColumnName>
						<NotFoundOrdinal>-1</NotFoundOrdinal> 
					</repositorySettings>
				</forms>
			</foundation>
		</moduleSettings>
	</sitecore>
</configuration> 

I want to point out that I’m deleting the Sitecore.ExperienceForms.Configuration.IFormsConfigurationSettings service from Sitecore’s configuration as I am adding it back in to the Sitecore IoC container with my own via the configurator above.

After deploying everything, I waited for my Sitecore instance to reload.

jones-testing.gif

Once Sitecore was responsive again, I navigated to “Forms” on the Sitecore Launch Pad and made sure everything had still worked as before.

It did.

Trust me, it did. 😉

living-in-db

If you have any questions/comments on any of the above, or would just like to drop a line to say “hello”, then please share in a comment below.

Otherwise, until next time, keep on Sitecoring.

Encrypt Sitecore Experience Forms Data in Powerful Ways

Last week, I was honoured to co-present Sitecore Experience Forms alongside my dear friend — and fellow trollster 😉 — Sitecore MVP Kamruz Jaman at SUGCON EU 2018. We had a blast showing the ins and outs of Experience Forms, and of course trolled a bit whilst on the main stage.

During our talk, Kamruz had mentioned the possibility of replacing the “Out of the Box” (OOTB) Sitecore.ExperienceForms.Data.IFormDataProvider — this lives in Sitecore.ExperienceForms.dll — whose class implementations serve as Repository objects for storing or retrieving from some datastore (in Experience Forms this is MS SQL Server OOTB) with another to encrypt/decrypt data when saving to or retrieving from the datastore.

Well, I had done something exactly like this for Web Forms for Marketers (WFFM) about five years ago — be sure to have a read my blog post on this before proceeding as it gives context to the rest of this blog post — so thought it would be appropriate for me to have a swing at doing this for Experience Forms.

I first created an interface for classes that will encrypt/decrypt strings — this is virtually the same interface I had used in my older post on encrypting data in WFFM:

namespace Sandbox.Foundation.Forms.Services.Encryption
{
	public interface IEncryptor
	{
		string Encrypt(string key, string input);

		string Decrypt(string key, string input);
	}
}

I then created a class to encrypt/decrypt strings using the RC2 encryption algorithm — I had also poached this from my older post on encrypting data in WFFM (please note, this encryption algorithm is not the most robust so do not use this in any production environment. Please be sure to use something more robust):

using System.Text;
using System.Security.Cryptography;

namespace Sandbox.Foundation.Forms.Services.Encryption
{
	public class RC2Encryptor : IEncryptor
	{
		public string Encrypt(string key, string input)
		{
			byte[] inputArray = UTF8Encoding.UTF8.GetBytes(input);
			RC2CryptoServiceProvider rc2 = new RC2CryptoServiceProvider();
			rc2.Key = UTF8Encoding.UTF8.GetBytes(key);
			rc2.Mode = CipherMode.ECB;
			rc2.Padding = PaddingMode.PKCS7;
			ICryptoTransform cTransform = rc2.CreateEncryptor();
			byte[] resultArray = cTransform.TransformFinalBlock(inputArray, 0, inputArray.Length);
			rc2.Clear();
			return System.Convert.ToBase64String(resultArray, 0, resultArray.Length);
		}

		public string Decrypt(string key, string input)
		{
			byte[] inputArray = System.Convert.FromBase64String(input);
			RC2CryptoServiceProvider rc2 = new RC2CryptoServiceProvider();
			rc2.Key = UTF8Encoding.UTF8.GetBytes(key);
			rc2.Mode = CipherMode.ECB;
			rc2.Padding = PaddingMode.PKCS7;
			ICryptoTransform cTransform = rc2.CreateDecryptor();
			byte[] resultArray = cTransform.TransformFinalBlock(inputArray, 0, inputArray.Length);
			rc2.Clear();
			return UTF8Encoding.UTF8.GetString(resultArray);
		}
	}
}

Next, I created the following class to store settings I need for encrypting and decrypting data using the RC2 algorithm class above:

namespace Sandbox.Foundation.Forms.Models
{
	public class FormEncryptionSettings
	{
		public string EncryptionKey { get; set; }
	}
}

The encryption key above is needed for the RC2 algorithm to encrypt/decrypt data. I set this key in a config object defined in a Sitecore patch configuration file towards the bottom of this post.

I then created an interface for classes that will encrypt/decrypt FormEntry instances (FormEntry objects contain submitted data from form submissions):

using Sitecore.ExperienceForms.Data.Entities;

namespace Sandbox.Foundation.Forms.Services.Encryption
{
	public interface IFormEntryEncryptor
	{
		void EncryptFormEntry(FormEntry entry);

		void DecryptFormEntry(FormEntry entry);
	}
}

The following class implements the interface above:

using System.Linq;

using Sitecore.ExperienceForms.Data.Entities;

using Sandbox.Foundation.Forms.Models;

namespace Sandbox.Foundation.Forms.Services.Encryption
{
	public class FormEntryEncryptor : IFormEntryEncryptor
	{
		private readonly FormEncryptionSettings _formEncryptionSettings;
		private readonly IEncryptor _encryptor;

		public FormEntryEncryptor(FormEncryptionSettings formEncryptionSettings, IEncryptor encryptor)
		{
			_formEncryptionSettings = formEncryptionSettings;
			_encryptor = encryptor;
		}

		public void EncryptFormEntry(FormEntry entry)
		{
			if (!HasFields(entry))
			{
				return;
			}

			foreach (FieldData field in entry.Fields)
			{
				EncryptField(field);
			}
		}

		protected virtual void EncryptField(FieldData field)
		{
			if(field == null)
			{
				return;
			}

			field.FieldName = Encrypt(field.FieldName);
			field.Value = Encrypt(field.Value);
			field.ValueType = Encrypt(field.ValueType);
		}

		protected virtual string Encrypt(string input)
		{
			return _encryptor.Encrypt(_formEncryptionSettings.EncryptionKey, input);
		}

		public void DecryptFormEntry(FormEntry entry)
		{
			if (!HasFields(entry))
			{
				return;
			}

			foreach (FieldData field in entry.Fields)
			{
				DecryptField(field);
			}
		}

		protected virtual bool HasFields(FormEntry entry)
		{
			return entry != null
					&& entry.Fields != null
					&& entry.Fields.Any();
		}

		protected virtual void DecryptField(FieldData field)
		{
			if(field == null)
			{
				return;
			}

			field.FieldName = Decrypt(field.FieldName);
			field.Value = Decrypt(field.Value);
			field.ValueType = Decrypt(field.ValueType);
		}

		protected virtual string Decrypt(string input)
		{
			return _encryptor.Decrypt(_formEncryptionSettings.EncryptionKey, input);
		}
	}
}

The EncryptFormEntry() method above iterates over all FieldData objects contained on the FormEntry instance, and passes them to the EncryptField() mehod which encrypts the FieldName, Value and ValueType properties on them.

Likewise, the DecryptFormEntry() method iterates over all FieldData objects contained on the FormEntry instance, and passes them to the DecryptField() mehod which decrypts the same properties mentioned above.

I then created an interface for classes that will serve as factories for IFormDataProvider instances:

using Sitecore.ExperienceForms.Data;
using Sitecore.ExperienceForms.Data.SqlServer;

namespace Sandbox.Foundation.Forms.Services.Factories
{
	public interface IFormDataProviderFactory
	{
		IFormDataProvider CreateNewSqlFormDataProvider(ISqlDataApiFactory sqlDataApiFactory);
	}
}

The following class implements the interface above:

using Sitecore.ExperienceForms.Data;
using Sitecore.ExperienceForms.Data.SqlServer;

namespace Sandbox.Foundation.Forms.Services.Factories
{
	public class FormDataProviderFactory : IFormDataProviderFactory
	{
		public IFormDataProvider CreateNewSqlFormDataProvider(ISqlDataApiFactory sqlDataApiFactory)
		{
			return new SqlFormDataProvider(sqlDataApiFactory);
		}
	}
}

The CreateNewSqlFormDataProvider() method above does exactly was the method name says. You’ll see it being used in the following class below.

This next class ultimately becomes the new IFormDataProvider instance but decorates the OOTB one which is created from the factory class above:

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


using Sitecore.ExperienceForms.Data;
using Sitecore.ExperienceForms.Data.Entities;
using Sitecore.ExperienceForms.Data.SqlServer;

using Sandbox.Foundation.Forms.Services.Encryption;
using Sandbox.Foundation.Forms.Services.Factories;

namespace Sandbox.Foundation.Forms.Services.Data
{
	public class FormEncryptionDataProvider : IFormDataProvider
	{
		private readonly IFormDataProvider _innerProvider;
		private readonly IFormEntryEncryptor _formEntryEncryptor;

		public FormEncryptionDataProvider(ISqlDataApiFactory sqlDataApiFactory, IFormDataProviderFactory formDataProviderFactory, IFormEntryEncryptor formEntryEncryptor)
		{
			_innerProvider = CreateInnerProvider(sqlDataApiFactory, formDataProviderFactory);
			_formEntryEncryptor = formEntryEncryptor;
		}

		protected virtual IFormDataProvider CreateInnerProvider(ISqlDataApiFactory sqlDataApiFactory, IFormDataProviderFactory formDataProviderFactory)
		{
			return formDataProviderFactory.CreateNewSqlFormDataProvider(sqlDataApiFactory);
		}

		public void CreateEntry(FormEntry entry)
		{
			EncryptFormEntryField(entry);
			_innerProvider.CreateEntry(entry);
		}

		protected virtual void EncryptFormEntryField(FormEntry entry)
		{
			_formEntryEncryptor.EncryptFormEntry(entry);
		}

		public void DeleteEntries(Guid formId)
		{
			_innerProvider.DeleteEntries(formId);
		}

		public IReadOnlyCollection<FormEntry> GetEntries(Guid formId, DateTime? startDate, DateTime? endDate)
		{
			IReadOnlyCollection<FormEntry>  entries = _innerProvider.GetEntries(formId, startDate, endDate);
			if(entries == null || !entries.Any())
			{
				return entries;
			}

			foreach(FormEntry entry in entries)
			{
				DecryptFormEntryField(entry);
			}

			return entries;
		}

		protected virtual void DecryptFormEntryField(FormEntry entry)
		{
			_formEntryEncryptor.DecryptFormEntry(entry);
		}
	}
}

The class above does delegation to the IFormEntryEncryptor instance to encrypt the FormEntry data and then passes the FormEntry to the inner provider for saving.

For decrypting, it retrieves the data from the inner provider, and then decrypts it via the IFormEntryEncryptor instance before returning to the caller.

Finally, I created an IServicesConfigurator class to wire everything up into the Sitecore container (I hope you are using Sitecore Dependency Injection capabilities as this comes OOTB — there are no excuses for not using this!!!!!!):

using System;

using Microsoft.Extensions.DependencyInjection;

using Sitecore.Abstractions;
using Sitecore.DependencyInjection;
using Sitecore.ExperienceForms.Data;

using Sandbox.Foundation.Forms.Models;
using Sandbox.Foundation.Forms.Services.Encryption;
using Sandbox.Foundation.Forms.Services.Data;
using Sandbox.Foundation.Forms.Services.Factories;

namespace Sandbox.Foundation.Forms
{
	public class FormsServicesConfigurator : IServicesConfigurator
	{
		public void Configure(IServiceCollection serviceCollection)
		{
			serviceCollection.AddSingleton(provider => GetFormEncryptionSettings(provider));
			serviceCollection.AddSingleton<IEncryptor, RC2Encryptor>();
			serviceCollection.AddSingleton<IFormEntryEncryptor, FormEntryEncryptor>();
			serviceCollection.AddSingleton<IFormDataProviderFactory, FormDataProviderFactory>();
			serviceCollection.AddSingleton<IFormDataProvider, FormEncryptionDataProvider>();
		}

		private FormEncryptionSettings GetFormEncryptionSettings(IServiceProvider provider)
		{
			return CreateConfigObject<FormEncryptionSettings>(provider, "moduleSettings/foundation/forms/formEncryptionSettings");
		}

		private TConfigObject CreateConfigObject<TConfigObject>(IServiceProvider provider, string path) where TConfigObject : class
		{
			BaseFactory factory = GetService<BaseFactory>(provider);
			return factory.CreateObject(path, true) as TConfigObject;
		}

		private TService GetService<TService>(IServiceProvider provider)
		{
			return provider.GetService<TService>();
		}
	}
}

Everything above is normal service class registration except for the stuff in the GetFormEncryptionSettings() method. Here, I’m creating an instance of a FormEncryptionSettings class but am instantiating it using the Sitecore Configuration Factory for the configuration object defined in the Sitecore patch configuration file below, and am making that available for being injected into classes that need it (the FormEntryEncryptor above uses it).

I then wired everything together using the following Sitecore patch configuration file:

<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
	<sitecore>
		<services>
			<configurator type="Sandbox.Foundation.Forms.FormsServicesConfigurator, Sandbox.Foundation.Forms" />
			<register serviceType="Sitecore.ExperienceForms.IFormDataProvider, Sitecore.ExperienceForms">
				<patch:delete />
			</register>
		</services>
		<moduleSettings>
			<foundation>
				<forms>
					<formEncryptionSettings type="Sandbox.Foundation.Forms.Models.FormEncryptionSettings, Sandbox.Foundation.Forms" singleInstance="true">
						<!-- I stole this from https://sitecorejunkie.com/2013/06/21/encrypt-web-forms-for-marketers-fields-in-sitecore/ -->
						<EncryptionKey>88bca90e90875a</EncryptionKey>
					</formEncryptionSettings>
				</forms>
			</foundation>
		</moduleSettings>
	</sitecore>
</configuration>

I want to call out that I’m deleting the OOTB IFormDataProvider above using a patch:delete. I’m re-adding it via the IServicesConfigurator above using the decorator class previously shown above.

Let’s take this for a spin.

I first created a new form (this is under “Forms” on the Sitecore Launchepad ):

I then put it on a page with an MVC Layout; published everything; navigated to the test page with the form created above; filled out the form; and then clicked the submit button:

Let’s see if the data was encrypted. I opened up SQL Server Management Studio and ran a query on the FormEntry table in my Experience Forms Database:

As you can see the data was encrypted.

Let’s export the data to make sure it gets decrypted. We can do that by exporting the data as a CSV from Forms in the Sitecore Launchpad:

As you can see the data is decrypted in the CSV:

I do want to mention that Sitecore MVP João Neto had provided two other methods for encrypting data in Experience Forms in a post he wrote last January. I recommend having a read of that.

Until next time, see you on the Sitecore Slack 😉

Yet Another Way to Store Data Outside of the Sitecore Experience Platform

Last February, Sitecore MVP Nick Wesselman shared an awesome blog post on storing data outside of the Sitecore® Experience Platform™ using the NHibernate ORM framework — if you haven’t had a chance to read this, I strongly recommend that you do — which is complete magic, and a simple solution where you don’t have to worry about spinning up your own database tables for storing information.

But, let’s suppose you aren’t allowed to use an ORM like NHibernate in your solution for some reason — I won’t go into potential reasons but let’s make pretend there is one — and you have to find a way to store Sitecore specific information but don’t want to go through the trouble of spinning up a new Sitecore database due to the overhead involved. What can you do?

Well, you can still store information in a non-Sitecore database using the Sitecore API. The following “proof of concept” does this, and is basically modeled after how Sitecore manages data stored in the IDTable and Links Database.

The code in the following “proof of concept” adds/retrieves/deletes alternative URLs for Sitecore Items in the following custom database table:

itemurls-sql-table

I’m not going to talk much about the SQL table or SQL statements used in this “proof of concept” since it’s beyond the scope of this post.

Of course we all love things that are performant — and our clients love when we make things performant — so I decided to start off my solution using the following adapter — this includes the interface and concrete class — for an instance of Sitecore.Caching.Cache (this lives in Sitecore.Kernel.dll):

using System;
using System.Collections;

using Sitecore.Caching;
using Sitecore.Data;
using Sitecore.Diagnostics.PerformanceCounters;

namespace Sitecore.Sandbox.Caching
{
    public interface ICacheProvider
    {
        bool CacheWriteEnabled { get; set; }

        int Count { get; }

        CachePriority DefaultPriority { get; set; }

        bool Enabled { get; set; }

        AmountPerSecondCounter ExternalCacheClearingsCounter { get; set; }

        ID Id { get; }

        long MaxSize { get; set; }

        string Name { get; }

        long RemainingSpace { get; }

        bool Scavengable { get; set; }

        long Size { get; }

        object SyncRoot { get; }

        object this[object key] { get; }

        void Add(ID key, ICacheable data);

        void Add(ID key, string value);

        void Add(string key, ICacheable data);

        void Add(string key, ID value);

        Cache.CacheEntry Add(string key, string data);

        void Add(ID key, object data, long dataLength);

        void Add(object key, object data, long dataLength);

        void Add(string key, object data, long dataLength);

        void Add(object key, object data, long dataLength, DateTime absoluteExpiration);

        void Add(object key, object data, long dataLength, TimeSpan slidingExpiration);

        Cache.CacheEntry Add(string key, object data, long dataLength, DateTime absoluteExpiration);

        void Add(string key, object data, long dataLength, EventHandler<EntryRemovedEventArgs> removedHandler);

        Cache.CacheEntry Add(string key, object data, long dataLength, TimeSpan slidingExpiration);

        void Add(object key, object data, long dataLength, TimeSpan slidingExpiration, DateTime absoluteExpiration);

        void Clear();

        bool ContainsKey(ID key);

        bool ContainsKey(object key);

        ArrayList GetCacheKeys();

        ArrayList GetCacheKeys(string keyPrefix);

        Cache.CacheEntry GetEntry(object key, bool updateAccessed);

        object GetValue(object key);

        void Remove(object key);

        void Remove<TKey>(Predicate<TKey> predicate);

        void RemoveKeysContaining(string value);

        void RemovePrefix(string keyPrefix);

        void Scavenge();
    }
}
using System;
using System.Collections;

using Sitecore.Caching;
using Sitecore.Data;
using Sitecore.Diagnostics;
using Sitecore.Diagnostics.PerformanceCounters;

namespace Sitecore.Sandbox.Caching
{
    public class CacheProvider : ICacheProvider
    {
        private Cache Cache { get; set; }

        public CacheProvider(string cacheName, string cacheSize)
        {
            Assert.ArgumentNotNullOrEmpty(cacheName, "cacheName");
            Assert.ArgumentNotNullOrEmpty(cacheSize, "cacheSize");
            Cache = new Cache(cacheName, StringUtil.ParseSizeString(cacheSize));
        }

        public bool CacheWriteEnabled 
        {
            get
            {
                return Cache.CacheWriteEnabled;
            }
            set
            {
                Cache.CacheWriteEnabled = value;
            }
        }

        public int Count 
        {
            get
            {
                return Cache.Count;
            }
        }

        public CachePriority DefaultPriority 
        {
            get
            {
                return Cache.DefaultPriority;
            }
            set
            {
                Cache.DefaultPriority = value;
            }
        }

        public bool Enabled 
        {
            get
            {
                return Cache.Enabled;
            }
            set
            {
                Cache.Enabled = value;
            }
        }

        public AmountPerSecondCounter ExternalCacheClearingsCounter 
        {
            get
            {
                return Cache.ExternalCacheClearingsCounter;
            }
            set
            {
                Cache.ExternalCacheClearingsCounter = value;
            }
        }

        public ID Id 
        {
            get
            {
                return Cache.Id;
            }
        }

        public long MaxSize 
        {
            get
            {
                return Cache.MaxSize;
            }
            set
            {
                Cache.MaxSize = value;
            }
        }

        public string Name 
        {
            get
            {
                return Cache.Name;
            }
        }

        public long RemainingSpace
        {
            get
            {
                return Cache.RemainingSpace;
            }
        }

        public bool Scavengable 
        {
            get
            {
                return Cache.Scavengable;
            }
            set
            {
                Cache.Scavengable = value;
            }
        }

        public long Size
        {
            get
            {
                return Cache.Size;
            }
        }

        public object SyncRoot
        {
            get
            {
                return Cache.SyncRoot;
            }
        }

        public object this[object key] 
        { 
            get
            {
                return Cache[key];
            } 
        }

        public void Add(ID key, ICacheable data)
        {
            Cache.Add(key, data);
        }

        public void Add(ID key, string value)
        {
            Cache.Add(key, value);
        }

        public void Add(string key, ICacheable data)
        {
            Cache.Add(key, data);
        }

        public void Add(string key, ID value)
        {
            Cache.Add(key, value);
        }

        public Cache.CacheEntry Add(string key, string data)
        {
            return Cache.Add(key, data);
        }

        public void Add(ID key, object data, long dataLength)
        {
            Cache.Add(key, data, dataLength);
        }

        public void Add(object key, object data, long dataLength)
        {
            Cache.Add(key, data, dataLength);
        }

        public void Add(string key, object data, long dataLength)
        {
            Cache.Add(key, data, dataLength);
        }

        public void Add(object key, object data, long dataLength, DateTime absoluteExpiration)
        {
            Cache.Add(key, data, dataLength, absoluteExpiration);
        }

        public void Add(object key, object data, long dataLength, TimeSpan slidingExpiration)
        {
            Cache.Add(key, data, dataLength, slidingExpiration);
        }

        public Cache.CacheEntry Add(string key, object data, long dataLength, DateTime absoluteExpiration)
        {
            return Cache.Add(key, data, dataLength, absoluteExpiration);
        }

        public void Add(string key, object data, long dataLength, EventHandler<EntryRemovedEventArgs> removedHandler)
        {
            Cache.Add(key, data, dataLength, removedHandler);
        }

        public Cache.CacheEntry Add(string key, object data, long dataLength, TimeSpan slidingExpiration)
        {
            return Cache.Add(key, data, dataLength, slidingExpiration);
        }

        public void Add(object key, object data, long dataLength, TimeSpan slidingExpiration, DateTime absoluteExpiration)
        {
            Cache.Add(key, data, dataLength, slidingExpiration, absoluteExpiration);
        }

        public void Clear()
        {
            Cache.Clear();
        }

        public bool ContainsKey(ID key)
        {
            return Cache.ContainsKey(key);
        }

        public bool ContainsKey(object key)
        {
            return Cache.ContainsKey(key);
        }

        public ArrayList GetCacheKeys()
        {
            return Cache.GetCacheKeys();
        }

        public ArrayList GetCacheKeys(string keyPrefix)
        {
            return Cache.GetCacheKeys(keyPrefix);
        }

        public Cache.CacheEntry GetEntry(object key, bool updateAccessed)
        {
            return Cache.GetEntry(key, updateAccessed);
        }
        
        public object GetValue(object key)
        {
            return Cache.GetValue(key);
        }

        public void Remove(object key)
        {
            Cache.Remove(key);
        }

        public void Remove<TKey>(Predicate<TKey> predicate)
        {
            Cache.Remove<TKey>(predicate);
        }

        public void RemoveKeysContaining(string value)
        {
            Cache.RemoveKeysContaining(value);
        }

        public void RemovePrefix(string keyPrefix)
        {
            Cache.RemovePrefix(keyPrefix);
        }

        public void Scavenge()
        {
            Cache.Scavenge();
        }
    }
}

I’m not going to talk about the above interface or class since it just wraps Sitecore.Caching.Cache, and there isn’t much to talk about here.

Next, I spun up the following class that represents an entry in our custom SQL table:

using System;

using Sitecore.Caching;
using Sitecore.Data;
using Sitecore.Reflection;

using Newtonsoft.Json;

namespace Sitecore.Sandbox.Data.Providers.ItemUrls
{
    public class ItemUrlEntry : ICacheable, ICloneable
    {
        public ID ItemID { get; set; }

        public string Site { get; set; }
        
        public string Database { get; set; }
        
        public string Url { get; set; }

        bool cacheable;
        bool ICacheable.Cacheable
        {
            get
            {
                return cacheable;
            }
            set
            {
                cacheable = value;
            }
        }

        bool ICacheable.Immutable
        {
            get
            {
                return true;
            }
        }

        event DataLengthChangedDelegate ICacheable.DataLengthChanged
        {
            add
            {
            }
            remove
            {
            }
        }

        long ICacheable.GetDataLength()
        {
            return TypeUtil.SizeOfID()
                    + TypeUtil.SizeOfString(Site) 
                    + TypeUtil.SizeOfString(Database) 
                    + TypeUtil.SizeOfString(Url);
        }

        public object Clone()
        {
            return new ItemUrlEntry { ItemID = ItemID, Site = Site, Database = Database, Url = Url };
        }

        public override string ToString()
        {
            return JsonConvert.SerializeObject(this);
        }
    }
}

Entries can contain the ID of the Sitecore Item; the specific site we are storing this url for; and the target Database.

You’ll notice I’ve implemented the Sitecore.Caching.ICacheable interface. I’ve done this so I can store entries in cache for performance. I’m not going to go much into the details of how this works since there isn’t much to point out.

I also override the ToString() method for testing purposes. You’ll see this in action later on when we test this together.

Next, we need some sort of provider to manage these entries. I’ve defined the following interface for such a provider:

using System.Collections.Generic;

using Sitecore.Data;
using Sitecore.Data.Items;
using Sitecore.Sites;

namespace Sitecore.Sandbox.Data.Providers.ItemUrls
{
    public interface IItemUrlsProvider
    {
        void AddEntry(ItemUrlEntry entry);
        
        void RemoveEntry(ItemUrlEntry entry);

        Item GetItem(ItemUrlEntry entry);

        ItemUrlEntry GetEntry(ItemUrlEntry entry);

        IEnumerable<ItemUrlEntry> GetAllEntries();
    }
}

IItemUrlsProviders should have the ability to add/remove/retrieve entries. They should also offer the ability to get all entries — I need this for testing later on in this post.

Plus, as a “nice to have”, these providers should return a Sitecore Item for a given entry. Such would be useful when retrieving and setting the context Sitecore Item via a custom Item Resolver (you would typically have an <httpRequestBegin> pipeline processor that does this).

I then created the following class that implements the IItemUrlsProvider interface defined above. This class is specific to adding/removing/retrieving entries from a custom SQL database:

using System;
using System.Collections.Generic;

using Sitecore.Caching;
using Sitecore.Configuration;
using Sitecore.Data;
using Sitecore.Data.DataProviders.Sql;
using Sitecore.Data.Items;
using Sitecore.Diagnostics;

using Sitecore.Sandbox.Caching;

namespace Sitecore.Sandbox.Data.Providers.ItemUrls.SqlServer
{
    public class SqlServerItemUrlsProvider : IItemUrlsProvider
    {
        private SqlDataApi SqlDataApi { get; set; }

        protected ICacheProvider CacheProvider { get; private set; }

        protected string CachePrefix { get; private set; }

        public SqlServerItemUrlsProvider(SqlDataApi sqlDataApi, ICacheProvider cacheProvider, string cachePrefix)
        {
            Assert.ArgumentNotNull(sqlDataApi, "sqlDataApi");
            Assert.ArgumentNotNull(cacheProvider, "cacheProvider");
            Assert.ArgumentNotNullOrEmpty(cachePrefix, "cachePrefix");
            SqlDataApi = sqlDataApi;
            CacheProvider = cacheProvider;
            CachePrefix = cachePrefix;
        }

        public void AddEntry(ItemUrlEntry entry)
        {
            Assert.ArgumentNotNull(entry, "entry");
            Assert.ArgumentCondition(!ID.IsNullOrEmpty(entry.ItemID), "entry.ItemID", "entry.ItemID cannot be null or empty");
            Assert.ArgumentNotNullOrEmpty(entry.Site, "entry.Site");
            Assert.ArgumentNotNullOrEmpty(entry.Database, "entry.Database");
            Assert.ArgumentNotNullOrEmpty(entry.Url, "entry.Url");
            const string addEntrySql = "INSERT INTO {0}ItemUrls{1} ( {0}ItemID{1}, {0}Site{1}, {0}Database{1}, {0}Url{1} ) VALUES ( {2}itemID{3}, {2}site{3}, {2}database{3}, {2}url{3} )";
            var success = Factory.GetRetryer().Execute(() =>
            {
                object[] parameters = new object[] { "itemID", entry.ItemID, "site", entry.Site, "database", entry.Database, "url", entry.Url };
                return SqlDataApi.Execute(addEntrySql, parameters) > 0;
            });

            if (success)
            {
                AddToCache(entry);
            }
        }

        public void RemoveEntry(ItemUrlEntry entry)
        {
            const string deleteEntrySql = "DELETE FROM {0}ItemUrls{1} WHERE {0}Site{1} = {2}site{3} AND {0}Database{1} = {2}database{3} AND {0}Url{1} = {2}url{3}";
            var success = Factory.GetRetryer().Execute(() =>
            {
                object[] parameters = new object[] { "site", entry.Site, "database", entry.Database, "url", entry.Url };
                return SqlDataApi.Execute(deleteEntrySql, parameters) > 0;
            });

            if (success)
            {
                RemoveFromCache(entry);
            }
        }

        public Item GetItem(ItemUrlEntry entry)
        {
            ItemUrlEntry foundEntry = GetEntry(entry);
            if(foundEntry == null)
            {
                return null;
            }

            Database database = Factory.GetDatabase(foundEntry.Database);
            if(database == null)
            {
                return null;
            }

            try
            {
                return database.Items[foundEntry.ItemID];
            }
            catch(Exception ex)
            {
                Log.Error(ToString(), ex, this);
            }

            return null;
        }

        public ItemUrlEntry GetEntry(ItemUrlEntry entry)
        {
            ItemUrlEntry foundEntry = GetFromCache(entry);
            if (foundEntry != null)
            {
                return foundEntry;
            }

            const string getEntrySql = "SELECT {0}ItemID{1} FROM {0}ItemUrls{1} WHERE {2}Site = {2}site{3} AND {2}Database{3} = {2}database{3} AND {0}Url{1} = {2}url{3}";
            object[] parameters = new object[] { "site", entry.Site, "database", entry.Database, "url", entry.Url };
            using (DataProviderReader reader = SqlDataApi.CreateReader(getEntrySql, parameters))
            {
                if (!reader.Read())
                {
                    return null;
                }

                ID itemID = ID.Parse(SqlDataApi.GetGuid(0, reader));
                if (ID.IsNullOrEmpty(itemID))
                {
                    return null;
                }

                foundEntry = entry.Clone() as ItemUrlEntry;
                foundEntry.ItemID = itemID;
                AddToCache(entry);
                return foundEntry;
            }
        }

        public IEnumerable<ItemUrlEntry> GetAllEntries()
        {
            const string getAllEntriesSql = "SELECT {0}ItemID{1}, {0}Site{1}, {0}Database{1}, {0}Url{1} FROM {0}ItemUrls{1}";
            IList<ItemUrlEntry> entries = new List<ItemUrlEntry>();
            using (DataProviderReader reader = SqlDataApi.CreateReader(getAllEntriesSql, new object[0]))
            {
                while(reader.Read())
                {
                    ID itemID = ID.Parse(SqlDataApi.GetGuid(0, reader));
                    if (!ID.IsNullOrEmpty(itemID))
                    {
                        entries.Add
                        (
                            new ItemUrlEntry 
                            {
                                ItemID = itemID, 
                                Site = SqlDataApi.GetString(1, reader), 
                                Database = SqlDataApi.GetString(2, reader), 
                                Url =  SqlDataApi.GetString(3, reader)
                            }
                        );
                    } 
                }
            }

            return entries;
        }

        protected virtual void AddToCache(ItemUrlEntry entry)
        {
            CacheProvider.Add(GetCacheKey(entry), entry);
        }

        protected virtual void RemoveFromCache(ItemUrlEntry entry)
        {
            CacheProvider.Remove(GetCacheKey(entry));
        }

        protected virtual ItemUrlEntry GetFromCache(ItemUrlEntry entry)
        {
            return CacheProvider[GetCacheKey(entry)] as ItemUrlEntry;
        }

        protected virtual string GetCacheKey(ItemUrlEntry entry)
        {
            Assert.ArgumentNotNull(entry, "entry");
            Assert.ArgumentNotNull(entry.Site, "entry.Site");
            Assert.ArgumentNotNull(entry.Database, "entry.Database");
            Assert.ArgumentNotNull(entry.Url, "entry.Url");
            return string.Join("#", CachePrefix, entry.Site, entry.Database, entry.Url);
        }
    }
}

Sitecore.Data.DataProviders.Sql.SqlDataApi and ICacheProvider instances along with a cache prefix are injected into the class instance’s constructor using the Sitecore Configuration Factory (you’ll get a better idea of how this happens when you have a look at the patch configuration file towards the bottom of this post). These are saved to properties on the class instance so they can be leveraged by the methods on the class.

One thing I would like to point out is the Sitecore.Data.DataProviders.Sql.SqlDataApi class is an abstraction — it’s an abstract class that is subclassed by Sitecore.Data.SqlServer.SqlServerDataApi in Sitecore.Kernel.dll. This concrete class does most of the leg work on talking to the SQL Server database, and we just utilize methods on it for adding/deleting/removing entries.

The AddEntry() method delegates the database saving operation to the Sitecore.Data.DataProviders.Sql.SqlDataApi instance, and then uses the ICacheProvider instance for storing the entry in cache.

The RemoveEntry() method also leverages the Sitecore.Data.DataProviders.Sql.SqlDataApi instance for deleting the entry from the database, and then removes the entry from cache via the ICacheProvider instance.

The GetEntry() method does exactly what you think it does. It tries to get the entry first from cache via the ICacheProvider instance and then the database via the Sitecore.Data.DataProviders.Sql.SqlDataApi instance if the entry was not found in cache. If the Item was not in cache but was in the database, the GetEntry() method then saves the entry to cache.

I then created the following Singleton for testing:

using System;
using System.Collections.Generic;

using Sitecore.Configuration;
using Sitecore.Data.Items;
using Sitecore.Diagnostics;

namespace Sitecore.Sandbox.Data.Providers.ItemUrls
{
    public class ItemUrlsProvider : IItemUrlsProvider
    {
        private static readonly Lazy<IItemUrlsProvider> lazyInstance = new Lazy<IItemUrlsProvider>(() => new ItemUrlsProvider());

        public static IItemUrlsProvider Current { get { return lazyInstance.Value; } }

        private IItemUrlsProvider InnerProvider { get; set; }

        private ItemUrlsProvider()
        {
            InnerProvider = GetInnerProvider();
        }

        public void AddEntry(ItemUrlEntry entry)
        {
            InnerProvider.AddEntry(entry);
        }

        public void RemoveEntry(ItemUrlEntry entry)
        {
            InnerProvider.RemoveEntry(entry);
        }

        public Item GetItem(ItemUrlEntry entry)
        {
            return InnerProvider.GetItem(entry);
        }

        public ItemUrlEntry GetEntry(ItemUrlEntry entry)
        {
            return InnerProvider.GetEntry(entry);
        }

        public IEnumerable<ItemUrlEntry> GetAllEntries()
        {
            return InnerProvider.GetAllEntries();
        }

        protected virtual IItemUrlsProvider GetInnerProvider()
        {
            IItemUrlsProvider provider = Factory.CreateObject("itemUrlsProvider", true) as IItemUrlsProvider;
            Assert.IsNotNull(provider, "itemUrlsProvider must be set in configuration!");
            return provider;
        }
    }
}

The Singleton above basically decorates the IItemUrlsProvider instance defined in Sitecore configuration — see the configuration file below — and delegates method calls to it.

I then wired everything together using the following patch configuration file:

<?xml version="1.0" encoding="utf-8" ?>
<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
  <sitecore>
    <itemUrlsProvider id="custom" type="Sitecore.Sandbox.Data.Providers.ItemUrls.$(database).$(database)ItemUrlsProvider, Sitecore.Sandbox" singleInstance="true">
      <param type="Sitecore.Data.$(database).$(database)DataApi, Sitecore.Kernel" desc="sqlDataApi">
        <param connectionStringName="$(id)"/>
      </param>
      <param type="Sitecore.Sandbox.Caching.CacheProvider, Sitecore.Sandbox" desc="cacheProvider">
        <param desc="cacheName">[ItemUrls]</param>
        <param desc="cacheSize">500KB</param>
      </param>
      <param desc="cachePrefix">ItemUrlsEntry</param>
    </itemUrlsProvider>
  </sitecore>
</configuration>

For testing, I whipped up a standalone ASP.NET Web Form (yes, there are more elegant ways to do this but it’s Sunday so cut me some slack 😉 ):

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

using Sitecore.Data.Items;

using Sitecore.Sandbox.Data.Providers.ItemUrls;

namespace Sitecore.Sandbox.Web.tests
{
    public partial class ItemUrlsProviderTest : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            IItemUrlsProvider provider = ItemUrlsProvider.Current;
            Item home = Sitecore.Context.Database.GetItem("/sitecore/content/home");
            StringBuilder output = new StringBuilder();
            
            ItemUrlEntry firstEntry = new ItemUrlEntry { ItemID = home.ID, Site = Sitecore.Context.Site.Name, Database = Sitecore.Context.Database.Name, Url = "/this/does/not/exist" };
            output.AppendFormat("Adding {0} as an entry.<br />", firstEntry);
            provider.AddEntry(firstEntry);

            ItemUrlEntry secondEntry = new ItemUrlEntry { ItemID = home.ID, Site = Sitecore.Context.Site.Name, Database = Sitecore.Context.Database.Name, Url = "/fake/url" };
            output.AppendFormat("Adding {0} as an entry.<br />", secondEntry);
            provider.AddEntry(secondEntry);

            ItemUrlEntry thirdEntry = new ItemUrlEntry { ItemID = home.ID, Site = Sitecore.Context.Site.Name, Database = Sitecore.Context.Database.Name, Url = "/another/fake/url" };
            output.AppendFormat("Adding {0} as an entry.<hr />", thirdEntry);
            provider.AddEntry(thirdEntry);

            ItemUrlEntry fourthEntry = new ItemUrlEntry { ItemID = home.ID, Site = Sitecore.Context.Site.Name, Database = Sitecore.Context.Database.Name, Url = "/blah/blah/blah" };
            output.AppendFormat("Adding {0} as an entry.<hr />", fourthEntry);
            provider.AddEntry(fourthEntry);

            ItemUrlEntry fifthEntry = new ItemUrlEntry { ItemID = home.ID, Site = Sitecore.Context.Site.Name, Database = Sitecore.Context.Database.Name, Url = "/i/am/a/url" };
            output.AppendFormat("Adding {0} as an entry.<hr />", fifthEntry);
            provider.AddEntry(fifthEntry);

            output.AppendFormat("Current saved entries:<br /><br />{0}<hr />", string.Join("<br />", provider.GetAllEntries().Select(entry => entry.ToString())));

            output.AppendFormat("Removing entry {0}.<br /><br />", firstEntry.ToString());
            provider.RemoveEntry(firstEntry);

            output.AppendFormat("Current saved entries:<br /><br />{0}", string.Join("<br />", provider.GetAllEntries().Select(entry => entry.ToString())));
            litResults.Text = output.ToString();
        }
    }
}

The test above adds five entries, and then deletes one. It also outputs what’s in the database after specific operations.

After doing a build, I pulled up the above Web Form in my browser and saw this once the page was done rendering:

ItemUrlsProviderTest

If you have any thoughts on this, please share in a comment.

Until next time, have a Sitecoredatalicious day!