C# MVC JsonResult dynamic datatable

Question

I have an MVC application and I need to draw tables from SQL to the web. I can't create a Model of a table beforehand, so I dynamically create a DataTable using JavaScript code in my CSHTML file:

var data,
  tableName = "#demotable",
  columns,
  str,
  jqxhr = $
    .ajax({
      data: { str: JSON.stringify("kurator") },
      url: MyAppUrlSettings.MyUsefulUrl,
      type: "GET",
      datatype: "json"
    })
    .done(function() {
      data = JSON.parse(jqxhr.responseText);
      debugger;
      // Iterate each column and print table headers for Datatables
      $.each(data.columns, function(k, colObj) {
        str = "<th>" + colObj.data + "</th>";
        $(str).appendTo(tableName + ">thead>tr");
        debugger;
      });
      // Add some Render transformations to Columns
      // Not a good practice to add any of this in API/ Json side
      data.columns[0].render = function(data, type, row) {
        return "<h4>" + data + "</h4>";
        debugger;
      };
      // Debug? console.log(data.columns[0]);
      $(tableName).dataTable({
        data: data.data,
        columns: data.columns,
        fnInitComplete: function() {
          // Event handler to be fired when rendering is complete (Turn off Loading gif for example)
          console.log("Datatable rendering complete");
        }
      });
      debugger;
    });

I need to return an array of JSON objects from ControllerAction. This is how I create my DataTable:

DataTable dt = new DataTable();

List<string> _columns = new List<string>() { "Kurator", "Filial", "Klient", "Saldo", "Docs", "no_Docs", "Change", "Status" };

for (int i = 0; i < _columns.Count; i++)
{
  cols.Add(new Columns { ColumnID = "data", ColumnName = _columns[i] });
  dt.Columns.Add(_columns[i], typeof(string));
}

// Add rows to Table

DataRow _ravi;

_ravi = dt.NewRow();                    

dt.Rows.Add(_ravi);

Then, finally, I need to put the array of columns and data into the JsonResult:

var data2 = debitor.Select(p => new {
  Kurator = p.Kurator,
  Filial = p.Filial,
  Klient = p.Klient,
  Saldo = p.Saldo,
  Docs = p.Docs,
  no_Docs = p.no_Docs,
  Change = p.Change,
  Status = p.Status
});

// var data1 = JsonConvert.DeserializeObject(data);

// var data = new JavaScriptSerializer().Serialize(dt, Formatting.Indented);

// JObject rss =  new JObject(new JProperty("title", "James Newton-King"));

var columns = cols.Select(p => new {
  data = p.ColumnName
});

return Json(new { data = data2, columns = columns }, JsonRequestBehavior.AllowGet);

This works correctly => it returns an array of objects (when from Model var data2), but when I try to get the array of rows from my DataTable (commented) there is no results in Json.

How can I correctly put the JsonResult into my DataTable rows? I can't use a Model because I don't know how many columns will be in the result of the SQL query.

Have you tried using a dynamic object? http://stackoverflow.com/questions/3142495/deserialize-json-into-c-sharp-dynamic-object — nik0lai, Dec 05 '16 at 09:12
Database result are just like a List of KeyValuePair (name of field and value of field). — User.Anonymous, Dec 05 '16 at 09:18
Tryed var data = JsonConvert.SerializeObject(dt); dynamic data1 = System.Web.Helpers.Json.Decode(data); return Json(data1 , JsonRequestBehavior.AllowGet); there is blank array of objects in JS data.data Here is response in browser: [{},{},{},{},{},{},{},{},{},{},{},{},{} — Roman Kozlov, Dec 05 '16 at 09:33
List of KeyValuePair gets array, but its add "Key" and "Value to result: "{"data":[{"Key":"Kurator","Value":1},{"Key":"Filial","Value":2} so cant be parsed by JS correctly it myst be {"data":[{"Kurator",1}... — Roman Kozlov, Dec 05 '16 at 10:07
string json = @"{'Klient':null,'Saldo':'16,866,147.39','Docs':'10,285,201.05','no_Docs':'6,580,946.34','Change':'00.00','Status':'4,141,030.29'}"; dynamic data = System.Web.Helpers.Json.Decode(json); return Json(new {data = data, columns = columns } , JsonRequestBehavior.AllowGet); Result in browser data:{}....nothing...Why??? — Roman Kozlov, Dec 05 '16 at 12:01
@RomanKozlov please edit your question if you have extra code to show. Code in comments is almost unreadable. — ADyson, Aug 22 '17 at 14:17

score 0 · Answer 1 · answered Feb 19 '19 at 02:06

The question is a bit compicated from my point of view.

If you are using Microsoft.AspNetCore.Mvc JsonResult class it will produce following results depending on input argument:

When using with List<>:

 using Microsoft.AspNetCore.Mvc;

 class SomeClass 
 {
     public string value1 {get; set;}
     public int value2 {get; set;}
 }

 // Controller code
 List<SomeClass> result = // Init list using Linq Select or similar
 return new JsonResult(result);

This code yeilds JSON like this (array of objects)

{
     [
          { value1: "some string",
            value2: 1
          },
          {
            value1: "another string",
            value2: 20
          }
     ]
}

When using with Dictionary<>

class AnotherClass 
{
    public string value1 {get; set;}
    public int value2 {get; set;}
}

// Controller code
// Dictionary should has key which became key in resulting object
Dictionary<string, AnotherClass> result = // Init dictionary 
return new JsonResult(result);

This code yeilds JSON like this (one big object). 'key1' and 'key2' are keys from Dictionary.

{
     key1: { 
               value1: "some string",
               value2: 1
           },
     key2: {
               value1: "another string",
               value2: 20
          }
}

C# MVC JsonResult dynamic datatable

1 Answers1