How to process data persistence to server in case of dynamic grid (no Model)

Question

I have a grid that I populate from a DataTable. This DataTable has no fixed columns and is dynamically generated at runtime.

I have received the support needed to get my grid to load the data - including pagination and additional parameters for the DataManager query - in this thread https://www.syncfusion.com/forums/138348/can-i-use-datatable-to-dynamically-populate-an-editable-grid

Now I'm working to implement saving any changes made to the data in the grid (Insert/Update/Delete) back to the server.

Based on documentation and other forum threads I've tried to get my server side code to work but haven't succeeded yet.
Documentation I consulted: https://ej2.syncfusion.com/aspnetcore/documentation/grid/edit.html#insert-record
(one note: there is a difference - error? - between the source code and the screenshot: the source code uses Insert([FromBody]EditableOrder value) whereas the screenshot uses Insert(EditableOrder value)
Forum threads I read: https://www.syncfusion.com/forums/127992/syntax-for-datasource-url-handler and https://www.syncfusion.com/forums/129711/crud-syntax-in-asp-net-corecontroller

As the data is loaded dynamically during runtime I can't implement a Model to pass to the grid. I'm creating a DataTable on-the-fly which I convert to Json as datasource for the grid.
As I don't have a model I cannot specify a model as parameter for the Controller action that is setup as the target for CRUD.
I've tried specifying a DataTable as parameter but this is always null.

I've done some testing with separate Insert/Update url, CrudUrl and BatchUrl using CRUDModel.
At debug I see a populated CRUDModel but I don't find a way to access all the data (like Children etc).

Question 1: how can I get the result back into an easy to process format as I need to iterate over all items to update the back end via an API. I would like to have an example for the indivudual Url's, CrudUrl as well as the BatchUrl.
Deserialization, converting back to DataTable, navigating the CRUDModel (Newtonsoft.Json.Linq.JObject) ... ??

Question 2: how can I access the values of the additional properties I added to the DataManagerRequest? I need to pass them to the back end API.

Question 3: what should be the return value of my Controller method given that I use a DataTable-converted-to-Json as datasource?

Sharing any tip to do things differently are also appreciated.

I've added source code of:

my datasource method (EntityDataSource)
the method that constructs the grid based on Metadata from the API (LoadEntityGridCrudPartial)
extended DataManagerRequest class
the beginning of an Insert method (EntityInsert) and Batch method (EntityBatchUpdate)

I've also added screenshots from debug showing that I am receiving data back in my controller. Both in case of individual Insert and in Batch.

        public IActionResult LoadEntityGridCrudPartial(Guid catalogGuid, Guid entityGuid)
        {
            if (mdsEntityCRUD == null || catalogGuid != mdsEntityCRUD.ModelId || entityGuid != mdsEntityCRUD.EntityId)
            {
                MdsEntityCRUD mdsEntityCRUD = new MdsEntityCRUD(mdsProxy, catalogGuid, entityGuid, _cache);
            }

            //First load the Attributes (fields) of the Entity
            MdsMetaData mdsMeta = new MdsMetaData(mdsProxy, _cache);
            metadataAttributes = Task.Run(async () => await mdsMeta.GetEntityAttributesAsync(catalogGuid, entityGuid)).Result;

//Build the grid columns based on the Attributes

            List<Syncfusion.EJ2.Grids.GridColumn> gridColumns = new List<Syncfusion.EJ2.Grids.GridColumn>();
            foreach (MetadataAttribute attr in metadataAttributes)
            {
                Syncfusion.EJ2.Grids.GridColumn gridcol = new Syncfusion.EJ2.Grids.GridColumn
                {
                    Field = attr.Identifier.Name,
                    HeaderText = attr.DisplayName,
                    EditType = attr.DataType == AttributeDataType.Number ? "NumericTextBox" : "TextBox"
                };
                gridColumns.Add(gridcol);
            }

            ViewData["columns"] = gridColumns;
            ViewData["catalogGuid"] = catalogGuid;
            ViewData["entityGuid"] = entityGuid;
            return PartialView("EntityGridCrudPartial", ViewData);
        }

        public IActionResult EntityDataSource([FromBody]ExtDataManagerRequest dmr)
        {
            mdsEntityCRUD = new MdsEntityCRUD(mdsProxy, dmr.CatalogGuid, dmr.EntityGuid, _cache);
            int totalMembers = 0;
DataTable dataTable = mdsEntityCRUD.GetData(dmr.Skip, dmr.Take, "", "", ref totalMembers);

            return Json(new { result = dataTable, count = totalMembers }, new JsonSerializerSettings()
            {
                NullValueHandling = NullValueHandling.Ignore,
                Converters = new List<JsonConverter>() { new Newtonsoft.Json.Converters.DataTableConverter() }
            });
        }

        public class ExtDataManagerRequest : DataManagerRequest
        {
            public Guid CatalogGuid { get; set; }
            public Guid EntityGuid { get; set; }
        }

        public IActionResult EntityInsert([FromBody]CRUDModel crudmodel)
        {
            var value = crudmodel.Value; // doesn't give access to Children ??
   ...
            return Json(value);
        }

        public IActionResult EntityBatchUpdate([FromBody]CRUDModel crudmodel)
        {
            if (crudmodel.Changed != null)
            {
                foreach (Newtonsoft.Json.Linq.JObject obj in crudmodel.Changed)
                {
                    if (obj.HasValues)
                    {
                        // Newtonsoft.Json.Linq.JToken item = obj.First;
                        var item = obj.First;

                        for (int i = 0; i < obj.Count; i++)
                        {
                            var nextitem = obj.Next;
                        }
                    }
                }
// or ??
                for (int i = 0; i < crudmodel.Changed.Count; i++)
                {
                    var something = crudmodel.Changed[i];

                    Console.WriteLine(crudmodel.Changed[i]);
                }
            }
}

thanks in advance

Attachment: 002_CRUDModel_at_debug_4f045e2c.zip

Ronald Dirkx · Answer

It's been 9 days since my initial request??

Hariharan J V · Answer

HI Adolfo, Thanks for contancting Syncfusion support. We have validated your requirement and the provided code and we get create dynamic class by using ‘ExpandoObject’ . Please find the code example and screenshot for your reference.

[controller] . . . public IActionResult Update([FromBody] ExpandoObject value) { // you can write your db code return View(); } public IActionResult Insert ([FromBody] ExpandoObject value) { // you can write your db code return View(); } public IActionResult Remove([FromBody] ExpandoObject value) { // you can write your db code return View(); } Query Response I would like to have an example for the indivudual Url's, CrudUrl as well as the BatchUrl. We have prepared a sample based on your requirement which can be download from following link, Sample:http://www.syncfusion.com/downloads/support/directtrac/general/ze/WebApplication8_(3)676013438 In that sample, we rendered the Grid with CRUD operation and define the CRUD methods in server side. Please refer to the following code example, Code example: @Grid

. . .

@server side code //Update the data public ActionResult CellEditUpdate([FromBody]CRUDModel value) { var ord = value.Value; Orders val = order.Where(or => or.OrderID == ord.OrderID).FirstOrDefault(); val.OrderID = ord.OrderID; val.EmployeeID = ord.EmployeeID; val.CustomerID = ord.CustomerID; val.Freight = ord.Freight; val.OrderDate = ord.OrderDate; val.ShipCity = ord.ShipCity; return Json(value.Value); } //insert the record public ActionResult CellEditInsert([FromBody]CRUDModel value) { order.Insert(0, value.Value); return Json(value); } //Delete the record public ActionResult CellEditDelete([FromBody]CRUDModel value) { order.Remove(order.Where(or => or.OrderID ==int.Parse(value.Key.ToString())).FirstOrDefault()); return Json(value); } If we set editmode as batch then batch url and server side code should be like as follows, @GRID

. . .

@server side code public ActionResult BatchUpdate([FromBody]CRUDModel value) { //do stuff } Screenshot 1: Screenshot 2: If we want to CRUD URL , you can perform crud operation by it action. Please find the code example and screen shot

Please find the documentation reference for persist data server with crud https://ej2.syncfusion.com/aspnetcore/documentation/grid/edit.html#persisting-data-in-server how can I access the values of the additional properties I added to the DataManagerRequest? I need to pass them to the back end API. You can pass the additional parameter by using “query” property. Please find the code example . . . what should be the return value of my Controller method given that I use a DataTable-converted-to-Json as datasource? We can return the value based on Data Adaptor. If you using URL Adaptor you need to return result and count format. Please find the documentation for your reference https://ej2.syncfusion.com/aspnetcore/documentation/grid/databinding.html Regards, Hariharan

Ronald Dirkx · Answer

Thanks for your response but this leaves me with almost the same questions:

1. I was looking for the actual code example on how to process or iterate through the result returned by the CRUD-operations of the Grid, not for "// you can write your db code". In the provided sample you are working based on a model which I don't have. (see my original question)

I searched the internet some more and found some technique I can apply on both the CRUDModel as on the ExpandoObject. See the following code snippet below.

Question 1A, is this the way to process the data or is there another more preferred way?

Question 1B, what would be the best, using CRUDModel or ExpandoObject?

For both questions keep in mind that the grid can have 200 columns or more.

Code snippet of 2 controller methods, 1 using ExpandoObject, 1 using CRUDModel:

        public IActionResult EntityInsert([FromBody]ExpandoObject crudmodel)
        {
            ExpandoObject value = (ExpandoObject)crudmodel.FirstOrDefault(x => x.Key == "value").Value;
            string xyz;
            foreach (KeyValuePair<string,object> item in value)
            {
                xyz = item.Key + " - " + item.Value.ToString() + " - " + item.GetType();
            }

            return Json(crudmodel);
        }

        public IActionResult EntityUpdate([FromBody]CRUDModel crudmodel, [FromBody]ExtDataManagerRequest dmr)
        {
            Newtonsoft.Json.Linq.JObject value = (Newtonsoft.Json.Linq.JObject)crudmodel.Value;

            string xyz;
            foreach (KeyValuePair<string, Newtonsoft.Json.Linq.JToken> item in value)
            {
                xyz = item.Key + " - " + item.Value.ToString() + " - " + item.GetType();
            }
            return Json(crudmodel);

}

Question 2: I need to access the values of the additional properties I added to the DataManagerRequest? I need to pass them to the back end API. I know how to access them in my Datasource method (I'm using UrlAdaptor) but I also need them in my CRUD methods. I tried to add the extended DataManagerRequest class as parameter of my method but this results in a null value of the DataManagerRequest object.

public IActionResult EntityUpdate([FromBody]CRUDModel crudmodel, [FromBody]ExtDataManagerRequest dmr)
{

// dmr is null

...

}

Question 3: what data do I need to return once I'm done with the back end database update? In all the examples I've found so far you are returning the inserted, modified or deleted record: return Json(value.Value) or return Json(value). Is this really what needs to be returned? Also in case of deletion? I'm working with UrlAdaptor so I wonder if I need to return the inserted/updated/deleted record or if I need to re-execute my call to the database with the right skip and take arguments (however, how do I access these?)

Hariharan J V · Answer

Hi Ronald, Thanks for your update. Query Response Without model Based on your requirement , we have created a sample with generic type without specify any model. Please find the code example [Home.Controller] . . . public IActionResult DataSource ([FromBody]DataManager dm) { IEnumerable DataSource = GetAllRecords(); int count = DataSource.Cast

Ronald Dirkx · Answer

Thanks, but this still leaves my original question unanswered.

Question 1A: how to process the data from CRUDModel in Insert, Update and Delete methods when I don't have a model to cast to?

Below is my Insert method (which is working), I just want you to confirm that this is a good way to work with the data that is passed by CRUDModel (not using a model but getting the data from CRUDModel direct) or if you have a better, more robust, scalable and performant way (with code example if you do please)

        public IActionResult EntityInsert([FromBody]CRUDModel crudmodel)
        {
            Guid catalogGuid = Guid.Empty;
            Guid entityGuid = Guid.Empty;
            if (crudmodel.Params.TryGetValue("CatalogGuid", out object objCatalogGuid))
            {
                Guid.TryParse(objCatalogGuid.ToString(), out catalogGuid);
            }
            if (crudmodel.Params.TryGetValue("EntityGuid", out object objEntityGuid))
            {
                Guid.TryParse(objEntityGuid.ToString(), out entityGuid);
            }

            Dictionary<string, string> recordfields = JsonConvert.DeserializeObject<Dictionary<string, string>>(crudmodel.Value.ToString());
            MdsEntityCRUD mdsEntityCRUD = new MdsEntityCRUD(mdsProxy, catalogGuid, entityGuid, _cache);
            mdsEntityCRUD.CreateMember(recordfields);

return Json(crudmodel.Value);
}

Question 1B: clear, I'm using CRUDModel for better performance

Question 2: perfect! The custom adaptor works great, passing the parameters I need

Question 3: what data to return from Insert, Update, Delete methods to grid when using the data from CRUDModel direct, not casted to a model.
Still having issues. Please see my Insert method above (my Update method is almost a copy of it). I return the Value from CRUDModel, not from a casted model. When I insert a row in the grid the Insert method executes and runs ok (a new record is created in the database) however when it returns the grid becomes non-responsive:

the "new" row is still displayed but all fields are empty
the grid doesn't respond to any action anymore (paging doesn't work, inserting or editing another record doesn't work ...)
the grid is not updated (the new row is not visible)
the record count on the Pager is not updated
DevTools shows a Syntax Error pointing to a script in the page (if I change order it points to another script so I think it just points to the first script it encounters)

Maybe important? The grid was originally rendered in a PartialView, not sure if that has any impact?

Would greatly appreciate a resolution for question 3 and some guidance for 1A.

thanks.

Hariharan J V · Answer

Hi Ronald, 
 
We have created a new incident under your Direct trac account to follow up with this query. We suggest you to follow up with the incident for further updates. Please log in using the below link.   
 
https://www.syncfusion.com/account/login   
 
Regards, 
Hariharan