Connecting SQL Server data to Blazor DataGrid using Entity Framework

4 Dec 202524 minutes to read

The Syncfusion^® Blazor DataGrid component supports multiple approaches for binding data from Microsoft SQL Server using Entity Framework. The DataGrid can connect to SQL Server through:

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 focuses on two primary approaches:

Using UrlAdaptor: Enables communication between the DataGrid and a remote API service connected to SQL Server through Entity Framework. 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 using Entity Framework.

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

Entity Framework Overview

Entity Framework (EF) is an object-relational mapper (ORM) for .NET applications that simplifies database interaction by mapping relational data to strongly typed classes. It provides:

LINQ Support: Query data using strongly typed LINQ expressions.
Change Tracking: Automatically tracks changes to entities for persistence.
Migrations: Simplifies schema evolution without manual SQL scripts.
Cross-Platform: Works with .NET Core and .NET 8 for cloud and on-premises apps.

For more details, refer to Microsoft documentation.

Binding data using Entity Framework from Microsoft SQL Server via an API service.

This section explains how to configure an ASP.NET Core Web API service to retrieve data from Microsoft SQL Server using Entity Framework and bind it to the Syncfusion^® Blazor DataGrid through UrlAdaptor. This approach is recommended when server-side operations such as paging, sorting, and filtering are required.

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 Required NuGet Packages

Install the following packages to enable Entity Framework and SQL Server connectivity:

Microsoft.EntityFrameworkCore – Provides core functionality for EF.
Microsoft.EntityFrameworkCore.SqlServer – Enables SQL Server support.
Use NuGet Package Manager in Visual Studio:

Tools → NuGet Package Manager → Manage NuGet Packages for Solution, then search and install both packages.

Alternatively, run these commands in the Package Manager Console:

  Install-Package Microsoft.EntityFrameworkCore
  Install-Package Microsoft.EntityFrameworkCore.SqlServer

Step 3: Configure DbContext and Entity Model

Define a DbContext class in the Models folder to manage database connections and entity mapping. Add a **DbSet** property for the **Orders** table.

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

OrderDbContext.cs

using Microsoft.EntityFrameworkCore;

public class OrderDbContext : DbContext
{
    public OrderDbContext(DbContextOptions<OrderDbContext> options) : base(options) { }
    public DbSet<Order> Orders { get; set; }
}

Step 4: Add API Controller

Create an API controller in the Controllers folder to handle requests from the Blazor DataGrid. This controller uses Entity Framework to retrieve data from the database and returns it in the required format (result and count) for remote data binding.

GridController.cs
[ApiController]
[Route("api/[controller]")]
public class GridController : ControllerBase
{
    private readonly OrderDbContext _context;

    public GridController(OrderDbContext context)
    {
        _context = context;
    }

    [HttpGet]
    public IActionResult GetOrders()
    {
        var data = _context.Orders.ToList();
        return Ok(new { result = data, count = data.Count });
    }
}

* The GridController class is decorated with [ApiController] and [Route] attributes to define the API endpoint.

* The **GetOrders** method retrieves all records from the Orders table using EF Core and returns them as a JSON object with result and count keys, which is the format expected by Syncfusion<sup style="font-size:70%">&reg;</sup> DataGrid.

Step 5: Run and Test the API

Start the ASP.NET Core Web API project in Visual Studio.
Open a browser and navigate to:

https://localhost:xxxx/api/Grid
Confirm that the API returns data in JSON format like this:


{
  "result": [
    {
      "OrderID": 10248,
      "CustomerID": "VINET",
      "EmployeeID": 5,
      "Freight": 32.38,
      "ShipCity": "Reims"
    }
  ],
  "count": 830
}

This ensures the API is working correctly before connecting it to the Blazor DataGrid.

Hosted API URL

Connecting Blazor DataGrid to an API service

This section explains how to bind data from Microsoft SQL Server to the Syncfusion^® Blazor DataGrid using an API service configured with Entity Framework. The UrlAdaptor enables server-side operations such as paging, sorting, filtering, and CRUD actions.

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:

Step 2: 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.c for available NuGet packages list with component details.

Step 3: 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();

Step 4: 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>

NOTE

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

Static Web Assets

CDN

Custom Resource Generator (CRG)

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 and specify the service endpoint in the Url property.

Index.razor
@using Syncfusion.Blazor.Grids
@using Syncfusion.Blazor.Data

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

    <GridColumns>
        <GridColumn Field=@nameof(Order.OrderID) HeaderText="Order ID" IsIdentity="true" IsPrimaryKey="true"
                    TextAlign="TextAlign.Right" Width="120"></GridColumn>
        <GridColumn Field=@nameof(Order.CustomerID) HeaderText="Customer Name" 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 {
    public class Order
    {
        public int OrderID { get; set; }
               public int EmployeeID { get; set; }
        public decimal Freight { get; set; }
        public string ShipCity { get; set; }
    }
}

