Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?

Offload your Episerver Find tracking

Read my blog here

Powered by ReSharper image

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
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]);
});
}
}
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);
}
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";
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.