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 >
    <table  id="SortTable">
        <thead>
            <tr>
                <th><div ></div>A</th>
                <th><div ></div>B</th>
                <th><div ></div>C</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var ticket in Model)
            {
                <tr >
                    <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?

CodePudding user response：

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 >
    <table  id="myTable">
        <thead>
            <tr>
                <th><div ></div>A</th>
                <th><div ></div>B</th>
                <th><div ></div>C</th>
            </tr>
        </thead>
        <tbody>
            @{int i = 1;}
            @foreach (var ticket in Model)
            {
            <tr >
                <td  id="@ticket.rowid">@i</td>
                @{i  ;}
                <td>@ticket.A</td>
                <td>@ticket.B</td>
                <td>@(ticket.C   "("   ticket.abc   ")")</td>
            </tr>
            }
        </tbody>
    </table>
</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);
                    });

                    var reslist1 = [{ "rowid": "id1", "A": "A1" }, { "rowid": "id2", "A": "A2" }];
                    $.ajax({
                        url: "/home/resetOrder",
                        data: JSON.stringify(reslist),
                        type: "post",
                        dataType: "json",
                        contentType: "application/json; charset=utf-8",
                        success: function (data) {
                            reslist = [];
                        }
                    });
                };
            $("#myTable")
                .sortable({
                    items: "tr.sortable",
                    helper: fixHelperModified,
                    stop: updateIndex
                })
                .dragtable({ dragHandle: ".dragHandle" })
                .tablesorter();
        })
    </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; }
    }

CodePudding user response：

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