Article Overview

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

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

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.

1. View - Dashboard.cshtml

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

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.

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.

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.

4.1. Load time

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

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

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.

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.