How to drag and drop rows with database update in ASP.NET Core using jQuery UI?

Question

I am using this jQuery UI Library to drag and drop rows of a table in an ASP.NET Core MVC application.

script.js

$(function(){ 
  $("#SortTable")
    .sortable({ items: "tr.sortable" })
    .dragtable({dragHandle: ".dragHandle"})
    .tablesorter();
})

cshtml

<div class="table-responsive">
    <table class="table" id="SortTable">
        <thead>
            <tr>
                <th><div class="dragHandle"></div>A</th>
                <th><div class="dragHandle"></div>B</th>
                <th><div class="dragHandle"></div>C</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var ticket in Model)
            {
                <tr class="sortable">
                    <td>@ticket.A</td>
                    <td>@ticket.B</td>
                    <td>@(ticket.C + "(" + ticket.abc + ")")</td>
                </tr>
            }
        </tbody>
    </table>
</div>

How can I send request to the controller and update the changes in the database?

Answer 1

"Reordering rows at database level" is always a tricky thing to do. The Stern-Brocot technique was designed to enable reordering of items while only having to update a single row in the database.

Stern-brocot

The theory is basically that when using fractions, adding together the numerators and adding together the denominators will result in a new fraction which is always in between the other two:

   1 /    1 = 1
   2 /    1 = 2

Now were dragging one in between

   1 /    1 = 1
   3 /    2 = 1.5
   2 /    1 = 2

Now were adding yet another one in between

   1 /    1 = 1
   4 /    3 = 1.333
   3 /    2 = 1.5
   2 /    1 = 2

Now put another between the second and the third

   1 /    1 = 1
   5 /    4 = 1.25
   4 /    3 = 1.333
   3 /    2 = 1.5
   2 /    1 = 2

This is all working fine in theory. The numbers don't skyrocket as fast as other techniques, and the values can be stored easily.

However I did make a proof-of-concept some time ago, and this clearly shows the limitations of the Stern-brocot technique.

While the numbers can be persisted with 100% accuracy, database servers only do number computations to a certain "precision" (if you're using doubles with a mantissa and exponent). The demo clearly shows that when you start off with 10 items to be reordered, you can already lose precision after a mere 47 moves (if you try your best).

Atlassian

Atlassian has a blog post on how they deal with reordering items effectively. However atm. I'm not able to find it.

Answer 2

My idea is push the new order along the row id into an array and send the array to backend and then in your backend you can update your database. I also refer to this high vote answer to achieve the reset order feature.

Try my code below:

My controller:

public IActionResult Tickets()
{
    List<TicketModel> list = new List<TicketModel>
    {
        new TicketModel{ rowid="aa", A="11",B="12",C="13",abc="hello"},
        new TicketModel{ rowid="bb", A="21",B="22",C="23",abc="world"},
        new TicketModel{ rowid="cc", A="31",B="32",C="33",abc="!!"}
    };
    return View(list);
}
[HttpPost]
public string resetOrder([FromBody] List<TicketModel> postData) {
    return "success";
}

My cshtml:

@model IEnumerable<WebAppMvc.Models.TicketModel>
<div class="table-responsive">
    <table class="table" id="myTable">
        <thead>
            <tr>
                <th><div class="dragHandle"></div>A</th>
                <th><div class="dragHandle"></div>B</th>
                <th><div class="dragHandle"></div>C</th>
            </tr>
        </thead>
        <tbody>
            @{int i = 1;}
            @foreach (var ticket in Model)
            {
            <tr class="sortable">
                <td class="index" id="@ticket.rowid">@i</td>
                @{i++;}
                <td>@ticket.A</td>
                <td>@ticket.B</td>
                <td>@(ticket.C + "(" + ticket.abc + ")")</td>
            </tr>
            }
        </tbody>
    </table>
    <button id="updateBtn">update</button>
</div>
@section scripts{
    <script src="~/js/jquery.ui.js"></script>
    <script src="~/js/jquery.dragtable.js"></script>
    <script src="~/js/jquery.tablesorter.min.js"></script>
    <script>
        $(function () {
            var reslist = [];
            var fixHelperModified = function (e, tr) {
                var $originals = tr.children();
                var $helper = tr.clone();
                $helper.children().each(function (index) {
                    $(this).width($originals.eq(index).width())
                });
                return $helper;
            },
                updateIndex = function (e, ui) {
                    $('td.index', ui.item.parent()).each(function (i) {
                        //$(this).html(i + 1);
                        var data = new Object();
                        data.rowid = $(this).attr("id");
                        data.order = (i + 1).toString();
                        reslist.push(data);
                        console.info(reslist);
                    });

                    
                };
            $("#myTable")
                .sortable({
                    items: "tr.sortable",
                    helper: fixHelperModified,
                    stop: updateIndex
                })
                .dragtable({ dragHandle: ".dragHandle" })
                .tablesorter();
        });
        $('#updateBtn').click(function(){
           $.ajax({
                        url: "/home/resetOrder",
                        data: JSON.stringify(reslist),
                        type: "post",
                        dataType: "json",
                        contentType: "application/json; charset=utf-8",
                        success: function (data) {
                            reslist = [];
                        }
                    });
        });
    </script>
}

My model:

public class TicketModel
    {
        public string rowid { get; set; }
        public string A { get; set; }
        public string B { get; set; }
        public string C { get; set; }
        public string abc { get; set; }
        public string order { get; set; }
    }