Further discussion on rowReorder behavior and requirements (C# .NET MSSQL)

sbsu · November 2021

I have a new requirement that could make use of rowReorder. The idea of course is to (1) drag and drop on the client to create a new table order and (2) construct the server-side editor (in C# .NET) to handle the reordering events and update the database (MSSQL) accordingly.

After reviewing the rowReorder example, implementing drag and drop reordering on the client side table is as easy as adding references to the rowReorder CSS and JS files and adding rowReorder properties to the client side table instance. Testing reveals that the rows are now indeed draggable. This solves the first part of the problem. Here is the client-side code:

JS client-side method

function buildTable() {

    editor = new $.fn.dataTable.Editor({

        ajax: tableCellEditProcessingUrl,
        table: tableId,

        fields: [
            {
                label: "TID",
                name: "0TestTable.TestTableId"
            },
            {
                label: "Zip Code",
                name: "0TestTable.ZipCode"
            },
            {
                label: "Email",
                name: "0TestTable.EmailAddress"
            },
            {
                label: "TLTID",
                name: "0TestTable.TestLookupTableId",
                type: "select"
            }
        ],

        formOptions: {
            inline: {
                onBlur: "submit"
            }
        }
    });

    $(tableId).on("click", "tbody td:not(:first-child)", function (e) {
        editor.inline(this);
    });

    table = $(tableId).DataTable(
        {
            dom: "Bfrtip",
            ajax: tableDataSourceUrl,

            order: [2, "asc"],

            columns: [
                {
                    data: null,
                    defaultContent: "",
                    className: "select-checkbox",
                    orderable: false,
                    width: "1%"
                },
                {
                    data: "0TestTable.TestTableId",
                    className: "reorder",
                    width: "5%"
                },
                {
                    data: "0TestTable.ZipCode"
                },
                {
                    data: "0TestTable.EmailAddress"
                },
                {
                    data: "0TestTable.TestLookupTableId"
                }

            ],

            keys: {
                columns: ":not(:first-child)",
                keys: [9],
                editor: editor,
                editOnFocus: true
            },

            select: true,

            rowReorder: {
                dataSrc: "0TestTable.TestTableId",
                editor: editor
            },

            buttons: [
                {
                    extend: "create",
                    editor: editor,
                    text: ' Add',
                    formTitle: 'Add Record',
                    className: 'btn fa fa-plus-square-o'
                },
                {
                    extend: "edit",
                    editor: editor,
                    text: " Edit",
                    formTitle: "Edit Item",
                    className: "btn fa fa-pencil-square-o"
                },
                {
                    extend: "remove",
                    editor: editor,
                    text: " Delete",
                    formTitle: "Delete Item",
                    className: "btn fa fa-trash-o"
                }
            ],
        }
    );

    editor
        .on('postCreate postRemove', function () {
            // After create or edit, a number of other rows might have been effected -
            // so we need to reload the table, keeping the paging in the current position
            //table.ajax.reload(null, false);
            refreshTable();
        })
        .on('initCreate', function () {
            // Enable order for create
            editor.field('0TestTable.TestTableId').enable();
        })
        .on('initEdit', function () {
            // Disable for edit (re-ordering is performed by click and drag)
            editor.field('0TestTable.TestTableId').disable();
        });
}

The second part of the problem is to build and configure the server-side editor methods and properties which will perform the database operations accordingly.

Whereas the server-side rowReorder example online is written in .PHP, I've experienced some difficulty building a server-side instance in C# that works properly. Perhaps I haven't found enough documentation but I also didn't see a C# rowReorder example in the C# .NET download.

Via search, I was able to find a user-provided example of a C# .NET server-side rowReorder editor instance example, which has been helpful. I've used it as a reference to create a server-side editor instance as follows:

C#.NET server-side Method

public DtResponse DTProcessor()
    {
        string strTp = string.Empty;
        Editor editor = null;

        HttpRequest formData = System.Web.HttpContext.Current.Request;

        using (var db = new Database("sqlserver", dbcs))
        {
            editor = new Editor(db, "0TestTable", "TestTableId")

            .Model<DatatablesNet0TestTable>("0TestTable")
            .Model<DatatablesNet0TestLookupTable>("0TestLookupTable")

            .Field(new Field("0TestTable.TestLookupTableId")
                .Options(new Options()
                    .Table("0TestLookupTable")
                    .Value("TestLookupTableId")
                    .Label("TestLookupTableDesc")
                )
            )

            .LeftJoin("0TestLookupTable", "0TestLookupTable.TestLookupTableId", "=", "0TestTable.TestLookupTableId");

            editor.PreCreate += (sender, e) => e.Editor.Db()
                .Query("update", "0TestTable")
                .Set("TestTableId", "TestTableId+1", false)
                .Where("TestTableId", e.Values["TestTableId"], ">=")
                .Exec();

            editor.PreRemove += (sender, e) =>
            {   // On remove, the sequence needs to be updated to decrement all rows beyond the deleted row.
                //Get the current reading order by id (don't use the submitted value in case of a multi-row delete).
                var order = e.Editor.Db()
                    .Select("0TestTable", new[] { "TestTableId" }, query => query.Where("TestTableId", e.Id))
                    .Fetch();

                e.Editor.Db()
                    .Query("update", "0TestTable")
                    .Set("TestTableId", "TestTableId-1", false)
                    .Where("TestTableId", order["TestTableId"], ">")
                    .Exec();
            };

            editor.TryCatch(true);
            editor.Debug(true);

            editor.Process(formData);
            editor.Data();
        }

        return editor.Data();
    }

However, on a simple test table, after a drag and drop sequence on the client-side user interface, the server responds with a primary key violation error:

Error details

XHRPOSThttps://[redacted]
[HTTP/2 200 OK 5ms]
    
draw    null
data    []
recordsTotal    null
recordsFiltered null
error   "Violation of PRIMARY KEY constraint 'PK_0TestTable'. Cannot insert duplicate key in object 'dbo.0TestTable'. The duplicate key value is (18).\r\nThe statement has been terminated."
fieldErrors []
id  null
meta    Object { }
options Object { }
searchPanes Object { options: {} }
options Object { }
files   Object { }
upload  Object { id: null }
id  null
debug   [ {…} ]
0   Object { Query: "UPDATE [0TestTable] SET [TestTableId] = @TestTableId WHERE [0TestTable].[TestTableId] = @where_0 ", Bindings: […] }
Query   "UPDATE [0TestTable] SET [TestTableId] = @TestTableId WHERE [0TestTable].[TestTableId] = @where_0 "
Bindings    [ {…}, {…} ]
0   Object { Name: "@where_0", Value: "17", Type: null }
Name    "@where_0"
Value   "17"
Type    null
1   Object { Name: "@TestTableId", Value: 18, Type: null }
Name    "@TestTableId"
Value   18
Type    null
cancelled   []

What I have not found yet in documentation is information about the requirements of the data model or information on how editor knows how to update the primary keys. For example, is Datatables designed only to work with a model which can generate its own PK values automatically (MSSQL IDENTITY)? I've experimented with an auto-incrementing index as well as other methods and still experience the same PK violation.

Here is the (MSSQL) database model:

The methods are firing but editor is getting confused and is trying to update the PK index with a value that already exists.

Why is this error occurring?

allan · November 2021

Hi,

The RowReorder example in the .NET download package is called SequenceController.cs. It looks basically the same as what you have above.

If I'm reading your code correctly, then it is the primary key id of the table that you are trying to change the value of in the row reordering - is that correct?

If so, that isn't going to work I'm afraid. You'd need a second integer in the table which will be the row ordering sequence.

For why it won't work - consider when you change the orders of rows 1 and 2 - what we need to do is first change the 2 to a 1 and then the 1 to a 2. But the first step will fail because at that point you would have two rows in the table with a primary key value of 1 - which the database is rightly rejecting in the error message you show.

Also, I would be a very strong advocate of never changing the primary key sequence value once the row has been created. Consider the case where two users are using the same table - one reorders a row just before the other edits one of those rows. Now the primary keys don't match to what the second user's browser thought they were and you'd have data loss due to an overwrite!

Allan

sbsu · November 2021

Thanks Allan again for your help.

It wasn't my goal to change the PK values, I just needed more information, which you provided and I have a working test case now. I simply added another integer field as you suggested (QueueId) and massaged the reorder targeting.Thank you.

On my test project, drag and drop rowReorder is working great, thanks. But I've noticed that when I try to add a checkbox to each row (as I do on non-reorderable datatables) in combination with the rowReorder functionality on the same table, the checkboxes no longer function as before.

So due to adding reordering capability, I have lost the ability to select a row for updating and deleting.

Have you seen this before? I believe it has to do with the rowReorder style and .js conflicting with the select-checkBox class name, which normally styles the cell to which the select-checkBox class is applied. Is it possible to make them both work as intended?

Here is part of the initial table rendering:

When I click the checkboxes, the client believes I'm going to drag the element to reposition it so I can't select the checkbox. However, I've noticed that when I click the adjacent cell (which is designated as disabled) first, I can now CTRL+Click to mark the checkbox. This is the only workaround I've discovered:

Perhaps you've seen this before.

Neither of these select statements work for both scenarios:

// Define how we select a cell and its associated style
select: {
     style: "os",
     selector: "td:first-child"
},

select: true,

kthorngren · November 2021

Use the rowReorder.selector to choose the elements used for reordering. You could use td:not(first-child) to use all the cells, except the first, for reordering.

Kevin

sbsu · November 2021

Thank you Kevin. That was the solution.

One other question related to rowReorder:

Now that drag and drop reorder is working great and I have different selectors working for different scenarios, I wondered if it would be possible to perform rowReorder by row selection instead of drag and drop.

Has anyone ever considered triggering a rowReorder operation not by drag and drop repositioning but by selecting 2 different checkboxes and having the rows swap positions?

The idea would be to leverage the existing server editor instance for handling the CRUD operations but on the client-side, the process is kicked off and fed via row selection instead of a drag and drop operation.

I could do this by writing all front-end and back-end code myself but it feels like 90 percent of this would already be possible with datatables...

kthorngren · November 2021

Start with this example to see how to get the selected rows. It also shows the count() API to count the number of selected rows. You can check if there are two selected then swap them if so. I would look at using the select and deselect events to check the number of rows selected. If the count() is 2 then enable a button to swap them. If using the Buttons extension you can use button().enable() to enable/disable the button.

You can get the QID of the two selected rows then use edit() to swap the QID of each row in the database.

Kevin

sbsu · December 2021

Thank you Kevin.

I've just now had some time to experiment with your suggestions.

I have been using the checkbox selection styling method to indicate selected and un-selected rows and this has presented some unexpected challenges.

For example, after adding a jQuery listener method which targets a <tr> click, I can capture the QID as well as other row/cell information. However, I need additional logic inside the click event handler to determine whether the checkbox style was applied before clicking the cell. In this fashion I would be able to only capture the QID when the checkbox is checked.

If the checkbox were an HTML input, I could easily target the checkbox itself and check it's status.

I've noticed that the checkbox style gets applied after my jQuery listener as well.

I haven't looked into the CSS yet to see if there is a data- flag that gets applied, which I could inspect within the click handler....

If I hook into the table.on("select".....) event and attempt a data pluck, the table.on("select....) event occurs at the correct time and properly does not execute when the row is deselected but pluck is returning an undefined object.

                // When a row is selected
                table.on("select", function (e, dt, type, indexes) {
                    if (type === "row") {
                        var testing = table.rows(indexes).data().pluck("fieldName");
                        console.log(testing);
                    }
                });

I have passed a variety of field names to the pluck method, to no avail.

kthorngren · December 2021

Your code snippet seems to work here:
http://live.datatables.net/jeficuvo/1/edit

I added a deselect event to show the indexes of the deselected rows and used toArray() for the console output.

I'm not sure I understand the problem description with the select event. Please update the test case, if needed, to demonstrate the problem and the steps to recreate.

I assume you are using option columns.data in order to use pluck("fieldName").

If you create your own checkbox the click event probably does fire before the row is selected. However you could use your own checkbox and use row().select() or row().deselect() and turn of the ability of the user to use the select extension. ie, don't use the select option.

Are you trying to reorder two selected rows?

Kevin

kthorngren · December 2021

Just in case you are still looking at swapping to selected rows. Looks like you are using the Editor with RowReorder. RowReorder just swaps the index used for the rowReorder.dataSrc. This example does something similar:
http://live.datatables.net/nupawufi/1/edit

It uses a custom button to swap the two rows. The button is enabled/disabled in the select event. The button uses e-edit() to edit the selected rows and field().multiGet() to get the current IDs. The IDs are swapped then field().multiSet() is used to update the values. submit() updates the table.

This was interesting to build. I need to practice my Editor skills Hope it helps.

Kevin

sbsu · December 2021

Kevin, thank you very much. You have created an elegant solution!

I have been late replying but you and I have had success in slightly different fashions. Since I last posted, I may have discovered why I haven't had success with data.pluck (need to investigate further). I also was able to build custom multi-delete and swap functionality.

I totally agree with you that this was a fun tool to build. DT already had the rowReorder ability and that is one of the coolest pieces of code functionality I've seen in a while. But when combined with multi-edit, multi-select, and multi-delete, I have been able to build a queuing tool that is the bees knees.

Before discovering datatables, I spent weeks researching various libraries to serve as replacements to ASP.NET gridview and detailsview controls to assist in migrating classic solutions to Core / .NET 5/6. I'm not very far into the dt libraries yet but I don't think there is anything that can't be done with dt, when having the proper examples and understanding of what's available.

It appears that Allan and the team have devoted over a decade of work to this amazing project and it shows. I have mere hours so far but hope to spend weeks and months gaining more proficiency with the dt tools because this library is a more than capable replacement of gridview and detailsview controls.

There does come a point where datatables business requirements begin to repeat across projects. I'd like to get to a point where I have built and utilized so much of the datatables and editor capabilities across various projects that I can combine all of the most-used features into a default package that I can configure, deploy, and use more easily.

These tools have proven nearly perfect for one-off customization of datatables but when using these tools over and over in every project, I feel like there must be much better way to develop than to code every single line and feature over and over again with different database field names.

I know the download builder is a step in this direction. I would like to get to a point where I am able to deploy a set of datatables / editor code in which all significant event methods (hollow/shell methods of course) are already in place so that I can simply come along and populate methods as needed, etc. In other words, I would be starting with a full(er) / richer set of functionality and I would simply remove what isn't needed. However, with careful planning, it would be possible to settle on a feature-rich set of code, which becomes a template for future projects.

ASP.NET Web Forms was a revolution in rapid development in many ways. Yes it has many, many drawbacks and a fair amount of silliness in some of its ideas as well but one of it's great features was its ability to look at and query the data source to return and build a list of table fields on the UI. This save hours of coding field names, types, etc. Maybe datatables already has these abilities but it would be great to point datatables at a datasource and have it return a fully featured set of code targeting the specific fields on the datasource. Then, one would simply populate methods as needed. I feel like this could also be done on the back end / controller / server side code as well.

I could see this as a set of VisualStudio extensions. It could also be built as a standalone executable, which, after answering a slew of exceptions, could build 75% of the UI and back end code. Then one could simply code the guts of the event methods, etc.

Switching back to the last problem I discussed regarding pluck(), I have typically not coded all of the database field names into the .NET back end editor instance in all occasions. Clearly, coding the field names into the back end editor is useful for many things, including validation and also of course feeding values to the editor so that it can properly populate inline selects within table cells. However, if pluck can only capture fields that are defined in editor, this will end up being the problem and solution. I will post back after I dig further.

Thank you also for posting your solution. I will compare with what I created and go from there and let you know. Thanks again.

allan · December 2021

Thank you very much for your kind words! A perfect "pick me up" on a rather dull Scottish Monday morning .

Regarding the ability to build a UI from a higher level component with DataTables, the approach we've taken for that is CloudTables. It is basically a web based wrapper around DataTables and Editor, letting you define the data set through the web-interface and then embedding it into your own pages.

It doesn't have the full set of abilities that DataTables and Editor provide yet, but we are gradually expanding upon its feature set.

Regarding the pluck method. It will be able to pick out any data that was populated into the DataTable.

Allan

Further discussion on rowReorder behavior and requirements (C# .NET MSSQL)

Further discussion on rowReorder behavior and requirements (C# .NET MSSQL)

Answers

Howdy, Stranger!

Categories

DataTables

Further discussion on rowReorder behavior and requirements (C# .NET MSSQL)

Further discussion on rowReorder behavior and requirements (C# .NET MSSQL)

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables