Connecting MySQL Server data in to Blazor DataGrid Component

13 Mar 202424 minutes to read

This section describes how to connect and retrieve data from a MySQL Server database using MySQL data and bind it to the Blazor DataGrid component.

MySQL Server database can be bound to the Blazor DataGrid component in different ways (i.e.) using DataSource property, CustomAdaptor feature and remote data binding using various adaptors. In this documentation, two approaches will be examined to connect a MySQL Server database to a Blazor DataGrid component. Both the approaches have capability to handle data and CRUD operations with built-in methods as well as can be customized as per your own.

  • Using UrlAdaptor

The UrlAdaptor serves as the base adaptor for facilitating communication between remote data services and an UI component. It enables the remote binding of data to the Blazor DataGrid component by connecting to an existing pre-configured API service linked to the MySQL Server database. While the Blazor DataGrid component supports various adaptors to fulfill this requirement, including Web API, OData, ODataV4, Url, and GraphQL, the UrlAdaptor is particularly useful for the scenarios where a custom API service with unique logic for handling data and CRUD operations is in place. This approach allows for custom handling of data and CRUD operations, and the resultant data returned in the result and count format for display in the Blazor DataGrid component.

  • Using CustomAdaptor

The CustomAdaptor serves as a mediator between the UI component and the database for data binding. While the data source from the database can be directly bound to the SfGrid component locally using the DataSource property, the CustomAdaptor approach is preferred as it allows for customization of both data operations and CRUD operations according to specific requirements. In this approach, for every action in the Blazor DataGrid component, a corresponding request with action details is sent to the CustomAdaptor. The Blazor DataGrid component provides predefined methods to perform data operations such as searching, filtering, sorting, aggregation, paging and grouping. Alternatively, your own custom methods can be employed to execute operations and return the data in the Result and Count format of the DataResult class for displaying in the Blazor DataGrid component. Additionally, for CRUD operations, predefined methods can be overridden to provide custom functionality. Further details on this can be found in the latter part of the documentation.

Binding data from MySQL Server using an API service

This section describes step by step process how to retrieve data from a MySQL Server using an API service and bind it to the Blazor DataGrid component.

Creating an API service

1. Open Visual Studio and create an ASP.NET Core Web App project type, naming it MyWebService. To create an ASP.NET Core Web application, follow the documentation link.

2. To connect a MySQL Server database using the MySQL driver in your application, you need to install the MySQL.Data NuGet package. To add MySQL.Data in the app, open the NuGet package manager in Visual Studio (Tools → NuGet Package Manager → Manage NuGet Packages for Solution), search and install it.

3. Create an API controller (aka, GridController.cs) file under Controllers folder that helps to establish data communication with the Blazor DataGrid component.

4. In an API controller (aka, GridController), connect to MySQL Server. In the Get() method MySqlConnection helps to connect the MySQL Server database. Next, using MySqlCommand and MySqlDataAdapter you can process the desired MySQL query string and retrieve data from the database. The Fill method of the DataAdapter is used to populate the MySQL data into a DataTable as shown in the following code snippet.

using Microsoft.AspNetCore.Mvc;
using MySql.Data.MySqlClient;
using System.Data;
using Syncfusion.Blazor;
using Syncfusion.Blazor.Data;
using System.ComponentModel.DataAnnotations;
using Newtonsoft.Json;

namespace MyWebService.Controllers
{
    [ApiController]
    public class GridController : ControllerBase
    {
        public static List<Order> Orders { get; set; }

        public class Order
        {
            [Key]
            public int? OrderID { get; set; }
            public string? CustomerName { get; set; }
            public int? EmployeeID { get; set; }
            public decimal? Freight { get; set; }
            public string? ShipCity { get; set; }
        }

        [Route("api/[controller]")]
        public List<Order> GetOrderData()
        {
            //TODO: Enter the connectionstring of database
            string ConnectionString = @"<Enter a valid connection string>";
            string QueryStr = "SELECT * FROM orders ORDER BY OrderID";
            MySqlConnection sqlConnection = new(ConnectionString);
            sqlConnection.Open();
            //Initialize the MySqlCommand
            MySqlCommand SqlCommand = new(QueryStr, sqlConnection);
            //Initialize the MySqlDataAdapter
            MySqlDataAdapter DataAdapter = new(SqlCommand);
            DataTable DataTable = new();
            // Using MySqlDataAdapter, process the query string and fill the data into the dataset
            DataAdapter.Fill(DataTable);
            sqlConnection.Close();
            //Cast the data fetched from MySqlDataAdapter to List<T>
            var DataSource = (from DataRow Data in DataTable.Rows
                            select new Order()
                            {
                                OrderID = Convert.ToInt32(Data["OrderID"]),
                                CustomerName = Data["CustomerName"].ToString(),
                                EmployeeID = Convert.ToInt32(Data["EmployeeID"]),
                                ShipCity = Data["ShipCity"].ToString(),
                                Freight = Convert.ToDecimal(Data["Freight"])
                            }).ToList();
            return DataSource;
        }
    }
}

5. Run the application and it will be hosted within the URL https://localhost:xxxx.

6. Finally, the retrieved data from MySQL Server database which is in the form of list of array can be found in an API controller available in the URL link https://localhost:xxxx/api/Grid, as shown in the browser page below.

Hosted API URL

Connecting Blazor DataGrid to an API service

1. Create a simple Blazor DataGrid component by following steps. This section briefly explains about how to include Blazor DataGrid component in your Blazor Web App using Visual Studio.

Prerequisites

Create a new Blazor Web App

You can create a Blazor Web App using Visual Studio 2022 via Microsoft Templates or the Syncfusion Blazor Extension.

You need to configure the corresponding Interactive render mode and Interactivity location while creating a Blazor Web Application.

Install Syncfusion Blazor Grid and Themes NuGet in the Blazor Web App

To add Blazor DataGrid component in the app, open the NuGet package manager in Visual Studio (Tools → NuGet Package Manager → Manage NuGet Packages for Solution), search and install Syncfusion.Blazor.Grid and Syncfusion.Blazor.Themes.

If you utilize WebAssembly or Auto render modes in the Blazor Web App need to be install Syncfusion Blazor components NuGet packages within the client project.

Alternatively, you can utilize the following package manager command to achieve the same.

Install-Package Syncfusion.Blazor.Grid -Version 26.2.4
Install-Package Syncfusion.Blazor.Themes -Version 26.2.4

Syncfusion Blazor components are available in nuget.org. Refer to NuGet packages topic for available NuGet packages list with component details.

Register Syncfusion Blazor Service

Open ~/_Imports.razor file and import the Syncfusion.Blazor and Syncfusion.Blazor.Grids namespace.

@using Syncfusion.Blazor
@using Syncfusion.Blazor.Grids

Now, register the Syncfusion Blazor Service in the ~/Program.cs file of your Blazor Web App. For a app with WebAssembly or Auto (Server and WebAssembly) interactive render mode, register the Syncfusion Blazor service in both ~/Program.cs files of your web app.

....
using Syncfusion.Blazor;
....
builder.Services.AddSyncfusionBlazor();
....

Add stylesheet and script resources

The theme stylesheet and script can be accessed from NuGet through Static Web Assets. Include the stylesheet reference in the <head> section and the script reference at the end of the <body> in the ~/Components/App.razor file as shown below:

