soft delete on child table shows old row after edit

dpanscik

I am not strong on javascript so I tend to do most heavy lifting in C# controllers for MVC ASP.Net

I have implemented soft delete with code on the server side.

I implemented server side soft delete by doing the following;

(1) capture an "edit" action command coming into the controller from the form data
(2) change the action command to a "create" command, let the native editor db action do its work adding in the newly edited line into a new record.
(3) add in the form data necessary for a bool field to update the old row and mark it as soft deleted.
(3) change the action command to a edit command, let the native editor db action do its work modifying the old line for the soft delete.
The result on the child table, the original line is still there (which I desire to not be there) and the new edited line is also there. A refresh of the child table and the original line does indeed disappear.

Should I be doing a certain type of refresh here on the javascript side?

Here is a visual on what is happening.

Screen shot of child table before editing.

The old row is still there, (highlighted in orange), it should be gone.

Here is the data return, the old row is not there. The controller is returning the proper data.

Should I be doing a certain type of refresh here on the javascript side?

allan

Sounds like the "where" condition for the soft deleted items isn't in place. Can you show me the controller code?

Allan

dpanscik

sure....

Here is the full controller showing the method I am using for a soft delete.

    public class AlternativeBilling002Controller : Controller
    {
        [Route("AlternativeBilling002/DataTransport")]
        [AcceptVerbs(HttpVerbs.Get | HttpVerbs.Post)]
        public ActionResult DataTransport(FormCollection form_id)
        {

            //var request = HttpContext.Current.Request;
            var requestForm = System.Web.HttpContext.Current.Request.Form;
            //var requestEdit = requestCreate.DeepClone();


            var settings = Properties.Settings.Default;

            double millisecondTime = DateTime.UtcNow.ToUniversalTime().Subtract(
                new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc)
                ).TotalMilliseconds;
            double millisecondTimeNoDec = (double)Math.Round(millisecondTime);


            var propInfo = requestForm.GetType().GetProperty("IsReadOnly", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic);
            propInfo.SetValue(requestForm, false, new object[] { });

            if (requestForm["action"] == "edit")
            {
                //loop through string and get all row ID numbers
                List<string> AllRowKeys = new List<string>();
                List<int> AllRowNumbers = new List<int>();
                foreach (string key in requestForm.Keys)
                {
                    if (key != "action")
                    {
                        string rowId = key;
                        rowId = rowId.Substring(0, rowId.IndexOf('[', rowId.IndexOf('[') + 1));
                        rowId = rowId.Replace("data[row_", "");
                        rowId = rowId.Replace("]", "");
                        AllRowKeys.Add(key);
                        AllRowNumbers.Add(Int32.Parse(rowId));
                    }

                }
                //remove duplicates
                AllRowKeys = AllRowKeys.Distinct().ToList();
                AllRowNumbers = AllRowNumbers.Distinct().ToList();

                //convert lines from edit lines to create lines
                var requestCreate = requestForm.DeepClone();

                //remove action edit, editedBy, and EditedDate
                requestCreate.Remove("action");
                requestCreate.Remove("data[row_" + AllRowNumbers[0].ToString() + "][EditedBy]");
                requestCreate.Remove("data[row_" + AllRowNumbers[0].ToString() + "][EditedDate]");


                //add action create editedBy, and EditedDate
                requestCreate.Add("action", "create");
                requestCreate.Add("data[row_" + AllRowNumbers[0].ToString() + "][EditedBy]", User.Identity.Name);
                requestCreate.Add("data[row_" + AllRowNumbers[0].ToString() + "][EditedDate]", DateTime.UtcNow.ToString());

                //remove everything from reqestForm except child locator
                foreach (var individualKey in AllRowKeys)
                {
                    if (!individualKey.Contains("ChildLocator"))
                    {
                        requestForm.Remove(individualKey);
                    }

                }
                //add back in OldVersion
                requestForm.Add("data[row_" + AllRowNumbers[0].ToString() + "][OldVersion]", "1");

                //use copy of form for table create (with action changed from edit to Create)
                using (var db = new Database(settings.DbType, settings.DbConnection))
                {
                    var response = new Editor(db, "GR_AlternativeBilling002", "TableID")
                        .Model<GR_AlternativeBilling002>()
                        .Where("OldVersion", false, "=")
                        .TryCatch(false)
                        .Process(requestCreate)
                        .Data();
                }

                //use original form (with OldVersion set to true) for table edit
                using (var db2 = new Database(settings.DbType, settings.DbConnection))
                {
                    var response = new Editor(db2, "GR_AlternativeBilling002", "TableID")
                        .Model<GR_AlternativeBilling002>()
                        .Where("OldVersion", false, "=")
                        .Field(new Field("Patient_DOB")
                        .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_8601))
                        .SetFormatter(Format.DateFormatToSql(Format.DATE_ISO_2822)))
                        .TryCatch(false)
                        .Process(requestForm)
                        .Data();

                    return Json(response, JsonRequestBehavior.AllowGet);
                }


            }
            if (requestForm["action"] == "create")
            {
                requestForm.Remove("data[0][ChildLocator]");
                requestForm.Remove("data[0][EditedBy]");
                requestForm.Remove("data[0][EditedDate]");

                requestForm.Add("data[0][ChildLocator]", millisecondTime.ToString());
                requestForm.Add("data[0][EditedBy]", User.Identity.Name);
                requestForm.Add("data[0][EditedDate]", DateTime.UtcNow.ToString());
            }

            using (var db = new Database(settings.DbType, settings.DbConnection))
            {

                var response = new Editor(db, "GR_AlternativeBilling002", "TableID")
                    .Model<GR_AlternativeBilling002>()
                    .Where("OldVersion", false, "=")
                    .Field(new Field("Patient_DOB")
                    .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_8601))
                    .SetFormatter(Format.DateFormatToSql(Format.DATE_ISO_2822)))
                    .TryCatch(false)
                    .Process(requestForm)
                    .Data();


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

