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

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

Sitecore Technology MVP 2016
Sitecore MVP 2015
Sitecore MVP 2014

Enter your email address to follow this blog and receive notifications of new posts by email.

Tweets

Advertisements

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.

Advertisements

1 Comment

  1. […] Grab Sitecore Experience Forms Configuration Settings from a Custom SQL Server Database Table […]

Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: