How to save position of the sortable (draggable) widgets

@model IEnumerable<WebApplication4.Controllers.UserWidget>

<link rel="stylesheet" href="https://code.jquery.com/ui/1.14.1/themes/base/jquery-ui.css">

<link 
    href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" 
    rel="stylesheet" 
    integrity="sha384-LN+7fdVzj6u52u30Kp6M/trliBMCMKTyK833zpbD+pXdCLuTusPj697FH4R/5mcr" 
    crossorigin="anonymous">

<style>
    ul {
        list-style-type: none;
        padding: 5px;
    }
</style>

<script src="https://code.jquery.com/jquery-3.7.1.js"></script>
<script src="https://code.jquery.com/ui/1.14.1/jquery-ui.js"></script>

<script 
    src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" 
    integrity="sha384-ndDqU0Gzau9qJ1lfW4pNLlhNTkCfHzAVBReH9diLvGRem5+R9g2FzA8ZGN954O5Q" 
    crossorigin="anonymous">
</script>

<link 
    rel="stylesheet" 
    href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/5.15.4/css/all.min.css">

<button 
    class="btn" 
    type="button" 
    id="btnSave" 
    title="Save" 
    onclick="save()"
>
    <i class="fas fa-save"></i>
</button>

<ul class="row" id="sortable">
    <li class="col-xl-4 col-md-6 li-sortable" id="TopProductsBySales">
        <div class="card">
            <div class="bg-transparent card-header">
                <div class="d-flex flex-wrap align-items-center justify-content-between">
                    <h5 class="card-title mb-0">Top Products by Sales</h5>
                </div>
            </div>
            <div class="card-body pb-0">
                <img src="~/Images/Top Products by Sales.png" width="80%" />
            </div>
        </div>
    </li>

    <li class="col-xl-4 col-md-6 li-sortable" id="TopProductsByVolume">
        <div class="card">
            <div class="bg-transparent card-header">
                <div class="d-flex flex-wrap align-items-center justify-content-between">
                    <h5 class="card-title mb-0">Top Products by Volume</h5>
                </div>
            </div>
            <div class="card-body pb-0">
                <img src="~/Images/Top Products by Volume.png" width="80%" />
            </div>
        </div>
    </li>

    <li class="col-xl-4 col-md-6 li-sortable" id="EarningReports">
        <div class="card">
            <div class="bg-transparent card-header">
                <div class="d-flex flex-wrap align-items-center justify-content-between">
                    <h5 class="card-title mb-0">Earning Reports</h5>
                </div>
            </div>
            <div class="card-body pb-0">
                <img src="~/Images/Earning Reports.png" width="80%" />
            </div>
        </div>
    </li>
</ul>

$("#sortable").sortable({
    cursor: 'move',
    revert: true
});

//Set at page load time
var arrNameOrder = ["TopProductsBySales", "TopProductsByVolume", "EarningReports"];
//default order to be displayed
var arrValuesOrderOld = [1, 2, 3];
var arrValuesOrderNew = [1, 2, 3];

//Get new display order
$.each(arrNameOrder, function (index, value) {
    $.each(model, function (i, item) {
        if (value == item.WidgetName) {
            arrValuesOrderNew[index] = item.DisplayOrder;
        }
    });
});

//Set new display order
var $ul = $("#sortable"),
    $items = $("#sortable").children();

$.each(arrNameOrder, function (index, value) {
    $.each(arrValuesOrderNew, function (i, item) {
        if (index == item - 1) {
            $ul.append($items.get(arrValuesOrderOld[i] - 1));
        }
    });
});

1.6. Save button for save operation.

function save() {

    //Save data
    var saveData = new FormData();
    var rowIndex = 0;

    const liWidgets = document.getElementsByClassName("li-sortable");

    //Set orders
    for (var i = 0; i < liWidgets.length; i++) {
        saveData.append("widgets[" + rowIndex + "].WidgetName", liWidgets[i].id);
        saveData.append("widgets[" + rowIndex + "].DisplayOrder", rowIndex + 1);
        rowIndex++;
    }

    //Call save controller action method
    $.ajax({
        type: 'POST',
        data: saveData,
        url: 'SaveWidget',
        contentType: false,
        datatype: "JSON",
        processData: false,
        success: function (response) {
            if ("Success") {
                alert('Success!');
            }
            else {
                alert('Fail!');
            }
        },
        error: function (jqXhr, textStatus, errorMessage) {
            alert('Error!');
        },
        beforeSend: function () {
                    
        },
        complete: function (data) {
                    
        }
    });
}

2. Controller - HomeController.cs

Server-side logic to save and retrieve data of widgets.

• Get the dashboard at load time
• Save action on the save button click
• Get widget method
• Save widget method
• Model UserWidget

2.1. Get dashboard at load time.

public ActionResult Dashboard()
{
    int userId = 1;
    List<UserWidget> objUserWidget = GetWidgets(userId);
    return View("Dashboard", objUserWidget);
}

userId should be taken from logged in user session but for easy to use here it is directly used.

2.2. Save action on the save button click.

