linuse
Jul 2, 2010
  11703
(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();
   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

Comments

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:

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

Please login to comment.
Latest blogs
Preview multiple Visitor Groups directly while browsing your Optimizely site

Visitor groups are great - it's an easy way to add personalization towards market segments to your site. But it does come with it's own set of...

Allan Thraen | Sep 26, 2022 | Syndicated blog

The Report Center is finally back in Optimizely CMS 12

With Episerver.CMS.UI 12.12.0 the Report Center is finally re-introduced in the core product.

Tomas Hensrud Gulla | Sep 26, 2022 | Syndicated blog

Dynamic Route in ASP.NET Core When MapDynamicControllerRoute Does Not Work

Background Creating one of the add-on for Optimizely I had to deal with challenge to register dynamically route for the API controller. Dynamic rou...

valdis | Sep 25, 2022 | Syndicated blog

404 Error on Static Assets Within an Optimizely plugin

Background With the move to CMS 12 and .NET 5/6, developers are now able to build Plugins and Extensions using Razor Class Libraries (RCL).  These...

Mark Stott | Sep 23, 2022