Populate a dropdown list in my MVC view from Kentico custom tables

Question

Using Kentico 12 MVC, I created a custom table to store infos used to populate a dropdown list in my view.

It is named MyCustom.Tables and have added a "ProgramName" and "ProgramID" fields with the relevant infos

How can I pass them to my view? I tried in my controller:

IEnumerable<ProgramList> allPrograms = CustomTableItemProvider.GetItems<ProgramList>("MyCustom.Tables"); 

and then in my view:

@Html.DropDownListFor(m => m.Data.ProgramSelected, new SelectList(Model.Data.AllPrograms, "ProgramID", "ProgramName"), "- Please Select -", new { @class = "browser-default" }) 

with the following model

public class ProgramList
{
    public string ProgramID { get; set; }
    public string ProgramName { get; set; }
}

But I get an error in the controller (No overload for method GetItems take 1 arguments)...

I also tried to change the controller to

IEnumerable allPrograms = CustomTableItemProvider.GetItems("MyCustom.Tables"); 

But my custom fields from my custom table are not available in this case only the default ItemID Kentico field.

Any ideas?

S.

######################### EDIT

I have added the following code generated by Kentico:

using System;
using System.Collections.Generic;

using CMS;
using CMS.Base;
using CMS.Helpers;
using CMS.DataEngine;
using CMS.CustomTables.Types.MySite;
using CMS.CustomTables;

[assembly: RegisterCustomTable(ProgramsItem.CLASS_NAME, typeof(ProgramsItem))]

namespace CMS.CustomTables.Types.MySite
{
    /// <summary>
    /// Represents a content item of type ProgramsItem.
    /// </summary>
    public partial class ProgramsItem : CustomTableItem
    {
        #region "Constants and variables"

        /// <summary>
        /// The name of the data class.
        /// </summary>
        public const string CLASS_NAME = "MySite.Programs";


        /// <summary>
        /// The instance of the class that provides extended API for working with ProgramsItem fields.
        /// </summary>
        private readonly ProgramsItemFields mFields;

        #endregion


        #region "Properties"

        /// <summary>
        /// Name.
        /// </summary>
        [DatabaseField]
        public string ProgramName
        {
            get
            {
                return ValidationHelper.GetString(GetValue("ProgramName"), @"");
            }
            set
            {
                SetValue("ProgramName", value);
            }
        }


        /// <summary>
        /// Gets an object that provides extended API for working with ProgramsItem fields.
        /// </summary>
        [RegisterProperty]
        public ProgramsItemFields Fields
        {
            get
            {
                return mFields;
            }
        }


        /// <summary>
        /// Provides extended API for working with ProgramsItem fields.
        /// </summary>
        [RegisterAllProperties]
        public partial class ProgramsItemFields : AbstractHierarchicalObject<ProgramsItemFields>
        {
            /// <summary>
            /// The content item of type ProgramsItem that is a target of the extended API.
            /// </summary>
            private readonly ProgramsItem mInstance;


            /// <summary>
            /// Initializes a new instance of the <see cref="ProgramsItemFields" /> class with the specified content item of type ProgramsItem.
            /// </summary>
            /// <param name="instance">The content item of type ProgramsItem that is a target of the extended API.</param>
            public ProgramsItemFields(ProgramsItem instance)
            {
                mInstance = instance;
            }


            /// <summary>
            /// Name.
            /// </summary>
            public string ProgramName
            {
                get
                {
                    return mInstance.ProgramName;
                }
                set
                {
                    mInstance.ProgramName = value;
                }
            }
        }

        #endregion


        #region "Constructors"

        /// <summary>
        /// Initializes a new instance of the <see cref="ProgramsItem" /> class.
        /// </summary>
        public ProgramsItem() : base(CLASS_NAME)
        {
            mFields = new ProgramsItemFields(this);
        }

        #endregion
    }
}

and tried the foowing code as suggested here https://docs.kentico.com/k12/developing-websites/retrieving-content-in-mvc-applications but itdoes not work fro the time being:

    IEnumerable<ProgramsItem> allPrograms = CustomTableItemProvider.GetItems<ProgramsItem>();
    ProgramsItem item = CustomTableItemProvider.GetItem<ProgramsItem>(1);

For example item above return null while I have records in my custom tables.

Based on https://www.bizstream.com/blog/may-2019/powerful-kentico-custom-table-visualization I also tried

    IEnumerable<ProgramList> GetAllPrograms = CustomTableItemProvider.GetItems<ProgramsItem>()
        .Select(program => new ProgramList
        {                    
            ProgramName = program.ProgramName
        });

but get again:

Unable to cast object of type 'CMS.CustomTables.CustomTableItem' to type 'CMS.CustomTables.Types.MySite.ProgramsItem'.

Thank you

Answer 1

Following discussion here: https://devnet.kentico.com/questions/unable-to-cast-object-of-type-cms-customtables-customtableitem-to-type and https://docs.kentico.com/k12/developing-websites/generating-classes-for-kentico-objects

I have to add

using CMS;
[assembly: AssemblyDiscoverable] 

in my generated class in order to make it accessible.

IEnumerable<ProgramList> GetAllPrograms = CustomTableItemProvider.GetItems<ProgramsItem>()
    .Select(program => new ProgramList
    {                    
        ProgramName = program.ProgramName
    });

works fine now.

S.

Answer 2

If ProgramList is generated code from custom code table (custom tables -> Edit -> Code -> Save Code and include it in solution), then you should call it without providing className:

CustomTableItemProvider.GetItems<ProgramList>(); 

This will allow you to access to the properties of custom tables directly (strongly typed results).

If ProgramList is your custom class, like noted in question, then you should call it only with className (without specifying object type). To access to custom item use GetValue (GetStringValue, GetBooleanValue, etc), for example:

allPrograms[0].GetValue("CustomFieldname")