How to update the data source after performing editing in an ASP.NET Core Pivot Table?

Introduction

When working with ASP.NET Core Pivot Table, you can dynamically edit the value cells by setting the allowEditing property in e-editSettings tag to true. However, in certain scenarios, you may want to update the data source in your controller after performing edit operations. This can be achieved by sending the edited data back to a controller file using jQuery AJAX. In this article, we will guide you through the process with code examples.

Step 1: Defining the data source in the controller

First, you need to create the data source in your controller in order to configure the pivot table control. To do this, create a class that represents the data structure, which in our scenario is OrderDetails. This class will contain properties that represent the necessary data fields for the pivot table, such as “Country”, “UniqueID”, “Amount”, and “Year”.

Here is an example of how to define a data source for the pivot table using a class called OrdersDetails in the controller (i.e., HomeController.cs) file:

[HomeController.cs]

using Microsoft.AspNetCore.Mvc;

public class HomeController : Controller
{

    public class OrdersDetails {
        // List to store order details.
        public static List<OrdersDetails> order = new List<OrdersDetails>();

        // Properties to define the data fields.
        public OrdersDetails(int UniqueId, string Country, string Year, int Amount) {
            this.UniqueID = UniqueId;
            this.Country = Country;
            this.Year = Year;
            this.Amount = Amount;
        }
        public static List<OrdersDetails> GetAllRecords()
        {
             // Check if the list is empty to avoid duplicate data.
             if (order.Count() == 0)
             {
                // Populating the order list with sample data.
                order.Add(new OrdersDetails(1, "France", "FY 2015", 52824));
             }
             return order;
        }
        // Properties to define the data fields.
        public string Country { get; set; }
        public int ? UniqueID { get; set; }
        public int Amount { get; set; }
        public string Year { get; set; }
    }
}

Step 2: Initializing the pivot table control

After defining the required data source in your controller file (i.e., HomeController), it’s essential to initialize the pivot table control on the cshtml page (i.e., ~/Pages/Index.cshtml) using that data source. The following code example demonstrates how to assign the data source from the controller file to the cshtml page:

[HomeController.cs]

using Microsoft.AspNetCore.Mvc;

public class HomeController : Controller
{
    public IActionResult Index()
    {
        // Fetch your data here
        var currentData = OrdersDetails.GetAllRecords().ToList();

        // Store the data in the ViewBag with a key to be accessed in the cshtml page.
        ViewBag.datasource = currentData;

        // Return the corresponding view.
        return View();
    }
}

[Index.cshtml]

<ejs-pivotview id="PivotView">
    <e-datasourcesettings dataSource="@ViewBag.datasource">
        <e-rows>
            <e-field name="Country"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Year"></e-field>
        </e-columns>
        <e-values>
            <e-field name="Amount"></e-field>
        </e-values>
    </e-datasourcesettings>
</ejs-pivotview>

In the controller, we retrieve the previously defined data source and store it in the currentData variable. This data is then assigned to ViewBag.datasource, which will be accessible on the cshtml page. Subsequently, on the cshtml page, we define the pivot table control using the ejs-pivotview tag and set its dataSource property to ViewBag.datasource to utilize the data passed from the controller.

Step 3: Including jQuery library

Once you’ve initialized the pivot table, you need to use a JavaScript library, like jQuery, to make an Ajax request to the controller file in order to update a data source after performing edit operations in a pivot table. To incorporate the jQuery library into a pivot table, it is necessary to include the following script references in the application’s ~/Views/Shared/Layout.cshtml file:

[Layout.cshtml]

<script src="https://ajax.aspnetcdn.com/ajax/jquery/jquery-2.2.0.min.js"
    asp-fallback-src="~/lib/jquery/dist/jquery.min.js"
    asp-fallback-test="window.jQuery"
    crossorigin="anonymous"
    integrity="sha384-K+ctZQ+LL8q6tP7I94W+qzQsfRV2a+AfHIi9k8z8l9ggpc8X+Ytst4yBo/hH+8Fk">
</script>

Step 4: Define the data transfer object

After adding the jQuery library, you need to send the edited data from the cshtml page (i.e., ~/Pages/Index.cshtml) to the controller (i.e., HomeController.cs). To accomplish this, first you need to define a Data Transfer Object (DTO) in the controller. This will carry the edited data from the cshtml page to the controller.

