Custom insert, update, delete method for Editor

sasoc

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

kthorngren

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

Kevin

sasoc

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

kthorngren

Checkout the last example in the ajax.data option.

Kevin

sasoc

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

kthorngren

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

sasoc

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

INR

Hi,

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

Regards

allan

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

INR

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)
    {

allan

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.khupe

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

colin

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

Colin

allan

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

ezdavis

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?

allan

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

ezdavis

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);
                }