Results 1 to 14 of 14

Thread: [RESOLVED] Dynamic Page Creation via SQL

  1. #1

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Resolved [RESOLVED] Dynamic Page Creation via SQL

    I'm using MVC and Kendo controls for this website. I have a form already preconfigured but am adding custom user defined fields to add more to it dynamically. The user/admin will select the controls in an admin backend of the site and designate its label caption and type etc. All of this is stored in a ms sql db.

    Looking for some architectural ideas on how best to store and populate the client facing page(s).

    Thanks
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Dynamic Page Creation via SQL

    I'm working on an ASP.NET Core project at the moment where we can add custom fields to records and the custom field data is stored in the database. It doesn't sound like your situation is exactly the same but it seems similar at least. When I get some time, I'll let you know how we're handling it.

  3. #3

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: Dynamic Page Creation via SQL

    Thanks John any help is appreciated.

    I know szlammy has done allot of sql winform design storage apps in the past so I'm hoping he may chime in.

    Hopefully tomorrow I will have more to update here as well.

    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  4. #4
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660

    Re: Dynamic Page Creation via SQL

    Yeah i would be interested in this also, creating pages dynamically based upon table data isn't that hard in webforms, but i dont see how it works in MVC where you have a fixed model.

    Can you have dynamic Models, or is there something else i am missing about doing this using MVC ?
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Dynamic Page Creation via SQL

    I'll post this quickly and go into more detail tomorrow. This is the partial view for the custom properties of a another record:
    Code:
    @model CustomPropertiesValueViewModel
    
    <input asp-for="CustomPropertiesAreReadOnly" />
    
    @{
        var dateTimePickers = new List<String>();
        var datePickers = new List<String>();
        var timePickers = new List<String>();
    
        if (Model != null && Model.CustomPropertyValues.Count > 0)
        {
    
            var customPropertyValues = Model.CustomPropertyValues.OrderBy(cp => cp.DisplayOrder)
                .ThenBy(cp => cp.Name)
                .ThenBy(cp => cp.OwnerCustomPropertyId)
                .ToList();
    
            for (var i = 0; i < customPropertyValues.Count; i++)
            {
                var customPropertyValue = customPropertyValues[i];
                var labelClass = customPropertyValue.IsMandatory ? "Mandatory" : "";
                var inputId = $"'CustomPropertyValues_{i}__Value'";
                var inputClass = WebViewPageHelper.IsTrue(ViewBag.CanUserConfigureCustomProperty) ? "InputWithInlineToolbar" : "";
                var toolbarItemClass = "";
                if (customPropertyValue.HasValidationError)
                {
                    inputClass += " input-validation-error";
                }
    
                var inputDataAttributes = customPropertyValue.IsMandatory ? $"data-val='true' dat-val-required='The {customPropertyValue.Name} field is required'" : "";
    
                if (customPropertyValue.CustomPropertyTypeName == Constants.CUSTOM_PROPERTY_TYPE_DATETIME)
                {
                    dateTimePickers.Add(inputId);
                }
    
                if (customPropertyValue.CustomPropertyTypeName == Constants.CUSTOM_PROPERTY_TYPE_DATE)
                {
                    datePickers.Add(inputId);
                }
    
                if (customPropertyValue.CustomPropertyTypeName == Constants.CUSTOM_PROPERTY_TYPE_TIME)
                {
                    timePickers.Add(inputId);
                }
    
                <li id="customProperty_CustomPropertyId_@customPropertyValue.CustomPropertyId" class="CustomProperty">
                    <label for="CustomPropertyValues[@i].Value" class="@labelClass">@customPropertyValue.Name:</label>
                    @if (customPropertyValue.CustomPropertyTypeName == Constants.CUSTOM_PROPERTY_TYPE_BOOLEAN)
                    {
                        <select id=@Html.Raw(inputId) name="CustomPropertyValues[@i].Value" class="@inputClass" @Html.Raw(inputDataAttributes)>
                            <option value=""></option>
                            @if (customPropertyValue.Value == "Yes" || customPropertyValue.Value == "True")
                            {
                                <option value="True" selected>Yes</option>
                                <option value="False">No</option>
                            }
                            else if (customPropertyValue.Value == "No" || customPropertyValue.Value == "False")
                            {
                                <option value="True">Yes</option>
                                <option value="False" selected>No</option>
                            }
                            else
                            {
                                <option value="True">Yes</option>
                                <option value="False">No</option>
                            }
                        </select>
                    }
                    else if (customPropertyValue.CustomPropertyTypeName == Constants.CUSTOM_PROPERTY_TYPE_NOTE)
                    {
                        <textarea id=@Html.Raw(inputId) name="CustomPropertyValues[@i].Value" class="@inputClass" @Html.Raw(inputDataAttributes)>@customPropertyValue.Value</textarea>
                    }
                    else if (customPropertyValue.CustomPropertyReferenceLists?.Count > 0)
                    {
                        <select id=@Html.Raw(inputId) name="CustomPropertyValues[@i].Value" class="@inputClass" @Html.Raw(inputDataAttributes)>
                            <option value=""></option>
                            @foreach (var customPropertyReferenceList in customPropertyValue.CustomPropertyReferenceLists.OrderBy(rl => rl.Value))
                            {
                                if (customPropertyReferenceList.Value == customPropertyValue.Value)
                                {
                                    <option selected>@customPropertyReferenceList.Value</option>
                                }
                                else
                                {
                                   <option>@customPropertyReferenceList.Value</option>
                                }
                            }
                        </select>
                    }
                    else
                    {
                        <input id=@Html.Raw(inputId) name="CustomPropertyValues[@i].Value" type="text" value="@customPropertyValue.Value" class="@inputClass" @Html.Raw(inputDataAttributes) />
                    }
                    @if (WebViewPageHelper.IsTrue(ViewBag.CanUserConfigureCustomProperty))
                    {
                        <div class="InlineToolbar">
                            <a asp-controller="customproperties" asp-action="customproperty" asp-route-id="@customPropertyValue.CustomPropertyId" asp-route-ownerType="@customPropertyValue.OwnerType" asp-route-ownerId="@customPropertyValue.OwnerId"><img src='@Url.Content("~/images/Edit.png")' alt="Edit" title="Edit" /></a>
                            @if (i == 0)
                            {
                                toolbarItemClass = "MoveDisplayOrder MoveDisplayOrderUp Hidden";
                            }
                            else
                            {
                                toolbarItemClass = "MoveDisplayOrder MoveDisplayOrderUp";
                            }
                            <a id="customProperty_MoveDisplayOrderUp_@customPropertyValue.CustomPropertyId" class="@toolbarItemClass" onclick="moveDisplayOrderUp(@customPropertyValue.CustomPropertyId, '@customPropertyValue.OwnerType', @customPropertyValue.OwnerId);"><img src='@Url.Content("~/images/MoveUp.png")' alt="Move up" title="Move up" /></a>
                            @if (i == customPropertyValues.Count - 1)
                            {
                                toolbarItemClass = "MoveDisplayOrder MoveDisplayOrderDown Hidden";
                            }
                            else
                            {
                                toolbarItemClass = "MoveDisplayOrder MoveDisplayOrderDown";
                            }
                            <a id="customProperty_MoveDisplayOrderDown_@customPropertyValue.CustomPropertyId" class="@toolbarItemClass" onclick="moveDisplayOrderDown(@customPropertyValue.CustomPropertyId, '@customPropertyValue.OwnerType', @customPropertyValue.OwnerId);"><img src='@Url.Content("~/images/MoveDown.png")' alt="Move down" title="Move down" /></a>
                        </div>
                    }
                    <input type="hidden" name="CustomPropertyValues[@i].OwnerCustomPropertyId" value="@customPropertyValue.OwnerCustomPropertyId" />
                    <input type="hidden" name="CustomPropertyValues[@i].OwnerType" value="@customPropertyValue.OwnerType" />
                    <input type="hidden" name="CustomPropertyValues[@i].OwnerId" value="@customPropertyValue.OwnerId" />
                    <input type="hidden" name="CustomPropertyValues[@i].CustomPropertyId" value="@customPropertyValue.CustomPropertyId" />
                    <input type="hidden" name="CustomPropertyValues[@i].CustomPropertyTypeName" value="@customPropertyValue.CustomPropertyTypeName" />
                    <input type="hidden" id="customPropertyValue_DisplayOrder_@customPropertyValue.CustomPropertyId" name="CustomPropertyValues[@i].DisplayOrder" value="@customPropertyValue.DisplayOrder" />
                    <input type="hidden" name="CustomPropertyValues[@i].IsMandatory" value="@customPropertyValue.IsMandatory.ToString()" />
                    <input type="hidden" name="CustomPropertyValues[@i].Name" value="@customPropertyValue.Name" />
                </li>
            }
        }
    
        <script type="text/javascript">
            function configureCustomProperties()
            {
                //date pickers
                SI.UI.attachDateTimePickers([@Html.Raw(string.Join(",", dateTimePickers))]);
                SI.UI.attachDatePickers([@Html.Raw(string.Join(",", datePickers))]);
                SI.UI.attachTimePickers([@Html.Raw(string.Join(",", timePickers))]);
    
                //set initial focus
                @if (Model.CustomPropertyValues.Count > 0 && Model.CustomPropertyValues[0].OwnerType == CustomPropertyOwningEntityType.EventEvaluationTemplate)
                {
                    @Html.Raw("$('#CustomPropertyValues_0__Value').focus();");
                }
            }
        </script>
    
        @Html.Partial("_CustomPropertyValueListScript")
    }
    The important point to note is that the view loops over the custom properties collection and outputs the appropriate control based on the data type.

  6. #6

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: Dynamic Page Creation via SQL

    It looks like you are designing it so the user can really design it. Giving them a toolbar to move control locations.

    We are doing a page that is less dynamic. We have a standard template of a page for this and any extra fields that the user needs added to this in the admin area of the site. If the user adds a text type field with a label caption it is to be shown in addition to the rest of the client facing page for data input. Now if they choose multiple fields of text, date or numeric types they will all be output in the order created. I seen the DisplayOrder property you have and that's a good idea but how are you designating its size? Are you giving them that much granularity of control?

    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  7. #7
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660

    Re: Dynamic Page Creation via SQL

    This is really interesting JMC i meant to reply earlier but forgot.

    How is the save handled, how is it wired up to push the data back to the model ?

    also what are you outputting in the - _CustomPropertyValueListScript ?
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Dynamic Page Creation via SQL

    Looks like I forgot to get back to this too. I'll have to leave it until tomorrow for now but I will post more details.

  9. #9

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: Dynamic Page Creation via SQL

    Since I only needed a partial of the page created dynamically I ended up doing something similar but to a lesser degree. I'm sure I can optimize this more but here it is for now.
    DataType sub-property of FieldControl collection holds all the controls detail properties which are populated from the db.

    System and Custom field sections are dynamic and configured on another page. Kinda like create a template and then use that template to populate data. Field labels are dynamic as well as the associated data control. I may add the kendo control html code to a field in the db so when the control is pulled it doesn't need an if statement to determine the html kendo code to use.

    Sample data page of concerned sections shown in attachment with fictitious data populated. Code example is for Custom Field section only. Note that System Fields only created 2 fields out of the possible 8 like Custom fields has.

    Using MVC, bootstrap and Razr

    Code:
    <div class="row row-custom">
        @foreach (WebProCore.Models.FieldControl oControl in @Model.CustomFieldControls)
        {
            <div class="col-md-3 custom-field-padding">
                <label for="customField1">@oControl.Name</label><br />
                @if (oControl.DataType.id == (long)WebProCore.Models.FieldControl.DATATYPE.TEXT)
                {
                    @(Html.Kendo().TextBox()
                    .Name(oControl.FieldName)
                    .Value(oControl.Value.ToString())
                    .HtmlAttributes(new { style = "width: 100%;" })
                    )
                }
                @if (oControl.DataType.id == (long)WebProCore.Models.FieldControl.DATATYPE.DATE)
                {
                    @(Html.Kendo().DatePicker()
                    .Name(oControl.FieldName)
                    .Value(oControl.Value)
                    .HtmlAttributes(new { style = "width: 100%;" })
                    )
                }
                @if (oControl.DataType.id == (long)WebProCore.Models.FieldControl.DATATYPE.NUMBER)
                {
                    @(Html.Kendo().NumericTextBox()
                    .Name(oControl.FieldName)
                    .RestrictDecimals(true)
                    .Decimals(0)
                    .Format("{0:n0}")
                    .Value(oControl.Value_Long)
                    .HtmlAttributes(new { style = "width: 100%;" })
                    )
                }
            </div>
        }
    </div>
    Name:  Dynamic Page1.jpg
Views: 1981
Size:  20.7 KB
    Last edited by RobDog888; Jan 17th, 2018 at 01:40 PM.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  10. #10
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660

    Re: [RESOLVED] Dynamic Page Creation via SQL

    Are you going to post any Further on this JMC ? it would be great to see an example of the code to save the data back.
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  11. #11
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: [RESOLVED] Dynamic Page Creation via SQL

    I will make every effort to do so this weekend. I've just been putting it off because it's going to take a little bit of time to get all the relevant stuff straight.

  12. #12
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660

    Re: [RESOLVED] Dynamic Page Creation via SQL

    no worries i appreciate you making the effort
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  13. #13
    New Member
    Join Date
    Feb 2017
    Location
    Elberfeld, Indiana
    Posts
    8

    Re: [RESOLVED] Dynamic Page Creation via SQL

    we also allow custom data on our fields at work, but it's incredibly silly how it works in my opinion. there is a form where you create a custom data field and then you say what table it applies to. It shows up in a popup when you click a button on that page. I wonder if i can implement something like you did here to a kendo mvc layout?

  14. #14
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660

    Re: [RESOLVED] Dynamic Page Creation via SQL

    Hey JMC,

    i had forgotten about this thread but if you are able to provide an example i would definitely be interested in it still
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width