<head>
    ....
    <link href="_content/Syncfusion.Blazor.Themes/fluent.css" rel="stylesheet" />
</head>
....
<body>
    ....
    <script src="_content/Syncfusion.Blazor.Core/scripts/syncfusion-blazor.min.js" type="text/javascript"></script>
</body>
  • Check out the Blazor Themes topic to discover various methods (Static Web Assets, CDN, and CRG) for referencing themes in your Blazor application. Also, check out the Adding Script Reference topic to learn different approaches for adding script references in your Blazor application.
  • In this Blazor Web app, set the rendermode as either InteractiveServer or InteractiveAuto as per your configuration.

2. Map the hosted API’s URL link https://localhost:xxxx/api/Grid to the Blazor DataGrid component in Index.razor by using the Url property of SfDataManager. To interact with remote data source, provide the endpoint Url.

3. The SfDataManager offers multiple adaptor options to connect with remote database based on an API service. Below is an example of the UrlAdaptor configuration where an API service are set up to return the resulting data in the result and count format.

4. The UrlAdaptor acts as the base adaptor for interacting with remote data service. Most of the built-in adaptors are derived from the UrlAdaptor.

@using Syncfusion.Blazor.Grids
@using Syncfusion.Blazor.Data
@using Syncfusion.Blazor

<SfGrid @ref="Grid" TValue="Order" AllowPaging="true" AllowFiltering="true" AllowSorting="true" AllowGrouping="true" Toolbar="@(new List<string>() { "Add", "Edit", "Delete", "Update", "Cancel", "Search" })">
    <SfDataManager Url="https://localhost:xxxx/api/Grid" BatchUrl="https://localhost:xxxx/api/Grid/Batch" InsertUrl="https://localhost:xxxx/api/Grid/Insert" UpdateUrl="https://localhost:xxxx/api/Grid/Update" RemoveUrl="https://localhost:xxxx/api/Grid/Delete" Adaptor="Adaptors.UrlAdaptor"></SfDataManager>
    <GridEditSettings AllowEditing="true" AllowDeleting="true" AllowAdding="true" Mode="EditMode.Normal"></GridEditSettings>
    <GridAggregates>
        <GridAggregate>
            <GridAggregateColumns>
                <GridAggregateColumn Field=@nameof(Order.Freight) Type="AggregateType.Sum" Format="C2">
                    <FooterTemplate>
                        @{
                            var aggregate = (context as AggregateTemplateContext);
                            <div>
                                <p>Sum: @aggregate.Sum</p>
                            </div>
                        }
                    </FooterTemplate>
                </GridAggregateColumn>
            </GridAggregateColumns>
        </GridAggregate>
        <GridAggregate>
            <GridAggregateColumns>
                <GridAggregateColumn Field=@nameof(Order.Freight) Type="AggregateType.Average" Format="C2">
                    <FooterTemplate>
                        @{
                            var aggregate = (context as AggregateTemplateContext);
                            <div>
                                <p>Average: @aggregate.Average</p>
                            </div>
                        }
                    </FooterTemplate>
                </GridAggregateColumn>
            </GridAggregateColumns>
        </GridAggregate>
    </GridAggregates>
    <GridColumns>
        <GridColumn Field=@nameof(Order.OrderID) HeaderText="Order ID"  ValidationRules="@(new ValidationRules{ Required= true })" IsPrimaryKey="true" TextAlign="TextAlign.Right" Width="120"></GridColumn>
        <GridColumn Field=@nameof(Order.CustomerName) HeaderText="Customer Name" ValidationRules="@(new ValidationRules{ Required= true, MinLength = 3 })" Width="150"></GridColumn>
        <GridColumn Field=@nameof(Order.EmployeeID) HeaderText="Employee ID" TextAlign="TextAlign.Right" Width="150"></GridColumn>
        <GridColumn Field=@nameof(Order.Freight) HeaderText="Freight" TextAlign="TextAlign.Right" Format="C2" Width="150"></GridColumn>
        <GridColumn Field=@nameof(Order.ShipCity) HeaderText="Ship City" Width="150"></GridColumn>
    </GridColumns>
</SfGrid>

@code {
    SfGrid<Order> Grid { get; set; }
    public List<Order> Orders { get; set; }
    public class Order
    {
        public int? OrderID { get; set; }
        public string CustomerName { get; set; }
        public int EmployeeID { get; set; }
        public decimal Freight { get; set; }
        public string ShipCity { get; set; }
    }
}
[ApiController]
public class GridController : ControllerBase
{ 
    /// <summary>
    /// Returns the data collection as result and count after performing data operations based on request from <see cref=”DataManagerRequest”/>
    /// </summary>
    /// <param name="DataManagerRequest">DataManagerRequest contains the information regarding searching, filtering, sorting, aggregates and paging which is handled on the Blazor DataGrid component side</param>
    /// <returns>The data collection's type is determined by how this method has been implemented.</returns>
    [HttpPost]
    [Route("api/[controller]")]
    public object Post([FromBody] DataManagerRequest DataManagerRequest)
    {
        IEnumerable<Order> DataSource = GetOrderData();
        int TotalRecordsCount = DataSource.Cast<Order>().Count();
        return new { result = DataSource, count = TotalRecordsCount};
    }
}

In the above Blazor DataGrid component, AllowSearching, AllowSorting, AllowFiltering, AllowPaging, AllowGrouping and CRUD-related properties have been enabled. The details on how to handle these actions are explained below.

When you run the application, the resultant Blazor DataGrid component will look like this

Blazor DataGrid component bound with MySQL Server data

  • The Syncfusion Blazor DataGrid component provides built-in support for handling various data operations such as searching, sorting, filtering, aggregate and paging on the server-side. These operations can be handled using methods such as PerformSearching, PerformFiltering, PerformSorting, PerformAggregation, PerformTake and PerformSkip available in the Syncfusion.Blazor.Data package. Let’s explore how to manage these data operations using the UrlAdaptor.
  • In an API service project, add Syncfusion.Blazor.Data by opening the NuGet package manager in Visual Studio (Tools → NuGet Package Manager → Manage NuGet Packages for Solution), search and install it.

Handling searching operation

To handle searching operation, ensure that your API endpoint supports custom searching criteria. Implement the searching logic on the server-side using the PerformSearching method from the DataOperations class. This allows the custom data source to undergo searching based on the criteria specified in the incoming DataManagerRequest object.

  • RAZOR
    • [HttpPost]
[Route("api/[controller]")]
public object Post([FromBody] DataManagerRequest DataManagerRequest)
{
    IEnumerable<Order> DataSource = GetOrderData();
    // Handling Searching in UrlAdaptor.
    if (DataManagerRequest.Search != null && DataManagerRequest.Search.Count > 0)
    {
        // Searching
        DataSource = DataOperations.PerformSearching(DataSource, DataManagerRequest.Search);
        //Add custom logic here if needed and remove above method
    }
    int TotalRecordsCount = DataSource.Cast<Order>().Count();
    return new { result = DataSource, count = TotalRecordsCount };
}

    Handling filtering operation

    To handle filtering operation, ensure that your API endpoint supports custom filtering criteria. Implement the filtering logic on the server-side using the PerformFiltering method from the DataOperations class. This allows the custom data source to undergo filtering based on the criteria specified in the incoming DataManagerRequest object.

  • RAZOR
    • [HttpPost]
