Views: 10977
Number of votes: 4
Average rating:

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();
   4:  
   5:     var a = from item in formData
   6:             where item.GetValue("Email").EndsWith(stringToMatch)
   7:             select item;
   8:  
   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);
   4:  
   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();
   4:  
   5:     var a = from item in formData
   6:             where ((int)item.CustomData["RandomValue"]) > threshold
   7:             select item;
   8:  
   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
(By Jonathan Roberts, 1/18/2013 11:36:40 AM)

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

linus.ekstrom
(By linus.ekstrom, 1/18/2013 12:07:29 PM)

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

Jonathan Roberts
(By Jonathan Roberts, 1/18/2013 12:30:53 PM)

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
(By Jonathan Roberts, 1/18/2013 2:00:39 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.

linus.ekstrom
(By linus.ekstrom, 1/22/2013 9:33:34 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
(By Sujit Senapati, 8/21/2014 11:34:59 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?

linus.ekstrom
(By linus.ekstrom, 8/25/2014 9:26:49 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
(By Jonathan Roberts, 6/7/2018 5:03:55 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?

linus.ekstrom
(By linus.ekstrom, 6/8/2018 10:28:32 AM)

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

https://world.episerver.com/documentation/class-library/?documentId=cms/7/8eefd4c0-65d6-26b1-ffa7-2779fc960571

Please login to comment.