Blazor WebAssembly CRUD Application with Entity Framework
Setup The Blazor Application
Follow the Getting Started
guide to set up your Blazor Application with Smart UI.
Make sure that that the application is ASP.NET Core hosted:
Create SQL Data
The following steps detail how to create a SQL Database in Visual Studio 2022 and fill it with data. If you already have SQL Database, continue to Connect Blazor to SQL Data.
- To create a table, first you need to create a database for your application. Navigate to View -> SQL Server Object Explorer
- Inside the localdb -> Databases directory, create a new SQL database by right-clicking on the Databases folder.
For the purpose of the Demo, we will create people.db
- To create a table, right-click on the database and select New Query.... Then paste the following SQL code to create a table of our clients:
CREATE TABLE [dbo].[peopleTable] ( [Id] INT IDENTITY(1,1) NOT NULL, [Name] NCHAR (50) NULL, [Balance] FLOAT (50) NULL, [City] NCHAR (50) NULL, [Country] NCHAR (50) NULL, PRIMARY KEY CLUSTERED ([Id] ASC) ); INSERT INTO [dbo].[peopleTable] ([Id], [Name], [Balance], [City], [Country]) VALUES (1, N'Maria Anders', 130.0000, N'Berlin', N'Germany') INSERT INTO [dbo].[peopleTable] ([Id], [Name], [Balance], [City], [Country]) VALUES (2, N'Ana Trujillo', 230.0000, N'Mxico D.F.', N'Mexico') INSERT INTO [dbo].[peopleTable] ([Id], [Name], [Balance], [City], [Country]) VALUES (3, N'Antonio Moreno', 3500.0000, N'Mxico D.F.', N'Mexico') INSERT INTO [dbo].[peopleTable] ([Id], [Name], [Balance], [City], [Country]) VALUES (4, N'Thomas Hardy', 55.0000, N'London', N'UK') INSERT INTO [dbo].[peopleTable] ([Id], [Name], [Balance], [City], [Country]) VALUES (5, N'Christina Berglund', 1500.0000, N'Lule', N'Sweden') INSERT INTO [dbo].[peopleTable] ([Id], [Name], [Balance], [City], [Country]) VALUES (6, N'Hanna Moos', 650.0000, N'Mannheim', N'Germany') INSERT INTO [dbo].[peopleTable] ([Id], [Name], [Balance], [City], [Country]) VALUES (7, N'Frdrique Citeaux', 50.0000, N'Strasbourg', N'France') INSERT INTO [dbo].[peopleTable] ([Id], [Name], [Balance], [City], [Country]) VALUES (8, N'Martn Sommer', 0.0000, N'Madrid', N'Spain')
Connect Blazor to SQL Data
The following steps detail how to connect your SQL Data to the Blazor Application. If your data is already connected, continue to Bind Grid to SQL Data
- Using the Visual Studio NuGet Package Manager, add the following dependancies to project:
Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.Tools
- Inside BlazorProject.Shared, create a new folder "Models", then create a new new item of type
Class
called Person.cs
This is where we will define the properties of each individual Person from our SQL table:namespace BlazorProject.Shared.Models { public class Person { public int Id { get; set; } public string Name { get; set; } public double Balance { get; set; } public string City { get; set; } public string Country { get; set; } } }
- Inside the same folder, create a new
Class
item called IDataAccessProvider.cs - this is the inerface, where we will store our CRUD methods: This is where we will create the LoadData function:namespace BlazorProject.Shared.Models { public interface IDataAccessProvider { void AddClientRecord(Person person); void UpdateClientRecord(Person person); void DeleteClientRecord(int id); Person GetClientRecord(int id); List<Person> GetClientRecords(); } }
- Navigate to BlazorProject.Shared, create a new folder "DataAccess", create a new item DomainModelSqlContext.cs, which will be our backend:
using BlazorProject.Shared.Models; using Microsoft.EntityFrameworkCore; namespace BlazorProject.Server.DataAccess { public class DomainModelSqlContext : DbContext { public DomainModelSqlContext(DbContextOptions<DomainModelSqlContext> options) : base(options) { } public DbSet<Person> peopleTable { get; set; } protected override void OnModelCreating(ModelBuilder builder) { base.OnModelCreating(builder); } public override int SaveChanges() { ChangeTracker.DetectChanges(); return base.SaveChanges(); } } }
In the same folder, create a new item called DataAccessSqlProvider.cs, which will execute the functions in our backend
using BlazorProject.Shared.Models; namespace BlazorProject.Server.DataAccess { public class DataAccessSqlProvider : IDataAccessProvider { private readonly DomainModelSqlContext _context; public DataAccessSqlProvider(DomainModelSqlContext context) { _context = context; } public void AddClientRecord(Person person) { _context.peopleTable.Add(person); _context.SaveChanges(); } public void UpdateClientRecord(Person person) { _context.peopleTable.Update(person); _context.SaveChanges(); } public void DeleteClientRecord(int id) { var entity = _context.peopleTable.First(t => t.Id == id); _context.peopleTable.Remove(entity); _context.SaveChanges(); } public Person GetClientRecord(int id) { return _context.peopleTable.First(t => t.Id == id); } public List<Person> GetClientRecords() { return _context.peopleTable.ToList(); } } }
-
To create our Web API controller, create a new item called ClientsController.cs inside the Controllers folder:
using BlazorProject.Shared.Models; using Microsoft.AspNetCore.Mvc; namespace BlazorProject.Server.Controllers { public class ClientsController : Controller { private readonly IDataAccessProvider _dataAccessProvider; public ClientsController(IDataAccessProvider dataAccessProvider) { _dataAccessProvider = dataAccessProvider; } [HttpGet] [Route("api/Clients/Get")] public IEnumerable<Person> Get() { return _dataAccessProvider.GetClientRecords(); } [HttpPost] [Route("api/Clients/Create")] public void Create([FromBody] Person person) { if (ModelState.IsValid) { _dataAccessProvider.AddClientRecord(person); } } [HttpGet] [Route("api/CLients/Details/{id}")] public Person Details(int id) { return _dataAccessProvider.GetClientRecord(id); } [HttpPut] [Route("api/Clients/Edit")] public void Edit([FromBody] Person person) { if (ModelState.IsValid) { _dataAccessProvider.UpdateClientRecord(person); } } [HttpDelete] [Route("api/Clients/Delete/{id}")] public void DeleteConfirmed(int id) { _dataAccessProvider.DeleteClientRecord(id); } } }
- Navigate to people.db using the SQL Server Object Explorer, right-click and select properties.
Then copy the value of the "Connection string" property:
Inside BlazorProject.Shared, navigate to appsettings.json and set ConnectionStrings.Default to the copied value:
- Finally, open Program.cs and add the following services:
using BlazorProject.Server.DataAccess; using BlazorProject.Shared.Models; using Microsoft.EntityFrameworkCore; .... ConfigurationManager configuration = builder.Configuration; builder.Services.AddDbContext<DomainModelSqlContext>(options => options.UseSqlServer(configuration.GetConnectionString("Default"))); builder.Services.AddScoped<IDataAccessProvider, DataAccessSqlProvider>();
Bind Grid to SQL Data
- Add the Grid component to the Pages/Index.razor file of BlazorProject.Client and set the Columns to display:
<Grid DataSource="@people" DataSourceSettings="@dataSourceSettings"> <Columns> <Column DataField="Name" Label="Client Name"></Column> <Column DataField="Balance" Label="Acccount Balance"></Column> <Column DataField="City" Label="City"></Column> <Column DataField="Country" Label="Country"></Column> </Columns> </Grid>
- Inject the necessary libraries and the Models at the top of the page:
@page "/" @inject HttpClient Http @using System.Net.Http.Json @using Smart.Blazor @using BlazorProject.Shared.Models
-
Inside the @code block, create an HTTP request to the table when the page has loaded and set the people Array as a DataSource to the Grid. Then specify the DataSourceType inside a GridDataSourceSettings object and set it as a property of the Grid.
Note that setting the DataType of the Columns is not mandatory, but it is recommended if you plan to use the Smart.Grid's Filtering & Sorting functionalities@page "/" @inject HttpClient Http @using System.Net.Http.Json @using Smart.Blazor @using BlazorProject.Shared.Models <PageTitle>Index</PageTitle> <h1>Blazor Web Assembly with SQL!</h1> @if(@people != null){ <Grid DataSource="@people" DataSourceSettings="@dataSourceSettings">} <Columns> <Column DataField="Name" Label="Client Name"></Column> <Column DataField="Balance" Label="Acccount Balance"></Column> <Column DataField="City" Label="City"></Column> <Column DataField="Country" Label="Country"></Column> </Columns> </Grid> } else{ <p>Loading...</p> } @code{ GridDataSourceSettings dataSourceSettings = new GridDataSourceSettings() { DataFields = new List<IGridDataSourceSettingsDataField>() { new GridDataSourceSettingsDataField() { Name = "Id", DataType = GridDataSourceSettingsDataFieldDataType.Number }, new GridDataSourceSettingsDataField() { Name = "Name", DataType = GridDataSourceSettingsDataFieldDataType.String }, new GridDataSourceSettingsDataField() { Name = "Balance", DataType = GridDataSourceSettingsDataFieldDataType.Number }, new GridDataSourceSettingsDataField() { Name = "City", DataType = GridDataSourceSettingsDataFieldDataType.String }, new GridDataSourceSettingsDataField() { Name = "Country", DataType = GridDataSourceSettingsDataFieldDataType.String } }, DataSourceType = GridDataSourceSettingsDataSourceType.Array }; Person[] people; protected override async Task OnInitializedAsync() { people = await Http.GetFromJsonAsync<Person[]>("/api/Clients/Get"); } }
![Grid bound to SQL](sql-grid.png)
CRUD with Blazor Grid
Once Blazor is connected with the database, it is not difficult to create a CRUD application using Smart.Grid
Add Create functionality
Create a "Add new row" Button. Then create an
AddRow()
function that creates a new person and then fetches the updated SQL Table:<Button OnClick="AddRow">Add new row</Button> ..... @code{ ..... private async Task AddRow() { Person person = new Person() { Name = "John", Balance = 1000, City = "Paris", Country = "France" }; await Http.PostAsJsonAsync("/api/Clients/Create", person); people = await Http.GetFromJsonAsync<Person[]>("/api/Clients/Get"); } }
Add Delete Functionality
Add a "Delete last row" Button. Then create a
DeleteLastRow()
function that removes the last SQL Record and then fetches the updated SQL Table:<Button OnClick="DeleteLastRow">Delete row</Button> ..... @code{ ..... private async Task DeleteLastRow() { int lastId = people[people.Length - 1].Id; await Http.DeleteAsync("api/Clients/Delete/" + lastId); people = await Http.GetFromJsonAsync<Person[]>("/api/Clients/Get"); } }
Add Update Functionality
To add Update functionality, first enable Grid Editing using the
Editing
property. We will use the OnEndEdit Event to update the SQL Table after every change:<Grid @ref="@grid" DataSource="@people" DataSourceSettings="@dataSourceSettings" OnEndEdit="OnEndEdit" Editing="@editing"> <Columns> <Column DataField="Name" Label="Client Name"></Column> <Column DataField="Balance" Label="Acccount Balance"></Column> <Column DataField="City" Label="City"></Column> <Column DataField="Country" Label="Country"></Column> </Columns> </Grid> @code{ Grid grid; GridEditing editing = new GridEditing() { Enabled = true, Mode = GridEditingMode.Cell }; ..... }
Create a new
OnEndEdit()
function. Using the Event.detail, get the values of the edited row and use theEdit
HTTP request to make changes in the SQL Table:private async Task OnEndEdit(Event ev) { GridEndEditEventDetail EventDetail = ev["Detail"]; dynamic Editedrow = JObject.Parse((await grid.GetRowData(EventDetail.Row)).ToString()); int EditedId = (Editedrow.Id).ToObject<int>(); string EditedName = (Editedrow.Name).ToObject<string>(); double EditedBalance = (Editedrow.Balance).ToObject<double>(); string EditedCity = (Editedrow.City).ToObject<string>(); string EditedCountry = (Editedrow.Country).ToObject<string>(); Person person = new Person() { Id = EditedId, Name = EditedName, Balance = EditedBalance, City = EditedCity, Country = EditedCountry }; await Http.PutAsJsonAsync("/api/Clients/Edit", person); }
We can see that the changes are reflected in the SQL Table: