Can I use DataTable to dynamically populate an editable Grid

Question

I'm searching for a solution to populate a grid without knowing the database schema at design time.

Some quick background: I'm new to web development. I have experience in the traditional 3GL and proprietary languages and a bunch of general IT knowledge (like SQL Server and T-SQL). For the past few months I have been studying C#, ASP.Net Core, MVC and so son.
I'm know developing a web application using ASP.Net Core 2.1 and VS2017. The database system - which I cannot modify - allows users to define their own tables and table layouts. My application needs to provide a UI for the users to define these tables and to enable them to enter and maintain data in these tables.

So while designing/developing the application I don't know which tables there will be or how many, nor do I know which and how many fields there are in a table, what the data types are and if there are relationships (lookups) defined between the tables. This also means I cannot define any Models in development.

I can access the database through web services with a specific API so at runtime I can list the tables and I can get a definition of a table (field names, data types, lookups). Small example, if a table has 10 fields (or columns) and contains 50 records I receive 500 instances of an object telling me the field name, data type and value.
Based on samples I found from Microsoft I have functions that convert the result from the webservice call (the 500 objects) into a DataTable and that act like a Datasource (e.g. CRUD, paging, sorting and filtering).

Function to get the data (I can call this from the Controller for example):

   public DataTable GetEntityData(string catalogName, string entityName)
   {

   mdsProxy = CreateMdsProxy("http://192.168.0.111:8888/service/service.svc");
   var crud = new MDSEntityCRUDOperations(mdsProxy, catalogName, entityName);
          int skip = 0; int top = 0; string orderBy = string.Empty; string searchTerm = string.Empty; int totalMembers = 0;
          return crud.GetData(skip, top, orderBy, searchTerm, ref totalMembers);

        }

Function GetData
public DataTable GetData(int skip, int top, string orderBy, string searchTerm, ref int totalMembers,string attributeGroup = null, List<string> attributeList = null)
    {
      List<MetadataAttribute> atts = GetAttributeList(attributeGroup);

      var members = GetMembers(skip, top, orderBy, searchTerm, attributeNames, ref totalMembers).ToList();
      var dt = ToDataTable(members, atts);
      return dt;
    }

Converting the result into a DataTable:

    public DataTable ToDataTable(IEnumerable<Member> members, IList<MetadataAttribute> attributes)
    {

      DataTable t = new DataTable();

      foreach (var att in attributes)
      {
        Type dataType;
        if (att.AttributeType == AttributeType.Domain) // should become a lookup column/cell
        {
          dataType = typeof(string);
        }
        else if (att.AttributeType == AttributeType.FreeForm)
        {
          if (att.DataType == AttributeDataType.DateTime)
          {
            dataType = typeof(DateTime);
          }
          else if (att.DataType == AttributeDataType.Link)
          {
            dataType = typeof(string);
          }
          else if (att.DataType == AttributeDataType.Number)
          {
            dataType = typeof(decimal);
          }
          else if (att.DataType == AttributeDataType.Text)
          {
            dataType = typeof(string);
          }
          else
          {
            throw new InvalidOperationException("Attribute DataType not supported" + att.DataType);
          }
        }
        var col = t.Columns.Add(att.Identifier.Name, dataType);
      }

t.PrimaryKey = new DataColumn[] { t.Columns["Code"] };

t.BeginLoadData();

var hasCode = t.Columns.Contains("Code");
var hasName = t.Columns.Contains("Name");

      foreach (var m in members)
      {

        var r = t.NewRow();
        if (hasCode)
        {
          r["Code"] = m.MemberId.Code;
        }

        if (hasName)
        {
          r["Name"] = m.MemberId.Name;
        }

        foreach (var att in m.Attributes)
        {
          var value = att.Value;
          if (value == null || (value is string && (string)value == string.Empty))
          {
            r[att.Identifier.Name] = DBNull.Value;

          }
          else if (value is MemberIdentifier)
          {
            r[att.Identifier.Name] = ((MemberIdentifier)value).Code;
          }
          else
          {
            r[att.Identifier.Name] = value;
          }

}

        t.Rows.Add(r);
      }
      t.EndLoadData();

return t;

}

My questions are:
1. can I use the DataTable as a source for an editable EJS2 grid?
2. if yes to 1 I'm still missing the link on how I can define the properties on the columns like type and lookup.
3. the grid needs to support all CRUD operations, preferably inline editting, is that possible with the DataTable as a source? I have functions on server side that can handle the CRUD towards the database using the web service.
4. Would you recommended another approach than using the DataTable? Can I use the EJ DataManager somehow?

Rahul Narayanasamy · Answer

Hi Ronald, Thanks for contacting Syncfusion support. Query 1: Can I use the DataTable as a source for an editable EJS2 grid? You can use DataTable as source for an editable grid. In this requirement, we have used URL adaptor to achieve your requirement. We have create the DataTable and pass it to the server by using custom convertor. Here we used Newtonsoft JSON convertor to convert JSON data to Table data. The DataTableConverter[Models/DataTableConvertor.cs] convert the JSON data into table data. Please find the code example for your reference. [HomeController.cs] public IActionResult UrlDatasource([FromBody]Data dm) { var order = getData(); DataTable dt = DTable(); var Data = order.ToList(); int count = order.Count(); DataSet dataSet = new DataSet("dataSet"); dataSet.Namespace = "NetFramework"; dataSet.Tables.Add(dt); return Json(new { result = dt, count = 2 }, new JsonSerializerSettings() { Converters = new List() { new Newtonsoft.Json.Converters.Core2.DataTableConverter() } }); } public DataTable DTable() { DataTable dt = new DataTable(); dt.Columns.AddRange(new DataColumn[4] { new DataColumn("OrderID", typeof(int)), new DataColumn("CustomerID", typeof(string)), new DataColumn("Freight", typeof(int)), new DataColumn("ShipCity", typeof(string)) }); dt.Rows.Add(1, "Ram",22, "Chennai"); dt.Rows.Add(2, "John", 33, "Mumbai"); dt.Rows.Add(3, "Raj", 44, "Delhi"); return dt; } [index.cshtml]

Query 2: if yes to 1 I'm still missing the link on how I can define the properties on the columns like type and lookup. You can define the properties for the column by using default grid properties. Please find complete set of properties in the below link. Link: https://help.syncfusion.com/cr/aspnetcore-js2/Syncfusion.EJ2.Grids.GridColumn.html Query 3: the grid needs to support all CRUD operations, preferably inline editting, is that possible with the DataTable as a source? I have functions on server side that can handle the CRUD towards the database using the web service. Grid supports all CRUD operations as DataTable as a source. You can perform CRUD operations in the server side. Please find the below link for your reference. Link: https://ej2.syncfusion.com/aspnetcore/documentation/grid/edit.html#persisting-data-in-server Query 4: Would you recommended another approach than using the DataTable? Can I use the EJ DataManager somehow? You can use DataTable, List based on your requirement. [index.cshtml]

We have created a simple sample based on your requirement. Please find the below sample for your reference. Sample Link: http://www.syncfusion.com/downloads/support/directtrac/general/ze/sample-dataTable1021036498 Regards, Rahul Narayanasamy.

Ronald Dirkx · Answer

Thank you for you extensive reply.I've been refactoring a few of my backend classes and will now start to try and implement your suggestions and examples.I'll provide feedback as soon as I can.

Madhu Sudhanan P · Answer

Hi Ronald, 
Please try the provided solution and get back to us if you require further assistance. 
Regards, 
Madhu Sudhanan P

Ronald Dirkx · Answer

I briefly explained in my intro that I don't know the fieldnames at design time so I can't list the columns with taghelpers in the .cshtml. I guess I need to construct the table on client side using Javascript? I have a simple page where the user can select the table he wants to edit using 2 dropdowns. I need to generate the table after a value is selected in the second dropdown.Can you provide me with a Javascript sample on how to iterate through the Columns definitions of the Datatable to generate the columns (field, header, edittype, required …) in the grid? (function GetEntityDate in the page). Would you generate the table in the same page or would you use a Partial View?@model MDS_MVC_Proto2.Models.EntitySelectModel@{ ViewData["Title"] = "Index";}

Select Catalog & Entity

@* -- Catalog *@

Select Catalog @Html.DropDownListFor(m => m.CatalogModel, new SelectList(Model.CatalogModel, "Id", "CatalogVersion"), new { @id = "selectCatalog", @onchange = "javascript:GetEntity(this.value)", @class = "form-control" })

@* -- Entity *@

Select Entity

What function or Url is executed when the users navigates to a different page using pagination? Do I need to handle this with Javascript as well?ThanksRonald

Rahul Narayanasamy · Answer

Hi Ronald, Query 1: I don't know the fieldnames at design time so I can't list the columns with taghelpers in the .cshtml. I guess I need to construct the table on client side using Javascript? The columns are automatically generated when columns declaration is empty or undefined while initializing the grid. All the columns in the DataSource are bound as grid columns. Please find the below code example for your reference. In the below code example, we don’t mention columns in index.cshtml. [Code example] [index.cshtml]