[Route("api/[controller]")]
public object Post([FromBody] DataManagerRequest DataManagerRequest)
{
    IEnumerable<Order> DataSource = GetOrderData();
    // Handling Filtering in UrlAdaptor.
    if (DataManagerRequest.Where != null && DataManagerRequest.Where.Count > 0)
    {
        // Filtering
        DataSource = DataOperations.PerformFiltering(DataSource, DataManagerRequest.Where, DataManagerRequest.Where[0].Operator);
        //Add custom logic here if needed and remove above method
    }
    int TotalRecordsCount = DataSource.Cast<Order>().Count();
    return new { result = DataSource, count = TotalRecordsCount };
}

    Handling sorting operation

    To handle sorting operation, ensure that your API endpoint supports custom sorting criteria. Implement the sorting logic on the server-side using the PerformSorting method from the DataOperations class. This allows the custom data source to undergo sorting based on the criteria specified in the incoming DataManagerRequest object.

  • RAZOR
    • [HttpPost]
[Route("api/[controller]")]
public object Post([FromBody] DataManagerRequest DataManagerRequest)
{
    IEnumerable<Order> DataSource = GetOrderData();
    // Handling Sorting in UrlAdaptor.
    if (DataManagerRequest.Sorted != null && DataManagerRequest.Sorted.Count > 0)
    {
        // Sorting
        DataSource = DataOperations.PerformSorting(DataSource, DataManagerRequest.Sorted);
        //Add custom logic here if needed and remove above method
    }
    int TotalRecordsCount = DataSource.Cast<Order>().Count();
    return new { result = DataSource, count = TotalRecordsCount };
}

    Handling aggregate operation

    To handle aggregate operation, ensure that your API endpoint supports custom aggregate criteria. Implement the aggregate logic on the server-side using the PerformAggregation method from the DataUtil class. This allows the custom data source to undergo aggregate based on the criteria specified in the incoming DataManagerRequest object.

  • RAZOR
    • [HttpPost]
 [Route("api/[controller]")]
 public object Post([FromBody] DataManagerRequest DataManagerRequest)
 {
    IEnumerable<Order> DataSource = GetOrderData();
    int TotalRecordsCount = DataSource.Cast<Order>().Count();
    // Handling Aggregation in UrlAdaptor.
    IDictionary<string, object> Aggregates = null;
    if (DataManagerRequest.Aggregates != null) 
    {  
        // Aggregation
        Aggregates = DataUtil.PerformAggregation(DataSource, DataManagerRequest.Aggregates);
        //Add custom logic here if needed and remove above method                
    }
    return new { result = DataSource, count = TotalRecordsCount, aggregates = Aggregates };
 }

    The server-side management of the PerformAggregation method is necessary only for the Footer Template aggregation. There is no need for explicit handling of the Aggregate operation for the Group Footer template and Group Caption template.

    Handling paging operation

    To handle paging operation, ensure that your API endpoint supports custom paging criteria. Implement the paging logic on the server-side using the PerformTake and PerformSkip method from the DataOperations class. This allows the custom data source to undergo paging based on the criteria specified in the incoming DataManagerRequest object.

  • RAZOR
    • [HttpPost]
[Route("api/[controller]")]
public object Post([FromBody] DataManagerRequest DataManagerRequest)
{
    IEnumerable<Order> DataSource = GetOrderData();
    int TotalRecordsCount = DataSource.Cast<Order>().Count();
    // Handling Paging in UrlAdaptor.
    if (DataManagerRequest.Skip != 0)
    {
        // Paging
        DataSource = DataOperations.PerformSkip(DataSource, DataManagerRequest.Skip);
        //Add custom logic here if needed and remove above method
    }
    if (DataManagerRequest.Take != 0)
    {
        DataSource = DataOperations.PerformTake(DataSource, DataManagerRequest.Take);
        //Add custom logic here if needed and remove above method
    }
    return new { result = DataSource, count = TotalRecordsCount };
}

    For optimal performance, it is recommended to follow this sequence of operations(Searching, Filtering, Sorting, Aggregate and Paging ) in the ReadAsync method.

    Handling CRUD operations

    To enable editing in this Blazor DataGrid component, utilize the GridEditSettings component. The Blazor DataGrid offers multiple edit modes including the Inline/Normal, Dialog, and Batch editing. For more details, refer to the Blazor DataGrid component editing documentation.

    In this scenario, the inline edit Mode and Toolbar property are configured to display toolbar items for editing purposes.

    <SfGrid @ref="Grid" TValue="Order" AllowPaging="true" AllowFiltering="true" AllowSorting="true" AllowGrouping="true" Toolbar="@(new List<string>() { "Add", "Edit", "Delete", "Update", "Cancel", "Search" })">
    <SfDataManager Url="https://localhost:xxxx/api/Grid" InsertUrl="https://localhost:xxxx/api/Grid/Insert" UpdateUrl="https://localhost:xxxx/api/Grid/Update" RemoveUrl="https://localhost:xxxx/api/Grid/Delete" BatchUrl="https://localhost:xxxx/api/Grid/Batch" Adaptor="Adaptors.UrlAdaptor"></SfDataManager>
    <GridEditSettings AllowEditing="true" AllowDeleting="true" AllowAdding="true" Mode="EditMode.Normal"></GridEditSettings>
    <GridColumns>
        <GridColumn Field=@nameof(Order.OrderID) HeaderText="Order ID" IsIdentity="true" ValidationRules="@(new ValidationRules{ Required= true })" IsPrimaryKey="true" TextAlign="TextAlign.Right" Width="120"></GridColumn>
        <GridColumn Field=@nameof(Order.CustomerName) HeaderText="Customer Name" ValidationRules="@(new ValidationRules{ Required= true, MinLength = 3 })" Width="150"></GridColumn>
        <GridColumn Field=@nameof(Order.EmployeeID) HeaderText="Employee ID" TextAlign="TextAlign.Right" Width="150"></GridColumn>
        <GridColumn Field=@nameof(Order.Freight) HeaderText="Freight" TextAlign="TextAlign.Right" Format="C2" Width="150"></GridColumn>
        <GridColumn Field=@nameof(Order.ShipCity) HeaderText="Ship City" Width="150"></GridColumn>
    </GridColumns>
</SfGrid>
    • Normal/Inline editing is the default edit Mode for the Blazor DataGrid component. To enable CRUD operations, ensure that the IsPrimaryKey property is set to true for a specific GridColumn, ensuring that its value is unique.
    • If database has an Autogenerated column, ensure to define IsIdentity property of GridColumn to disable them during adding or editing operations.

    Insert Operation:

    To insert a new row, simply click the Add toolbar button. The new record edit form will be displayed as shown below. Upon clicking the Update toolbar button, record will inserted into the Orders table by calling the following POST method of an API.

    [HttpPost]
