Connecting MySQL Server data in to Blazor DataGrid Component

2 Dec 202524 minutes to read

The Syncfusion^® Blazor DataGrid component supports binding data from a MySQL Server database using multiple approaches. Common methods include:

Using the DataSource property for local binding.
Implementing a CustomAdaptor for custom logic.
Configuring remote data binding through adaptors such as UrlAdaptor.

Using UrlAdaptor

The UrlAdaptor enables communication between the DataGrid and a remote API service connected to MySQL Server. This approach is suitable when the API implements custom logic for data operations and returns results in the result and count format.

Using CustomAdaptor

The CustomAdaptor provides full control over data operations and CRUD functionality. It allows implementing custom logic for searching, filtering, sorting, paging, and grouping directly in the server-side code.

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

Create an ASP.NET Core Web API Project

In Visual Studio, create a new ASP.NET Core Web API project named MyWebService.

Refer to Microsoft documentation for detailed steps.

Install SQL Client Package

Add the MySQL.Data NuGet package to the project using NuGet Package Manager (Tools → NuGet Package Manager → Manage NuGet Packages for Solution).

Add API Controller

Create a controller named GridController.cs under the Controllers folder.

Fetch Data from SQL Server

Use SqlConnection and SqlDataAdapter to retrieve data and convert it into a collection of Order objects:

GridController.cs
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;
        }
    }
}

Run and test the application

Start the API and access https://localhost:xxxx/api/Grid to view the data.

Hosted API URL

Connecting Blazor DataGrid to an API service

This section explains how to retrieve data from a MySQL Server database using UrlAdaptor and bind it to the Syncfusion^® Blazor DataGrid component.

Prerequisites

System requirements for Blazor components

Create a Blazor Web App

Create a Blazor Web App using Visual Studio 2022. Use Microsoft Templates or the Syncfusion^® Blazor Extension.

Configure the Interactive render mode and Interactivity location during project creation.

Install Syncfusion Packages

Open the NuGet Package Manager in Visual Studio (Tools → NuGet Package Manager → Manage NuGet Packages for Solution). Search and install the following packages:
- Syncfusion.Blazor.Grid
- Syncfusion.Blazor.Themes
Alternatively, use the Package Manager Console:

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

When using WebAssembly or Auto render modes in a Blazor Web App, install Syncfusion^® Blazor component NuGet packages within the client project.

Syncfusion^® Blazor components are available on nuget.org. Refer to the NuGet packages topic for a complete list of available packages.

Register Syncfusion Blazor service

Add the required namespaces in ~/_Imports.razor:

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

For apps using WebAssembly or Auto (Server and WebAssembly) render modes, register the service in both ~/Program.cs files.

using Syncfusion.Blazor;

builder.Services.AddSyncfusionBlazor();

Add stylesheet and script resources

Access the theme stylesheet and script from NuGet using Static Web Assets. Include the stylesheet reference in the <head> section and the script reference at the end of the <body> in ~/Components/App.razor:

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

Refer to Blazor Themes for additional methods such as Static Web Assets, CDN, and CRG.

Set the render mode to InteractiveServer or InteractiveAuto in the Blazor Web App configuration.

Configure DataGrid with UrlAdaptor

The SfDataManager component supports multiple adaptors for remote data binding. For API services, set the Adaptor property to Adaptors.UrlAdaptor and specify the service endpoint in the Url property.

Index.razor
GridController.cs
@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};
    }
}

Handling data operations in UrlAdaptor

The Syncfusion^® Blazor DataGrid supports server-side operations such as searching, sorting, filtering, aggregating, and paging when using the UrlAdaptor.

The DataManagerRequest object provides details for each operation, and these can be applied using built-in methods from the DataOperations class:

PerformSearching -Applies search criteria to the data source based on search filters.
PerformFiltering - Filters the data source using conditions specified in the request.
PerformSorting - Sorts the data source according to one or more sort descriptors.
PerformTake - Retrieves a specified number of records for paging.
PerformSkip - Skips a defined number of records before returning results.

These methods enable efficient handling of large datasets by performing operations on the server side. The following sections demonstrate how to manage these operations using the UrlAdaptor.

To enable these operations, add the Syncfusion.Blazor.Data package to the API service project using NuGet Package Manager in Visual Studio (Tools → NuGet Package Manager → Manage NuGet Packages for Solution).

Handling searching operation

Enable server-side searching by implementing logic in the API controller with the PerformSearching method from the DataOperations class. This method applies search criteria to the collection based on filters specified in the incoming DataManagerRequest.

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

Enable server-side filtering by implementing logic in the API controller using the PerformFiltering method from the DataOperations class. This method applies filter conditions to the collection based on the criteria specified in the incoming DataManagerRequest.

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

