Connecting Microsoft SQL Server data to Blazor DataGrid

2 Dec 202524 minutes to read

The Syncfusion^® Blazor DataGrid component supports multiple approaches for binding data from Microsoft SQL Server:

• Configure the DataSource property for local data binding.
• Implement a CustomAdaptor for custom server-side logic.
• Use remote data binding with adaptors such as UrlAdaptor.

This guide explains two primary approaches for integrating Microsoft SQL Server data with the Blazor DataGrid:

Using UrlAdaptor: Enables communication between the DataGrid and a remote API service connected to SQL 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: Provides full control over data operations and CRUD functionality. It allows implementing custom logic for searching, filtering, sorting, paging, and grouping directly in server-side code.

Both approaches support CRUD operations and can be customized to meet application-specific requirements.

Microsoft SQL Server Overview

Microsoft SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is designed to store, retrieve, and manage data efficiently for enterprise applications. SQL Server uses Structured Query Language (SQL) for querying and manipulating data.

Key Features

• Relational Database Model: Organizes data into tables with rows and columns.
• T-SQL Support: Provides Transact-SQL for advanced querying and procedural programming.
• High Availability: Features like Always On Availability Groups for failover and disaster recovery.
• Security: Includes encryption, authentication, and role-based access control.
• Integration: Works with .NET applications, Azure services, and supports REST APIs.
• Scalability: Handles large datasets and supports both on-premises and cloud deployments.

For more details, refer to the official Microsoft documentation.

Binding data from Microsoft SQL Server using an API service

Data from Microsoft SQL Server can be retrieved through an ASP.NET Core Web API and bound to the Syncfusion^® Blazor DataGrid using UrlAdaptor. This approach enables server-side operations such as paging, sorting, and filtering.

Creating an API service

Step 1: Create an ASP.NET Core Web API Project

Create a new ASP.NET Core Web API project in Visual Studio. Refer to Microsoft documentation for detailed instructions.

Step 2: Install SQL Client Package

• Install the System.Data.SqlClient package using NuGet Package Manager:

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

• Alternatively, use the Package Manager Console:

Install-Package System.Data.SqlClient

Step 3: Add API Controller

Create a controller named GridController.cs under the Controllers folder. Implement logic to fetch data from SQL Server and return it as a collection of Order objects.

GridController.cs

using Microsoft.AspNetCore.Mvc;
using System.Data;
using System.Data.SqlClient;
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? CustomerID { 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 dbo.Orders ORDER BY OrderID;";
            SqlConnection sqlConnection = new(ConnectionString);
            sqlConnection.Open();
            //Initialize the SqlCommand
            SqlCommand SqlCommand = new(QueryStr, sqlConnection);
            //Initialize the SqlDataAdapter
            SqlDataAdapter DataAdapter = new(SqlCommand);
            DataTable DataTable = new();
            // Using SqlDataAdapter, process the query string and fill the data into the dataset
            DataAdapter.Fill(DataTable);
            sqlConnection.Close();
            //Cast the data fetched from SqlDataAdapter to List<T>
            var DataSource = (from DataRow Data in DataTable.Rows
                              select new Order()
                              {
                                  OrderID = Convert.ToInt32(Data["OrderID"]),
                                  CustomerID = Data["CustomerID"].ToString(),
                                  EmployeeID = Convert.IsDBNull(Data["EmployeeID"]) ? 0 : Convert.ToUInt16(Data["EmployeeID"]),
                                  ShipCity = Data["ShipCity"].ToString(),
                                  Freight = Convert.ToDecimal(Data["Freight"])
                              }).ToList();
            return DataSource;
        }
    }
}

Step 4: Run and test the API

