Universal dataTable adaptable to different data schemas

Question

I have a need to create an ajax dataTable that can adapt to a handful of different data sources. Each data source has different column names and data types. Therefore I cannot have set column names or data types when coding the dataTable. The one thing the various data sources have in common is each has a row_id identity column which is queried on to return one one record.

I have tried many variations of the following but have not yet been successful.

Sample tables in the database:

Table A:

Table B:

Details HTML:

<table id="tblDetails" class="tbl"></table>

Details Controller:

        public ActionResult GetDetails(int row_id, string table)
        {            
            DataTable dt = new DataTable();
            dt = helper.GetDetails(row_id, table);
            JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
            List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
            Dictionary<string, object> row;
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    row = new Dictionary<string, object>();
                    foreach (DataColumn col in dt.Columns)
                    {
                        row.Add(col.ColumnName, dr[col]);
                    }
                    rows.Add(row);
                }
            }
            return Json(rows, JsonRequestBehavior.AllowGet);
        }

js:

    var objParams = { 'table': table, 'row_id': row_id };
    $.ajax({
        url: $("#GetDetailsUrl").val(),
        method: "post",
        data: JSON.stringify(objParams),
        dataType: 'json',
        contentType: "application/json; charset=utf-8",
        success: function (data) {            
            var key = Object.keys(data)[0];
            var value = data[key];
            Object.keys(data).forEach(function (key) {
                console.log(key, value);
                // Everything works fine until the next line of code
                var row = '<tr><td>' + key + '</td><td> ' + value + '</td></tr>';
                $('#tblDetails').append(row);
            })
         }
});

When querying tableB & row_id = 2, the `console.log(key, value);' produces the following console output as expected:

However, var row = '<tr><td>' + key + '</td><td> ' + value + '</td></tr>'; fails. It results in key = 0 and value = undefined.

How can I get key & value to work in var row as it does when I output it to the console?

Answer 1

You will need to iterate over each key item in each row object using the Array.forEach(). In the snippet below I simulated your table examples in JSON. I created a function that requires passing in the table node and the table data. Then it creates the table columns no matter how many there are. I commented each line in the function so you can see what it is doing.

const tableDataA = [{
    row_id: 1,
    customer: `Company 1`,
    name: `Smith`
  },
  {
    row_id: 2,
    customer: `Company 2`,
    name: `Jones`
  },
];

const tableDataB = [{
    row_id: 1,
    one: 1,
    two: 2,
    canBeAnything: `XyZ`
  },
  {
    row_id: 1,
    one: 5,
    two: 62,
    canBeAnything: `foo`
  },
];

const tblDetailsA = document.querySelector(`#tblDetails-a`);
const tblDetailsB = document.querySelector(`#tblDetails-b`);

createTable(tblDetailsA, tableDataA);
createTable(tblDetailsB, tableDataB);

function createTable(tableNode, tableDataArray) {
  // Create the thead element
  const thead = document.createElement('thead');
  // Create the tr for the header
  const headerRow = document.createElement('tr');
  // Get the title for each column by getting 
  // the keys of the first object in the array.
  const keys = Object.keys(tableDataArray[0]);
  // Iterate through each of the keys 
  keys.forEach(key => {
    // Create the th element for the column title
    const th = document.createElement('th');
    // Assign the inner text of the th to the value of the key
    th.innerText = key;
    // Append the the element to the header row
    headerRow.append(th);
  });
  // Append the header row to the thead element
  thead.append(headerRow);
  // Append the thead element to the table
  tableNode.append(thead);
  
  // Create the tbody element
  const tbody = document.createElement('tbody');
  // Iterate though each object of the data array
  tableDataArray.forEach(row => {
    // Create the tr element for the row
    const tr = document.createElement('tr');
    // Iterate through each key in the row Object
    Object.keys(row).forEach(col => {
      // Create the td element for the table data
      const td = document.createElement('td');
      // Assign the inner text of the td
      td.innerText = row[col];
      // Add the td to the row
      tr.append(td);
    });
    // add the row to the tbody
    tbody.append(tr);
  });
  // add the tbody to the table  
  tableNode.append(tbody);
}

<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-rbsA2VBKQhggwzxH7pPCaAqO46MgnOM80zW1RWuH61DGLwZJEdK2Kadq2F9CUG65" crossorigin="anonymous">

<div class="container">
  <h5 class="text-center">Table A</h5>
  <table id="tblDetails-a" class="table"></table>
</div>
<div class="container">
  <h5 class="text-center">Table B</h5>
  <table id="tblDetails-b" class="table"></table>
</div>