The part I think you are interested in is;

                //use original form (with OldVersion set to true) for table edit
                using (var db2 = new Database(settings.DbType, settings.DbConnection))
                {
                    var response = new Editor(db2, "GR_AlternativeBilling002", "TableID")
                        .Model<GR_AlternativeBilling002>()
                        .Where("OldVersion", false, "=")
                        .Field(new Field("Patient_DOB")
                        .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_8601))
                        .SetFormatter(Format.DateFormatToSql(Format.DATE_ISO_2822)))
                        .TryCatch(false)
                        .Process(requestForm)
                        .Data();

                    return Json(response, JsonRequestBehavior.AllowGet);
                }

This controller produces an expected ajax return. The return works perfectly. Only two lines in the return. This is exactly what is expected.

The issue is datatable holds the old row. I think in this soft edit scenario, that datatable needs a certain type of refresh.

Screenshot of child table before editing the two original rows
https://datatables.net/forums/uploads/editor/k7/kkey0tb4ww3b.png

Screenshot of child table after editing there are three rows in datatable which is odd, when the ajax return only has two lines in its return. I believe a certain refresh needs to happen here.
https://datatables.net/forums/uploads/editor/fl/ie7b7g5smktu.png

allan

Hmmm - there is a lot going on there that has never been tested, as I never expected it to be used that way.