Please find the below sample link for your reference. Sample Link: http://www.syncfusion.com/downloads/support/directtrac/general/ze/sample587227874 Query 2: What function or Url is executed when the users navigates to a different page using pagination? Do I need to handle this with Javascript as well? When you navigate to another page, UrlDatasource action will be executed. In server side, the following parameters are available to perform server side operations. Parameters Description requiresCounts If it is true then the total count of records will included in response. skip It holds the number of records to skip. take It holds the number of records to take. sorted It contains details of current sorted column and its direction. table It defines data source table name. where It contains details of current filter column name and its constraints. Please get back to us if you need further assistance on this. Regards, Rahul Narayanasamy.

Ronald Dirkx · Answer

Hi Rahul,I can generate the grid and navigation is working fine. Serialization of the DataTable to Json went well (although I did use Newtonsoft.Json.Converters.DataTableConverter() and not Newtonsoft.Json.Converters.Core2.DataTableConverter() as Core2 doesn't exist in the latest NewtonSoft package 11.0.2 from Nuget ) and using the properties from the DataManagerRequest for paging works fine.However I'm achieving this by some hardcoding in my controller method because when I define the grid in HTML it renders the grid when the page is loaded/rendered. As I explained in my reply from July 5th the user first needs to select which table from the database he wants to edit. Only after the user made his selection can I render the grid. I see 2 options for this; 1) using javascript invoked on the onchange of the second select component, 2) rendering a partial view.I'm not sure but I think I prefer the first option unless you have a better solution of course.A) Can you provide me with a sample javascript that generates the grid which is currently defined in HTML as following:

B) Additionally I would like to render the columns in javascript as well because I need more control over the data types and foreign key lookups than what the AutoGenerate is doing. Can you show me how to loop through the columns definition and use values like name, type and caption? I need Caption as I need to support multiple languages.C) I need to pass 2 additional variables to my controller method. I think I found a thread in the forums that these can be added to the DataManagerRequest??

Ronald Dirkx · Answer

EDIT: do I need to define an empty grid in HTML first or just a div that I refer to from script?

Rahul Narayanasamy · Answer

Hi Ronald, Query 1: Can you provide me with a sample javascript that generates the grid which is currently defined in HTML as following: We have validated your query and you can achieve your requirement by using below method. You have to create PartialView in Shared folder. In that partial view, you need to add ejs grid structure. You need to scripts tag in your code as like below. [partial view page] [GridViewPartial]

You need to return partial view data from HomeController. [HomeController] public IActionResult GridViewPartial() { return PartialView("GridViewPartial"); } Query 2: Additionally I would like to render the columns in javascript as well because I need more control over the data types and foreign key lookups than what the AutoGenerate is doing. Can you show me how to loop through the columns definition and use values like name, type and caption? I need Caption as I need to support multiple languages. To achieve this requirement, we have to generate data from server side and you need to mention in your partial view grid structure like below. [partial view page] [GridViewPartial]

[HomeController] public IActionResult GridViewPartial() { ViewData["columns"] = new List() { new Syncfusion.EJ2.Grids.GridColumn() { Field = "OrderID" }, new Syncfusion.EJ2.Grids.GridColumn() { Field = "CustomerID" } }; return PartialView("GridViewPartial", ViewData); } In the above code, we have showed the way to set the columns in the server side, you can set the type, header text so on at the server side itself. If this didn’t helped then please let us know, we will provide the way to achieve this using javascript. Query 3: I need to pass 2 additional variables to my controller method. I think I found a thread in the forums that these can be added to the DataManagerRequest?? For passing additional parameters, you need to extend the DataManagerRequest and use the extended class to bind the model as below. [HomeController] public IActionResult UrlDatasource([FromBody]ExtendedDataManagerRequest dm) { var order = getData(); DataTable dt = DTable(); var Data = order.ToList(); int count = order.Count(); DataSet dataSet = new DataSet("dataSet"); dataSet.Namespace = "NetFramework"; dataSet.Tables.Add(dt); return Json(new { result = dt, count = 2 }, new JsonSerializerSettings() { Converters = new List() { new Newtonsoft.Json.Converters.Core2.DataTableConverter() } }); } Please get back to us if you need further assistance on this. Regards, Rahul Narayanasamy.

Ronald Dirkx · Answer

Hi Rahul,I got most of it working. I just have additional questions on passing additional parameters to the server as I need those to retrieve the data from the backend.I need to pass more than 1 parameter from the grid to the server: is the following syntax correct??<ejs-grid id="entityGrid" columns="@ViewData["columns"]" query="new ej.data.Query().addParams('catalogGuid', @ViewData["catalogGuid"]).addParams('entityGuid', @ViewData["entityGuid"])">I don't have the DataManagerRequest extended class at my disposal (I'm using ASP.Net Core).Can you provide me with an example on how to access the parameters from the DataManagerRequest? (I think I saw it sometime ago on the forum but can't find it again)Thanks

Rahul Narayanasamy · Answer

Hi Ronald, 
 
Query: I don't have the DataManagerRequest extended class at my disposal (I'm using ASP.Net Core). Can you provide me with an example on how to access the parameters from the DataManagerRequest? 
 