Start the API and verify the endpoint (e.g., https://localhost:xxxx/api/Grid) returns data.

Connecting Blazor DataGrid to an API service

After creating and testing the API, configure the Syncfusion^® Blazor DataGrid to bind data from the API endpoint using UrlAdaptor.

Prerequisites

• System requirements for Blazor components

Step 1: Create a Blazor Web App

Create a Blazor Web App in Visual Studio 2022 using Microsoft templates or the Syncfusion^® Blazor Extension.
Configure:

• Interactive render mode
• Interactivity location

Step 2: Install Syncfusion Packages

1. Open NuGet Package Manager in Visual Studio:

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

2. Search and install the following packages:

   • Syncfusion.Blazor.Grid
   • Syncfusion.Blazor.Themes

3. For projects using WebAssembly or Auto interactive render modes, ensure these packages are installed in the Client project.

4. Alternatively, use the Package Manager Console:

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

NOTE

Syncfusion^® Blazor components are available on nuget.org. For a complete list of packages, refer to NuGet packages.

Step 3: Register Syncfusion Blazor service

1. Add the required namespaces in ~/_Imports.razor:

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

1. 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();

Step 4: Add stylesheet and script resources

Include theme and script references in 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>

NOTE

• Refer to Blazor Themes for various methods to reference themes in a Blazor application:

* [Static Web Assets](https://blazor.syncfusion.com/documentation/appearance/themes#static-web-assets)
* [CDN](https://blazor.syncfusion.com/documentation/appearance/themes#cdn-reference)
* [Custom Resource Generator (CRG)](https://blazor.syncfusion.com/documentation/common/custom-resource-generator)

• For script reference options, see Adding Script References.
• Set the render mode to InteractiveServer or InteractiveAuto in the Blazor Web App configuration.

Step 5: Configure DataGrid with UrlAdaptor

Use DataManager to connect the DataGrid to the API endpoint and set the Adaptor property to Adaptors.UrlAdaptor.

Index.razor

@using Syncfusion.Blazor.Grids
@using Syncfusion.Blazor.Data
@using Syncfusion.Blazor
@using Microsoft.Data.SqlClient;

<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" 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" IsIdentity="true" ValidationRules="@(new ValidationRules{ Required= true })" IsPrimaryKey="true" TextAlign="TextAlign.Right" Width="120"></GridColumn>
        <GridColumn Field=@nameof(Order.CustomerID) 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 CustomerID { get; set; }
        public int EmployeeID { get; set; }
        public decimal Freight { get; set; }
        public string ShipCity { get; set; }
    }
}

GridController.cs

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

Perform data operations in UrlAdaptor

When UrlAdaptor is used, the Syncfusion^® Blazor DataGrid sends operation details to the API through a DataManagerRequest object. These details can be applied to the data source using methods from the DataOperations class.

Common Methods in DataOperations

• PerformSearching - Applies search criteria to the collection.
• PerformFiltering - Filters data based on conditions.
• PerformSorting - Sorts data by one or more fields.
• PerformSkip - Skips a defined number of records for paging.
• PerformTake - Retrieves a specified number of records for paging.
• PerformAggregation – Calculates aggregate values such as Sum, Average, Min, and Max.

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

When using UrlAdaptor, server-side searching can be implemented by applying the PerformSearching method from the DataOperations class. This method applies search criteria from the DataManagerRequest object to the data collection.

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

Key Points

• request.Search contains search descriptors sent from the DataGrid.
• PerformSearching applies these descriptors to the collection.
• Custom logic can be added if required before or after applying the built-in method.

Handling filtering operation

When using UrlAdaptor, server-side filtering can be implemented by applying the PerformFiltering method from the DataOperations class. This method applies filter conditions from the DataManagerRequest object to the data collection.

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

Key Points

• request.Where contains filter descriptors sent from the DataGrid.
• PerformFiltering applies these descriptors to the collection.
• The Operator property determines how conditions are combined (e.g., AND, OR).
• Custom logic can be added before or after applying the built-in method.

Handling sorting operation

When using UrlAdaptor, server-side sorting can be implemented by applying the PerformSorting method from the DataOperations class. This method applies sort descriptors from the DataManagerRequest object to the data collection.

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

Key Points

• request.Sorted contains sort descriptors sent from the DataGrid.
• PerformSorting applies these descriptors to the collection.
• Supports multiple sort fields and directions (ascending or descending).
• Custom sorting logic can be added before or after applying the built-in method.

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

When using UrlAdaptor, server-side aggregation can be implemented by applying the PerformAggregation method from the DataUtil class. This method calculates aggregate values such as Sum, Average, Min, and Max based on the configuration in the DataManagerRequest object.

RAZOR

[HttpPost]
[Route("api/[controller]")]
public object Post([FromBody] DataManagerRequest request)
{
    IEnumerable<Order> dataSource = GetOrderData();
    int count = dataSource.Count();

    // Apply aggregation
    IDictionary<string, object> aggregates = null;
    if (request.Aggregates != null)
    {
        aggregates = DataUtil.PerformAggregation(dataSource, request.Aggregates);
    }

    return new { result = dataSource, count, aggregates };
}

Key Points

• request.Aggregates contains aggregate descriptors sent from the DataGrid.
• PerformAggregation calculates summary values for specified fields.
• The server-side implementation of the PerformAggregation method is required only for Footer aggregates.
• Group Footer aggregates and Group Caption aggregates are calculated automatically by the DataGrid and do not require explicit handling.

Handling paging operation

When using UrlAdaptor, server-side paging can be implemented by applying the PerformSkip and PerformTake methods from the DataOperations class. These methods apply paging details from the DataManagerRequest object to the data collection.

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

Key Points

• request.Skip specifies the number of records to skip.
• request.Take specifies the number of records to retrieve.
• PerformSkip and PerformTake enable efficient server-side paging.
• Custom paging logic can be added before or after applying these methods.

Best Practice:
For optimal performance, apply operations in the following sequence on the server side:
Searching → Filtering → Sorting → Aggregation → Paging

Handling CRUD operations Using UrlAdaptor

The Syncfusion^® Blazor DataGrid supports Create, Read, Update, and Delete (CRUD) operations through the DataManager component. API endpoints for these operations are mapped 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. Set the Mode property to EditMode.Normal for inline editing.

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:7033/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.CustomerID) 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>

• Set IsPrimaryKey to true for a column that contains unique values.
• If the database includes an auto-generated column, set IsIdentity for that column to disable editing during add or update operations.

Insert Operation:

To insert a new record, click Add in the toolbar. After entering the required values, click Update. This action inserts the record into the Orders table by calling a 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(CustomerID,Freight,ShipCity,EmployeeID) values('{Value.Value.CustomerID}','{Value.Value.Freight}','{Value.Value.ShipCity}','{Value.Value.EmployeeID}')";
    SqlConnection SqlConnection = new SqlConnection(ConnectionString);
    SqlConnection.Open();
    SqlCommand SqlCommand = new SqlCommand(Query, SqlConnection);
    //Execute this code to reflect the changes into the database
    SqlCommand.ExecuteNonQuery();
    SqlConnection.Close();
    //Add custom logic here if needed and remove above method
}

