Using editor with stored procedure

tanya.ganzman@cga.ct.gov

Hi all, is it possible to use Editor with stored procedure instead of simple table. We use .Net ,Web API 2, VS 2015.
Is yes, please point to an example. Please, let me know what additional information I can provide.

Thanks.

allan

Hi,

Unfortunately no - at least not with the provided .NET libraries. To use with a stored procedure you would need to write the server-side to be compatible with Editor's client / server communication.

It is something I would like to add to Editor in future, but it is at least one or two major versions away.

Allan

tanya.ganzman@cga.ct.gov

Allan, thank you very much for prompt replay.
What are my options if any to pull complex data set (it is build with views and functions) into the Editor, but update is needed only for one table that is part of that data set?

Thanks

allan

That is slightly different and a bit easier. Because you can specify the DataTables Ajax data source separately from the Editor Ajax source you can tell them to use different end points.

The DataTables endpoint could be a relatively simple custom script that gets the data you need from the stored procedure and dumps it out as JSON, while Editor could use the standard libraries.

The one bit that you must take into account is that the row data structure Editor expects in return to its requests must be in the same format as that which was used to initially load the DataTable. So you'd probably need to intercept the return from Editor, call the stored procedure on the row(s) that were created or edited and then return that data.

So a lot more tractable this way, but still a little tricky.

Allan

tanya.ganzman@cga.ct.gov

Any examples on implementing it? I assume it will intercept JSON ( we initially can load DataTable with it), send it to the server for update, server returns updated JSON back. How to replace that data in the DataTable/Editor at the point?
Right now, we are updating the row and return updated JSON back, but Editor doesn't not show the update until we refresh the page.

allan

I'm afraid not - it is something I will look at adding to the examples on the site though.

Basically what you would need to do is instead of simply JSON encoding the result from the Editor.Data() method, like most of the examples do you would need to instead execute your stored procedure for the rows which have been added or changed (you could get the row ids from the response or the submitted data) and return that information.

The client / server documentation for Editor might also be useful as it shows the data format that Editor expects on the client-side.

The other option (ugly hack!) is to simply reload the table whenever Editor does an update:

editor.on( 'submitComplete', function () {
  table.ajax.reload();
} );

Allan

tanya.ganzman@cga.ct.gov

And we do send data back in the same format the documentation for the Editor shows, we do not get any errors, but do not get into 'submitComplete' event either. At the same time Editor stops working. Any ideas?

allan

Quick update for anyone else that finds this conversion via search, the issue was that the success function was being defined in the Editor ajax options. The result is that it overwrites Editor's own success callback and it never executes the required internal functions in Editor to allow the updated data to be actioned.

I'll be adding a catch into Editor to disallow a custom success function.

Regards,
Allan