Blazor DataGrid component bound with Microsoft SQL Server data

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.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 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 POST API method:

OrdersController.cs
[HttpPost]
[Route("api/Grid/Insert")]
/// <summary>
/// Inserts a new data item into the Orders table.
/// </summary>
/// <param name="value">Contains details of the new record to insert.</param>
public void Insert([FromBody] CRUDModel<Order> value)
{
    using (var context = new OrderDbContext(_connectionString))
    {
        context.Orders.Add(value.Value);
        context.SaveChanges();
    }
}

Update Operation:

To update an existing record, select the row and click the Edit toolbar button. This action displays the edit form for modifying the record details. After making the necessary changes, click the Update toolbar button. The record will be updated in the Orders table by invoking this POST API method:

OrdersController.cs
[HttpPost]
[Route("api/Grid/Update")]
/// <summary>
/// Updates an existing data item in the Orders table.
/// </summary>
/// <param name="value">Contains details of the record to update.</param>
public void Update([FromBody] CRUDModel<Order> value)
{
    using (var context = new OrderDbContext(_connectionString))
    {
        var existingOrder = context.Orders.Find(value.Value.OrderID);
        if (existingOrder != null)
        {
            context.Entry(existingOrder).CurrentValues.SetValues(value.Value);
            context.SaveChanges();
        }
    }
}

Delete Operation:

To delete a record, select the row and click the Delete toolbar button. This action removes the record from the Orders table by invoking this POST API method:

OrdersController.cs
[HttpPost]
[Route("api/Grid/Delete")]
/// <summary>
/// Removes a specific data item from the Orders table.
/// </summary>
/// <param name="value">Contains the primary key of the record to delete.</param>
public void Delete([FromBody] CRUDModel<Order> value)
{
    int orderId = Convert.ToInt32(value.Key);
    using (var context = new OrderDbContext(_connectionString))
    {
        var order = context.Orders.Find(orderId);
        if (order != null)
        {
            context.Orders.Remove(order);
            context.SaveChanges();
        }
    }
}

Batch Operation:

To perform batch updates, set the edit Mode to Batch in the GridEditSettings component and configure the BatchUrl property in the SfDataManager.
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.

OrdersController.cs
[HttpPost]
[Route("api/Grid/Batch")]
/// <summary>
/// Handles batch operations (Insert, Update, Delete) in a single request.
/// </summary>
/// <param name="value">Contains details of all changes to apply.</param>
public void Batch([FromBody] CRUDModel<Order> value)
{
    using (var context = new OrderDbContext(_connectionString))
    {
        if (value.Changed != null)
        {
            foreach (var record in value.Changed)
            {
                context.Update(record);
            }
        }

        if (value.Added != null)
        {
            context.Orders.AddRange(value.Added);
        }

        if (value.Deleted != null)
        {
            foreach (var record in value.Deleted)
            {
                var existingOrder = context.Orders.Find(record.OrderID);
                if (existingOrder != null)
                {
                    context.Orders.Remove(existingOrder);
                }
            }
        }

        context.SaveChanges();
    }
}

Blazor DataGrid component bound with Microsoft SQL Server data using Entity Framework

Find the complete implementation in this GitHub location.

Binding data from Microsoft SQL Server using Entity Framework with CustomAdaptor

This section explains how to use Entity Framework with a CustomAdaptor to retrieve data from Microsoft SQL Server and bind it to the Syncfusion^® Blazor DataGrid component.

Step 1: Create the Blazor DataGrid Component

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

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

Step 2: Install Required NuGet Packages

Install the following packages to enable Entity Framework and SQL Server connectivity:

Microsoft.EntityFrameworkCore
Microsoft.EntityFrameworkCore.SqlServer
Use NuGet Package Manager in Visual Studio:
Tools → NuGet Package Manager → Manage NuGet Packages for Solution
Alternatively, use the Package Manager Console:

Install-Package Microsoft.EntityFrameworkCore
Install-Package Microsoft.EntityFrameworkCore.SqlServer

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

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

Create a custom adaptor by extending the DataAdaptor class. Override the ReadAsync method to fetch data using Entity Framework.

Index.razor
OrderData.cs
@rendermode InteractiveServer

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

<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();
            //Here RequiresCount is passed from the control side itself, where ever the on-demand data fetching is needed then the RequiresCount is set as true in component side itself.
            return DataManagerRequest.RequiresCounts ? new DataResult() { Result = DataSource, Count = TotalRecordsCount } : (object)DataSource;
        }
    }
}
public class OrderData
{
    public async Task<List<Order>> GetOrdersAsync()
    {
        using (var Context = new OrderDbContext(ConnectionString))
        {
            // Retrieve orders from the Orders DbSet and convert to list asynchronously
            var orders = await Context.Orders.ToListAsync();
            return orders; 
        }            
    }
}
public class OrderDbContext : DbContext
{
    private readonly string _ConnectionString;
    // Constructor to initialize the DbContext with a connection string
    public OrderDbContext(string ConnectionString)
    {
        _ConnectionString = ConnectionString;
    }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // Configure DbContext to use SQL Server with the provided connection string
        optionsBuilder.UseSqlServer(_ConnectionString);
    }
    // DbSet representing a collection of Order entities in the database
    public DbSet<Order> Orders { get; set; }
}

