Load content dynamically from database table

Dec 30, 2014 at 10:28 AM
I have a content type named JobCategory. I have a database table that contains the names of job categories.

Is it possible to create content items dynamically from the database table? How?

My goal is to create a dropdown selector for another content type (Job), in which one of the fields is the job category. I want to be able to select job categories using the dropdown, with the dropdown being loaded dynamically. If there's another way to do this, please let me know.

Thanks!
Dec 30, 2014 at 10:48 AM
You would need the mew api to create a custom admin form

I would start with the feedback example and then for the categories dropdown from a content entity rebuild that as a dropdown from database there are databae example apps from where you can get the code.

Armand
2DNN
Jan 5, 2015 at 9:40 AM
Hi

I don't know if you got it working but here is a working example based on the "Tutorial webapi app" I modified the feedback form webapi template, replaced the subject field with dropdwn with values from the files table.
@using System.Configuration
@using System.Data.SqlClient
@functions{
    private SqlDataReader myReader;

    // Official place to provide data preparation. Is automatically called by 2SexyContent
    public override void CustomizeData()
    {
        var conString = DotNetNuke.Common.Utilities.Config.GetConnectionString("SiteSqlServer");
        var con = new SqlConnection(conString);
        con.Open();
        var command = new SqlCommand("Select Top 10 * from Files Where PortalId = @PortalId", con);
        command.Parameters.Add("@PortalId", Dnn.Portal.PortalId);
        myReader = command.ExecuteReader();
    }
}

@{
    // the ModuleId is used in all form-name attributes, to make sure we have the right one
    // otherwise a submit could have multiple "subject" fields and we wouldn't know which one is ours
    var mid = Dnn.Module.ModuleID; // make sure that multiple modules don't have same IDs
    var submitId = "Submit_" + mid;
}

<div id="Form_@mid">

    <div>
        <span>@App.Resources.LabelSubject</span>
        <span>
         <!--   <input id="xSubject_@mid" name="Subject_@mid"></input> -->

<select id="Subject_@mid" name="Subject_@mid">

        @while (myReader.Read())
        {

<option value="@myReader["FileName"]">@myReader["FileName"]</option>
        }
    </select>
    @{
        myReader.Close();
    }

</select>
        </span>
    </div>
    <div>
        <span>@App.Resources.LabelCategory</span>
        <span>
            @* Create list of related categories - use the EntityId as the reference value *@
            <select id="Category_@mid" name="Category_@mid">
                @foreach(var c in AsDynamic(App.Data["Feedback Category"])) {
                    <option value="@c.EntityId">@c.EntityTitle</option>
                }
            </select>
        </span>
    </div>
    <div>
        <span>@App.Resources.LabelMessage</span>
        <span><textarea id="Message_@mid" name="Message_@mid" rows="8"></textarea></span>
    </div>
    <div>
        <a href="javascript:submitFeedback(@mid)">@App.Resources.LabelSubmit</a>
    </div>

</div>
<div id="Sending_@mid" style="display: none">@Html.Raw(App.Resources.SendingMessage)</div>
<div id="Thanks_@mid" style="display: none">@Html.Raw(App.Resources.ThanksMessage)</div>

@* this would be the right way to do it :) *@
<script type="text/javascript" src="/DesktopModules/ToSIC_SexyContent/Js/2sxc.api.js" data-enableoptimizations="true"></script>
<script>
    function submitFeedback(moduleId) {
        $("#Form_" + moduleId).hide();
        $("#Sending_" + moduleId).show();
        $2sxc(moduleId).webApi.post("Feedback/Create", {}, { 
                Message: $("#Message_" + moduleId).val(),
                Subject: $("#Subject_" + moduleId).val(),
                Category: $("#Category_" + moduleId).val()
            }
        ).then(function (result) {
            $("#Sending_" + moduleId).hide();
            $("#Thanks_" + moduleId).show();
        });
    }
</script>

Jan 5, 2015 at 2:49 PM
Thanks!

I realized that I didn't need to go in that direction, and could use the other entity as a field in the second entity.

But ... I'm sure that I'll be referring back to this in the future!

Coordinator
Jan 13, 2015 at 7:15 AM
@jncraig

There are other options as well - depending on what you want.
  1. you could do a 1-time migration of the table using code (see the API-documentation for that)
  2. You could do an import (with xml). The feature is hidden as it's only 98% mature, but works wherever we use it. If you need it, contact me
  3. You could keep the data in the table, and visualize it w/your template using the SqlDataDataSource
  4. You could keep the data in the table and read it through sql and show it that way (requires Razor)