Blazor FlexGrid - Using Entity Framework Core and an SQL Server
When Microsoft released Blazor, it provided an alternative to developing single page applications (SPA) that afforded a clean user experience and interactivity without relying on page refreshes and postbacks like some earlier ASP. NET platforms. It also provides a real option for .NET developers who want to bring their application to the web without converting it to JavaScript.
We have outlined the steps required to connect a Blazor Server project to a SQL database using Entity Framework Core while providing the full feature set you're used to with ComponentOne FlexGrid.
Launch Visual Studio and Create a New Blazor App
- Name the new project
- Select the default values for configuration and settings
Creating a server app integrates the data layer and UI in the same project.
Add NuGet Packages to both EntityFramework Core and ComponentOne to the project.
- Right-click on Dependencies and select Manage NuGet Packages
Add the following
- Microsoft.EntityFrameworkCore.SqlServer
- Microsoft.EntityFrameworkCore.Tools
- C1.Blazor.Grid
- C1.DataCollection.EntityFrameworkCore
Use the Entity Framework Core Scaffold-DbContext command-line tool to create the Models and Context for this project. The goal is to display the Orders table. We also want to scaffold additional tables due to the foreign key relationships in the database.
Execute the following code in the Package Manager Console in Visual Studio:
Scaffold-DbContext “Data Source=CEDATASERVER;Initial Catalog=Northwind;Persist Security Info=True;User ID=dbuser2;Password=xxxxxxx” Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Tables Orders, Customers, Employees, Shippers
- Data Source: the standard connection string for the SQL database
- OutputDir: places the class files and context file in the Models folder
- Tables: lists the specific table to extract from the Database
This adds a Models folder in the project and creates a class for each table and the NorthwindContext.cs file.
Rather than creating a service and writing all the code to handle the individual CRUD operations, we will utilize the C1DataCollection Nuget package we added earlier. By binding the Context to the C1DataCollection, it will handle all the various operations, as well as providing options for server side filtering and sorting.
- Bind the Context to a C1EntityFrameworkCoreVirtualDataCollection
- Bind the FlexGrid to the DataCollection
Start Building the Orders Table Page
- Right-click on the Pages folder in the Solution Explorer and select Add > New Item
- Select Razor Component and name it OrdersTable.razor
- Define the @page to assist with navigation
- Add @using statements to the top for the FlexGrid, DataCollection and solution’s Models
- Down the @code block, create a C1EntityFrameworkVirtualDataCollection of type Orders named collection
- Use the built-in async Task OnInitializedAsync to instance the Context
- Await loading and initialize the DataColection with the loaded Context
The VirtualDataCollection named, “collection” now contains an Orders table.
- Go into the body of the page and create an instance of a FlexGrid
- Set the ItemSource property to collection
Before running the application, go into the Pages / _Host.cshtml and add the necessary .css and .js references to the <head> tag.
<link rel="stylesheet" href="~/_content/C1.Blazor.Core/styles.css" />
<link rel="stylesheet" href="~/_content/C1.Blazor.Grid/styles.css" />
<link rel="stylesheet" href="~/_content/C1.Blazor.ListView/styles.css" />
<link rel="stylesheet" href="~/_content/C1.Blazor.Input/styles.css" />
<link rel="stylesheet" href="~/_content/C1.Blazor.DataPager/styles.css" />
<script src="~/_content/C1.Blazor.Core/scripts.js"></script>
<script src="~/_content/C1.Blazor.Input/scripts.js"></script>
<script src="~/_content/C1.Blazor.Grid/scripts.js"></script>
Go into the Shared / NavMenu.razor. Add a link to the new OrdersTable page so the user can navigate it when the application runs.
<li class="nav-item px-3">
<NavLink class="nav-link" href="OrdersTable">
<span class="oi oi-spreadsheet" aria-hidden="true"></span> Orders Table
</NavLink>
</li>
Run the application.
Click the Orders Table link in the left navigation bar. It displays the table with FlexGrid.
When editing cells, the changes get persisted back to the SQL server upon completion. Click on the header row for column sorting and other features.
Using ComponentOne FlexGrid Features
The Employee Id and Ship Via columns both display foreign keys from those tables and not user-friendly text. Fix this problem with the FlexGrid’s DataMap feature.
- Go back to the OrdersTable.razor.
- In the @code section define two GridDataMap objects
- In the OnInitializedAsync event, bind those object’s ItemsSources to the appropriate tables via the context
- Set the DisplayMemberPath to the corresponding column for display in the grid
- Set the SelectedValuePath to the index value that will be stored
To set specific column properties, turn off FlexGrid’s AutoGenerateColumn feature and define the columns individually.
- Set the DataMap property for the Employee ID and Ship Via columns to the GridDataMaps
- Define the three Date columns as GridDateTimeColumns
- Set the Format to “d” and load a DateTime picker when the column is edited
- Set a MinWidth of 180 for those columns and ensure the content displays correctly
Run the application.
Now the Employee ID and Ship Via columns display useful information and present a nice AutoLookup dropdown box for editing.
The three date columns also use the DateTime picker when editing.
The C1DataCollection used with the Entity Framework Context implements GrapeCity's Virtual Scrolling technology. It only fetches the number of rows needed to fill the display the user sees at a given time. The scrollbar still displays the full range and additional rows are fetched as the user scrolls. For this to work efficiently, define a max height for the grid. This allows it to calculate the view window’s size and how much data is needed to fill it.
For this case, set the Style/max-height property to 800px.
Run the application.
A scroll bar appears for the grid itself on the right. Loading performance and general responsiveness both increase with this feature.
The sample application is attached below. Note, it will not work out of the box as the SQL server is behind our firewall.
Download: BlazorFlexGridEFCoreSQL.zip
Modify the connection string in the Models / NorthwindContext.cs to point to your SQL server and we provide a .MDF copy of Northwind in the Document/ComponentOne Samples/Common folder.
Alternatively, start your own application and point the Scaffolder at your tables and try the Blazor FlexGrid with your own data and workflows.
Please leave us your thoughts in the comments below, and happy coding!
Andy Paxinos