Bind data to the Syncfusion® Controls using ODataV4Adaptor

4 Dec 202418 minutes to read

In this topic, you can learn how to retrieve data from RESTful web services, bind data to a ASP.NET Core Grid control, and perform CRUD operations. Here, data is fetched from the ODataV4 service using ODataV4Adaptor in DataManger. It is recommended to choose the suitable adaptor based on the RESTful service which you are using to bind data for the Syncfusion® ASP.NET Core control.

Prerequisite software

The following software are needed

  • Visual Studio 2022 v17.0 or later.
  • .NET SDK 6.0 or later

Create the database

Open Visual Studio, select View -> SQL Server Object Explorer. Right-click on the Databases folder to create a new Database and name it as OrdersDetails.

Add new database
Adding database name and location

Right-click on the Tables folder of the created database and click Add New Table.

Add table

Use the following query to add a new table named Orders.

Create Table Orders(
 OrderId BigInt Identity(1,1) Primary Key Not Null,
 CustomerId Varchar(100) Not Null,
 Freight int Null,
 OrderDate datetime null
)

Now, the Orders table design will look like below. Click on the Update button.

Database table design

Now, click on Update Database.

Update database

Create OData service project

Open Visual Studio and create an empty ASP.NET Core Web Application with MVC(views and controllers) and name it as ODataServiceProject.

Getting Started

After creating the application, install Microsoft.AspNetCore.OData package by running the following command in the Package Manager Console.

  • Install-Package Microsoft.AspNetCore.OData -Version 8.0.7: This package contains everything you need to create OData v4.0 endpoints using ASP.NET Core MVC and to support OData query syntax for your web APIs.

Generate DbContext and model class from the database

Now, scaffold DbContext and model classes from the existing OrdersDetails database. To perform scaffolding and work with the SQL Server database in our application, install the following NuGet packages.

Run the following commands in the Package Manager Console.

  • Install-Package Microsoft.EntityFrameworkCore.Tools -Version 6.0.2: This package creates database context and model classes from the database.
  • Install-Package Microsoft.EntityFrameworkCore.SqlServer -Version 6.0.2: The database provider that allows Entity Framework Core to work with SQL Server.

Once the above packages are installed, you can scaffold DbContext and Model classes. Run the following command in the Package Manager Console.

Scaffold-DbContext “Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=OrdersDetails;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False” Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

The above scaffolding command contains the following details for creating DbContext and model classes for the existing database and its tables.

  • Connection string: Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=OrdersDetails;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False
  • Data provider: Microsoft.EntityFrameworkCore.SqlServer
  • Output directory: -OutputDir Models

After running the above command, the OrdersDetailsContext.cs and Order.cs files will be created under the ODataServiceProject.Models folder as follows.

Models folder

The OrdersDetailsContext.cs file contains the connection string details in the OnConfiguring method.

Remove connection string from context file

It is not recommended to have a connection string with sensitive information in the OrdersDetailsContext.cs file, so move the connection string to the appsettings.json file.

Add connection string in appsettings

Now, the DbContext must be configured using connection string and registered as scoped service using the AddDbContext method in Program.cs.

Register service

Creating ODataV4 Service

The application is now configured to connect with the OrdersDetails database using Entity Framework. Now, it’s time to consume data from the OrdersDetails database. To do so, an OData controller is required to serve data from the DbContext to the ASP.NET Core application.

To create OData controller, right-click Controller folder in ODataServiceProject and select Add -> New Item -> API controller with read/write actions. This controller is named as OrdersController as it returns Orders table records.

Now, replace the controller with the following code which contains code to handle CRUD operations in the Orders table.

using Microsoft.AspNetCore.Mvc;
using ODataServiceProject.Models;
using Microsoft.AspNetCore.OData.Query;
using Microsoft.AspNetCore.OData.Deltas;
using Microsoft.AspNetCore.OData.Formatter;
using Microsoft.AspNetCore.OData.Routing.Controllers;

// For more information on enabling Web API for empty projects, visit https://go.microsoft.com/fwlink/?LinkID=397860