[HttpPost]
public ActionResult SaveWidget(List<UserWidget> widgets)
{
    int userId = 1;
    var response = "Success";

    System.Data.DataTable dtWidget = new System.Data.DataTable();

    dtWidget.Columns.Add(new System.Data.DataColumn("WidgetName", typeof(string)));
    dtWidget.Columns.Add(new System.Data.DataColumn("DisplayOrder", typeof(int)));

    for (int i = 0; i < widgets.Count; i++)
    {
        dtWidget.Rows.Add(widgets[i].WidgetName, widgets[i].DisplayOrder);
    }
    SaveWidgets(userId, dtWidget);
    return Json(response, JsonRequestBehavior.AllowGet);
}

2.3. Get widget method.

[NonAction]
public List<UserWidget> GetWidgets(int userId)
{
    List<UserWidget> userWidgets = new List<UserWidget>();

    string CS = System.Configuration.ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

    using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(CS))
    {
        System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("GetWidgets", con);
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@UserId", userId);
        con.Open();
        System.Data.SqlClient.SqlDataReader rdr = cmd.ExecuteReader();

        while (rdr.Read())
        {
            userWidgets.Add(new UserWidget() { WidgetName = rdr["WidgetName"].ToString() , DisplayOrder = Convert.ToInt32(rdr["DisplayOrder"]) });
        }
    }
    return userWidgets;
}

2.4. Save widget method.

[NonAction]
public string SaveWidgets(int userId, System.Data.DataTable dtWidgets)
{
    string status = "Fail";

    string CS = System.Configuration.ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

    using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(CS))
    {
        try
        {
            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("SaveWidgets", con);

            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@UserId", userId);
            cmd.Parameters.AddWithValue("@UDT_Widget", dtWidgets);

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            status = "Success";
        }
        catch (Exception ex)
        {

        }
    }
    return status;
}

2.5. Model UserWidget.

public class UserWidget
{
    public string WidgetName { get; set; }
    public int DisplayOrder { get; set; }
}

3. MS SQL - Table, User-Defined Table Types, and Stored Procedures

Tables and stored procedures to perform database operations such as saving and retrieving.

• Table
• User-Defined Table Type
• Stored Procedures for Get and Save

3.1. Table

CREATE TABLE [dbo].[Widget](  
    [Id] [int] IDENTITY(1,1) NOT NULL,  
    [WidgetName] [varchar](50) NOT NULL,  
    [DisplayOrder] [int] NOT NULL,  
	[UserId] [int] NOT NULL,
 CONSTRAINT [PK__Widget__7AD04F11A993CC91] PRIMARY KEY CLUSTERED   
(  
    [Id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]  

3.2. User-Defined Table Type.

IF object_id('dbo.SaveWidgets') is not null
    DROP PROCEDURE [dbo].[SaveWidgets]
GO

IF EXISTS (SELECT	1 FROM	sys.table_types WHERE	name = 'UDT_Widget')
BEGIN
	DROP TYPE [dbo].[UDT_Widget]
END
GO
CREATE TYPE [dbo].[UDT_Widget] AS TABLE(
	[WidgetName] [varchar](50) NULL,
	[DisplayOrder] [int] NULL
)
GO

3.3. Stored Procedures for Get and Save.

Create Or Alter  PROCEDURE [dbo].[GetWidgets] 
(          
	@UserId				INT        
)   
As
Begin
	Select Id, WidgetName, DisplayOrder from Widget where UserId = @UserId
End

Create Or Alter PROCEDURE [dbo].[SaveWidgets] 
(         
	@UserId			int, 
	@UDT_Widget		[UDT_Widget] READONLY        
)   
As
Begin
	MERGE [Widget] AS T  
	USING  @UDT_Widget AS S  
	ON T.WidgetName = S.WidgetName and T.UserId = @UserId 
	WHEN NOT MATCHED BY TARGET THEN  
	INSERT 
		(WidgetName, DisplayOrder, UserId)  
	VALUES  
		(S.WidgetName, S.DisplayOrder, @UserId)  
	WHEN MATCHED 
	THEN UPDATE SET 
		T.DisplayOrder = S.DisplayOrder;
End
GO

4. Output - Load time, move widgets, after save

Output of the user interface.

• Load time
• Move widgets
• After save

4.1. Load time

At page load time, no preference/widget is saved to the database; therefore, the default widget order is loaded.

4.2. Move widgets

Click on the widget, then drag and drop it to another place or as desired, in the order in which you want to display it.

4.3. After save

Click the "Save image button located at the top left once the order is set. It will be saved to the database. Next time you return to this page, it will retrieve the data from the database and display the widgets based on the saved data.

Summary

To save the position of sortable (draggable) widgets, you typically need to.

• Track the order of widgets after sorting.
• Store the new order in a persistent storage, like a database.
• Restore the order when the page reloads.

Here’s a general approach using JavaScript with jQuery UI Sortable and MS SQL database as an example. Sortable is used to reorder the elements in a list or grid using the mouse.

Reference

For more details, refer to the following references.

• Sortable jQuery
• jQuery UI Sortable
• Insert and Update in SQL Using User-Defined Table Type and XML
• User-Defined Table Type and Table Valued Parameters In Stored Procedure To Reduce The Code Size In Code Behind File
• Insert Data Into Database by Stored Procedure in ASP.NET C#