add fields to 'system table' via API

Brian Bennewitz asked on April 15, 2014 21:30

I understand how to extend 'system tables' via the portal interface (i.e. CMSSiteManager -> Development (tab) -> System tables (tree-node) -> Select table -> + New Attribute).

In particular, I am interested in adding a few fields to 'COM_SKU'. I have written an ITask / 'scheduled job' which synchronizes products with Kentico from another system and expects the extra fields. This task works provided I have already extended the 'COM_SKU' table with some additional columns.

My question: How can I programmatically check for the existence of the additional columns in 'COM_SKU' and if they do not exist, create them via the API in a way that I can include this in the ITask implementation? Also, if I synchronize products in this way, will I be able to use the 'content staging' feature to move the custom SKU's from a staging environment to a UAT environment?

Thanks, ~B

Correct Answer

Brenden Kehren answered on April 16, 2014 00:21

I have a way to compare the column data which is stored in the cms_class table as xml. It works very well assuming the column names in both areas are the same. Below is the code for that.

Regarding creating a new field via the API. I believe you can look at the FieldEditor.ascx control for this. On about line 1980 of \CMSModules\AdminControls\Controls\Class\FieldEditor\FieldEditor.accx.cs you will find the SaveSelectedField() method that has all the "stuff" you need to create a new column in a table.

public static bool CreateCustomTableItem(DataClassInfo Dci, DataRow Row)   
    bool okInsert = false;

    // Creates new Custom table item provider
    CustomTableItemProvider customTableProvider = new CustomTableItemProvider(ui);

    if (Dci == null)
        throw new Exception("Data class info cannot be null.");
        // Creates new custom table item object
        CustomTableItem newCustomTableItem = CustomTableItem.New(Dci.ClassName, customTableProvider);

        List<string> classProperties = new List<string>();
        // get the class definition
        var doc = XDocument.Parse(Dci.ClassFormDefinition);
        // get a list of all the tables properties by column value
        List<string> elements = doc.Descendants("field").Select(el => el.Attribute("column").Value).ToList();

        // got the class properties now compare
        foreach (string s in elements)
            // see if the class info contains the column in the data row passed in this method
            if (Row.Table.Columns.Contains(s))
                // contains the column so set a value
                newCustomTableItem.SetValue(s, Row[s]);
                okInsert = true;

        if (okInsert)
            // Inserts the custom table item into database
    return okInsert;
0 votesVote for this answer Unmark Correct answer

   Please, sign in to be able to submit a new answer.