Blog

How to display dynamic data results using C# ASP.Net MVC and ag - grid ???

After long time i found a good challenge when i was trying to generate a dynamic front end report. I faced two problems while dealing this requirement, first one when we tried to implement this change and later when we were upgrading/enhancing the requirement. Let me explain the problem statement clearly before we move on to the solution.

Problem 1 : Based on user requirement, we needed to provide an interface in the front end to view certain feed tables. So, what it does is, provide the user a chance to look at the data in these tables with out learning SQL, logging into SSMS and then writing a select query on these tables.Simply put, empower a normal web user abstracting away the complexities of writing sql queries and provide an interface.

Design : So the front end would have a drop down which displays all the required feed tables, when any of them is selected, we would display the columns in that table in a select list. User would have a choice to choose which one them would show up in the end results. Also, user has a chance to filter the data (equivalent to where clauses in select queries) using "AND" boolean operator and filter conditions drop down would have all the logical operators -> , = , > , < , >= , <= , Like, Not Like and between. Based on this i have written a procedure which takes all the above as inputs, creates a dynamic query and returns two data tables in the form of a data set. First data table contains the report name, report description and report columns. Second data table contains the actual data.

Solution: As we were already using JQuery grid to display results in other pages , for consistency we had to use the same here as well and do an ajax call to get the results and typically a function to make such ajax calls would look like this. Refer https://datatables.net/manual/data/ for more info on using jquery data tables.

function GetResults() {
//section to gather parameters needed to make ajax call

.....
$('#LoadingIcon').show();

$('#spinnergif').show();
$.ajax({
url: <url with params> ,
type: "GET",
contentType: "application/json; charset=utf-8",
success: function (data) {
if (typeof data !== "undefined" && data != "" && data.length > 0) {
dataTable = {
"data": data
};
columns = [ {<any data manipulations or simply display data for each column> },{},{},{}.... ];
columnDefs = [{
'targets': 0
}];
BuildDataTable("tableid", dataTable, columns, columnDefs, other variables for grid customization);
}
$('#LoadingIcon').hide();
$('#spinnergif').hide();
},
error: function (error) {
console.log(error);
}

});

};

But I was lazy this time and din't want to use ajax since i have to make an effort to do some data processing to build the columns part (highlighted above in yellow color). So the solution became simple. Use dynamic type and expando objects and build the datatable. Then, my model would consist

public List<string> columns{ get; set; }
public List<dynamic> reportdata { get; set; }
and using below method i would convert data table to dynamic list.

private List<dynamic> ConvertDtToList(DataTable dt)

{
var data = new List<dynamic>();
foreach (var item in dt.AsEnumerable())
{
// Expando objects are IDictionary<string, object>
IDictionary<string, object> dn = new ExpandoObject();
foreach (var column in dt.Columns.Cast<DataColumn>())
{
dn[column.ColumnName] = item[column];
}
data.Add(dn);
}
return data;
}

and then I would iterate over the 'columns' and 'reportdata' in razor view using foreach loop, something like this.

<thead>
@{
<tr>
@foreach (var c in Model.columns)
{
<th style="text-align:center; width:auto">@c</th>
}
</tr>
}
</thead>
<tbody>
@{
foreach (var c in Model.reportdata)
{
<tr>
@foreach (KeyValuePair<string, object> prop in c)
{
<td>@prop.Value</td>
}
</tr>
}
}
</tbody>

Now to the next question, how do i format this to a jquery data table ? and that is very simple.

var reporttable = $('#tableid').DataTable();
$('#tableid').attr("class", 'tts-datatable table table-hover dataTable no-footer dtr-inline');

BOOM !!! your results are formatted as jquery data table and you get a search box to filter and everything falls into place. Now i rest for some time.

Problem 2 : As time passed, we upgraded from jquery data table to ag-grid and the inevitable question popped. Is it possible to upgrade the dynamic report generator too ?? so that it is possible to apply filters like "like" and other "group by", "order by" conditions.

Solution 2 : Now I had to scrap the plan of iterating in razor view. I had to make the ajax call work for dynamic data. But the problem is the ag-grid expects the data in a fixed format

eg: refer https://www.ag-grid.com/javascript-grid/ for more detailed explanation of how to use ag grid (also, below example is from the same site.)

[
  {make: "Toyota", model: "Celica", price: 35000},
  {make: "Ford", model: "Mondeo", price: 32000},
  {make: "Porsche", model: "Boxter", price: 72000}
];

But when you send the dynamic list, it is interpreted in json as

(3) [Array(3), Array(3), Array(3)]
0: Array(3)
0: {Key: "make", Value: "Toyota"}
1: {Key: "model", Value: "Celica"}
2: {Key: "price", Value: 35000}
..
..
length: 3
But this is not what ag-grid expects, so i had to rethink my strategy. One option which i never attempted before was to create a class during run time and in fact after few failed attempts of formatting the data by rearranging and parsing to JSON, it felt like the only option. So reluctantly i started creating the class needed for it. Refer this wonderful article https://www.c-sharpcorner.com/UploadFile/87b416/dynamically-create-a-class-at-runtime/ for more info.

After the base is set up, all i had to do was intantiate the object, assign value from the result set returned from the proc and add to the list.

List<string> repColumns = new List<string>();
List<Type> repColTypes = new List<Type>();
foreach (var col in myList.ReportColumns)
{
repColumns.Add(col);//gathering column names to build column definitions
repColTypes.Add(typeof(string));//considering each data vale as string
}
string className = myList.ReportName.Split(' ').Aggregate((a, b) => a + b).ToString();
MyClassBuilder MCB = new MyClassBuilder(className);//dynamic class creation, refer article linked above
var myclass = MCB.CreateObject(repColumns.ToArray(), repColTypes.ToArray());
Type TP = myclass.GetType();
IList list1 = (IList)Activator.CreateInstance(typeof(List<>).MakeGenericType(new Type[] { myclass.GetType() }));
foreach (var i in myList.ReportData)
{
var a = Activator.CreateInstance(myclass.GetType());
foreach (var k in i)
{
PropertyInfo pinfo = myclass.GetType().GetProperty(k.Key);
pinfo.SetValue(a, k.Value.ToString());
}
list1.Add(a);
}

yayyyyyyy !!!! Job done !!! Cheers !!! Now you have your ag - grid with dynamic data. Sleep well.

All Blogs