Linus Ekström
Jul 2, 2010
(4 votes)

Using the dynamic data store with XForms

In EPiServer CMS 6 the data storage of XForms and XForm postings have been moved from “object store” to the new dynamic data store (DDS). This has a lot of advantages. Object store stored it’s objects in serialized form which did not allow fast lookups and searches for objects in the database. For instance, when calculating form statistics in EPiServer CMS 5, we had to load all form postings from the database, deserialize them and then calculate the statistics. Since object store did not a built in cache this was a pretty heavy operation for forms with a lot of posts.

The form statistics has also been rewritten so that the calculation is done in the database which should remove the need for developers on sites with forms with a lot of postings to add a cache layer for statistics. The form data tab in edit mode has also been rewritten to utilize database paging when browsing through form postings.

Searching form postings

I was a bit curious on what new capabilities that this change gave for the developer so I sat down yesterday for a few hours to test if I could do LINQ to SQL queries against the form postings as a site developer. I started writing a method that would get the count of postings that had the “email” field ending with a specified value as I would have done in EPiServer CMS 5.

   1: public static int GetNumberOfPostsWithGivenEmailEndingV1(XForm form, string stringToMatch)
   2: {
   3:     IList<XFormData> formData = form.GetPostedData();
   5:     var a = from item in formData
   6:             where item.GetValue("Email").EndsWith(stringToMatch)
   7:             select item;
   9:     return a.Count<XFormData>();
  10: }

Now I tried to do the same using LINQ to SQL:

   1: public static int GetNumberOfPostsWithGivenEmailEndingV2(XForm form, string stringToMatch)
   2: {
   3:     DynamicDataStore store = XFormData.GetStore((Guid)form.Id);
   5:     return (from post in store.ItemsAsPropertyBag()
   6:             where ((string)post["Email"]).EndsWith(stringToMatch)
   7:             select post).Count();
   8: }

Note that I’m using the ItemAsPropertyBag method to be able to seach the form fields. I’m also using the form id as an identifier to get the store.

Adding custom data

Since XForm postings saved it’s data mostly as xml it was possible to add data that did not match a form field in EPiServer CMS 4/5. In the DDS, each form field is mapped against a table column and a view is set up for each form to be able to quickly search and fetch data for a form. To still allow custom data to be stored to a form posting, a Dictionary<string, object> was added to the XFormData class. This makes it possible to store custom data as  typed objects instead of strings. Here is an example how to add custom data by attaching an event handler to the BeforeSubmitPostedData event on an XFormControl instance.

   1: protected void FormControl_BeforeSubmitPostedData(object sender, SaveFormDataEventArgs e)
   2: {
   3:     int randomValue = new Random().Next(100);
   4:     //Using string value
   5:     e.FormData.SetValue("RandomValue", randomValue.ToString());
   6:     //Using typed value
   7:     e.FormData.CustomData["RandomValue"] = randomValue;
   8: }

I tried doing a search against the custom data by using LINQ to Objects:

   1: public static int GetNumberOfPostsWithRandomValueOverThreshold(XForm form, int threshold)
   2: {
   3:     IList<XFormData> formData = form.GetPostedData();
   5:     var a = from item in formData
   6:             where ((int)item.CustomData["RandomValue"]) > threshold
   7:             select item;
   9:     return a.Count<XFormData>();
  10: }

Last of all I wanted to execute the same search by using LINQ to SQL. Here my success came to an halt though. Since the custom data is stored in a field of type Dictionary<string, object> this means that I have to do a search using nested property bags. This is a feature currently not supported by the DDS.

Conclusions: It’s possible to use LINQ to SQL to seach for form postings that match a given criteria in EPiServer CMS 6, although it’s currently not possible to search on custom data.

Jul 02, 2010


Jonathan Roberts
Jonathan Roberts Jan 18, 2013 10:36 AM

Is there any way you can encrypt the form data before it is submitted into the database?

Jan 18, 2013 11:07 AM

Have you tried attaching to one of the XForm events, like "BeforeSubmitPostedData", and changing the values there?

Jonathan Roberts
Jonathan Roberts Jan 18, 2013 11:30 AM

Great, that works a treat. Thanks.

Now my thoughts turn to the export of the data. Im using the "Composer XForm Data" section in the CMS. Is it possible to Decrypt the data for the screen display and exporting the data to XML and Excel? Im not sure where to begin on this. I cant think of a way to override any functions etc.
Would I need to create a new class to override functions?

Thanks again for your help

Jonathan Roberts
Jonathan Roberts Jan 18, 2013 01:00 PM

This is how I did the Encrypt code in case people are interested. Not sure if this is the best way but it works.
public void FormControl_BeforeSubmitPostedData(object sender, SaveFormDataEventArgs e)
var clone = e.FormData.Data.Clone();

var formValueCollection = e.FormData.GetValues();
foreach (var key in formValueCollection.AllKeys)

e.FormData.SetValue(key, security.Encrypt(formValueCollection[key], false));



-- Security Encrypt is a Triple Des class that we use.

Jan 22, 2013 08:33 PM

@Jonathan: Have you tried the built in UI in EPiServer CMS for viewing and exporting data to for instance xml or excel?

Sujit Senapati
Sujit Senapati Aug 21, 2014 09:34 PM

Hi Linus,

I understand we can encrypt data using BeforeSubmitPostedData. However, is there anyway I can show data again in built-in UI "View Form Data" after decrypting the xform data without creating a new UI?

Aug 25, 2014 07:26 AM

Hi Sujit!

As far as I can see, there is no such event or option so you probably have to create your own statistics.

Jonathan Roberts
Jonathan Roberts Jun 7, 2018 03:03 PM

Is there a way that you can delete data from the database or stop it from being entered into the database and use a different method?

Jun 8, 2018 08:28 AM

Try using the CancelSubmit property on the event args to true:

Please login to comment.
Latest blogs
IDX21323 - RequireNonce is true, Nonce was null

We have multiple clients configured with Azure Active Directory (Microsoft Entra) for requiring authentication when accessing their website. The...

David Drouin-Prince | Oct 1, 2023 | Syndicated blog

Minimum Detectable Effect in Optimizely Web Experimentation

Understanding Minimum Detectable Effect Minimum Detectable Effect (MDE) is a core statistical calculation in Optimizely Web Experimentation. In...

Matthew Dunn | Oct 1, 2023 | Syndicated blog

Configured Commerce - Introduction to Long-Term Support (LTS) Releases

First off, for those who have not had a chance to meet me yet, my name is John McCarroll, and I am the Technical Product Manager for the Optimizely...

John McCarroll | Sep 29, 2023

Auto-translate with OpenAI in Optimizely CMS

You can now auto-translate content using your favorite online AI service, inside the old trustworthy Episerver.Labs.LanguageManager!

Tomas Hensrud Gulla | Sep 29, 2023 | Syndicated blog