[Route("api/Grid/Insert")]
/// <summary>
/// Inserts a new data item into the data collection.
/// </summary>
/// <param name="CRUDModel<T>">The set of information along with new record detail which is need to be inserted.</param>
/// <returns>Returns void</returns>
public void Insert([FromBody] CRUDModel<Order> Value)
{
    //TODO: Enter the connectionstring of database
    string ConnectionString = @"<Enter a valid connection string>";
    //Create query to insert the specific into the database by accessing its properties
    string Query = $"Insert into Orders(OrderID, CustomerName,Freight,ShipCity,EmployeeID) values( '{Value.Value.OrderID}','{Value.Value.CustomerName}','{Value.Value.Freight}','{Value.Value.ShipCity}','{Value.Value.EmployeeID}')";
    MySqlConnection Connection = new MySqlConnection(ConnectionString);
    Connection.Open();
    MySqlCommand Command = new MySqlCommand(Query, Connection);
    //Execute this code to reflect the changes into the database
    Command.ExecuteNonQuery();
    Connection.Close();
    //Add custom logic here if needed and remove above method
}

    Update Operation:

    To edit a row, first select desired row and click the Edit toolbar button. The edit form will be displayed and proceed to modify any column value as per your requirement. Clicking the Update toolbar button will update the edit record in the Orders table by involving the following Post method of an API.

    [HttpPost]
[Route("api/Grid/Update")]
/// <summary>
/// Update a existing data item from the data collection.
/// </summary>
/// <param name="CRUDModel<T>">The set of information along with updated record detail which is need to be updated.</param>
/// <returns>Returns void</returns>
public void Update([FromBody] CRUDModel<Order> Value)
{
    //TODO: Enter the connectionstring of database
    string ConnectionString = @"<Enter a valid connection string>";
    //Create query to update the changes into the database by accessing its properties
    string Query = $"Update Orders set CustomerName='{Value.Value.CustomerName}', Freight='{Value.Value.Freight}',EmployeeID='{Value.Value.EmployeeID}',ShipCity='{Value.Value.ShipCity}' where OrderID='{Value.Value.OrderID}'";
    MySqlConnection Connection = new MySqlConnection(ConnectionString);
    Connection.Open();
    //Execute the MySQL Command
    MySqlCommand Command = new MySqlCommand(Query, Connection);
    //Execute this code to reflect the changes into the database
    Command.ExecuteNonQuery();
    Connection.Close();
    //Add custom logic here if needed and remove above method
}

    Delete Operation:

    To delete a row, simply select the desired row and click the Delete toolbar button. This action will trigger a DELETE request to an API, containing the primary key value of the selected record. As a result corresponding record will be removed from the Orders table.

    [HttpPost]
[Route("api/Grid/Delete")]
/// <summary>
/// Remove a specific data item from the data collection.
/// </summary>
/// <param name="CRUDModel<T>">The set of information along with specific record detail which is need to be removed.</param>
/// <returns>Returns void</returns>
public void Delete([FromBody] CRUDModel<Order> Value)
{
    //TODO: Enter the connectionstring of database
    string ConnectionString = @"<Enter a valid connection string>";
    //Create query to remove the specific from database by passing the primary key column value.
    string Query = $"Delete from Orders where OrderID={Value.Key}";
    MySqlConnection Connection = new MySqlConnection(ConnectionString);
    Connection.Open();
    //Execute the MySQL Command
    MySqlCommand Command = new MySqlCommand(Query, Connection);
    //Execute this code to reflect the changes into the database
    Command.ExecuteNonQuery();
    Connection.Close();
    //Add custom logic here if needed and remove above method
}

    Batch Operation:

    To perform batch operation, define the edit Mode as Batch and specify the BatchUrl property in the SfDataManager. Use the Add toolbar button to insert new row in batch editing mode. To edit a cell, double-click the desired cell and update the value as required. To delete a record, simply select the record and press the Delete toolbar button. Now, all CRUD operations will be executed in batch editing mode. Clicking the Update toolbar button will update the newly added, edited, or deleted records from the Orders table using a single API POST request.

  • RAZOR
    • [HttpPost]
[Route("api/Grid/Batch")]
/// <summary>
/// Batchupdate (Insert, Update, Delete) a collection of data items from the data collection.
/// </summary>
/// <param name="CRUDModel<T>">The set of information along with details about the CRUD actions to be executed from the database.</param>
/// <returns>Returns void</returns>
public void Batch([FromBody] CRUDModel<Order> Value)
{
    //TODO: Enter the connectionstring of database
    string ConnectionString = @"<Enter a valid connection string>";
    if (Value.Changed != null)
    {
        foreach (var Record in (IEnumerable<Order>)Value.Changed)
        {
            //Create query to update the changes into the database by accessing its properties
            string Query = $"Update Orders set CustomerName='{Record.CustomerName}', Freight='{Record.Freight}',EmployeeID='{Record.EmployeeID}',ShipCity='{Record.ShipCity}' where OrderID='{Record.OrderID}'";
            MySqlConnection Connection = new MySqlConnection(ConnectionString);
            Connection.Open();
            //Execute the MySQL Command
            MySqlCommand Command = new MySqlCommand(Query, Connection);
            //Execute this code to reflect the changes into the database
            Command.ExecuteNonQuery();
            Connection.Close();
            //Add custom logic here if needed and remove above method
        }

    }
    if (Value.Added != null)
    {
        foreach (var Record in (IEnumerable<Order>)Value.Added)
        {
            //Create query to insert the specific into the database by accessing its properties 
            string Query = $"Insert into Orders(CustomerName,Freight,ShipCity,EmployeeID) values('{Record.CustomerName}','{Record.Freight}','{Record.ShipCity}','{Record.EmployeeID}')";
            MySqlConnection Connection = new MySqlConnection(ConnectionString);
            Connection.Open();
            //Execute the MySQL Command
            MySqlCommand Command = new MySqlCommand(Query, Connection);
            //Execute this code to reflect the changes into the database
            Command.ExecuteNonQuery();
            Connection.Close();
            //Add custom logic here if needed and remove above method
        }
    }
    if (Value.Deleted != null)
    {
        foreach (var Record in (IEnumerable<Order>)Value.Deleted)
        {
            //Create query to remove the specific from database by passing the primary key column value.
            string Query = $"Delete from Orders where OrderID={Record.OrderID}";
            MySqlConnection Connection = new MySqlConnection(ConnectionString);
            Connection.Open();
            //Execute the MySQL Command
            MySqlCommand Command = new MySqlCommand(Query, Connection);
            //Execute this code to reflect the changes into the database
            Command.ExecuteNonQuery();
            Connection.Close();
            //Add custom logic here if needed and remove above method
        }
    }
}

    When you run the application, the resultant Blazor DataGrid component will look like this

    Blazor DataGrid component bound with MySQL Server data

    Find the sample from this GitHub location.

    Binding data from MySQL Server using CustomAdaptor

    This section describes step by step process how to retrieve data from a MySQL Server using CustomAdaptor and bind it to the Blazor DataGrid component.

    1. To create a simple Blazor DataGrid component, the procedure is explained in the above-mentioned topic on Connecting Blazor DataGrid to an API service

    • In this Blazor Web app, set the rendermode as either InteractiveServer or InteractiveAuto as per your configuration.

    2. To connect a MySQL Server database using the MySQL driver in your application, you need to install the MySQL.Data NuGet package. To add MySQL.Data in the app, open the NuGet package manager in Visual Studio (Tools → NuGet Package Manager → Manage NuGet Packages for Solution), search and install it.

    3. If you intend to inject your own service into the CustomAdaptor and utilize it, you can achieve this as follows.

    @rendermode InteractiveServer

