In Part I, I wrote about the basic usage of Episerver’s Dynamic Data Store (DDS). Now you can find out how Episerver implemented its solution of storing data and how exactly yours is stored.

Behind the Episerver code

You already know how to retrieve and update data using DDS, but what exactly happens in your database when you create a dynamic data store and save your data? To get a better view of how DDS works, let’s add one more property to PageViewsData class introduced in Part I. Let’s say we want to keep some additional notes:

public IEnumerable Notes { get; set; }

When you execute this line of code for the first time:

DynamicDataStore store = typeof(PageViewsData).GetOrCreateStore();

Episerver will create a store definition which is held in 5 tables in your database:

1. tblBigTableStoreConfig – here you can find the ID of your store (column pkId), a name of your store (column StoreName) and the name of the table in which your data is stored (column TableName). In our example, the values are respectively Setapp.DataStore.PageViewsData and tblBigTable. The store name is the full name of the class (including its namespace) but it can be changed with another parameter in EPiServerDataStore attribute and that parameter is called simply StoreName. tblBigTable is the default table that already exists in the Episerver database
2. tblBigTable – data from simple fields is stored here
3. tblBigTableReference – data from fields like lists and dictionaries is stored here
4. tblBigTableStoreInfo – this is where Episerver stores information about how a class is mapped into all the columns in tblBigTable
5. tblBigTableIdentity – here you can find a unique GUID of a store

DDS class mapping

Simple types

All simple values like int, string, float etc are stored in tblBigTable. The table contains 72 columns. A single row represents a part or the whole data from an object of a given store class.

There is a pkId column that contains Identity property data which is saved as a simple integer. Another important column is StoreName, containing the name of a store also used in tblBigTableStoreConfig. Column ItemType defines the type of an item stored in the row, in our case it is equal to:

Setapp.DataStore.PageViewsData, DDSProject, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null

Almost every other column is a column dedicated to store data for a certain property type. For example, there are 5 columns that can store boolean values and are named simply Boolean01, Boolean02, Boolean03, Boolean04, Boolean05. The same thing is implemented for types like Integer (10 columns), Long (5), DateTime (5), Guid (3), Float (7), Decimal (2), String (10) and Binary (5).

In case of PageViewsData class and its ViewsAmount property of type int – DDS will find the first available Integer column, in this case it’s Integer01and will put a value there. For another property of that type it will save a value in Integer02 and so on. But what if there are, let’s say, 12 integer properties in a class? Well, there is also another column that can help us and it’s called Row. In the mentioned case, every object will be stored in two rows of tblBigTable.

The first one will have a value 1 in a column Rowand 10 first integer properties values (columns from Integer01 to Integer10). The second row will contain 2 in a column Row and the remaining two integer values. As in the first row, they will be assigned from Integer01. The same procedure is repeated for another simple (not collection) types.

But wait, there’s more! Remember we set up an index on PageId property? There are also special columns in tblBigTable on which an index is created. So PageId, instead of being put into Integer02 column, will be stored in Indexed_Integer01! Just like in unindexed columns, there are many more columns for each simple type, using the same naming convention (increasing the last number in a name) so you’ll get Indexed_String01, Indexed_String02 and so on.

Collection types

All collections like IEnumerable<int> are stored in tblBigTableReference. PkId columns store an ID of a store. PropertyName in our case would be Notes. The CollectionType column defines what type a collection is, in our example it’s:

System.Collections.Generic.List`1[[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]], mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089

and ElementType indicates the type of a single element so here you’d get:

System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089

Index columns store the index of a stored element in a collection. The collection can be a simple list or an array but it can also be a bit more complicated, for example, you can store a Dictionary. For the sake of it, the table also contains the IsKey column which is a boolean value and indicates if a given row stores a key or a value of a given KeyValuePair.

Similar with tblBigTable, tblBigTableReference contains a column for each simple type that can be stored, so you get BooleanValue, IntegerValue, StringValue etc.

If you store an object like this:

var example = new PageViewsData
{
    PageId = 1,
    ViewsAmount = 10,
    Notes = new List { "Note 1", "Note 2" }
};

store.Save(example);

tblBigTableReference will contain three rows. The first one represents the definition of a list itself, filling only pkId, PropertyName, CollectionType with the same data as in the other two rows, but having a NULL value in ElementType and -1 in Indexcolumns. The other rows will also contain a column StringValue with Note 1 and Note 2 respectively.

Storing DDS definition

Episerver will not calculate a property mapping on every call. This information is saved in tblBigTableStoreInfo on a store creation. The table assigns a certain property (PropertyName column) to a column in tblBigTable (ColumnName column) and its rows number (ColumnRowIndex). It also contains additional information:

• the type of a property (PropertyType),
• the type of a mapping (for example 1 is for simple types, 3 is for collections),
• if the mapping is currently in use (Active),
• a version of a mapping (Version) – it’s incremented every time you change a definition of a class and a store definition needs to be rebuilt.

Reading data from the store in SQL

After the first use of the store, Episerver creates an SQL view which can be used to read data easily. So if you need to check some data in your database, you don’t need to go through all the tables mentioned before and join them. Instead, you can simply query a view. Its name pattern is VW_{name_of_a_store}, so an example would be VW_PageViewsData. Columns of the view correspond to a store’s class properties so in our case we have columns like Id, StoreId, ExternalId, ItemType, PageId, ViewsAmount.

Conclusions

As you have probably noticed, by default ALL data from ALL stores that you create is stored in the same big table. And it’s not very efficient since even for simple data retrievals, it needs to make a lot of searches on how to map the object and where to find its values. In the end, it ends up with lots of table joins and an unnecessary search through a big, big table.

But fortunately, there are ways to store custom data in Episerver in a much more efficient way! In Part III of this article, you will learn how to get your custom data store in a separate table. No more searching through objects from a different store! Coming soon!

P.S. At Setapp, we are a team of talented and experienced.Net developers working on Episerver and other CMS solutions. If you’d like to be part of our growing team, get in touch with us!