Enable server-side sorting by implementing logic in the API controller using the PerformSorting method from the DataOperations class. This method sorts the collection based on one or more sort descriptors specified in the incoming DataManagerRequest.

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

Enable server-side aggregation by implementing logic in the API controller using the PerformAggregation method from the DataUtil class. This method calculates aggregate values such as Sum, Average, Min, and Max for the specified fields based on the incoming DataManagerRequest.

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 implementation of the PerformAggregation method is required only for Footer aggregates. Explicit handling is not necessary for Group Footer aggregates or Group Caption aggregates.

Handling paging operation

Enable server-side paging by implementing logic in the API controller using the PerformSkip and PerformTake methods from the DataOperations class. These methods apply paging based on the Skip and Take values provided in the incoming DataManagerRequest.

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

NOTE

For optimal performance, apply operations in the following sequence: Searching → Filtering → Sorting → Aggregation → Paging → Grouping in ReadAsync method.

Handling CRUD operations

The Syncfusion^® Blazor DataGrid supports Create, Read, Update, and Delete (CRUD) operations through the SfDataManager component. These operations are mapped to API endpoints using properties such as:

InsertUrl – API endpoint for inserting new records.
UpdateUrl – API endpoint for updating existing records.
RemoveUrl – API endpoint for deleting records.
CrudUrl – Single endpoint for all CRUD operations.
BatchUrl – API endpoint for batch editing.

To enable editing, configure the Toolbar and GridEditSettings properties, and set the Mode property to EditMode.Normal to allow adding, editing, and deleting records.

RAZOR
<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 Mode for the Blazor DataGrid component.

To enable CRUD operations, set the IsPrimaryKey property to true for a column that contains unique values.

If the database includes an auto-generated column, set the IsIdentity property for that column to disable editing during add or update operations.

Insert Operation:

To insert a new record, click the Add toolbar button. This action displays the edit form for entering the new record details. After providing the required values, click the Update toolbar button. The record will be added to the Orders table by invoking the following POST API method.

OrdersController.cs
[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, select the required row and click the Edit button in the toolbar. An edit form will appear, allowing changes to any column value. After making the changes, click the Update button in the toolbar to save the record in the Orders table. This action triggers the following POST method in the API.

OrdersController.cs
[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 remove a record, select the desired row and click the Delete toolbar button. This action sends a DELETE request to the configured API endpoint, passing the primary key of the selected record. The corresponding entry will be deleted from the Orders table by invoking the following POST API method.

OrdersController.cs
[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 updates, set the edit Mode to Batch in the GridEditSettings component and configure the BatchUrl property in the DataManager.
In batch mode:

Use the Add toolbar button to insert new rows.
Double-click a cell to edit its value.
Select a row and click Delete to remove it.
Click Update to commit all changes (insert, update, delete) in a single request 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
        }
    }
}

Blazor DataGrid component bound with MySQL Server data

Find the complete implementation in this GitHub repository.

Binding data from MySQL Server using CustomAdaptor

This section explains how to retrieve data from a Microsoft SQL Server database using CustomAdaptor and bind it to the Blazor DataGrid component.

Step 1: Create the Blazor DataGrid Component

Follow the procedure described in Connecting Blazor DataGrid to an API service.

Set the rendermode to InteractiveServer or InteractiveAuto based on application configuration.

Step 2: Install MySQL NuGet Package

Install the MySql.Data package to connect to MySQL Server.

In Visual Studio, open (Tools → NuGet Package Manager → Manage NuGet Packages) for Solution, search for MySql.Data, and install it.

Step 3: Configure the DataGrid with CustomAdaptor

Inject a custom service into the CustomAdaptor and configure the component as shown below:

Index.razor
Orderdata.cs
@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; }
 }

Step 4: Implement Data Retrieval Logic

Implement the ReadAsync method in CustomAdaptor to fetch data from the service by calling GetOrdersAsync.

Use SqlDataAdapter to retrieve data from Microsoft SQL Server.
Populate a DataSet using the Fill method and convert it into a List.
Return the response as a Result and Count pair in the ReadAsync method to bind data to the Blazor DataGrid.

Index.razor
OrderData.cs
@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;
        }
}

Blazor DataGrid component bound with MySQL Server data

Handling data operations in a Custom Adaptor

The Syncfusion^® Blazor DataGrid supports server-side operations such as searching, filtering, sorting, paging, and aggregating when using a CustomAdaptor. These operations are implemented by overriding the Read or ReadAsync method of the DataAdaptor abstract class.

The DataManagerRequest object provides the necessary details for each operation, and these can be applied using built-in methods from the DataOperations and DataUtil classes:

PerformSearching – Applies search criteria to the data source based on search filters.
PerformFiltering – Filters the data source using conditions specified in the request.
PerformSorting – Sorts the data source according to one or more sort descriptors.
PerformSkip – Retrieves a specified number of records for paging.
PerformTake – Skips a defined number of records before returning results.
PerformAggregation – Applies aggregate details to calculate summary values such as Sum, Average, Min, and Max.