@using Syncfusion.Blazor.Grids
@using Syncfusion.Blazor.Data
@using Syncfusion.Blazor
@using MySql.Data.MySqlClient;

<SfGrid TValue="Order" AllowSorting="true" AllowFiltering="true" AllowGrouping="true" AllowPaging="true" Toolbar="@(new List<string>() { "Add", "Edit", "Delete", "Update", "Cancel", "Search" })">
    <SfDataManager AdaptorInstance="@typeof(CustomAdaptor)" Adaptor="Adaptors.CustomAdaptor"></SfDataManager>
    <GridEditSettings AllowEditing="true" AllowDeleting="true" AllowAdding="true" Mode="@EditMode.Normal"></GridEditSettings>
    <GridAggregates>
        <GridAggregate>
            <GridAggregateColumns>
                <GridAggregateColumn Field=@nameof(Order.Freight) Type="AggregateType.Sum" Format="C2">
                    <FooterTemplate>
                        @{
                            var aggregate = (context as AggregateTemplateContext);
                            <div>
                                <p>Sum: @aggregate.Sum</p>
                            </div>
                        }
                    </FooterTemplate>
                </GridAggregateColumn>
            </GridAggregateColumns>
        </GridAggregate>
        <GridAggregate>
            <GridAggregateColumns>
                <GridAggregateColumn Field=@nameof(Order.Freight) Type="AggregateType.Average" Format="C2">
                    <FooterTemplate>
                        @{
                            var aggregate = (context as AggregateTemplateContext);
                            <div>
                                <p>Average: @aggregate.Average</p>
                            </div>
                        }
                    </FooterTemplate>
                </GridAggregateColumn>
            </GridAggregateColumns>
        </GridAggregate>
    </GridAggregates>
    <GridColumns>
        <GridColumn Field=@nameof(Order.OrderID) HeaderText="Order ID" ValidationRules="@(new ValidationRules{ Required= true })"  IsPrimaryKey="true" TextAlign="TextAlign.Right" Width="120"></GridColumn>
        <GridColumn Field=@nameof(Order.CustomerName) HeaderText="Customer Name" ValidationRules="@(new ValidationRules{ Required= true, MinLength = 3 })" Width="150"></GridColumn>
        <GridColumn Field=@nameof(Order.EmployeeID) HeaderText="Employee ID" TextAlign="TextAlign.Right" Width="150"></GridColumn>
        <GridColumn Field=@nameof(Order.Freight) HeaderText="Freight" TextAlign="TextAlign.Right" Format="C2" Width="150"></GridColumn>
        <GridColumn Field=@nameof(Order.ShipCity) HeaderText="Ship City" Width="150"></GridColumn>
    </GridColumns>
</SfGrid>

@code {
    SfGrid<Order> Grid { get; set; }
}
    public class Order
 {
     public int? OrderID { get; set; }
     public string CustomerName { get; set; }
     public int EmployeeID { get; set; }
     public decimal Freight { get; set; }
     public string ShipCity { get; set; }
 }

    4. Within the CustomAdaptor’s ReadAsync method, fetch the data from the service by calling the GetOrdersAsync method.

    • In this GetOrdersAsync method, the MySQL Server database data is fetch by using the MySqlDataAdapter class.

    • Employ the Fill method of the DataAdapter to populate a DataSet with the results of the Select command of the DataAdapter, followed by conversion of the DataSet into a List.

    • Finally, return the response as a Result and Count pair object in the ReadAsync method to bind the data to the Blazor DataGrid component.

    @rendermode InteractiveServer

@using Syncfusion.Blazor.Grids
@using Syncfusion.Blazor.Data
@using Syncfusion.Blazor
@using MySql.Data.MySqlClient;
@using System.Collections

<SfGrid TValue="Order" AllowSorting="true" AllowFiltering="true" AllowGrouping="true" AllowPaging="true" Toolbar="@(new List<string>() { "Add", "Edit", "Delete", "Update", "Cancel", "Search" })">
    <SfDataManager AdaptorInstance="@typeof(CustomAdaptor)" Adaptor="Adaptors.CustomAdaptor"></SfDataManager>
    <GridEditSettings AllowEditing="true" AllowDeleting="true" AllowAdding="true" Mode="@EditMode.Normal"></GridEditSettings>
    <GridAggregates>
        <GridAggregate>
            <GridAggregateColumns>
                <GridAggregateColumn Field=@nameof(Order.Freight) Type="AggregateType.Sum" Format="C2">
                    <FooterTemplate>
                        @{
                            var aggregate = (context as AggregateTemplateContext);
                            <div>
                                <p>Sum: @aggregate.Sum</p>
                            </div>
                        }
                    </FooterTemplate>
                </GridAggregateColumn>
            </GridAggregateColumns>
        </GridAggregate>
        <GridAggregate>
            <GridAggregateColumns>
                <GridAggregateColumn Field=@nameof(Order.Freight) Type="AggregateType.Average" Format="C2">
                    <FooterTemplate>
                        @{
                            var aggregate = (context as AggregateTemplateContext);
                            <div>
                                <p>Average: @aggregate.Average</p>
                            </div>
                        }
                    </FooterTemplate>
                </GridAggregateColumn>
            </GridAggregateColumns>
        </GridAggregate>
    </GridAggregates>
    <GridColumns>
        <GridColumn Field=@nameof(Order.OrderID) HeaderText="Order ID" ValidationRules="@(new ValidationRules{ Required= true })"  IsPrimaryKey="true" TextAlign="TextAlign.Right" Width="120"></GridColumn>
        <GridColumn Field=@nameof(Order.CustomerName) HeaderText="Customer Name" ValidationRules="@(new ValidationRules{ Required= true, MinLength = 3 })" Width="150"></GridColumn>
        <GridColumn Field=@nameof(Order.EmployeeID) HeaderText="Employee ID" TextAlign="TextAlign.Right" Width="150"></GridColumn>
        <GridColumn Field=@nameof(Order.Freight) HeaderText="Freight" TextAlign="TextAlign.Right" Format="C2" Width="150"></GridColumn>
        <GridColumn Field=@nameof(Order.ShipCity) HeaderText="Ship City" Width="150"></GridColumn>
    </GridColumns>
</SfGrid>

