How to create a Datatable that has a dynamic column structure?

Question

Hello, I need to create a Datatable in my MVC project that will not have predefined columns. The amount of columns should be defined from the amount of data existing in a BD field.

I have a field in my Model called Low Tier Id, it is a FK of another table (Low Tier table). I need to create columns for each Low Tier Id I have registered in the bank. Also, the columns cannot have the Low Tier Id as the title, but the Low Tier Name field, which I must pull through the FK.

The data that will appear in the fields will be from the Quantity field (this field is from the Base Hour table, the main one of the View). Each column should show the respective Quantity to the Low Tier Id column.

Until then, I was trying to create the columns this way, but still without passing the data that the fields should show and the titles of the correct columns:

JAVASCRIPT

var colsBaseLT = [];
function CreateColumnsBH() {
$.ajax({
    url: urlLTList, //referencia o método LT List da Controller
    dataType: 'json',
    type: 'POST',
    aynsc: false,
    success: function (result) {
        //a result trás um array de Low Tier Ids
        //A função abaixo percorre o Array e tenta criar uma coluna para cada elemento dele
        var ltLength = result.length;
        for (var i = 0; i < ltLength; i++) {
            colsBaseLT.push({ data: null, orderable: false, className: 'col-sm-2 text-center', title: "titulo", defaultContent: '' });
        }
    }
});


function CreateTableBH() {
$.ajax({
    url: urlCreateTableBH, //referencia o método GetBaseHour da Controller
    type: 'POST',
    success: function (result) {
        dataTableBH = DataTablesAjaxScrollHeight('#tableBaseHour', colsBaseLT, result.Grid, null, "350px");
    }
}); 

$(document).ready(function () {
    CreateColumnsBH();
    DataTablesAjaxPagination("#tableBaseHour", colsBaseLT, null, null, 12, 'BaseHour');
    CreateTableBH();
}

CONTROLLER (C#)

    public JsonResult GetBaseHour()
    {
        //pega os dados da tabela Base Hour, a principal da View
        return Json(new { Grid = new BaseHourBusiness().GetList<BaseHour>(Util.AuxiliaryMethods.BMPerRequestInstance)});
    }

    public JsonResult LTList()
    {
        var lowTierList = new LowTierBusiness().GetList<LowTier>(Util.AuxiliaryMethods.BMPerRequestInstance).Select(
                s => new
                {
                    s.Name
                }).ToList();
        //no Join acima eu tento pegar os LowTierNames presentes na tabela Low Tier
        return Json(lowTierList);
    }

EDIT: To better illustrate the Datatable structure I want to create, I leave the following image:

The names in the columns are the Low Tier Names, fields that come from the Low Tier table. The BD table of this View is called Base Hour and it has a FK of the Low Tier table which is the Low Tier Id field.

I need to pull the Low Tier Names by the Low Tier Ids so that the columns can acquire these names, and of course build the columns according to the amount of existing Low Tier ID.

The numbers, which are the table fields, come from the Quantity field. They should be displayed according to the corresponding Low Tier ID column.

Answer 1

Hello, using the Jquery Datatables it is possible to do something about.

As I do today: Let’s say that in my query, my json returns a list of items where the item object has {id, name, email}

ajaxResponse = [
 {id: 1, name: "Pedro Santos", email: "[email protected]"},
 {id: 2, name: "Fulano Santos", email: "[email protected]"},
 {id: 3, name: "Beltrano Santos", email: "[email protected]"},
];

The Jquery Datatables Plugin can be instantiated as follows:

$("#myTable").DataTable({
 data: data,
 columns: columns
});

Where the date expects an array of values, and Columns expects an array of objects. It got strange right, but the example will clear, let’s go to it.

data: as I said date expects an array that contains an array of values only, so date should be interpreted as such:

data = [
 ["1", "Pedro Santos", "[email protected]"],
 ["1", "Fulano Santos", "[email protected]"],
 ["1", "Beltrano Santos", "[email protected]"],
];

Columns: as I said Columns expects an array of objects, but this object will contain only the title attribute, which is the field title of its column, let’s see how it looks:

columns = [
 {title: "ID"},
 {title: "NOME"},
 {title: "EMAIL"},
];

Finally, as long as you return from your backend, a Columns header in this format, and an array list of values in the correct order of columns, your table will dynamically have the expected format.

See here a little of how my code turned out:

First I create an empty dataset; Then I create how I want to display the columns, remembering as I said, this information can come from your back-end, in this format to make your table with dynamic columns OK?

Now I took my return from ajax, and went through, formatting it as I want, once again, remembering that you can pre-format in the back end and bring the die in your way. Here I bring the data clean, and the device you will consume treats as you see fit.

After I assembled the line I add the array of values, into my dataset.

And finally??

I call a function that passes my table ID, data and columns.

IMPORTANT: if your idea is to have dynamic column, it is necessary to destroy the table before and recreate with this component of Jquery Datatable, after all both column and data have changed and its quantity also.

and bonus there goes the full example of my table getting like this:

I hope I helped, this was my first contribution on Stack Overflow, I was just a consumer.

If you do not use the plugin Jquery Datatables recommend you use, but still it is possible you make your table dynamic columns and data.

First, Voce needs to return the columns back-end, being an array of values with the columns name:

ajaxResponse = {
 colunas: ["ID", "NOME", "EMAIL"],
};

After your back-end needs returns data from columns ai can be an array of objects:

ajaxResponse = {
 colunas: ["ID", "NOME", "EMAIL"],
 linhas: [
  {id: 1, nome: "Pedro Santos", email: "[email protected]"}
 ]
};

now you need to assemble the table:

<table>
 <thead>
  <tr id='colunas'>
  
  </tr>
 </thead>
 <tbody id='linhas'>
 
 </tbody>
</table>

And your js, or jquery will look like this:

$(function(){
 $("#colunas").html(""); //apaga as colunas da ultima consulta se existir
 
 ajaxResponse.colunas.map((coluna) => {
  $("#colunas").append(`<td>${coluna}</td>`);
 });
 
 $("#linhas").html(""); //apaga as linhas da ultima consulta se existir

 ajaxResponse.linhas.map((linha) => {
  $("#linhas").append(`<tr><td>${linha.id}</td><td>${linha.nome}</td><td>${linha.email}</td></tr>`);
 });
});

so you’ll have your table of dynamic columns as your back-end returns.

Well, I hope I helped you.

Answer 2

If I understand your question correctly, you just need to mount a grid/table with the dynamic data coming from code-Behind, correct?

If this is the case, because you do not use in this case a Viewbag, with a list of your columns to be able to mount your Table head. Next to this, you pass another Viewbag with the results and put in a foreach to mount with Razor your HTML Dynamically once your Action is requested? Theoretically it would work for both a View and a _Partialview;