You need to extend DataManagerRequest class for passing additional parameters. The derived class should contain the additional parameter name as their properties. Please find the below code example for your reference. Using the below code, we can get the additional parameter Id at the server side, in such a way add your additional parameter name as property to this class. 
 
[Code Example]  [HomeController]  [ExtendedDataManagerRequest class] 




public class ExtendedDataManagerRequest: DataManagerRequest 
        { 
             
            public int id { get; set; } 
         // place name same as parameter name 
        } 
 
[HomeController] 




public IActionResult UrlDatasource([FromBody]ExtendedDataManagerRequest dm)  
    {  
      var order = getData();  
            DataTable dt = DTable();  
            var Data = order.ToList();  
            int count = order.Count();  
  
            DataSet dataSet = new DataSet("dataSet");  
            dataSet.Namespace = "NetFramework";  
            dataSet.Tables.Add(dt);  
            return Json(new { result = dt, count = 2 }, new JsonSerializerSettings() {  
                Converters = new List<JsonConverter>() { new Newtonsoft.Json.Converters.Core2.DataTableConverter() }  
            });  
        }  
 
 
 
Please get back to us if you need further assistance on this. 
 
Regards, 
Rahul Narayanasamy.

Ronald Dirkx · Answer

Hi Rahul,thanks, not that experienced with C# yet.I extended the class but how can I pass the values for these variables from the grid (client) to the server? Is the below the correct syntax:thanksRonald

Ronald Dirkx · Answer

Based on some other threads in the forum I've added actionBegin="setGuids" to the grid definition and created the following script:

actionBegin="setGuids">

function setGuids(args) {
args.model.query.addParams("CatalogGuid", '@ViewData["catalogGuid"]');
args.model.query.addParams("EntityGuid", '@ViewData["entityGuid"]');
}

If I add the script to the Partial View I get the following error in Edge DevTools:
SCRIPT5009: 'setGuids' is not defined

I then added the script to the view containing the call to the Partial View and then when running the application I get the following error in Edge DevTools:
SCRIPT5007: Unable to get property 'query' of undefined or null reference

Any suggestions?

Mohammed Farook J · Answer

Hi Ronald, Query : I extended the class but how can I pass the values for these variables from the grid (client) to the server? Is the below the correct syntax: Yes it is correct syntax for send additional parameter by using ‘ViewData’. Please ensure to include this variable is in the extended DataManagerRequest class definition as follows. Please find the code example for your reference. public class ExtendedDataManagerRequest: DataManagerRequest { . . . public int catalogGuid { get; set; } public int entityGuid { get; set; } } // get data from url public IActionResult UrlDatasource([FromBody]ExtendedDataManagerRequest dm) { . . . }); } Query : If I add the script to the Partial View I get the following error in Edge DevTools:SCRIPT5009: 'setGuids' is not defined Please define the actionBegin definition in your View where grid is rendered or in your index page. Please find the code example. GridViewPartial.cshtml

Or GridViewPartial.cshtml

Index.cshtml Query: I then added the script to the view containing the call to the Partial View and then when running the application I get the following error in Edge DevTools:SCRIPT5007: Unable to get property 'query' of undefined or null reference We have validated the provided code and we found that you have used Essential JS1 Grid code for actionBegin event. Please find the code example for accessing actionBegin event in Essential JS2 as follows: Regards, J Mohammed Farook

Ronald Dirkx · Answer

Hi Mohammedfor some reason I can't get the script to work regardless of in which View I put the script the error is always 'script not defined'.However, using the query property of the grid it is working now, I only needed to enclose the Viewdata-part with single quotation marks in order to get them passed across properly, like so:query="new ej.data.Query().addParams('CatalogGuid', '@ViewData["catalogGuid"]').addParams('EntityGuid', '@ViewData["entityGuid"]')"Thanks, I have the grid working as needed now.

Mohammed Farook J · Answer

Hi Ronald,  
 
Thanks for your update. 
 
We have validated the reported issue and please ensure the followings: 
 

Place the script and grid in the same page.
Refer or place the script code/file before grid rendered code.
 
Still if you have faced same issue , please share the order of the script and grid code in your page and share the script code if possible. 
 
Regards, 
J Mohammed Farook

Parameters	Description
requiresCounts	If it is true then the total count of records will included in response.
skip	It holds the number of records to skip.
take	It holds the number of records to take.
sorted	It contains details of current sorted column and its direction.
table	It defines data source table name.
where	It contains details of current filter column name and its constraints.