Update Operation:

To update an existing record, select the row and click Edit in the toolbar. Modify the required values in the edit form, then click Update. This action updates the record in the Orders table by calling a POST API method:

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 CustomerID='{Value.Value.CustomerID}', Freight='{Value.Value.Freight}',EmployeeID='{Value.Value.EmployeeID}',ShipCity='{Value.Value.ShipCity}' where OrderID='{Value.Value.OrderID}'";
    SqlConnection SqlConnection = new SqlConnection(ConnectionString);
    SqlConnection.Open();
    //Execute the SQL Command
    SqlCommand SqlCommand = new SqlCommand(Query, SqlConnection);
    //Execute this code to reflect the changes into the database
    SqlCommand.ExecuteNonQuery();
    SqlConnection.Close();
    //Add custom logic here if needed and remove above method
}

Delete Operation:

To delete a record, select the row and click Delete in the toolbar. This action removes the record from the Orders table by calling a 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}";
    SqlConnection SqlConnection = new SqlConnection(ConnectionString);
    SqlConnection.Open();
    //Execute the SQL Command
    SqlCommand SqlCommand = new SqlCommand(Query, SqlConnection);
    //Execute this code to reflect the changes into the database
    SqlCommand.ExecuteNonQuery();
    SqlConnection.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 CustomerID='{Record.CustomerID}', Freight='{Record.Freight}',EmployeeID='{Record.EmployeeID}',ShipCity='{Record.ShipCity}' where OrderID='{Record.OrderID}'";
            SqlConnection SqlConnection = new SqlConnection(ConnectionString);
            SqlConnection.Open();
            //Execute the SQL Command
            SqlCommand SqlCommand = new SqlCommand(Query, SqlConnection);
            //Execute this code to reflect the changes into the database
            SqlCommand.ExecuteNonQuery();
            SqlConnection.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(CustomerID,Freight,ShipCity,EmployeeID) values('{Record.CustomerID}','{Record.Freight}','{Record.ShipCity}','{Record.EmployeeID}')";
            SqlConnection SqlConnection = new SqlConnection(ConnectionString);
            SqlConnection.Open();
            //Execute the SQL Command
            SqlCommand SqlCommand = new SqlCommand(Query, SqlConnection);
            //Execute this code to reflect the changes into the database
            SqlCommand.ExecuteNonQuery();
            SqlConnection.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}";
            SqlConnection SqlConnection = new SqlConnection(ConnectionString);
            SqlConnection.Open();
            //Execute the SQL Command
            SqlCommand SqlCommand = new SqlCommand(Query, SqlConnection);
            //Execute this code to reflect the changes into the database
            SqlCommand.ExecuteNonQuery();
            SqlConnection.Close();
            //Add custom logic here if needed and remove above method
        }
    }
}

Find the complete implementation in this GitHub repository.

Binding data from Microsoft SQL Server using CustomAdaptor

The Syncfusion^® Blazor DataGrid can bind data from a Microsoft SQL Server database using a CustomAdaptor for scenarios that require full control over data operations. This approach allows implementing custom logic for fetching, updating, and managing data directly within the application without relying on API endpoints.

Steps to Configure CustomAdaptor

1: Create the Blazor DataGrid Component

Set up the Blazor DataGrid by following the steps outlined in Connecting Blazor DataGrid to an API service.

Step 2: Install Required NuGet Package

