How to save position of the sortable (draggable) widgets

Article Overview

• Background
• How do you reorder, save, and reset the widgets' position?
• Prerequisites
• Complete example
  • View - Dashboard.cshtml
  • Controller - HomeController.cs
  • MS SQL - Table, User-Defined Table Types, and Stored Procedures
• Example in detail
  • View - Dashboard.cshtml
    • Model
    • Include - links and styles
    • Save button for the save operation
    • Sortable widgets - with ul element
    • Load widgets according to display order
    • Save button for the save operation
  • Controller - HomeController.cs
    • Get the dashboard at load time
    • Save action on the save button click
    • Get widget method
    • Save widget method
    • Model UserWidget
  • MS SQL
    • Table
    • User-Defined Table Type
    • Stored Procedures for Get and Save
  • Output
• Summary
• Reference

Background

Let us assume that there is a dashboard with different widgets such as “Top Products by Sales”, “Top Products by Volume”, and “Earning Reports” now, how to add a feature where user can re-arrange the order of widgets according to their own need and save that so that the next time it can be loaded in the order in which it was saved.

Sortable is used to reorder the elements in a list or grid using the mouse.

This is described in this article step by step with a detailed explanation:

• How do you drag and drop widgets using jQuery sortable?
• How to save the latest current positions of the widgets using User-Defined Table Type in MS SQL?
• How to retrieve widget position from the database and display accordingly using custom code?

How to re-order, save, and reset widget positions?

To save the position of draggable widgets, it requires capturing their final position after a drag operation and then persisting that data. The saving method depends on the technology used for the draggable widgets, and the data needs to be stored on the server-side.

The following are the four steps to achieve this.

• Make an element draggable by using the sortable plugin of jQuery.
• Capture the position and send it to the server when the save button is clicked.
• Store data in a database using a stored procedure with a User-Defined Table type in MS SQL.
• Retrieve it on Page Load and re-arrange the widgets with custom logic.

Prerequisites

This example is prepared in .NET MVC using C# and MS SQL stored procedure by User-Defined Table Type and jQuery sortable. Hence, it will be good to have knowledge of these.

Complete Example

The uploaded complete example has mainly three parts.

• View: Dashboard.cshtml
• Controller: HomeController.cs
• MS SQL: Table, User-Defined Table Types, and Stored Procedures

View - Dashboard.cshtml

Client-side user interaction for changing widget positions and sending them for saving.

Controller - HomeController.cs

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

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

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

Example in detail

Let's understand the complete code step by step in four groups.

• View: Dashboard.cshtml
• Controller: HomeController.cs
• MS SQL: Table, User-Defined Table Types, and Stored Procedures
• Output: Load time, move widgets, after save

1. View - Dashboard.cshtml

Client-side user interaction for changing widget positions and sending for saving.

• Model
• Include - links and styles
• Save button for the save operation
• Sortable widgets - with UL element
• Load widgets according to display order
• Save button for the save operation

1.1. Model

@model IEnumerable<WebApplication4.Controllers.UserWidget>

1.2. Include - links and styles.

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

1.3. Save button for save operation.

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

1.4. Sortable widgets - with UL element.

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

1.5. Load widgets according to display order.

$("#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#