Custom insert, update, delete method for Editor

Custom insert, update, delete method for Editor

sasocsasoc Posts: 5Questions: 1Answers: 0
edited March 2019 in Free community support

I have a ASP.NET MVC 5 application and I have a DataTable which gets data from MVC controller (not ApiController) of class JsonResult and uses serverside pagination, using MS SQL server stored procedures and this works just fine.
I want to integrate the Editor, and want Create, Update, Delete buttons to call my custom MVC Controller method, which will perform database insert, update, delete (again via stored procedure).

Is this possible and if it is, can you direct to some example. A am googling around, but can't find useful example/way to go.

Regards,
Sašo

Answers

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,735

    Is this what you are looking for?
    https://editor.datatables.net/examples/advanced/REST.html

    Kevin

  • sasocsasoc Posts: 5Questions: 1Answers: 0
    edited March 2019

    Thank you for the answer.

    Using your answer I can see now in Chrome Inspector that i.e. Update button sends this structure to my .Net MVC Controller:

    action=edit&data%5B73%5D%5BDOKU_ID%5D=73&data%5B73%5D%5BSTATUS%5D=V_DELU&data%5B73%5D%5BNAZIV%5D=My+test+naziv&data%5B73%5D%5BARHIVSKA_STEVILKA%5D=25651-288%2F2018&data%5B73%5D%5Bstart_date%5D=2019-03-28

    In more readable view this is
    data[73][DOKU_ID]: 73
    data[73][STATUS]: V_DELU
    data[73][NAZIV]: My test naziv
    data[73][ARHIVSKA_STEVILKA]: 25651-288/2018
    data[73][start_date]: 2019-03-28

    But for the .Net MVC Controller to be able to map this to object, this structure should be JSON:
    {"DOKU_ID":73, "STATUS":"V_DELU", "NAZIV":"My test naziv","ARHIVSKA_STEVILKA":"25651-288/2018","startdate":"2019-03-28"}]

    So, how to force the Update button to send JSON ?

    Regards,
    Sašo

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,735

    Checkout the last example in the ajax.data option.

    Kevin

  • sasocsasoc Posts: 5Questions: 1Answers: 0

    Hallo again.

    Now I get JSON like this:
    {"action":"edit","data":{"73":{"DOKU_ID":"73","STATUS":"V_DELU","NAZIV":"My test naziv","ARHIVSKA_STEVILKA":"25651-288/2018","start_date":"2019-03-28"}}}

    As MVC use automatic mapping between JSON from client to object in C# I have to construct C# class which fits to this JSON. But JSON looks little strange: the bolded 73 isn't class property name but ID of the row, so i think automapping will not happen.

    If on the client (together with JSON.stringify) I can extract just actual fields to get simple class like this, than atomapper can happen.
    {"DOKU_ID":"73","STATUS":"V_DELU","NAZIV":"My test naziv","ARHIVSKA_STEVILKA":"25651-288/2018","start_date":"2019-03-28"}

    So how to extract just this part and send it to controller?

    Thank you,
    Sašo

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,735

    The example has this:

            data: function ( d ) {
                return JSON.stringify( d );
            }
    

    d is a Javascript variable. You can use standard Javascript to extract what you want and return just that portion. Just make sure the response follows the requirements here:
    https://editor.datatables.net/manual/server#Example-data-exchanges

    Kevin

  • sasocsasoc Posts: 5Questions: 1Answers: 0
    edited March 2019

    Ok, I succeed the way I can also keep classic ASP.NET MVC 5 Controller (not ApiController):

    DT Editor initializations for Create/Edit/Delete look this way:

    var editor = new $.fn.dataTable.Editor({
        ajax: {
            create: {
                type: 'POST',
                url: "@Url.Action("SavePorociloDTEditor", "SeznamPorocil")",
                dataType: "text",
                contentType: 'application/json',
                data: function (d) {
                    var dteAction = d.action;
                    var dteData = d.data;
                    var key = Object.keys(dteData)[0]
                    var formData = dteData[key]
                    return JSON.stringify(formData);
                }
            },
            edit: {
                type: 'POST',
                url: "@Url.Action("SavePorociloDTEditor", "SeznamPorocil")",
                dataType: "text",
                contentType: 'application/json',
                data: function (d) {
                    var dteAction = d.action;
                    var dteData = d.data;
                    var key = Object.keys(dteData)[0]
                    var formData = dteData[key]
                    return JSON.stringify(formData);
                }
            },
            remove: {
                /*to-do*/
                }
            },
        },
        ... other DT Editor initializers ...
    

    The crucial thing here is extracting just ViewModel Object (in ASP.NET MVC terms) from JSON providet by DT Editor in d parameter:

    {"action":"edit","data":{"73":{"DOKU_ID":"73","STATUS":"V_DELU","NAZIV":"My test naziv","ARHIVSKA_STEVILKA":"25651-288/2018"}}}

    var dteData = d.data;                              //get the data part
    var key = Object.keys(dteData)[0]          //get the record key/identifier
    var formData = dteData[key]                  //get the actual data
    

    And when we return JSON.stringify(formData) to ASP.NET MVC Controller and when we have in Controller:

    [HttpPost]
    public JsonResult SavePorociloDTEditor(GLIB_DOKUMENTI formData) 
    

    And where GLIB_DOKUMENTI is class:

    public class GLIB_DOKUMENTI
    {
              public Int64 DOKU_ID { get; set; }
              public string STATUS { get; set; }
              public string NAZIV { get; set; }
              public string ARHIVSKA_STEVILKA { get; set; }
    }
    

    The effect is that MVC binding mechanism can translate formData JS object to C# object.

    And in SavePorociloDTEditor we must implement insert/update into database. As I found out - at the end of the process the DataTable itself performs refresh from server (Database).

    What do you thing @Kevin, is this the right way, or I missed something?
    Ok, I know I must now find out, how to implement Create because key/identifier must come from database sequence ...

    Regards,
    Sašo

  • INRINR Posts: 20Questions: 2Answers: 1

    Hi,

    This solution works for single row. how do we pass multiple rows to the MVC controller ?

    Regards

  • allanallan Posts: 61,436Questions: 1Answers: 10,049 Site admin

    var key = Object.keys(dteData)[0]

    That is getting just the first row edited. If you are submitting multiple rows, you'd need to loop over all of the keys.

    Personally I'd recommend not changing the data structure Editor is sending - just loop over the data array on the server-side.

    Allan

  • INRINR Posts: 20Questions: 2Answers: 1

    Thanks Allan, but I couldnt make the default to work with MVC controller.

    I did like below and it works.

            ajaxEditor = new $.fn.dataTable.Editor($.extend(true, {
                    ajax: {
                        url: 'home/sampleMVController',
                        type: 'POST',
                        dataType: "json",
                        contentType: "application/json; charset=utf-8",
                        data: function (d) {
                            var newdata = [];
                            $.each(d.data, function (key, value) {
                                newdata.push(value);
                            });
                            return JSON.stringify(newdata);
                        },
                        success: function (data) {
                            if (data.success) {
                                ShowToaster("success", data.responseText);
                                PriceReview_Reload();
                            }
                            else {
                                ShowToaster("error", data.responseText);
                            }
                        },
                        error: function (errormessage) {
                            ShowToaster("error", errormessage);
                        }
                    }
                },
            editorOpts));
    

    MVC Controller

        [HttpPost]
        public ActionResult sampleMVController(List<GetResults> data)
        {
    
  • allanallan Posts: 61,436Questions: 1Answers: 10,049 Site admin

    That looks like it should give you just the raw data in an array sent to the server in the POST body. There are two issues I immediately see with that:

    1. The primary key value is not being retained - so you'd need to add key to the value object for your server-side to read it.
    2. You'd also want to include the d.action parameter in the information sent to the server so you know if you are required to do an insert, update or delete.

    Aside from that it looks like you should be getting information sent to the server. Checking the browser's developer control will be able to confirm if that is the case.

    On the server-side, I couldn't honestly say if that is the correct function decleration to use.

    Allan

  • nathan.khupenathan.khupe Posts: 5Questions: 2Answers: 0

    This was very useful for my scenario, but from the server side do i have to respond with a specific response type/format/class?

    I have to say that although I like the features of editor Idon't like the way it wants to impose its server processing. I was just looking for a cool inline editing UI. Didn;t expect all these difficulies

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    Yep, look at the "Ajax data" tab on this example, the server is expected to respond with the updated values.

    Colin

  • allanallan Posts: 61,436Questions: 1Answers: 10,049 Site admin

    You could use local table editing only if you don't want to have to deal with Editor's client / server interface.

    But you'd still need to get the information to the server somehow so it can be stored (if you are using local editing, then listen for submitComplete and then post the data to the server using whatever format you need).

    We had to define a client / server format for the Ajax, since we have certain requirements - like how to handle errors (we couldn't just accept any old format, because it wouldn't know how to handle it!).

    Allan

  • ezdavisezdavis Posts: 35Questions: 3Answers: 0

    So after reading through this thread I'm still stuck on this. I've got it working both ways listed above but as Allan mentions the primary key value is not be retained (which becomes an issue when editing and using "submit: changes") and you also lose the action.

    How can I get this to work with the key? When you send editor as normal I get

    data[4650][SeverityID]: 3
    data[4650][Subject]: Test 14-1
    action: edit
    

    And if I use JSON.Stringify(data) in the ajax call to get it in the json format I need I get

    {
        "data": {
            "4650": {
                "SeverityID": "3",
                "Subject": "Test 14-1"
            }
        },
        "action": "edit"
    }
    

    But what I'm actually needing is

    {
      "data": [
            {
                "ID": "4650",
                "SeverityID": "3",
                "Subject": "Test 14-1"
            }
        ],
        "action": "edit"
    }
    

    How is this possible?

  • allanallan Posts: 61,436Questions: 1Answers: 10,049 Site admin

    You would need to use the preSubmit event handlers to rewrite the data being submitted from the object based data to array based. Editor doesn't have a built in transformer for that I'm afraid, so one would need to be written.

    Allan

  • ezdavisezdavis Posts: 35Questions: 3Answers: 0
    edited April 2022

    Thanks Allan.

    I was able to get it working how I want. For anyone who may find this thread here's what I did.

    editor.on('preSubmit', function (e, data, action) {
            var newdata = [];
            $.each(data.data, function (key, value) {
                value["Key"] = key;
                newdata.push(value);
            });
            data.data = newdata;
        });
    

    The data of your ajax call will then look like this

    data: function (d) {
                        return JSON.stringify(d);
                    }
    
Sign In or Register to comment.