These methods enable efficient server-side data handling in a custom adaptor implementation for MySQL Server.

NOTE

To enable these operations, install the Syncfusion.Blazor.Data package using NuGet Package Manager in Visual Studio:

(Tools → NuGet Package Manager → Manage NuGet Packages for Solution).

Handling searching operation

When using CustomAdaptor, the searching operation is implemented by overriding the Read or ReadAsync method of the DataAdaptor abstract class.

The built-in PerformSearching method of the DataOperations class applies search criteria from the DataManagerRequest to the data source. Custom logic can also be implemented to handle searching as required.

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 implementing CustomAdaptor, the filtering operation is managed by overriding theRead or ReadAsync method of the DataAdaptor abstract class.

The built-in PerformFiltering method in the DataOperations class applies filter criteria from the DataManagerRequest to the data collection. Custom filtering logic can also be implemented to meet specific requirements.

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 implementing CustomAdaptor, the sorting operation is handled by overriding the Read or ReadAsync method of the DataAdaptor abstract class.

The built-in PerformSorting method in the DataOperations class applies sort criteria from the DataManagerRequest to the data collection. Custom sorting logic can also be implemented to meet specific requirements.

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 implementing CustomAdaptor, aggregate operations are managed by overriding the Read or ReadAsync method of the DataAdaptor abstract class.

The built-in PerformAggregation method in the DataUtil class calculates aggregate values based on the criteria specified in the DataManagerRequest. Custom aggregation logic can also be implemented when specific requirements exist.

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 implementation of the PerformAggregation method is required only for Footer aggregates. Explicit handling is not necessary for Group Footer aggregates or Group Caption aggregates.

Handling paging operation

When implementing CustomAdaptor, paging is managed by overriding the Read or ReadAsync method of the DataAdaptor abstract class.

The built-in PerformSkip and PerformTake methods in the DataOperations class apply paging criteria from the DataManagerRequest to the data collection. Custom paging logic can also be implemented when specific requirements exist.

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 implementing CustomAdaptor, grouping is managed by overriding the Read or ReadAsync method of the DataAdaptor abstract class.

The built-in Group method in the DataUtil class applies grouping logic based on the configuration in the DataManagerRequest. Custom grouping logic can also be implemented when specific requirements exist.

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

NOTE

For optimal performance, apply operations in the following sequence: Searching → Filtering → Sorting → Aggregation → Paging → Grouping in ReadAsync method.

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

The Syncfusion^® Blazor DataGrid component supports Create, Read, Update, and Delete (CRUD) operations through the GridEditSettings configuration. Multiple edit modes are available, including Inline, Dialog, and Batch editing. For details, refer to the Editing documentation.

When using CustomAdaptor, CRUD operations are implemented by overriding the following methods of the DataAdaptor class:

Insert / InsertAsync – Handles record insertion.
Update / UpdateAsync – Handles record updates.
Remove / RemoveAsync – Handles record deletion.
BatchUpdate / BatchUpdateAsync – Handles batch operations (insert, update, delete).

Each method can be customized to execute SQL commands against the Microsoft SQL Server database.

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>

Normal(Inline) editing is the default Mode for the Blazor DataGrid component.

To enable CRUD operations, set the IsPrimaryKey property to true for a column that contains unique values.

If the database includes an auto-generated column, set the IsIdentity property for that column to disable editing during add or update operations.

Insert Operation:

To implement record insertion, override the Insert or InsertAsync method in the CustomAdaptor class.

Index.razor
Orderdata.cs
/// <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 implement record updates, override the Update or UpdateAsync method in the CustomAdaptor class.

Index.razor
Orderdata.cs
/// <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 record deletion, override the Remove or RemoveAsync method in the CustomAdaptor class.

Index.razor
Orderdata.cs
/// <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 implement batch updates such as insert, update, and delete in a single request, override the BatchUpdate or BatchUpdateAsync method in the CustomAdaptor class.

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

A complete sample implementation is available in the GitHub repository.

Search docs

Ask HelpBot

Search docs

Ask HelpBot

Connecting MySQL Server data in to Blazor DataGrid Component

Binding data from MySQL Server using an API service

Creating an API service

Connecting Blazor DataGrid to an API service

Handling data operations in UrlAdaptor

Handling searching operation

Handling filtering operation

Handling sorting operation

Handling aggregate operation

Handling paging operation

Handling CRUD operations

Binding data from MySQL Server using CustomAdaptor

Handling data operations in a Custom Adaptor

Handling searching operation

Handling filtering operation

Handling sorting operation

Handling aggregate operation

Handling paging operation

Handling grouping operation

Handling CRUD operations