@code {
    /// <summary>
    /// Implementing CustomAdaptor by extending the <see cref=“DataAdaptor”/> class.
    /// The Blazor DataGrid component support for custom data binding, which enables the binding and manipulation of data in a personalized way, using user-defined methods.
    /// </summary>
    public class CustomAdaptor : DataAdaptor
    {
        public OrderData OrderService = new OrderData();
        /// <summary>
        /// Returns the data collection after performing data operations based on request from <see cref=”DataManagerRequest”/>
        /// </summary>
        /// <param name="DataManagerRequest">DataManagerRequest contains the information regarding searching, filtering, sorting, aggregates paging and grouping which is handled on the Blazor DataGrid component side</param>
        /// <param name="Key">An optional parameter that can be used to perform additional data operations.</param>
        /// <returns>The data collection's type is determined by how this method has been implemented.</returns>
        public override async Task<object> ReadAsync(DataManagerRequest DataManagerRequest, string Key = null)
        {
            IEnumerable<Order> DataSource = await OrderService.GetOrdersAsync();
            int TotalRecordsCount = DataSource.Cast<Order>().Count();
            return DataManagerRequest.RequiresCounts ? new DataResult() { Result = DataSource, Count = TotalRecordsCount } : (object)DataSource;
        }
    }
}
    public class OrderData
{
     public async Task<List<Order>> GetOrdersAsync()
        {
            //Create query to fetch data from database
            string Query = "SELECT * FROM orders ORDER BY OrderID;";
            List<Order> Orders = null;
            //Create SQL Connection
            using (MySqlConnection Connection = new MySqlConnection(ConnectionString))
            {
                //Using MySqlDataAdapter and Query create connection with database 
                MySqlDataAdapter adapter = new MySqlDataAdapter(Query, Connection);
                DataSet data = new DataSet();
                Connection.Open();
                // Using MySqlDataAdapter, process the query string and fill the data into the dataset
                adapter.Fill(data);
                //Cast the data fetched from MySqlDataAdapter to List<T>
                Orders = data.Tables[0].AsEnumerable().Select(r => new Order
                {
                    OrderID = r.Field<int>("OrderID"),
                    CustomerName = r.Field<string>("CustomerName"),
                    EmployeeID = r.Field<int>("EmployeeID"),
                    ShipCity = r.Field<string>("ShipCity"),
                    Freight = r.Field<decimal>("Freight")
                }).ToList();
                Connection.Close();
            }
            return Orders;
        }
}
    • The DataManagerRequest encompasses details about the Blazor DataGrid component actions such as searching, filtering, sorting, aggregate, paging and grouping.
    • In the above Blazor DataGrid, AllowSearching, AllowSorting, AllowFiltering, AllowPaging, AllowGrouping and CRUD-related properties have been enabled. The details on how to handle these actions are explained below.

    When the application is executed, the Blazor DataGrid component will appear as depicted below.

    Blazor DataGrid component bound with MySQL Server data

    Handling searching operation

    When utilizing the CustomAdaptor, managing the searching operation involves overriding the Read or ReadAsync method of the DataAdaptor abstract class.

    In the code example below, searching a custom data source can be accomplished by employing the built-in PerformSearching method of the DataOperations class. Alternatively, you can implement your own method for searching operation and bind the resultant data to the Blazor DataGrid component.

  • RAZOR
    • public class CustomAdaptor : DataAdaptor
{
    public OrderData OrderService = new OrderData();
    // Performs data read operation
    public override async Task<object> ReadAsync(DataManagerRequest DataManagerRequest, string Key = null)
    {
        IEnumerable<Order> DataSource = await OrderService.GetOrdersAsync();
        // Handling Searching in CustomAdaptor.
        if (DataManagerRequest.Search != null && DataManagerRequest.Search.Count > 0)
        {
            // Searching
            DataSource = DataOperations.PerformSearching(DataSource, DataManagerRequest.Search);
            //Add custom logic here if needed and remove above method
        }
        int TotalRecordsCount = DataSource.Cast<Order>().Count();
        return DataManagerRequest.RequiresCounts ? new DataResult() { Result = DataSource, Count = TotalRecordsCount } : (object)DataSource;
    }
}

    Handling filtering operation

    When employing the CustomAdaptor, handling the filtering operation involves overriding the Read or ReadAsync method of the DataAdaptor abstract class.

    In the code example below, filtering a custom data source can be achieved by utilizing the built-in PerformFiltering method of the DataOperations class. Alternatively, you can implement your own method for filtering operation and bind the resulting data to the Blazor DataGrid component.

  • RAZOR
    • public class CustomAdaptor : DataAdaptor
{
    public OrderData OrderService = new OrderData();
    // Performs data read operation
    public override async Task<object> ReadAsync(DataManagerRequest DataManagerRequest, string Key = null)
    {
        IEnumerable<Order> DataSource = await OrderService.GetOrdersAsync();
        // Handling Filtering in CustomAdaptor.
        if (DataManagerRequest.Where != null && DataManagerRequest.Where.Count > 0)
        {
            // Filtering
            DataSource = DataOperations.PerformFiltering(DataSource, DataManagerRequest.Where, DataManagerRequest.Where[0].Operator);
            //Add custom logic here if needed and remove above method
        }
        int TotalRecordsCount = DataSource.Cast<Order>().Count();
        return DataManagerRequest.RequiresCounts ? new DataResult() { Result = DataSource, Count = TotalRecordsCount } : (object)DataSource;
    }
}

    Handling sorting operation

    When utilizing the CustomAdaptor, managing the sorting operation involves overriding the Read or ReadAsync method of the DataAdaptor abstract class.

    In the code example below, sorting a custom data source can be accomplished by employing the built-in PerformSorting method of the DataOperations class. Alternatively, you can implement your own method for sorting operation and bind the resulting data to the Blazor DataGrid component.

  • RAZOR
    • public class CustomAdaptor : DataAdaptor
{
    public OrderData OrderService = new OrderData();
    // Performs data read operation
    public override async Task<object> ReadAsync(DataManagerRequest DataManagerRequest, string Key = null)
    {
        IEnumerable<Order> DataSource = await OrderService.GetOrdersAsync();
        // Handling Sorting in CustomAdaptor.
        if (DataManagerRequest.Sorted != null && DataManagerRequest.Sorted.Count > 0)
        {
            // Sorting
            DataSource = DataOperations.PerformSorting(DataSource, DataManagerRequest.Sorted);
            //Add custom logic here if needed and remove above method
        }
        int TotalRecordsCount = DataSource.Cast<Order>().Count();
        return DataManagerRequest.RequiresCounts ? new DataResult() { Result = DataSource, Count = TotalRecordsCount } : (object)DataSource;
    }
}

    Handling aggregate operation

    When employing CustomAdaptor, the aggregate operation must be managed within the Read or ReadAsync method of the CustomAdaptor.

    The provided sample code illustrated how to implement the aggregate operation within CustomAdaptor,

  • RAZOR
    • public class CustomAdaptor : DataAdaptor
{
    public OrderData OrderService = new OrderData();
    // Performs data read operation
    public override async Task<object> ReadAsync(DataManagerRequest DataManagerRequest, string Key = null)
    {
        IEnumerable<Order> DataSource = await OrderService.GetOrdersAsync();
        int TotalRecordsCount = DataSource.Cast<Order>().Count();
        // Handling Aggregation in CustomAdaptor.
        IDictionary<string, object> Aggregates = null;
        if (DataManagerRequest.Aggregates != null) // Aggregation
        {
            Aggregates = DataUtil.PerformAggregation(DataSource, DataManagerRequest.Aggregates);
            //Add custom logic here if needed and remove above method
        }
        return DataManagerRequest.RequiresCounts ? new DataResult() { Result = DataSource, Count = TotalRecordsCount, Aggregates = Aggregates } : (object)DataSource;
    }
}

    The server-side management of the PerformAggregation method is necessary only for the Footer Template aggregation. There is no need for explicit handling of the Aggregate operation for the Group Footer template and Group Caption template.

    Handling paging operation

    When employing the CustomAdaptor, handling paging operation involves overriding the Read or ReadAsync method of the DataAdaptor abstract class.

    In the code example below, paging a custom data source can be achieved by utilizing the built-in PerformTake and PerformSkip method of the DataOperations class. Alternatively, you can use your own method for paging operation and bind the resulting data to the Blazor DataGrid component.

  • RAZOR
    • public class CustomAdaptor : DataAdaptor
{
    public OrderData OrderService = new OrderData();
    // Performs data read operation
    public override async Task<object> ReadAsync(DataManagerRequest DataManagerRequest, string Key = null)
    {
        IEnumerable<Order> DataSource = await OrderService.GetOrdersAsync();
        int TotalRecordsCount = DataSource.Cast<Order>().Count();
        // Handling paging in CustomAdaptor.
        if (DataManagerRequest.Skip != 0)
        {
            // Paging
            DataSource = DataOperations.PerformSkip(DataSource, DataManagerRequest.Skip);
            //Add custom logic here if needed and remove above method
        }
        if (DataManagerRequest.Take != 0)
        {
            // Taking
            DataSource = DataOperations.PerformTake(DataSource, DataManagerRequest.Take);
            //Add custom logic here if needed and remove above method
        }
        return DataManagerRequest.RequiresCounts ? new DataResult() { Result = DataSource, Count = TotalRecordsCount } : (object)DataSource;
    }
}

    Handling grouping operation

    When employing CustomAdaptor, the grouping operation must be managed within the Read or ReadAsync method of the CustomAdaptor.

    In the code example below, grouping a custom data source can be achieved by utilizing the Group method from the DataUtil class. Alternatively, you can use your own method for grouping operation and bind the resulting data to the Blazor DataGrid component.

  • RAZOR
    • public class CustomAdaptor : DataAdaptor
{
    public OrderData OrderService = new OrderData();
    // Performs data read operation
    public override async Task<object> ReadAsync(DataManagerRequest DataManagerRequest, string Key = null)
    {
        IEnumerable<Order> DataSource = await OrderService.GetOrdersAsync();
        int TotalRecordsCount = DataSource.Cast<Order>().Count();
        DataResult DataObject = new DataResult();
        // Handling Group operation in CustomAdaptor.
        if (DataManagerRequest.Group != null)
        {
            IEnumerable ResultData = DataSource.ToList();
            // Grouping
            foreach (var group in DataManagerRequest.Group)
            {
                ResultData = DataUtil.Group<Order>(ResultData, group, DataManagerRequest.Aggregates, 0, DataManagerRequest.GroupByFormatter);
                //Add custom logic here if needed and remove above method
            }
            DataObject.Result = ResultData;
            DataObject.Count = TotalRecordsCount;
            return DataManagerRequest.RequiresCounts ? DataObject : (object)ResultData;
        }
        return DataManagerRequest.RequiresCounts ? new DataResult() { Result = DataSource, Count = TotalRecordsCount } : (object)DataSource;
    }
}
    • For optimal performance, it is recommended to follow this sequence of operations(Searching, Filtering, Sorting, Aggregate, Paging and Grouping) in the ReadAsync method.
    • If both grouping and aggregate operations are enabled, the code provided below demonstrates how to implement these operations within the CustomAdaptor.
    public class CustomAdaptor : DataAdaptor
{
    public OrderData OrderService = new OrderData();
    // Performs data Read operation
    public override async Task<object> ReadAsync(DataManagerRequest DataManagerRequest, string Key = null)
    {
        IEnumerable<Order> DataSource = await OrderService.GetOrdersAsync();
        int TotalRecordsCount = DataSource.Cast<Order>().Count();
        DataResult DataObject = new DataResult();
        // Handling both Grouping and Aggregation in CustomAdaptor.
        if (DataManagerRequest.Aggregates != null || DataManagerRequest.Group != null) // Aggregation
        {
            if (DataManagerRequest.Group != null)
            {
                IEnumerable ResultData = DataSource.ToList();
                // Grouping
                foreach (var group in DataManagerRequest.Group)
                {
                    ResultData = DataUtil.Group<Order>(ResultData, group, DataManagerRequest.Aggregates, 0, DataManagerRequest.GroupByFormatter);
                    //Add custom logic here if needed and remove above method
                }
                DataObject.Result = ResultData;
            }
            else
            {
                DataObject.Result = DataSource;
            }
            DataObject.Count = TotalRecordsCount;
            DataObject.Aggregates = DataUtil.PerformAggregation(DataSource, DataManagerRequest.Aggregates);

            return DataManagerRequest.RequiresCounts ? DataObject : (object)DataSource;
        }
        return DataManagerRequest.RequiresCounts ? new DataResult() { Result = DataSource, Count = TotalRecordsCount } : (object)DataSource;
    }
}

    Handling CRUD operations

    To enable editing in the Blazor DataGrid component, utilize the GridEditSettings component. The Blazor DataGrid component offers multiple edit modes including the Inline/Normal, Dialog and Batch editing. For more details, refer to the Blazor DataGrid component editing documentation.

    In this scenario, the inline edit Mode and Toolbar property configured to display toolbar items for editing purpose.

  • RAZOR
    • <SfGrid TValue="Order" AllowSorting="true" AllowFiltering="true" AllowGrouping="true" AllowPaging="true" Toolbar="@(new List<string>() { "Add", "Edit", "Delete", "Update", "Cancel", "Search" })">
    <SfDataManager AdaptorInstance="@typeof(CustomAdaptor)" Adaptor="Adaptors.CustomAdaptor"></SfDataManager>
    <GridEditSettings AllowEditing="true" AllowDeleting="true" AllowAdding="true" Mode="@EditMode.Normal"></GridEditSettings>
    <GridColumns>
        <GridColumn Field=@nameof(Order.OrderID) HeaderText="Order ID" IsIdentity="true" ValidationRules="@(new ValidationRules{ Required= true })" IsPrimaryKey="true" TextAlign="TextAlign.Right" Width="120"></GridColumn>
        <GridColumn Field=@nameof(Order.CustomerName) HeaderText="Customer Name" ValidationRules="@(new ValidationRules{ Required= true, MinLength = 3 })" Width="150"></GridColumn>
        <GridColumn Field=@nameof(Order.EmployeeID) HeaderText="Employee ID" TextAlign="TextAlign.Right" Width="150"></GridColumn>
        <GridColumn Field=@nameof(Order.Freight) HeaderText="Freight" TextAlign="TextAlign.Right" Format="C2" Width="150"></GridColumn>
        <GridColumn Field=@nameof(Order.ShipCity) HeaderText="Ship City" Width="150"></GridColumn>
    </GridColumns>
