Maciej & Code

SqlFormDataProvider.DeleteEntries won't delete my entries

Maciej Zwierzchlewski -

Sitecore Forms provides out-of-the-box functionality for saving entered data into the ExperienceForms database. The data can then be exported to a CSV file using the Forms dashboard.

This week, I had to do some work on the saved data and remove it from Sitecore if everything goes right in the processing pipeline. I looked up how Sitecore accomplishes it and stumbled upon the IFormDataProvider interface and its SqlFormDataProvider implementation. Retrieving the submitted data was very easy, all that was required of me was the form item's Id.

The retrieved object is an IEnumerable of FormEntry objects, all of which have the following properties :

  • Form Entry Id (Guid)
  • Form Item Id (Guid)
  • Created (DateTime)
  • Fields (ICollection)

Honestly, I was really happy with how easy everything was up until I got to the point where I had to remove the data from the database. I was shocked that by default there is only the option to remove either all entries for a given form or remove entries from a given period. There is no out-of-the-box functionality to remove a form entry by its Id. This seems quirky, but it must have been enough for Sitecore.

I saw two paths to go forward:

  • Create a custom stored procedure on the ExperienceForms database and write code similar to the FormDataDelete class, which would allow for deleting entries with a given ID. This is how the SqlFormDataProcider does its magic.
  • Use the existing method and specify the smallest time range possible to single out the correct entry.

Because I was only working on a proof-of-concept I chose the second option. If you're working on a real project, I would suggest going the first way as it is a much cleaner solution.

After processing the data, I called the SqlFormDataProvider.DeleteEntries and hoped for the best. I checked the stored procedure using SQL Server Management Studio and saw that the date is compared using greater-or-equal and less-or-equal operators. I wanted to be sneaky and used the same date as both the startDate and endDate parameters. Unfortunately, Sitecore developers were sneakier and I got the following message:

EndDate must be after StartDate.

Bummer.  I used the DateTime.AddTicks method on the endDate parameter to create the smallest possible range. Ran the code again, but nothing was deleted from the database.

I added some logging to check if the date and form ID were correct and it seemed that they were. After some head-scratching, I removed one tick from the startDate, and guess what - it worked...

... for one of the three entries I had in the database. So I fired up dotPeek again and dived deeper. While rummaging through the Sitecore.ExperienceForms.Data.SqlServer.Commands.FormDataDelete class, the SetParameters method piqued my interest:

protected override void SetParameters(SqlParameterCollection parameters)
{
    parameters.Add(new SqlParameter(Sitecore.ExperienceForms.Data.SqlServer.Constants.StoredProcedureParameters.FormDataDelete.FormDefinitionId, (object) this.FormDefinitionId));
    if (this.StartDate.HasValue)
    	parameters.Add(new SqlParameter(Sitecore.ExperienceForms.Data.SqlServer.Constants.StoredProcedureParameters.FormDataDelete.StartDate, (object) this.StartDate));
    if (!this.EndDate.HasValue)
    	return;
    parameters.Add(new SqlParameter(Sitecore.ExperienceForms.Data.SqlServer.Constants.StoredProcedureParameters.FormDataDelete.EndDate, (object) this.EndDate));
}

From my earlier trip to the database, I knew that the entry creation date was stored as datetime2(3) format:

datetime2 is available since SQL Server 2008 and is a far superior type than its predecessor datetime as it allows for a larger range of values and higher precision. The older type only allows for precision up to 1/300th of a second.

When constructing a new SqlParameter instance, .NET is able to implicitly deduce the correct type. However, in the case of DateTime it's not that simple as there are two ways of storing date in SQL Server. Unfortunately, the way SqlParameter is used by Sitecore, a lot of precision is lost as the created objects SqlDbType property is DateTime and not DateTime2:

This is how the parameter object should be created:

My first idea of solving the problem was creating a custom class inheriting the FormDataDelete command and a custom IFormDataProvider implementation based on the SqlFormDataProvider. Unfortunately, both the FormDataDelete and its base class BaseCommand<T> are marked as internal therefore preventing any inheritance.

Because I had to write all that code anyway, I decided that creating a new stored procedure capable of removing form entries by their Id would cause me much fewer headaches in the longer run. So that's what I did. The procedure is very simple:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [sitecore_forms_storage].[FormData_DeleteSingle]
	@FormEntryId UNIQUEIDENTIFIER
