readme.md

Offload your Episerver Find tracking

Read my blog here

Powered by ReSharper

FindTrackingQueue.sql

CREATE TABLE [dbo].[tblFindTrackingQueue]
(
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
    [TrackingId] [nvarchar](max) NOT NULL,
	[NrOfHits] [int] NOT NULL,	
	[Query] [nvarchar](max) NOT NULL,
	[Tags] [nvarchar](max) NOT NULL
);
GO

CREATE PROCEDURE [dbo].[FindTrackingQueue_DeleteById]
(
	@Id BIGINT
)
AS
BEGIN
	DELETE FROM tblFindTrackingQueue
	WHERE Id = @Id
END
GO

CREATE PROCEDURE [dbo].[FindTrackingQueue_DeleteAll]
AS
BEGIN
	TRUNCATE TABLE tblFindTrackingQueue
END
GO

CREATE PROCEDURE [dbo].[FindTrackingQueue_GetAll]
AS
BEGIN
	SELECT * FROM tblFindTrackingQueue
END
GO

CREATE PROCEDURE [dbo].[FindTrackingQueue_GetById]
(
	@Id BIGINT
)
AS
BEGIN
	SELECT * FROM tblFindTrackingQueue
	WHERE Id = @Id
END
GO

CREATE PROCEDURE [dbo].[FindTrackingQueue_Save]
(
	@TrackingId NVARCHAR(max),
    @NrOfHits INT,
    @Query NVARCHAR(max),
	@Tags NVARCHAR(max)
)
AS
BEGIN
	INSERT INTO [dbo].[tblFindTrackingQueue]
           ([TrackingId]
           ,[NrOfHits]
           ,[Query]
		   ,[Tags])
     VALUES
           (@TrackingId, @NrOfHits, @Query, @Tags)
END
GO

FindTrackingService.cs

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading;
using System.Web;

using EPiServer.Data.Providers.Internal;
using EPiServer.Find;
using EPiServer.Find.Api;
using EPiServer.Find.Api.Querying;
using EPiServer.Find.Api.Querying.Queries;
using EPiServer.Find.Framework.Statistics;
using EPiServer.Find.Helpers;
using EPiServer.Find.Helpers.Text;
using EPiServer.Find.Statistics;
using EPiServer.Find.Statistics.Api;
using EPiServer.Find.UnifiedSearch;
using EPiServer.Logging;


public class FindTrackingService : IFindTrackingService
{
	private const string IdColumn = "Id";

	private const string NrOfHitsColumn = "NrOfHits";

	private const string QueryColumn = "Query";

	private const string TrackingIdColumn = "TrackingId";
	
	private const string TagsColumn = "Tags";

	private readonly IClient client;

	private readonly ILogger logger = LogManager.GetLogger();

	private readonly IStatisticTagsHelper statisticTagsHelper;

	private readonly IDatabaseConnectionResolver databaseConnectionResolver;

	public FindTrackingService(
		IClient client,
		IStatisticTagsHelper statisticTagsHelper,
		IDatabaseConnectionResolver databaseConnectionResolver)
	{
		this.client = client;
		this.statisticTagsHelper = statisticTagsHelper;
		this.databaseConnectionResolver = databaseConnectionResolver;
	}

	public bool DeleteAll()
	{
		try
		{
			string connectionString = this.databaseConnectionResolver.Resolve().ConnectionString;
			using (SqlConnection connection = new SqlConnection(connectionString: connectionString))
			{
				connection.Open();
				using (SqlTransaction transaction = connection.BeginTransaction())
				{
					SqlCommand command = new SqlCommand
									  {
										  Connection = transaction.Connection,
										  Transaction = transaction,
										  CommandType = CommandType.StoredProcedure,
										  CommandText = "FindTrackingQueue_DeleteAll"
									  };
					command.ExecuteNonQuery();
					transaction.Commit();
				}
			}

			return true;
		}
		catch (Exception exception)
		{
			this.logger.Error(message: exception.Message, exception: exception);
		}

		return false;
	}

	public bool DeleteById(long id)
	{
		try
		{
			string connectionString = this.databaseConnectionResolver.Resolve().ConnectionString;
			using (SqlConnection connection = new SqlConnection(connectionString: connectionString))
			{
				connection.Open();
				using (SqlTransaction transaction = connection.BeginTransaction())
				{
					SqlCommand command = new SqlCommand
									  {
										  Connection = transaction.Connection,
										  Transaction = transaction,
										  CommandType = CommandType.StoredProcedure,
										  CommandText = "FindTrackingQueue_DeleteById"
									  };
					command.Parameters.Add(new SqlParameter("@Id", value: id));
					command.ExecuteNonQuery();
					transaction.Commit();
				}
			}

			return true;
		}
		catch (Exception exception)
		{
			this.logger.Error(message: exception.Message, exception: exception);
		}

		return false;
	}

