Kentico 8 Technology – Custom tables data API
Read this article if you are interested in details about custom tables and managing custom table data using the API.
Custom tables have been available in Kentico for several versions now. Their main purpose is to allow you to easily create and manage database tables and their data data without getting your hands dirty in SQL syntax. Custom tables are your best choice anytime you need to store content in a flat structure. Let’s take a more detailed look at the features that custom tables offer.
Creating Custom Tables
To create a new custom table, simply navigate to the Custom tables application. Here, you need to go through a multistep wizard to specify all the necessary properties.
In the second step of the wizard, you are asked to enter a database table name. More importantly, you need to select which system fields should be available for the data. Below, you can find a list of system fields, together with their purpose:
-
ItemGUID – Stores a unique identifier for the data item. Include this field if you want the data to be synchronized by content staging.
-
ItemCreatedBy – Stores an ID of the user who created the item.
-
ItemCreatedWhen – Stores the date and time of item creation.
-
ItemModifiedBy – Stores an ID of the user who edited the item for the last time.
-
ItemModifiedWhen – Stores the date and time of the last modification of the item.
-
ItemOrder – Stores the order of the item. Include this field if you’re concerned about the order of the items. You can then sort the items in the UI.
In the next step, you can define additional custom fields to store your own data. Below, you can see the table with all the system fields and a ItemText custom field.
Please note: Use the "Item" prefix for the field name to avoid conflicts with SQL reserved keywords.
Now, we have the custom table created and it’s time to dig into some code.
Creating New Data Items
When you want to work with data items, you need to know the name of the custom table. You can find the name in the UI of the Custom tables application:
To create a new item instance, you need to call the New() method instead of the standard constructor. In the future, you will be able to use a generated API class for the custom table using this approach. This will allow you to access the properties in a type-safe way.
Here is a code snippet that creates a new data item with a custom field ItemText. System fields are populated automatically based on context.
// Create new item for custom table with "customtable.sample" code name
var item = CustomTableItem.New("customtable.sample");
item.SetValue("ItemText", "Sample text");
item.Insert();
You can use CMSActionContext to change user context. The code below inserts the data item in the context of administrator user:
var user = UserInfoProvider.GetUserInfo("administrator");
using (new CMSActionContext(user))
{
var item = CustomTableItem.New("customtable.sample");
item.SetValue("ItemText", "Sample text");
item.Insert();
}
Retrieving Data From Database
The CustomTableItemProvider class benefits from the support of ObjectQuery based on DataQuery. This allows you to retrieve data the same way as you would for any other object type. You only need to provide one extra parameter – the custom table name:
var user = UserInfoProvider.GetUserInfo("administrator");
var items = CustomTableItemProvider.GetItems("customtable.sample")
.TopN(10)
.WhereEquals("ItemCreatedBy", user.UserID)
.OrderBy("ItemCreatedWhen");
This code retrieves the 10 top data items ordered by the date of creation which were created by administrator user. You can simply enumerate the result and use the CustomTableItem class to get the properties.
In version 8, the CustomTableItemProvider class was refactored to behave like any other standard provider. Therefore, you don’t need to create a provider instance anymore.
Managing Data Items
You can retrieve existing data items either by an ID or a GUID (depends on system field availability). To access a particular field, you need to use the GetValue method. Use the SetValue method to then store a value in the field:
// Get item with ID 1 of custom table with code name "customtable.sample"
var existing = CustomTableItemProvider.GetItem(1, "customtable.sample");
var text = ValidationHelper.GetString(existing.GetValue("ItemText", null), "");
existing.SetValue("ItemText", text + " - MODIFIED");
existing.Update();
To delete an existing item, simply call the Delete method on the instance.
Sorting Data Items
When you create a custom table with the ItemOrder system field, you can sort the items and move them up/down in the list. You can check if the table contains the ItemOrder field programmatically:
// Get item with ID 1 of custom table with code name "customtable.sample"
var existing = CustomTableItemProvider.GetItem(1, "customtable.sample");
if (existing.OrderEnabled)
{
existing.Generalized.MoveObjectDown();
}
This code snippet moves the existing data item below its current position in the list. Custom tables use general support for sorting of objects. This means that you have to use the Generalized property to access sorting methods.
Please note: Similar management API is also available for form records. Due to backward compatibility (the original name of the application was BizForms), the classes are named BizFormItem and BizFormItemProvider.