namespace ODataServiceProject.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class OrdersController : ODataController
    {
        private OrdersDetailsContext _db;
        public OrdersController(OrdersDetailsContext context)
        {
            _db = context;
        }


        [HttpGet]
        [EnableQuery]
        public IActionResult Get()
        {
            return Ok(_db.Orders);
        }

        [EnableQuery]
        public async Task<IActionResult> Post([FromBody] Order book)
        {
            _db.Orders.Add(book);
            _db.SaveChanges();
            return Created(book);
        }
        [EnableQuery]
        public async Task<IActionResult> Patch([FromODataUri] long key, [FromBody] Delta<Order> book)
        {
            var entity = await _db.Orders.FindAsync(key);
            book.Patch(entity);
            await _db.SaveChangesAsync();
            return Updated(entity);
        }
        [EnableQuery]
        public long Delete([FromODataUri] long key)
        {
            var deleterow = _db.Orders.Find(key);
            _db.Orders.Remove(deleterow);
            _db.SaveChanges();
            return key;
        }
    }
}

Open Program.cs file and configure by referring to the following codes.

using Microsoft.EntityFrameworkCore;
using ODataServiceProject.Models;
using Microsoft.AspNetCore.OData;
using Microsoft.OData.Edm;
using Microsoft.OData.ModelBuilder;

static IEdmModel GetEdmModel()
{
    ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
    var books = builder.EntitySet<Order>("Orders");
    FunctionConfiguration myFirstFunction = books.EntityType.Collection.Function("MyFirstFunction");
    myFirstFunction.ReturnsCollectionFromEntitySet<Order>("Orders");
    return builder.GetEdmModel();
}

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddDbContext<OrdersDetailsContext>(option => option.UseSqlServer(builder.Configuration.GetConnectionString("OrdersDetailsDatabase")));
builder.Services.AddControllersWithViews().AddOData(opt => opt.AddRouteComponents("odata", GetEdmModel()).Count().Filter().OrderBy().Expand().Select().SetMaxTop(null));

var app = builder.Build();

// Configure the HTTP request pipeline.
if (!app.Environment.IsDevelopment())
{
    app.UseExceptionHandler("/Home/Error");
    // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
    app.UseHsts();
}

app.UseHttpsRedirection();
app.UseStaticFiles();

app.UseRouting();

app.UseAuthorization();

app.MapControllerRoute(
    name: "default",
    pattern: "{controller=Home}/{action=Index}/{id?}");

app.Run();

Install ASP.NET Core package in the application

Syncfusion® ASP.NET Core controls are available in nuget.org. Refer to NuGet packages topic to learn more about installing NuGet packages in various OS environments. To add ASP.NET Core controls in the application, open the NuGet package manager in Visual Studio (Tools → NuGet Package Manager → Manage NuGet Packages for Solution), search for Syncfusion.EJ2.AspNet.Core and then install it.

NOTE

The Syncfusion.EJ2.AspNet.Core NuGet package has dependencies, Newtonsoft.Json for JSON serialization and Syncfusion.Licensing for validating Syncfusion® license key.

Add Syncfusion® ASP.NET Core Tag Helper

Open ~/Views/_ViewImports.cshtml file and import the Syncfusion.EJ2 TagHelper.

@addTagHelper *, Syncfusion.EJ2

Add Style Sheet

Checkout the Themes topic to learn different ways (CDN, NPM package, and CRG) to refer styles in ASP.NET Core application, and to have the expected appearance for Syncfusion® ASP.NET Core controls. Here, the theme is referred using CDN inside the <head> of ~/Views/Shared/_Layout.cshtml file as follows,

<head>
    ...
    <!-- Syncfusion ASP.NET Core controls styles -->
    <link rel="stylesheet" href="https://cdn.syncfusion.com/ej2/28.1.33/material.css" />
</head>

Add Script Reference

In this getting started walk-through, the required scripts are referred using CDN inside the <head> of ~/Views/Shared/_Layout.cshtml file as follows,

<head>
    ...
    <!-- Syncfusion ASP.NET Core controls scripts -->
    <script src="https://cdn.syncfusion.com/ej2/28.1.33/dist/ej2.min.js"></script>
</head>

Register Syncfusion® Script Manager

Open ~/Views/Shared/_Layout.cshtml page and register the script manager at the end of `<body>` in the ASP.NET Core application as follows.

<body>
    ...
    <!-- Syncfusion Script Manager -->
    <ejs-scripts></ejs-scripts>
</body>

Add DataGrid control to an application

In previous steps, the Syncfusion® ASP.NET Core package in the application is successfully configured. Now, add the grid control to your Index.cshtml view page which is present under Views/Home folder.

<ejs-grid id="Grid"></ejs-grid>

Binding data to DataGrid control using ODataV4Adaptor

To consume data from the OData Controller, add the DataManager with ODataV4Adaptor like below.

<ejs-grid id="Grid">
    <e-data-manager url="https://localhost:7029/odata/Orders" adaptor="ODataV4Adaptor" crossdomain="true"></e-data-manager>
</ejs-grid>

NOTE

In the above code example, use localhost address from the application. Instead of localhost, you can give the exact URL of your OData service.

Grid columns can be defined by using the GridColumn component.

<ejs-grid id="Grid">
    <e-data-manager url="https://localhost:7029/odata/Orders" adaptor="ODataV4Adaptor" crossdomain="true"></e-data-manager>
    <e-grid-editSettings allowDeleting="true" allowEditing="true" allowAdding="true"></e-grid-editSettings>
    <e-grid-columns>
                <e-grid-column field="OrderId" headerText="Order ID" visible="false" isPrimaryKey="true" width="140"></e-grid-column>
                <e-grid-column field="CustomerId" headerText="Customer Name" validationRules="@(new { required=true})" width="150"></e-grid-column>
                <e-grid-column field="Freight" headerText="Freight" validationRules="@(new { required=true})" textAlign="Right" editType="numericedit" format="C2" width="140"></e-grid-column>
                <e-grid-column field="OrderDate" headerText="Order Date" editType="datetimepickeredit" customFormat="@(new {type = "datetime", format = "M/d/y hh:mm a" })" width="160"></e-grid-column>
    </e-grid-columns>
 </ejs-grid>

When the application is run, the Get() method will be called in OData controller.

[Route("api/[controller]")]
[ApiController]
public class OrdersController : ODataController
{
    private OrdersDetailsContext _db;
    public OrdersController(OrdersDetailsContext context)
    {
        _db = context;
    }

    [HttpGet]
    [EnableQuery]
    public IActionResult Get()
    {
        return Ok(_db.Orders);
    }
    ...
}

Handling CRUD operations with Syncfusion® DataGrid control

Editing can be enabled in the grid control using the GridEditSettings component. Grid provides various modes of editing options such as Inline/Normal, Dialog, and Batch editing.

Here, Inline edit mode and Toolbar property are used to show toolbar items for editing.

<ejs-grid id="Grid" toolbar="@(new List<string>() { "Add", "Edit", "Delete", "Cancel", "Update" })">
    <e-data-manager url="https://localhost:7029/odata/Orders" adaptor="ODataV4Adaptor" crossdomain="true"></e-data-manager>
    <e-grid-editSettings allowAdding="true" allowDeleting="true" allowEditing="true" mode="Normal"></e-grid-editSettings>
    <e-grid-columns>
                <e-grid-column field="OrderId" headerText="Order ID" visible="false" isPrimaryKey="true" width="140"></e-grid-column>
                <e-grid-column field="CustomerId" headerText="Customer Name" validationRules="@(new { required=true})" width="150"></e-grid-column>
                <e-grid-column field="Freight" headerText="Freight" validationRules="@(new { required=true})" textAlign="Right" editType="numericedit" format="C2" width="140"></e-grid-column>
                <e-grid-column field="OrderDate" headerText="Order Date" editType="datetimepickeredit" customFormat="@(new {type = "datetime", format = "M/d/y hh:mm a" })" width="160"></e-grid-column>
    </e-grid-columns>
 </ejs-grid>

NOTE

Normal editing is the default edit mode for the Grid component. Set the IsPrimaryKey property of Column as true for a particular column, whose value is a unique value for editing purposes.

Insert a row

To insert a new row, click the Add toolbar button.

Insert Operation

Clicking the Update toolbar button will insert the record in the Orders table by calling the below POST method of the OData controller.

[EnableQuery]
public async Task<IActionResult> Post([FromBody] Order book)
{
    _db.Orders.Add(book);
    _db.SaveChanges();
    return Created(book);
}

Insert Operation

Update a row

To edit a row, select any row and click the Edit toolbar button. The edit form will look like below. Edit the Customer Name column.

Update Operation

Clicking the Update toolbar button will update the record in the Orders table by calling the below PATCH method of the OData controller.

[EnableQuery]
public async Task<IActionResult> Patch([FromODataUri] long key, [FromBody] Delta<Order> book)
{
    var entity = await _db.Orders.FindAsync(key);
    book.Patch(entity);
    await _db.SaveChangesAsync();
    return Updated(entity);
}

Update Operation

Delete a row

To delete a row, select any row and click the Delete toolbar button. Deleting operation will send a DELETE request to the OData controller with the selected record`s primary key value to remove the corresponding record from the Orders table.

[EnableQuery]
public long Delete([FromODataUri] long key)
{
    var deleterow = _db.Orders.Find(key);
    _db.Orders.Remove(deleterow);
    _db.SaveChanges();
    return key;
}

NOTE

View sample in GitHub