	public bool DeleteById(IEnumerable<long> idList)
	{
		try
		{
			string connectionString = this.databaseConnectionResolver.Resolve().ConnectionString;
			using (SqlConnection connection = new SqlConnection(connectionString: connectionString))
			{
				connection.Open();
				using (SqlTransaction transaction = connection.BeginTransaction())
				{
					foreach (long id in idList)
					{
						SqlCommand command = new SqlCommand
										  {
											  Connection = transaction.Connection,
											  Transaction = transaction,
											  CommandType = CommandType.StoredProcedure,
											  CommandText = "FindTrackingQueue_DeleteById"
										  };
						command.Parameters.Add(new SqlParameter("@Id", value: id));
						command.ExecuteNonQuery();
					}

					transaction.Commit();
				}
			}

			return true;
		}
		catch (Exception exception)
		{
			this.logger.Error(message: exception.Message, exception: exception);
		}

		return false;
	}

	public bool SaveTrackingInformation(string query, int nrOfHits, string id, IEnumerable<string> tags)
	{
		try
		{
			string connectionString = this.databaseConnectionResolver.Resolve().ConnectionString;
			using (SqlConnection connection = new SqlConnection(connectionString: connectionString))
			{
				connection.Open();
				using (SqlTransaction transaction = connection.BeginTransaction())
				{
					SqlCommand command = new SqlCommand
											 {
												 Connection = transaction.Connection,
												 Transaction = transaction,
												 CommandType = CommandType.StoredProcedure,
												 CommandText = "FindTrackingQueue_Save"
											 };
					command.Parameters.Add(new SqlParameter("@TrackingId", value: id));
					command.Parameters.Add(new SqlParameter("@NrOfHits", value: nrOfHits));
					command.Parameters.Add(new SqlParameter("@Query", value: query));
					command.Parameters.Add(new SqlParameter("@Tags", value: string.Join("&", tags)));
					command.ExecuteNonQuery();
					transaction.Commit();
				}
			}

			return true;
		}
		catch (Exception exception)
		{
			this.logger.Error(message: exception.Message, exception: exception);
		}

		return false;
	}

	public int SendTrackingInformationToFind()
	{
		int count = 0;

		try
		{
			string connectionString = this.databaseConnectionResolver.Resolve().ConnectionString;

			using (SqlConnection connection = new SqlConnection(connectionString: connectionString))
			{
				connection.Open();

				SqlCommand getAllCommand = new SqlCommand
											   {
												   Connection = connection,
												   CommandType = CommandType.StoredProcedure,
												   CommandText = "FindTrackingQueue_GetAll"
											   };

				using (SqlDataReader reader = getAllCommand.ExecuteReader())
				{
					while (reader.Read())
					{
						if (count % 25 == 0)
						{
							Thread.Sleep(1000);
						}

						this.TrackQuery(row: reader);

						SqlCommand deleteCommand = new SqlCommand
													   {
														   Connection = connection,
														   CommandType = CommandType.StoredProcedure,
														   CommandText = "FindTrackingQueue_DeleteById"
													   };

						deleteCommand.Parameters.Add(
							new SqlParameter("@Id", Convert.ToInt64(reader[name: IdColumn])));

						deleteCommand.ExecuteNonQuery();

						count += 1;
					}
				}
			}
		}
		catch (Exception exception)
		{
			this.logger.Error(message: exception.Message, exception: exception);
			return count;
		}

		return count;
	}

	public void TrackQuery(string query, int nrOfHits, string id)
	{
		this.client.Statistics().TrackQuery(
			query,
			x =>
				{
					x.Id = id;
					x.Tags = this.statisticTagsHelper.GetTags(false);
					x.Query.Hits = nrOfHits;
				});
	}

	private void TrackQuery(IDataReader row)
	{
		this.client.Statistics().TrackQuery(
			row[name: QueryColumn].ToString(),
			x =>
				{
					x.Id = row[name: TrackingIdColumn].ToString();
					x.Tags = row[name: TagsColumn].ToString().Split(
						new[] { '&' },
						StringSplitOptions.RemoveEmptyEntries);
					x.Query.Hits = Convert.ToInt32(row[name: NrOfHitsColumn]);
				});
	}
}

IFindTrackingService.cs

using System.Collections.Generic;

public interface IFindTrackingService
{
	bool SaveTrackingInformation(string query, int nrOfHits, string id, IEnumerable<string> tags);

	bool DeleteById(long id);

	bool DeleteById(IEnumerable<long> idList);

	bool DeleteAll();

	int SendTrackingInformationToFind();

	void TrackQuery(string query, int nrOfHits, string id);
}

SendTrackingToFindScheduledJob.cs

using EPiServer.PlugIn;
using EPiServer.Scheduler;

[ScheduledPlugIn(
	DisplayName = "Send Tracking To Find",
	GUID = "10084af7-6ffa-4cd0-943f-64d80bafd9e9",
	Restartable = false)]
public class SendTrackingToFindScheduledJob : ScheduledJobBase
{
	private readonly IFindTrackingService findTrackingService;

	public SendTrackingToFindScheduledJob(IFindTrackingService findTrackingService)
	{
		this.findTrackingService = findTrackingService;
		this.IsStoppable = false;
	}

	public override string Execute()
	{
		this.OnStatusChanged("Sending tracking to Find");

		int count = this.findTrackingService.SendTrackingInformationToFind();

		return $"{count} tracking data sent to Find";
	}
}