[HomeController.cs]

public class DataResult
{
    // Update properties of your entity here
    public List<OrdersDetails> AddedData { get; set; }
    public List<int> ModifiedIndex { get; set; }
}

The DataResult class includes two properties: AddedData, which holds the edited data, and ModifiedIndex, which may carry the indices of the modified rows or other relevant information.

Step 5: Write a controller action

After defining the data transfer object, you need to handle the incoming edited data. To achieve this, create an action method (i.e., BatchUpdate) that the AJAX call will target.

Here is an example of how to receive the edited data and perform CRUD operations:
[HomeController.cs]

[HttpPost]
    public IActionResult BatchUpdate([FromBody] DataResult DataResult)
     {
         // Here we get the current data source
         currentData = OrdersDetails.GetAllRecords().ToList();
         for (var i = 0; i < DataResult.ModifiedIndex.Count; i++)
         {
             if (DataResult.AddedData.Count > 0)
             {
                 for (var j = 0; j < DataResult.AddedData.Count; j++)
                 {
                     if (currentData[DataResult.ModifiedIndex[i]].UniqueID == DataResult.AddedData[j].UniqueID)
                     {
                         // Here we update the modified data to data source
                         currentData[DataResult.ModifiedIndex[i]] = DataResult.AddedData[j];
                     }
                     else
                     {
                         // Here we include the newly added data to the data source
                         DataResult.AddedData[j].UniqueID = currentData.Count + 1;
                         currentData.Add(DataResult.AddedData[j]);
                     }
                 }
             }
             else
             {
                 // Here we remove the data from data source
                 currentData.RemoveAt(DataResult.ModifiedIndex[i]);
             }
         }
         return Json(new { result = currentData, count = currentData.Count });
     }

In the above, we iterate over the ModifiedIndex property of the DataResult class, which contains the indexes of the edited records. For each index, we check if there is any newly added or edited data by checking the count of the AddedData property. If there is, we iterate over the AddedData collection and check whether the UniqueID of the added data already exists in the current data source. If this condition is met, we update the corresponding data in the data source by assigning the added data to the currentData list. Otherwise, we assign a new UniqueID to the added data and include it in the data source by appending it to the currentData list. Should there be no added data, we remove the data at the modified index from the currentData list, effectively deleting it from the data source.

Step 6: Send edited data using jQuery

In the cshtml page (i.e., ~/Pages/Index.cshtml), you need to set up the jQuery AJAX call in a script within the editCompleted event. This event triggers when value cells are edited completely.

Here is an example of how to send the data to the controller:

<ejs-pivotview id="PivotView" height="300" editCompleted="editCompleted">
 <e-editSettings allowAdding="true" allowDeleting="true" allowEditing="true" mode="Normal"></e-editSettings>
 </ejs-pivotview>
<script>
 function editCompleted(args) {
     $.ajax({
         url: "/Home/BatchUpdate",
         type: "POST",
         data: JSON.stringify({ 'addedData': args.currentData, 'modifiedIndex': args.previousPosition }),
         dataType: 'json',
         contentType: 'application/json; charset=UTF-8',
         success: function (response) {
             console.log("Edit Completed");
         }
     });
 }
</script>

In this code snippet, we are using jQuery’s $.ajax method to send a POST request to the BatchUpdate action method in the HomeController. The args.currentData contains the newly edited data, and args.previousPosition contains the indexes of the modified data rows. This data is serialized into JSON format and sent to the controller. After the controller receives the edited data, the data source can be updated as mentioned in the previous step.

The following screenshot, which portrays the results of the code snippet mentioned above,

Screenshot:

You can refer to this GitHub sample for a practical demonstration of this code.

Conclusion:

I hope you enjoyed learning how to update the data source after performing editing in an ASP.NET Core Pivot Table.

You can refer to our ASP.NET Core Pivot Table feature tour page to learn about its other groundbreaking feature representations and documentation, and how to quickly get started for configuration specifications. You can also explore our ASP.NET Core Pivot Table example to understand how to create and manipulate data.

For current customers, you can check out our components from the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our other controls.

If you have any queries or require clarifications, please let us know in the comments section below. You can also contact us through our support forums, support portal, or feedback portal. We are always happy to assist you!