Can you enable add .Debug(true) just before the various .Process(...) calls, and then show me the JSON return for an edit, and also the data that was submitted (both can be got from the browser's network inspector).

Allan

dpanscik

Hi Allan,

As always I appreciate your smart brain and kind responses.

Here you go... some screen shots for a visual as well as outgoing form and incoming response.

Step 1 - The unedited child table

Step 2 - Edit one of the lines in the child table

Step 3 - Notice Note 002 has both the original line as well as the edited version both showing. The original line should not be there, we should only see the EDITED version.

Step 4 - Close the child table

Step 5 - Reopen the child table now only the new edit shows (this is the result that is desired in step #3 above).

Outgoing Form;

data[row_4622][NoteType]: record 002
data[row_4622][Note]: test record 002 - EDITED
data[row_4622][EditedBy]: 
data[row_4622][EditedDate]: /Date(1723610410000)/
action: edit
ForTableID: 789
ChildLocator: 1723585171582

Response Coming Back;

{
    "draw": null,
    "data": [
        {
            "DT_RowId": "row_4621",
            "TableID": 4621,
            "ForTableName": "AlternativeBilling",
            "ForTableID": 789,
            "NoteType": "record 001",
            "Note": "test record 001",
            "EditedBy": "",
            "EditedDate": "\/Date(1723610398000)\/",
            "ParentLocator2": 1723585171582,
            "OldVersion": false
        },
        {
            "DT_RowId": "row_4623",
            "TableID": 4623,
            "ForTableName": "AlternativeBilling",
            "ForTableID": 789,
            "NoteType": "record 003",
            "Note": "test record 003",
            "EditedBy": "",
            "EditedDate": "\/Date(1723610422000)\/",
            "ParentLocator2": 1723585171582,
            "OldVersion": false
        },
        {
            "DT_RowId": "row_4624",
            "TableID": 4624,
            "ForTableName": "AlternativeBilling",
            "ForTableID": null,
            "NoteType": "record 002",
            "Note": "test record 002 - EDITED",
            "EditedBy": "",
            "EditedDate": "\/Date(1723610586000)\/",
            "ParentLocator2": 1723585171582,
            "OldVersion": false
        }
    ],
    "recordsTotal": null,
    "recordsFiltered": null,
    "error": null,
    "fieldErrors": [],
    "id": null,
    "meta": {},
    "options": {},
    "searchBuilder": null,
    "searchPanes": null,
    "files": {},
    "upload": {
        "id": null
    },
    "debug": [
        "Editor .NET libraries - version 2.1.1",
        {
            "Query": "SELECT  [TableID] as \u0027TableID\u0027, [ForTableName] as \u0027ForTableName\u0027, [ForTableID] as \u0027ForTableID\u0027, [NoteType] as \u0027NoteType\u0027, [Note] as \u0027Note\u0027, [EditedBy] as \u0027EditedBy\u0027, [EditedDate] as \u0027EditedDate\u0027, [ParentLocator2] as \u0027ParentLocator2\u0027, [OldVersion] as \u0027OldVersion\u0027 FROM  [GR_Notes] WHERE [ParentLocator2] = @where_0 AND [OldVersion] = @where_1 ",
            "Bindings": [
                {
                    "Name": "@where_0",
                    "Value": "1723585171582",
                    "Type": null
                },
                {
                    "Name": "@where_1",
                    "Value": false,
                    "Type": null
                }
            ]
        }
    ],
    "cancelled": []
}

allan

Thanks for the information. Is that JSON response from the data load, or the edit? Thinking about it, the mix of two server-side Editor classes will result in incomplete debug information... That's a bit of a problem here.

I think the most direct way of address this is going to be to simply reload the data using ajax.reload when the submitComplete event happens. We know the data on a fresh load is correct, so that should at least display the table correctly, at the cost of an extra XHR.

The fundamental issue is that Editor's edit action wasn't designed to delete the existing row, and insert a new one. It does allow for one or the other, but I don't think both at the same time is possible as it stands. That is something I'd need to look into.

Allan

dpanscik

Hi Allan,

The submitComplete event triggering an Ajax reload was exactly what I was looking for.

To answer your question,

Is that JSON response from the data load, or the edit?

The json response is a response to a create command.
An edit command goes out in the form post, and I return the json data from the controllers create funciton.

In my situation I could alternatively return the edit function from the controller but then I suspect I would then see the old version of the line disappear (as desired) but I would not see the new version of the line that is created. This approach would also require an ajax.reload

dpanscik

I discovered another way to solve this problem is to set "serverSide": true then no refresh trickery needed on the javascript side.

allan

Hah - yes, that is a neat little workaround. It basically does the same thing, just without needing that extra code.

Allan