Mapping Kentico - Custom Tables
When I was first introduced to Kentico I thought it was an intuitive CMS with great scope for extensibility.
However, one aspect that I took a dislike to was the handling of custom database tables through their exposed API.
In the first project I worked on at True: Pet Drugs Online we made heavy use of these custom tables. Custom tables work by returning an object (or a set of objects) that allows you to access all of the data in a given custom table record (or set of records). Whilst this provided us with a simple means to access, update and maintain numerous different data structures, what it did not provide was a strongly typed C# object to represent the data in each custom table. These objects each contain a string indexer that allow you to access the data for a given record by column name. Hence using the Kentico custom table API out-of-the-box would lead to heavily ‘stringly-typed’ code, a definite code smell in my eyes.
What I wanted was to be able to work with each of my custom tables using objects that directly modelled the data that they contained in a way that was in keeping with the other Kentico APIs: I wanted to be able to map data directly from Kentico into my own custom types.
To this end I decided on the following requirements:
- A way to model specific custom table, using the equivalent C# type for each SQL type. These objects would be suffixed with ‘Info’ for no other reason than to be in keeping with the Kentico naming convention.
- A provider class that acts as an interface to a custom table. These objects would be suffixed with ‘InfoProvider’ for no other reason than to be in keeping with the Kentico naming convention.
- The ability to both read from and write to the custom table via modelled data.
- Complete independence from the Kentico custom table API once an object has been mapped.
Let’s use an example to demonstrate this idea. In my site I need a table that will contain all of the information for a particular person: their first-name, their second-name, their job title and their age. I created this table as a custom table from the Kentico administrator interface.
Creating a custom table like this will result in a table in the SQL database driving Kentico that looks roughly as follows (a few auto-generated columns have been omitted for simplicity):
What I want is to be able to take the information from a record in this table and use it to populate my own custom object:
Using the Kentico custom table API you might end up with a method that looks something like the following:
The methods in the example above do little more than abstract away some unboxing casts from values obtained via a string indexer. By instead using the string indexer directly, the above can be reduced to the following:
How can we simplify this?
The approach I decided upon was to use custom attributes to decorate properties on my model classes that would directly correspond to the column names of the custom table that it models. Then, using a little bit of reflection magic I could use these attributes to allow me to bridge the connection between my model and its associated custom table. By using the appropriate attributes I can use my CustomTableMapper to read information from a custom table into my model, or write to a custom table from my model.
Taking the previous example, all I need to do to make this work is decorate the properties of my custom object with a few attributes that correspond to the table column names. Then, using my mapper, I can create a provider that mirrors the Kentico way of accessing non-page related data.
Using this provider, I can work with my custom table without ever having to worry about the custom table API. All I need to do is deal with PersonInfo objects and let the PersonInfoProvider act as the interface to my custom table via the mapper.
So how does it work?
Let us take the simplest case of getting a single person from our custom table. Assuming a valid person is found in the custom table, the mapper gets to work. Since each attribute corresponds to a column on the item being mapped, the mapper simply reads the data from the item and sets it on the person model.
It is as simple as that. Now instead of dealing with custom table items everywhere, you can instead treat every custom table record as a strongly typed object.
It doesn’t end there though; it is also possible to update or insert new custom table items, or to get collections of model items from a custom table using the mapper.