AS
BEGIN
	SET NOCOUNT ON;

	IF(@FormEntryId IS NULL)
    BEGIN
        ;THROW 51000, N'Parameter @FormEntryId cannot be null', 0
    END

	DELETE
        [sitecore_forms_storage].[FieldData]
    WHERE
        [FormEntryId] = @FormEntryId

    DELETE
        [sitecore_forms_storage].[FormEntries]
    WHERE
        [Id]  = @FormEntryId

    SELECT @@ROWCOUNT AS [DeletedEntryCount]
END
GO

Then, I created a BaseCommand class similar to the one found in Sitecore assemblies:

public abstract class BaseCommand<TResult>
{
    protected string ConnectionString { get; }
    protected IRetryable Retryer { get; }
    protected ILogger Logger { get; }
    protected abstract string StoredProcedureName { get; }

    protected BaseCommand(string connectionString, IRetryable retryer, ILogger logger)
    {
        Assert.ArgumentNotNullOrEmpty(connectionString, nameof(connectionString));
        Assert.ArgumentNotNull(retryer, nameof(retryer));
        Assert.ArgumentNotNull(logger, nameof(logger));
        ConnectionString = connectionString;
        Retryer = retryer;
        Logger = logger;
    }

    public TResult Execute()
    {
        ValidateProperties();
        try
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                using (var command = connection.CreateCommand())
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = StoredProcedureName;
                    SetParameters(command.Parameters);
                    return Retryer.Execute(() =>
                    {
                        connection.Open();
                        TResult results;
                        using (var reader = command.ExecuteReader())
                        {
                            results = ParseResults(reader);
                        }

                        connection.Close();
                        return results;
                    });
                }
            }
        }
        catch (Exception ex)
        {
            Logger.LogError("Forms database communication error.", ex, this);
        }

        return default;
    }

    protected virtual void ValidateProperties()
    { }

    protected virtual void SetParameters(SqlParameterCollection parameters)
    { }

    protected abstract TResult ParseResults(SqlDataReader reader);
}

Then it was time for the proper command class:

public class FormDataDeleteSingle : BaseCommand<bool>
{
    public Guid FormEntryId { get; set; }
    protected override string StoredProcedureName => $"[{DatabaseSchema.Storage}].[{Constants.StoredProcedures.FormDataDeleteSingle.Name}]";

    public FormDataDeleteSingle(string connectionString, IRetryable retryer, ILogger logger) : base(connectionString, retryer, logger)
    { }

    protected override void ValidateProperties()
    {
        Assert.ArgumentCondition(!FormEntryId.Equals(Guid.Empty), "FormEntryId", "The FormEntryId property cannot be empty.");
    }

    protected override void SetParameters(SqlParameterCollection parameters)
    {
        parameters.Add(new SqlParameter(Constants.StoredProcedures.FormDataDeleteSingle.Parameters.FormEntryId, FormEntryId));
    }

    protected override bool ParseResults(SqlDataReader reader)
    {
        reader.Read();
        return (int) reader[Constants.StoredProcedures.FormDataDeleteSingle.Parameters.DeletedEntryCount] == 1;
    }
}

And an IFormDataProvider extension:

public interface IBetterFormDataProvider : IFormDataProvider
{
    bool DeleteEntry(Guid entryId);
}

And its implementation:

public class BetterSqlFormDataProvider : Sitecore.ExperienceForms.Data.SqlServer.SqlFormDataProvider, IBetterFormDataProvider
{
    public SqlFormDataProvider(IConnectionSettings connectionSettings, ILogger logger,
        IFormDataParser formDataParser, IFormFieldDataConverter formFieldDataConverter) : 
        base(connectionSettings, logger, formDataParser, formFieldDataConverter)
    { }

    [Obsolete("No longer used.")]
    public SqlFormDataProvider(ISqlDataApiFactory sqlServerApiFactory) : base(sqlServerApiFactory)
    { }

    public bool DeleteEntry(Guid entryId)
    {
        return new FormDataDeleteSingle(ConnectionSettings.ConnectionString, Factory.GetRetryer(), Logger)
        {
            FormEntryId = entryId
        }.Execute();
    }
}

After that, all that was left was a configuration patch registering the class in the dependency injection container:

<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
  <sitecore>
    <services>
      <register serviceType="Foundation.ExperienceForms.Providers.IBetterFormDataProvider, Foundation.ExperienceForms" implementationType="Foundation.ExperienceForms.Providers.SqlFormDataProvider, Foundation.ExperienceForms" lifetime="Transient" />
    </services>
  </sitecore>
</configuration>

After getting it to correctly remove all the entries I wanted all that was left for me was to wonder why I didn't go this way from the beginning. To be honest, I didn't expect such a misuse of the SqlParameter class 😉. Still, should have measured twice and cut once!

Cover photo by Kristaps Grundsteins on Unsplash