Install the System.Data.SqlClient package to connect to Microsoft SQL Server.

In Visual Studio, open Tools → NuGet Package Manager → Manage NuGet Packages for Solution, search for System.Data.SqlClient, 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

@rendermode InteractiveServer

@using Syncfusion.Blazor.Grids
@using Syncfusion.Blazor.Data
@using Syncfusion.Blazor
@using Microsoft.Data.SqlClient;

<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" IsIdentity="true" ValidationRules="@(new ValidationRules{ Required= true })" IsPrimaryKey="true" TextAlign="TextAlign.Right" Width="120"></GridColumn>
        <GridColumn Field=@nameof(Order.CustomerID) 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; }
}

Orderdata.cs

public class Order
  {
      public int? OrderID { get; set; }
      public string CustomerID { 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

@rendermode InteractiveServer

@using Syncfusion.Blazor.Grids
@using Syncfusion.Blazor.Data
@using Syncfusion.Blazor
@using Microsoft.Data.SqlClient;

<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" IsIdentity="true" ValidationRules="@(new ValidationRules{ Required= true })" IsPrimaryKey="true" TextAlign="TextAlign.Right" Width="120"></GridColumn>
        <GridColumn Field=@nameof(Order.CustomerID) 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 paging, grouping, filtering, searching, sorting 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;
        }
    }
}

OrderData.cs

public class OrderData
{
    public async Task<List<Order>> GetOrdersAsync()
        {
            //Create query to fetch data from database
            string Query = "SELECT * FROM dbo.Orders ORDER BY OrderID;";
            List<Order> Orders = null;
            //Create SQL Connection
            using (SqlConnection Connection = new SqlConnection(ConnectionString))
            {
                //Using SqlDataAdapter and Query create connection with database 
                SqlDataAdapter Adapter = new SqlDataAdapter(Query, Connection);
                DataSet Data = new DataSet();
                Connection.Open();
                // Using SqlDataAdapter, process the query string and fill the data into the dataset
                Adapter.Fill(Data);
                //Cast the data fetched from SqlDataAdapter to List<T>
                Orders = Data.Tables[0].AsEnumerable().Select(r => new Order
                {
                    OrderID = r.Field<int>("OrderID"),
                    CustomerID = r.Field<string>("CustomerID"),
                    EmployeeID = r.Field<int>("EmployeeID"),
                    ShipCity = r.Field<string>("ShipCity"),
                    Freight = r.Field<decimal>("Freight")
                }).ToList();
                Connection.Close();
            }
            return Orders;
        }
}

Perform 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 Microsoft SQL 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;
    }
}

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

NOTE

Best Practice: 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 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.CustomerID) 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

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

Orderdata.cs

public async Task AddOrderAsync(Order Value)
        {
            //Create query to insert the specific into the database by accessing its properties 
            string Query = $"Insert into Orders(CustomerID,Freight,ShipCity,EmployeeID) values('{(Value as Order).CustomerID}','{(Value as Order).Freight}','{(Value as Order).ShipCity}','{(Value as Order).EmployeeID}')";
            SqlConnection Connection = new SqlConnection(ConnectionString);
            Connection.Open();
            //Execute the SQL Command
            SqlCommand SqlCommand = new SqlCommand(Query, Connection);
            //Execute this code to reflect the changes into the database
            SqlCommand.ExecuteNonQuery();
            Connection.Close();
        }

Update Operation:

To implement record updates, override the Update or UpdateAsync method in the CustomAdaptor class.

Index.razor

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

Orderdata.cs

public async Task UpdateOrderAsync(Order Value)
{
    //Create query to update the changes into the database by accessing its properties
    string Query = $"Update Orders set CustomerID='{(Value as Order).CustomerID}', Freight='{(Value as Order).Freight}',EmployeeID='{(Value as Order).EmployeeID}',ShipCity='{(Value as Order).ShipCity}' where OrderID='{(Value as Order).OrderID}'";
    SqlConnection Connection = new SqlConnection(ConnectionString);
    Connection.Open();
    //Execute the SQL Command
    SqlCommand SqlCommand = new SqlCommand(Query, Connection);
    //Execute this code to reflect the changes into the database
    SqlCommand.ExecuteNonQuery();
    Connection.Close();
}

Delete Operation:

To perform record deletion, override the Remove or RemoveAsync method in the CustomAdaptor class.

Index.razor

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

Orderdata.cs

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}";
    SqlConnection Connection = new SqlConnection(ConnectionString);
    Connection.Open();
    //Execute the SQL Command
    SqlCommand SqlCommand = new SqlCommand(Query, Connection);
    //Execute this code to reflect the changes into the database
    SqlCommand.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;
}

A complete sample implementation is available in the GitHub repository.