</SfGrid>
    • Inline/Normal editing is the default edit Mode for the Blazor DataGrid component. To enable CRUD operations, ensure that the IsPrimaryKey property is set to true for a specific GridColumn, ensuring that its value is unique.
    • If database has an Autogenerated column, ensure to define IsIdentity property of GridColumn to disable them during adding or editing operations.

    The CRUD operations can be performed and customized on our own by overriding the following CRUD methods of the DataAdaptor abstract class.

    • Insert/InsertAsync
    • Remove/RemoveAsync
    • Update/UpdateAsync
    • BatchUpdate/BatchUpdateAsync

    Let’s see how to perform CRUD operation using MySQL Server data with Syncfusion Blazor DataGrid component.

    Insert Operation:

    To execute the insert operation, you will need to override the Insert or InsertAsync method of the CustomAdaptor. Then, integrate the following code snippet into the CustomAdaptor class. The below code snippet demonstrated how to handle the insertion of new records within the InsertAsync method of CustomAdaptor component. Modify the logic within this method according to the requirements of your application.

    /// <summary>
/// Inserts a new data item into the data collection.
/// </summary>
/// <param name="DataManager">The DataManager is a data management component used for performing data operations in application.</param>
/// <param name="Value">The new record which is need to be inserted.</param>
/// <param name="Key">An optional parameter that can be used to perform additional data operations.</param>
/// <returns>Returns the newly inserted record details.</returns>
public override async Task<object> InsertAsync(DataManager DataManager, object Value, string Key)
{
    // Add your insert logic here
    // This method will be invoked when inserting new records into the Blazor DataGrid component.
    await OrderService.AddOrderAsync(Value as Order);
    return Value;
}
    public async Task AddOrderAsync(Order Value)
        {
            //Create query to insert the specific into the database by accessing its properties 
            string Query = $"Insert into Orders(OrderID,CustomerName,Freight,ShipCity,EmployeeID) values('{(Value as Order).OrderID}','{(Value as Order).CustomerName}','{(Value as Order).Freight}','{(Value as Order).ShipCity}','{(Value as Order).EmployeeID}')";
            MySqlConnection Connection = new MySqlConnection(ConnectionString);
            Connection.Open();
            //Execute the MySQL Command
            MySqlCommand Command = new MySqlCommand(Query, Connection);
            //Execute this code to reflect the changes into the database
            Command.ExecuteNonQuery();
            Connection.Close();
        }

    Update Operation:

    To execute the update operation, override the Update or UpdateAsync method of the CustomAdaptor. Then, integrate the following code snippet into the CustomAdaptor class. The below code snippet demonstrated how to handle the updating of existing records within the UpdateAsync method of the CustomAdaptor component. Modify the logic within this method according to the requirements of your application.

    /// <summary>
/// Updates an existing data item in the data collection.
/// </summary>
/// <param name="DataManager">The DataManager is a data management component used for performing data operations in application.</param>
/// <param name="Value">The modified record which is need to be updated.</param>
/// <param name="KeyField">The primary column name specifies the field name of the primary column.</param>
/// <param name="Key">An optional parameter that can be used to perform additional data operations.</param>
/// <returns>Returns the updated data item.</returns>
public override async Task<object> UpdateAsync(DataManager DataManager, object Value, string KeyField, string Key)
{
    // Add your update logic here
    // This method will be invoked when updating existing records in the Blazor DataGrid component.
    await OrderService.UpdateOrderAsync(Value as Order);
    return Value;
}
    public async Task UpdateOrderAsync(Order Value)
{
    //Create query to update the changes into the database by accessing its properties
    string Query = $"Update Orders set CustomerName='{(Value as Order).CustomerName}', Freight='{(Value as Order).Freight}',EmployeeID='{(Value as Order).EmployeeID}',ShipCity='{(Value as Order).ShipCity}' where OrderID='{(Value as Order).OrderID}'";
    MySqlConnection Connection = new MySqlConnection(ConnectionString);
    Connection.Open();
    //Execute the MySQL Command
    MySqlCommand Command = new MySqlCommand(Query, Connection);
    //Execute this code to reflect the changes into the database
    Command.ExecuteNonQuery();
    Connection.Close();
}

    Delete Operation:

    To perform the delete operation, you need to override the Remove or RemoveAsync method of the CustomAdaptor. Below is the code snippet that you can add to CustomAdaptor class. The below code snippet demonstrated how to handle the deletion of existing records within the RemoveAsync method of CustomAdaptor component. Modify the logic within this method according to the requirements of your application.

    /// <summary>
/// Removes a data item from the data collection.
/// </summary>
/// <param name="DataManager">The DataManager is a data management component used for performing data operations in application.</param>
/// <param name="Value">The Value specifies the primary column value which is needs to be removed from the grid record.</param>
/// <param name="KeyField">The KeyField specifies the field name of the primary column.</param>
/// <param name="Key">An optional parameter that can be used to perform additional data operations.</param>
/// <returns>Returns the removed data item.</returns>
public override async Task<object> RemoveAsync(DataManager DataManager, object Value, string KeyField, string Key)
{
    // Add your delete logic here
    // This method will be invoked when deleting existing records from the Blazor DataGrid component.
    await OrderService.RemoveOrderAsync(Value as int?);
    return Value;
}
    public async Task RemoveOrderAsync(int? Key)
{
    //Create query to remove the specific from database by passing the primary key column value.
    string Query = $"Delete from Orders where OrderID={Key}";
    MySqlConnection Connection = new MySqlConnection(ConnectionString);
    Connection.Open();
    //Execute the MySQL Command
    MySqlCommand Command = new MySqlCommand(Query, Connection);
    //Execute this code to reflect the changes into the database
    Command.ExecuteNonQuery();
    Connection.Close();
}

    Batch Operation:

    To perform the batch operation, override the BatchUpdate or BatchUpdateAsync method of the CustomAdaptor and add the following code in the CustomAdaptor. The below code snippet demonstrated how to handle the batch update request within the BatchUpdateAsync method of CustomAdaptor component. Modify the logic within this method according to the requirements of your application.

  • RAZOR
    • /// <summary>
/// /// Batchupdate (Insert, Update, Delete) a collection of data items from the data collection.
/// </summary>
/// <param name="DataManager">The DataManager is a data management component used for performing data operations in application.</param>
/// <param name="Changed">The Changed specifies the collection of record updated in batch mode which needs to be updated from the grid record.</param>
/// <param name="Added">The Added specifies the collection of record inserted in batch mode which needs to be inserted from the grid record.</param>
/// <param name="Deleted">The Deleted specifies the collection of record deleted in batch mode which needs to be removed from the grid record.</param>
/// <param name="KeyField">The KeyField specifies the field name of the primary column.</param>
/// <param name="Key">An optional parameter that can be used to perform additional data operations.</param>
/// <param name="DropIndex">An optional parameter that can be used to perform row drag and drop operation.</param>
/// <returns>Returns the removed data item.</returns>
public override async Task<object> BatchUpdateAsync(DataManager DataManager, object Changed, object Added, object Deleted, string KeyField, string Key, int? DropIndex)
{
    if (Changed != null)
    {
        foreach (var record in (IEnumerable<Order>)Changed)
        {
            await OrderService.UpdateOrderAsync(record as Order);
        }
    }
    if (Added != null)
    {
        foreach (var record in (IEnumerable<Order>)Added)
        {
            await OrderService.AddOrderAsync(record as Order);
        }
    }
    if (Deleted != null)
    {
        foreach (var record in (IEnumerable<Order>)Deleted)
        {
            await OrderService.RemoveOrderAsync((record as Order).OrderID);
        }
    }
    return Key;
}

    Blazor DataGrid component bound with MySQL Server data

    You can find the sample in this GitHub location.