Grid<dynamic> and ForeignKey (for dropdown)

[Kemal]

Hi, 

I'm creating grid with dynamic model structure. But i could not use ForeignKey for dropdowns.

i'am using same viewdata method for non-dynamic grid structure and its working. Because i know model name and model tree.

I want to use dropdowns with dynamic grid structure.

var countries = _adminBusinessLayer.Get("sys_Address_Countries"); //Countries for dropdown
ViewData["Countries"] = countries;

 

@(Html.Kendo().Grid<dynamic>()
      .Name("Grid")
      .Columns(columns =>
      {
          foreach (System.Data.DataColumn column in Model.Columns)
          {
              var c = columns.Bound(column.ColumnName);
              if (column.ColumnName == "CountryID")
              {
                  //columns.ForeignKey(d => d.CountryID, (System.Collections.IEnumerable)ViewData["Countries"], "CountryID", "countryNameTr").HeaderTemplate("<i class='fa fa-user'></i> Country");
              }
          }

...

[Stamo Gochev]

Hi,

Can you send me a runable project, so I can investigate the scenario? Note that you can replace the following lines:

var countries = _adminBusinessLayer.Get("sys_Address_Countries"); //Countries for dropdown
ViewData["Countries"] = countries;

with hard-coded list of items in the "countries" variable, so that the example is fully runable on my side.

Regards,
Stamo Gochev
Progress Telerik

[Kemal]

Hi Mr. Stamo,

I have no problem with ViewData. Its working perfectly in Kendo().Grid<Cities> and i'am creating dropdowns with ForeignKey method. Cities is known model and ForeignKey using this model and tree. I'm trying to create generic view. When i try to use Kendo().Grid<dynamic> ForeignKey don't know model tree.I could not use.

So i have problem in razor view. I want to use Grid<dynamic> instead of Grid<Cities>

Also, dynamic grid working perfectly. But no ForeignKey method. How can i use ForeignKey for dropdowns in Kendo().Grid<dynamic>

@(Html.Kendo().Grid<dynamic>()
      .Name("Grid")
      .Columns(columns =>
      {
          foreach (DataColumn column in Model.Columns)
          {
                  columns.Bound(column.ColumnName);
          }
          columns.Command(p => { p.Edit(); }).Width(250);
      })
      .Pageable()
      .Sortable()
      .Editable(ed => ed.Mode(GridEditMode.PopUp))
      .Filterable()
      .Groupable()
      //.Scrollable()
      .DataSource(dataSource => dataSource
          .Ajax()
          .Model(a =>
          {
              var id = Model.PrimaryKey[0].ColumnName;
              a.Id(id);

              foreach (DataColumn column in Model.Columns)
              {
                  var field = a.Field(column.ColumnName, column.DataType);
                  if (column.ColumnName == id)
                  {
                      field.Editable(false);
                  }
              }

          })
          .Read(read => read.Action("Read", "Test"))
          .Update(update => update.Action("Update", "Test"))
      ))

[Stamo Gochev]

Hello,

As far as I understand from the provided information, you need to use a concrete model (instead of dynamic) unless the fields that are used in the foreign key column are specified in the Model of the Schema. Currently, "ContryID" and "countryNameTr" are not defined explicitly (I cannot be sure what happens inside the loop in the model definition).

If this does not work, can you send me the project that you are testing with instead of the code snippets? Please make sure it is runable and replace the dependency that will not be available on my side, e.g.:

var countries = _adminBusinessLayer.Get("sys_Address_Countries"); //Countries for dropdown
ViewData["Countries"] = countries;

can be replaced with hard-coded list of items.

Regards,
Stamo Gochev
Progress Telerik

[Kemal]

Hello,

I have 130 tables in db. I don't want to use 130 view page for every table. I'am calling every table in one view page. This is dynamic and generic method. This is working. I have only ForeignKey problem. Yes. ForeignKey use model of the schema. I know. I'm searching solution for this. Maybe we can use different method? But how for mvc grid? This is question.

1. JsonResult Get;

public JsonResult Get([DataSourceRequest] DataSourceRequest request, string tableName)
{
    var cities = _adminBusinessLayer.Get(tableName);
    return Json(cities.ToDataSourceResult(request), JsonRequestBehavior.AllowGet);
}

2. Business Layer

public IQueryable Get(string tableName)
{
    return _adminGeneralDl.Get(tableName);
}

3. Data Layer

public IQueryable Get(string tableName)
{
    var result = _db.GetType().GetProperty(tableName)?.GetValue(_db, null) as IQueryable;
    return result;
}

This is my generic and parametric GET method... And ADD, SET, DEL working perfectly.

Get(Countries), Get(Cities), Get(Others)... Table name is parametric. I'am using same Get method for viewdata.
It is working for grid<Cities>

[Stamo Gochev]

Hello,

I investigated the case and a possible way to get the foreign key working when the grid is bound to a DataTable and dynamic model is to create a SelectList in the ViewData beforehand like this:

public ActionResult Index()
{
    DataTable products = Products();

    PopulateCategories();
    return View(products);
}

private void PopulateCategories()
{
    //var dataContext = new SampleEntities();
    var categories = new[]
    {
        new
        {
            CategoryID = 1,
            CategoryName = "category1"
        },
        new
        {
            CategoryID = 2,
            CategoryName = "category2"
        },
    };

    ViewData["CategoryID"] = new SelectList(categories, "CategoryID", "CategoryName", categories.First().CategoryID);

    ViewData["categories"] = categories;
}

Then this variable can be used in the view:

@(Html.Kendo().Grid<dynamic>()
    .Name("Grid")
    .Columns(columns =>
    {
        foreach (System.Data.DataColumn column in Model.Columns)
        {
            var c = columns.Bound(column.ColumnName);


            if (column.ColumnName == "UnitPrice")
            {
                c.ClientFooterTemplate("sum:#:sum#").ClientGroupFooterTemplate("sum:#:sum#");
            }
            else if (column.ColumnName == "UnitsInStock")
            {
                c.ClientFooterTemplate("max:#:max#").ClientGroupFooterTemplate("avg:#:average#");
            }
            else if (column.ColumnName.Contains("Category"))
            {
                columns.ForeignKey(column.ColumnName, (SelectList) ViewData[column.ColumnName]).Title("Category").Width(150);
            }
        }

        columns.Command(cmd=>cmd.Edit());
    }

Please note that you might need to make additional modifications to the column, e.g. change its template, title, etc. as this is more or less a workaround because of the dynamic configuration of the grid.

I am attaching a modified version of this example:

https://github.com/telerik/ui-for-aspnet-mvc-examples/tree/master/grid/binding-to-datatable

with the above suggestions applied.

Regards,
Stamo Gochev
Progress Telerik

[Kemal]

Hi,
i tried this line it gives no error,

columns.Bound(column.ColumnName)

but this line,

columns.ForeignKey(column.ColumnName,...

not working. I'm getting error "'object' does not contain a definition for 'CountryID'" becouse its needs to be in model and schema.

i tried this,

var CountryID = "";

columns.ForeignKey(p => CountryID,...

Now its working only with variable name if it is same as field name. Variable value or variable type does not matter.

Also, i tried with this "column.ColumnName" but not working. Because it requires CountryID as variable and same name with field name.

var CountryID = "";
columns.ForeignKey(p => column.ColumnName,...

In other words, I am trying to get column.ColumnName and convert it to CountryID in a way so that I can manage to work out this line to have dynamic dropdown with ForeignKey.

[Stamo Gochev]

Hello,

The project that I sent n my previous post works with dynamic setting of the foreign key column and no error is thrown when the column and its values is set like this:

columns.ForeignKey(column.ColumnName, (SelectList) ViewData[column.ColumnName]).Title("Category").Width(150);

ViewData["CategoryID"] = new SelectList(categories, "CategoryID", "CategoryName", categories.First().CategoryID);

Can you have a look at it and use the same approach? Furthermore, can you confirm that you have specified the selectedValue parameter in the SelectList in the above statement:

ViewData["CategoryID"] = new SelectList(categories, "CategoryID", "CategoryName", categories.First().CategoryID);

If this is not so, it is expected for the mentioned error to be thrown as setting that is a requirement for the DropDownList to function as expected.

What I can suggest in this case (if you cannot default to "categories.First().CategoryID") is to use Reflection and get this value dynamically. This is a general approach that is not connected to the grid component, so feel free to use any other method that suits your needs (or update the current one). The following snippet demonstrates this idea:

foreach (DataColumn column in data.Columns)
{
    if (column.ColumnName.Contains("Category"))
    {
        var defaultCategory = categories.First();
        var field = column.ColumnName.Remove(column.ColumnName.IndexOf("ID"));
        var dataValueField = field + "Name";

        ViewData[column.ColumnName] = new SelectList(categories, column.ColumnName, dataValueField,defaultCategory.GetType().GetProperty(column.ColumnName).GetValue(defaultCategory));

        //ViewData["CategoryID"] = new SelectList(categories, "CategoryID", "CategoryName", categories.First().CategoryID);
    }
}

Note that you can do the same for other properties as well. I am attaching a modified version of my previous project with the above suggestion applied.

On the other hand, it is also required to know which column(s) will be treated as foreign key column(s) beforehand in order to define them correctly, i.e. populate the DropDownList with values from the ViewData object. If they are not known beforehand, how will you determine whether a certain column should be initialized as a bound column or a foreign key column? The project that I sent relies on such a condition:

...
else if (column.ColumnName.Contains("CategoryID"))
{
    columns.ForeignKey(column.ColumnName, (SelectList) ViewData[column.ColumnName]).Title("Category").Width(150);
}
...

In addition, it is expected for the foreign key column to work with an ID (in this case "CategoryID"), which matches the field from the model as this field makes the foreign key relationship and is used by the DropDownList in the editor template. As I explained in my previous post, having such a dynamic structure of the grid requires certain configurations that might not be necessary in a scenario, in which the model is strongly-typed.

Regards,
Stamo Gochev
Progress Telerik

[Kemal]

I have done Mr. Stamo,

its working full dynamic now.

Thank you.

 

[Kemal]

and you have done :) thank you.

[John]

Hello,

I have been asked to implement a very similar grid, and have it about 95% of the way there, thanks to Mr. Stamo's example. The biggest difference between the provided example and mine, is that my client would like the grid to be InLine editable. I have it working to the point where the user can select a list, and it will return the correct id to the controller - but i'm seeing strange metadata that I believe to be from the select list above the actual list:

 

[John]

Sorry, wasn't done there. I've attached a picture of this occurring. I believe it to be related to how the SelectList i'm passing through the ViewData, somehow. If I change it to this:

 

1.FKData.Add(new SelectListItem { Text = "Germany", Value = "1"});
2.FKData.Add(new SelectListItem { Text = "France", Value = "2"});
3.ViewData["CountryID"] = new SelectList(FKData, "Value", "Text", 1);

 

..then instead of 'FalseFalse', I see '12' appear above the-dropdown. Please help - this works perfectly outside of the strange metadata above the drop down. I'll include code-snippets below:

entitygrid.cshtml

@(Html.Kendo().Grid<dynamic>
                            ()
                            .Name("EntityGrid")
                            .Columns(columns =>
                            {
                                foreach (System.Data.DataColumn column in Model.Columns)
                                {
                                    var c = columns.Bound(column.ColumnName);

                                    //Attempt at mapping Foreign Key drop-downs to grid.
                                    if (column.ExtendedProperties["IsForeignKey"].Equals(true))
                                    {

                                        c.ClientTemplate("#=GetFKName(" + column.ColumnName + ",'" + Model.Namespace + "','" + column.ExtendedProperties["ForeignKeyTableName"] + "')#");
                                        c.Title(column.ExtendedProperties["ForeignKeyTableName"] + " Name");

                                        columns.ForeignKey(column.ColumnName, (SelectList)ViewData[column.ColumnName])
                                                .Title(column.ExtendedProperties["ForeignKeyTableName"] + " Name")
                                                .ClientTemplate("#=GetFKName(" + column.ColumnName + ",'" + Model.Namespace + "','" + column.ExtendedProperties["ForeignKeyTableName"] + "')#")
                                                .HeaderHtmlAttributes(new { style = "text-align: center" })
                                                .ClientHeaderTemplate("").ClientGroupHeaderTemplate("")
                                                .Hidden(true)
                                                //.EditorTemplateName("GridForeignKey");

                                    }

                                }

                                columns.Command(cmd => cmd.Edit());
                                columns.Command(cmd => cmd.Destroy());
                            })
                            .Pageable()
                            .Sortable()
                            .ToolBar(toolBar => toolBar.Create())
                            .ToolBar(tb => tb.Save())
                            .Editable(ed => ed.Mode(GridEditMode.InLine))
                            .Events(events => events.Edit("OnEdit"))
                            .Filterable()
                            .Groupable()
                            .AutoBind(true)
                            .Scrollable()
                            .DataSource(dataSource => dataSource
                            .Ajax()
                            .Model(model =>
                            {
                                if (Model.Columns.Count > 0)
                                {

                                    var id = Model.PrimaryKey[0].ColumnName;
                                    model.Id(id);
                                    foreach (System.Data.DataColumn column in Model.Columns)
                                    {
                                        var field = model.Field(column.ColumnName, column.DataType);
                                        if (column.ColumnName == id)
                                        {
                                            field.Editable(false);
                                        }
                                    }
                                }


                            })
                            .PageSize(20)
                            .Create(create => create.Action("Insert", "Entity", new { EntityName = Model.TableName }))
                            .Read(read => read.Action("Read", "Entity", new { SelectedSchema = Model.Namespace, SelectedTable = Model.TableName }))
                            .Update(update => update.Action("Update", "Entity", new { EntityName = Model.TableName }))
                            .Destroy(delete => delete.Action("Delete", "Entity", new { EntityName = Model.TableName }))
                            )

)

 

PopulateCategories.cs

private void PopulateForeignKeys(List<ColumnDetail> ColumnDetails)
 {
     IList<DynamicForeignKey> FKData = new List<DynamicForeignKey>();

     foreach (ColumnDetail FKColumn in ColumnDetails)
     {

     }


     DynamicForeignKey test1 = new DynamicForeignKey(1, "Germany");
    DynamicForeignKey test2 = new DynamicForeignKey(2, "France");

     FKData.Add(test1);
     FKData.Add(test2);


     //FKData.Add(new SelectListItem { Text = "Germany", Value = "3", Disabled = true, Selected = false });

     //SelectListItem newItem = new SelectListItem("France", "2", true, false);
    // newItem.Selected = true;
     //FKData.Add(newItem);

     ViewData["CountryID"] = new SelectList(FKData, "CountryID", "name", 1);

     ViewData["FKData"] = FKData;

 }

 

 

[Kemal]

Hi John,

i'am posting working code for dynamic dropdowns..

var xxf = AdminBusinessLayer.GetDd(selectedTable, ddName);

var enumerator = xxf.GetEnumerator();
while (enumerator.MoveNext())
{
    var first = enumerator.Current;
    var ValueControl = first.GetType().GetProperty("Value").GetValue(first).ToString();
    ViewData[columnName] = new SelectList(xxf, "Key", "Value", first.GetType().GetProperty("Key").GetValue(first));
}

columns.ForeignKey(columnName, (SelectList)ViewData[columnName]).Width(500);

///// AND MY DATALAYER CONTROLLER (Never Mind about extra language codes... look to dropdown KEY VALUE variables)

public IQueryable GetDd(string tableName, string translationTableFieldName)
{
    var connection = ConfigurationManager.ConnectionStrings["xEntities"].ConnectionString;
    var id = translationTableFieldName.Replace("NAME", "ID");
    var languageCode = Globalisation.GetDefaultOrAssignedLanguageCode();

    if (translationTableFieldName == "languageCultureCode")
    {
        id = "LanguageCultureID";
    }

    var command = "some query ";

    var data = new List<dynamic>();
    using (var dataAdapter = new SqlDataAdapter(command, connection))
    {
        var dataTable = new DataTable();
        dataAdapter.Fill(dataTable);

        foreach (DataRow row in dataTable.Rows)
        {
            if (row[translationTableFieldName].ToString() != "")
            {
                data.Add(new
                {
                    Key = row[id].ToString(),
                    Value = row[translationTableFieldName].ToString(),
                    Field = id,
                    Fname = translationTableFieldName
                });
            }
        }
        return data.AsEnumerable().AsQueryable();
    }
}

[John]

Hey Kemal,

Thank you for taking the time to post your solution - i've learned that the strange metadata I was seeing in the FK column while in edit mode had to do with how I was manipulating the ViewData. I incorporated your ideas, and changed my usage of the ViewData - this fixed it completely. Thank you so much - saved me a giant headache : )