Blazor DataGrid component bound with Microsoft SQL Server data

Performing Data Operations in CustomAdaptor

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.

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();
        //Here RequiresCount is passed from the control side itself, where ever the on-demand data fetching is needed then the RequiresCount is set as true in component side itself.
        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();
        //Here RequiresCount is passed from the control side itself, where ever the on-demand data fetching is needed then the RequiresCount is set as true in component side itself.
        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();
        //Here RequiresCount is passed from the control side itself, where ever the on-demand data fetching is needed then the RequiresCount is set as true in component side itself.
        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
        }
        //Here RequiresCount is passed from the control side itself, where ever the on-demand data fetching is needed then the RequiresCount is set as true in component side itself.
        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)
        {
            DataSource = DataOperations.PerformTake(DataSource, DataManagerRequest.Take);
            //Add custom logic here if needed and remove above method
        }
        //Here RequiresCount is passed from the control side itself, where ever the on-demand data fetching is needed then the RequiresCount is set as true in component side itself.
        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;
        }
        //Here RequiresCount is passed from the control side itself, where ever the on-demand data fetching is needed then the RequiresCount is set as true in component side itself.
        return DataManagerRequest.RequiresCounts ? new DataResult() { Result = DataSource, Count = TotalRecordsCount } : (object)DataSource;
    }
}

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

If both grouping and aggregate operations are enabled, the code provided below demonstrates how to implement these operations within the CustomAdaptor.

public class CustomAdaptor : DataAdaptor
{
    public OrderData OrderService = new OrderData();
    // Performs data Read operation
    public override async Task<object> ReadAsync(DataManagerRequest DataManagerRequest, string Key = null)
    {
        IEnumerable<Order> DataSource = await OrderService.GetOrdersAsync();
        int TotalRecordsCount = DataSource.Cast<Order>().Count();
        DataResult DataObject = new DataResult();
        // Handling both Grouping and Aggregation in CustomAdaptor.
        if (DataManagerRequest.Aggregates != null || DataManagerRequest.Group != null) // Aggregation
        {
            if (DataManagerRequest.Group != null)
            {
                IEnumerable ResultData = DataSource.ToList();
                // Grouping
                foreach (var group in DataManagerRequest.Group)
                {
                    ResultData = DataUtil.Group<Order>(ResultData, group, DataManagerRequest.Aggregates, 0, DataManagerRequest.GroupByFormatter);
                    //Add custom logic here if needed and remove above method
                }
                DataObject.Result = ResultData;
            }
            else
            {
                DataObject.Result = DataSource;
            }
            DataObject.Count = TotalRecordsCount;
            DataObject.Aggregates = DataUtil.PerformAggregation(DataSource, DataManagerRequest.Aggregates);

            return DataManagerRequest.RequiresCounts ? DataObject : (object)DataSource;
        }
        //Here RequiresCount is passed from the control side itself, where ever the on-demand data fetching is needed then the RequiresCount is set as true in component side itself.
        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).

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
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)
{
    using (var Context = new OrderDbContext(ConnectionString))
    {
        // Add the provided order to the Orders DbSet
        Context.Orders.Add(Value);
        // Save changes asynchronously to the database
        await Context.SaveChangesAsync();
    }
}

This method is invoked when adding new records to the Blazor DataGrid. The value parameter contains the new record details, which are persisted to the database using Entity Framework.

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)
{
    using (var Context = new OrderDbContext(ConnectionString))
    {
        // Update the provided order in the Orders DbSet
        Context.Orders.Update(Value);
        // Save changes asynchronously to the database
        await Context.SaveChangesAsync();
    }
}

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)
{
    using (var Context = new OrderDbContext(ConnectionString))
    {
        var Order = await Context.Orders.FindAsync(Key);
        if (Order != null)
        {
            // Remove the order from the Orders DbSet
            Context.Orders.Remove(Order);
            // Save changes asynchronously to the database
            await Context.SaveChangesAsync();
        }
    }
}

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 Microsoft SQL Server data using Entity Framework

A complete sample implementation is available in the GitHub repository.

Search docs

Ask HelpBot

Search docs

Ask HelpBot

Connecting SQL Server data to Blazor DataGrid using Entity Framework

Entity Framework Overview

Binding data using Entity Framework from Microsoft SQL Server via 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 Microsoft SQL Server using Entity Framework with CustomAdaptor

Performing Data Operations in CustomAdaptor

Handling searching operation

Handling filtering operation

Handling sorting operation

Handling aggregate operation

Handling paging operation

Handling grouping operation

Handling CRUD operations