Supply On Demand Data To A DevExpress Data Grid Control Using Code Generated By AspCoreGen3MVC

Technologies Used: DevExpress, Data Grid, AspCoreGen3MVC, AspCoreGen3Razor, ASP.NET, C#

DevExpress is a popular company who makes software components used by developers worldwide. This tutorial will show how to use the back end code generated by AspCoreGen3MVC as a datasource for a DevExpress Data Grid Control. The DevExpress Data Grid Control will be able to dynamically get data on demand using paging and sorting by column.

The generated back end code (middle tier, data tier, web api, stored procedures, etc) for either AspCoreGen3MVC or AspCoreGen3Razor can be reused in any project that needs data from a Microsoft SQL Server database. You can reuse the generated back end code in a web, mobile, desktop, or even web api projects. You can easily fill UI components with as little as one line of code using the generated code as a datasource.

Note: The code reuse also applies, but not limited to: DevExpress Blazor, MVC, Web Forms, Angular, React, Vue, WinForms, WPF, and many more.

You need the following for this tutorial:
  • Microsoft Northwind Database (please Google it)
  • Microsoft SQL Server 2003 and above
  • You will need a User with an Admin username/password or with enough rights in the MS SQL Server (or Azure SQL). A blank username/password will not work.
  • Visual Studio 2019 (the generated code will not work in Visual Studio 2017 and below)
  • AspCoreGen 3.0 MVC Professional Plus or AspCoreGen 3.0 Razor Professional Plus (both optional)
  • AspCoreGen 3.0 MVC Ad-Hoc SQL Sample Project
  • DevExtreme ASP.NET MVC Controls Demo Application

Here's a snapshot of what we're trying to do:

RecordPaging.cshtml View

Note: You need to have the Microsoft Northwind Database installed in a Microsoft SQL Server for this tutorial. Download and installation of this database is beyond the scope of this tutorial. We will be using the Products table to retrieve rows of data from this database.

Northwind Database

1. Download the #5. Ad-Hoc SQL Sample Project here: Generated Projects. You will see the project towards the bottom of the page. Click the Download Sample Code link. This web application project was generated by AspCoreGen3MVC, the solution contains 2 projects, a Web Application and a reusable Class Library project. We only need the Class Library project which consist of the Business Object and Data Tier class files and we can discard the web application project.

FYI: You can download and use any of the six (6) projects from AspCoreGen3MVC's Web Generated Project Examples or AspCoreGen3Razor's Web Generated Project Examples. Or generate your own code using AspCoreGen3MVC or AspCoreGen3Razor. Any of these will work, just follow along with the tutorial.

2. Download the DevExpress Universal Free Trial. Install it in your computer and open the demo project in Visual Studio 2019.

For the purposes of this tutorial, we just need to install the DevExtreme ASP.NET MVC Controls sample application. This includes the ASP.NET Core demo that we will use.

Install DevExtreme ASP.NET MVC Controls

FYI: You can use the generated code from AspCoreGen3MVC or AspCoreGen3Razor for any DevExpress product listed above, they will work just fine. If you already bought a DevExpress License, then the demo application should already be installed in your computer/system, in this case just follow along (no need to download).

3. Open the DevExtreme.NETCore.Demos.sln solution (in Visual Studio 2019) that was installed in your computer. In my case, the solution file can be found in C:\Users\Public\Documents\DevExpress Demos 20.2\DevExtreme\ASP.NET MVC Controls\WidgetsGallery\ASP.NET Core directory.


Solution in Visual Studio

Note: You can also open the Sample Source Code by clicking on the respective Technical Demo (Core) from the DevExpress dashboard.

Link to Sample Source Code

4. Copy the Class Library Project (NwndAdHcAPI) that we downloaded in #1 to the C:\Users\Public\Documents\DevExpress Demos 20.2\DevExtreme\ASP.NET MVC Controls\WidgetsGallery folder, the same folder where the ASP.NET Core project/folder is located, as shown below.

Copy NwndAdHcAPI Class Library Project

5. Back in Visual Studio, add the NwndAdHcAPI.csproj to the DevExtreme.NETCore.Demos solution by adding an existing project as shown below.

Add Existing Project

Add Existing Project

6. You should now have 2 projects showing in the Visual Studio Solution Explorer.

2 Projects in Solution

7. Open the AppSettings.cs class under the NwndAdHcAPI project and update the DatabaseUserName and DatabasePassword for the Northwind Database that you're using.

Generated ASP.NET Core 3.1 Razor Page Web Application

8. Change the NwndAdHcAPI project's Target Framework to .NET Core 2.1 from .NET Core 3.1 because the DevExtreme.NETCore.Demos was made in .NET Core 2.1. Right-click on the NwndAdHcAPI project under the Solution Explorer in Visual Studio, and then choose Properties in the context menu. Under the Target Framework, choose .NET Core 2.1, and then save it.

Target Framework

9. Now that both the DevExtreme.NETCore.Demos and NwndAdHcAPI projects are using the same Target Framework, add the NwndAdHcAPI project as a Project Reference to the DevExtreme.NETCore.Demos project.

Project Reference

Project Reference

10. Instead of creating a new MVC view, we will update/edit an existing one. Open the RecordPaging.cshtml view in the DevExtreme.NETCore.Demos project, under the Views/DataGrid folder. Delete all code and replace with the code shown below:


11. Now open the related DataGridProductsController.cs web api controller under the Controllers/ApiControllers folder. Update the code to what you see below:

DataGridProductsController.cs web api

The GetData web method shown above will only return the amount of data the DevExpress Data Grid component is asking for. The code that we deleted (DevExpress example code) returns all data from the database while the code we have above returns data on-demand.

The GetData web method is called whenever an event is triggered on the DevExpress Data Grid component found in the RecordPaging.cshtml view. When the Sort or the Paging event is triggered, the Data Grid passes a DataSourceLoadOptions to the GetData web method in the DataGridProductsController web api.

As shown below, with the DataSourceLoadOptions, we can figure out:

- How many rows (Page Size) to get from the database
- Where to start taking data from the database (Skip)
- The Column/Field to Sort and the Sort Direction (Ascending or Descending)

Rows, Skip, Take, Sort Order

We can then retrieve just the right amount of data on-demand after getting these parameters.

Get data on demand

The DevExpress Data Grid component expects the Total Number of Records for the specific Datasource it's using, for our example, it's grabbing data from the Northwind database's Products table. We need to return the Total Number of Records from the Products table so that the DevExpress Data Grid component will be able to calculate the number of Pages to show.

Total Number of Records

The DevExpress Data Grid component expects a JSON-formatted data that would contain parameters such as:

- totalCount (Total Number of Records)
- groupCount (Used for grouping)
- summary (Used for summaries)
- @data (Data)

JSON Formatted Data

12. Run the project by pressing F5 in Visual Studio. Go to the Record Paging link under the Paging and Scrolling group.

RecordPaging.cshtml View

Click around to see the functionality of the DevExpress Data Grid component with data supplied by the AspCoreGen3MVC Generated Code.

And we're done.

As always, the code and the article are provided "As Is", there is absolutely no warranties. Use at your own risk.

Happy Coding!!!

Date Created: Thursday, February 11, 2021