Editing Multiple DataBase Tables with one Editor Table using the .NET DataTables library

christopher.vergaray

I am trying to create one editor that will modify two or more database tables at one time. I have a main table, with information about a piece of hardware, and additional tables with extended information about the hardware. However, the extended information is not always required. As mentioned, I am using the .NET DataTables editor library.

The data is stored in an Oracle 11 database using this table structure. Also, for clarification, I have a trigger set up on the TEST_TABLE_2 table that auto-numbers the ID field from a sequence. (Since Oracle can't auto-increment)

My HomeController contains the following for handling CRUD:

public ActionResult TestCRUD() {
    var formData = HttpContext.Request.Form;
    using (var db = DatabaseUtilityModel.GetNewDb()) {
        var editor = new Editor(db, "TEST_TABLE", "ID")
            .Model<TEST_TABLEModel>()
            .MJoin(new MJoin("TEST_TABLE_2")
                .Link("TEST_TABLE.ID", "TEST_TABLE_2.TEST_TABLE_ID")
                .Model<TEST_TABLE_2Model>()
                .Field(new Field("ADDITIONAL_VALUE"))
                .Field(new Field("MORE_DATA")))
            .Field(new Field("ID").Validator(Validation.Required()))
            .Field(new Field("VALUE").Validator(Validation.Required()));

        var response = editor.Process(formData).Data();

        return Json(response, JsonRequestBehavior.AllowGet);
    }
}

With the following data classes (Note that these are not nested like indicated in the examples at the time of this writing):

using DataTables;
namespace HardwareInventory.Models.Database_Models
{
    public class TEST_TABLEModel : EditorModel
    {
        public int ID { get; set; }
        public string VALUE { get; set; }
    }
}

using DataTables;
namespace HardwareInventory.Models.Database_Models
{
    public class TEST_TABLE_2Model : EditorModel
    {
        public int ID { get; set; }
        public int TEST_TABLE_ID { get; set; }
        public string ADDITIONAL_VALUE { get; set; }
        public string MORE_DATA { get; set; }
    }
}

I initialize the DataTable editor with the following code. You will notice that the editor fields pull their data from an array of "Additional_value" and "More_data" but are named without the array syntax. This is because when I used the array syntax on the name, the data would populate in the form, but it would only send the "More_data" field to the server, omitting the "Additional_Value". This properly initializes the table with the data it receives from the server (with the joined data in an array) and attempts to send all the data.

$(document).ready(function() {
    editor = new $.fn.dataTable.Editor({
        ajax: "@Url.Content("~/Home/TestCRUD")",
        table: "#test",
        fields: [
            {
                label: "test Id:",
                name: "ID"
            },
            {
                label: "value:",
                name: "VALUE"
            }
            ,
            {
                label: "Additional value:",
                name: "TEST_TABLE_2.ADDITIONAL_VALUE",
                data: "TEST_TABLE_2[].ADDITIONAL_VALUE"
            }
            ,
            {
                label: "More Data:",
                name: "TEST_TABLE_2.MORE_DATA",
                data: "TEST_TABLE_2[].MORE_DATA"
            }
        ]
    });

    $('#test').DataTable({
        dom: "Bfrtip",
        ajax: "@Url.Content("~/Home/TestCRUD")",
        columns: [
            { data: "ID" },
            { data: "VALUE" }
            , { data: "TEST_TABLE_2", render: "[, ].ADDITIONAL_VALUE" }
            , { data: "TEST_TABLE_2", render: "[, ].MORE_DATA" }
        ],
        select: true,
        buttons:
        [
            { extend: "create", editor: editor },
            { extend: "edit", editor: editor },
            { extend: "remove", editor: editor }
        ]
    });
});

Using the initialization above, the data object of the submission will include a variable called TEST_TABLE_2 which is the following collection of name/value pairs:

{"ADDITIONAL_VALUE":"foo","MORE_DATA":"bar","ID":10493,"TEST_TABLE_ID":1}

But, since this is using the MJoin, /manual/net/mjoin the server expects an collection of collections of name/value pairs. So, the following converts the single collection into what the server library expects. Note that this should be included in the $(document).ready() function.

    editor.on("preSubmit", function (e, submission, action) {
        try {
            var data = submission.data;
            for (var row in data) {
                if (data.hasOwnProperty(row)) {
                    data[row].TEST_TABLE_2 = { 0 : data[row].TEST_TABLE_2 };
                }
            };
        } catch
        (exception) {
            var debug = exception;
        }
    });

So, while this works, I find intercepting and manipulating the data structure of the submission a bit icky. Does anyone know of a cleaner way of doing this?

allan

Hi,

I'm really impressed that you got this working (not specifically yourself, but anyone!). The problem with Mjoin, as you have seen is that it expects data to be combined together, which is not currently something Editor on the client-side does.

Your solution is currently the only way to make that happen. So yes, I'm afraid that your hack is the "correct" way of doing this.

It isn't really a feature of Editor at the moment, the ability to edit in an Mjoined table! I'm not yet certain if it ever will be due to the added complexity that it can cause for both the UI and the backend processing to make this work in the generic sense that Editor has to.

Allan

christopher.vergaray

Thanks, Allan; I was pretty determined to make it work, at least to see if I could.
Since we still have some flexibility in how our tables are built, I'm just going to put everything in the same table and allow the extended data to be null-able. I think the loss of space efficiency will be better than the loss of maintainability, since hacks are generally harder to maintain.
But, at least this code can help anyone else who may need it.

Vyacheslav

Thanks christopher.vergaray for this nice and elegant solution!