In this article, we are going to discuss how we can perform CRUD operations in the Blazor server app without Entity Framework.
In this post, We will do insert, update, and delete(Crud Operation) functionality in the Blazor app without using Entity Framework i.e we are going to use the ADO.NET for database operation, we will use SqlConnection and SQLCommand for performing the DB operation.
So Let’s start, step by step, We will create a basic page where we will list teams with add button at the top for adding new teams, an edit button for updating existing team detail, and a delete button to delete any team record with a notification alert so that user can see if record inserted or not.
we can divide our task in the below step
- Creating New Empty Blazor app Project in Visual Studio 2022
- Creating a DB Table for performing an operation in the database
- Adding Blazor components
- Install Radzen Blazor for showing alert notification
- Create Model decorate data annotation attribute for the validation
- Create a Data Access layer for database logic
- Implement Logic crud operation
- Build and run Project
So let’s start
Step 1. Creating a New Empty Blazor app Project in Visual Studio 2022
First create a Blazor Application, open visual studio 2022, and add an empty Blazor project.
Step 2. Create SQL table
Let’s create the database table, for our DB operation. I have created a table called FootballTeam which is got, the Id, TeamName, TeamCaptain, and CaptainEmail columns.You can copy the table script from the blow code snip for practice purposes.
CREATE TABLE [dbo].[FootballTeam]( [Id] [int] IDENTITY(1,1) NOT NULL, [TeamName] [nvarchar](max) NULL, [TeamCaptain] [nvarchar](max) NULL, [CaptainEmail] [nvarchar](max) NULL, CONSTRAINT [PK_FootballTeam] PRIMARY KEY CLUSTERED ( [Id] ASC ) ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO
Step 3. Adding Blazor components
Right, Click on “Pages” folder =>Add=>Razor component
Now, add 3 Blazor components inside a folder named “Pages”.
Now, add 3 Blazor components inside a folder named “Pages”.
- AllTeams.razor
- AddTeam.razor
- UpdateTeam.razor
Step4.Install Radzen Blazor for showing alert notification
As we want to show the notification alert on every action for that we are going to use the radzen blazor component. you learn more from here https://blazor.radzen.com/get-started on how to install the radzen blazor component in your project.
Open NuGet Package Manager
and install Radzen.Blazor
and add reference of CSS and js file in _layout.cshtml.
<link rel="stylesheet" href="_content/Radzen.Blazor/css/default-base.css">
<script src="_content/Radzen.Blazor/Radzen.Blazor.js"></script>So final _layout.cshtml
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<base href="~/" />
<link rel="stylesheet" href="css/bootstrap/bootstrap.min.css" />
<link href="css/site.css" rel="stylesheet" />
<link href="BlazorAppTestApp.styles.css" rel="stylesheet" />
<link rel="stylesheet" href="_content/Radzen.Blazor/css/default-base.css">
<component type="typeof(HeadOutlet)" render-mode="ServerPrerendered" />
</head>
<body>
@RenderBody()
<div id="blazor-error-ui">
<environment include="Staging,Production">
An error has occurred. This application may no longer respond until reloaded.
</environment>
<environment include="Development">
An unhandled exception has occurred. See browser dev tools for details.
</environment>
<a href="" class="reload">Reload</a>
<a class="dismiss">🗙</a>
</div>
<script src="_framework/blazor.server.js"></script>
<script src="_content/Radzen.Blazor/Radzen.Blazor.js"></script>
</body>
</html>
Add notification component in MainLayout.razor
@using Radzen.Blazor
@inherits LayoutComponentBase
<PageTitle>BlazorAppTestApp</PageTitle>
<RadzenNotification />
<div class="page">
<div class="sidebar">
<NavMenu />
</div>
<main>
<div class="top-row px-4">
<a href="https://docs.microsoft.com/aspnet/" target="_blank">About</a>
</div>
<article class="content px-4">
@Body
</article>
</main>
</div>
Add notification component in MainLayout.razor
var builder = WebApplication.CreateBuilder(args);
// Add services to the container.
builder.Services.AddRazorPages();
builder.Services.AddServerSideBlazor();
builder.Services.AddSingleton<WeatherForecastService>();
builder.Services.AddScoped<NotificationService>();
var app = builder.Build();
// Configure the HTTP request pipeline.
if (!app.Environment.IsDevelopment())
{
app.UseExceptionHandler("/Error");
app.UseHsts();
}
app.UseHttpsRedirection();
app.UseStaticFiles();
app.UseRouting();
app.MapBlazorHub();
app.MapFallbackToPage("/_Host");
app.Run();Step5.Create Model decorate data annotation attribute for the validation
using System.ComponentModel.DataAnnotations;
namespace BlazorAppTestApp.Data
{
public class FootballTeam
{
public int Id { get; set; }
[Required]
public string TeamName { get; set; }
[Required]
public string TeamCaptain { get; set; }
[Required]
[EmailAddress]
public string CaptainEmail { get; set; }
}
}
Step6.Create a Data Access layer for database logic
using System.Data.SqlClient;
namespace BlazorAppTestApp.Data
{
public class DbCrudOption
{
static string constr = "Data Source=SQLEXPRESS01;Initial Catalog=TestDb;User ID=apps;Password=apps@1234";
public static List<FootballTeam> GetAllHockeyTeams()
{
List<FootballTeam> footballTeams = new List<FootballTeam>();
string query = "SELECT * FROM FootballTeam";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
footballTeams.Add(new FootballTeam
{
Id = Convert.ToInt32(sdr["Id"]),
TeamName = Convert.ToString(sdr["TeamName"]),
TeamCaptain = Convert.ToString(sdr["TeamCaptain"]),
CaptainEmail = Convert.ToString(sdr["CaptainEmail"]),
});
}
}
con.Close();
}
}
if (footballTeams.Count == 0)
{
footballTeams.Add(new FootballTeam());
}
return footballTeams;
}
public static FootballTeam GetTeamById(int Id)
{
FootballTeam footballTeam = new FootballTeam();
string query = "SELECT * FROM FootballTeam where Id=" + Id;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
footballTeam = new FootballTeam
{
Id = Convert.ToInt32(sdr["Id"]),
TeamName = Convert.ToString(sdr["TeamName"]),
TeamCaptain = Convert.ToString(sdr["TeamCaptain"]),
CaptainEmail = Convert.ToString(sdr["CaptainEmail"]),
};
}
}
con.Close();
}
}
return footballTeam;
}
public static bool CreateTeam(FootballTeam footballTeam)
{
using (SqlConnection con = new SqlConnection(constr))
{
//inserting Patient data into database
string query = "insert into FootballTeam values (@TeamName, @TeamCaptain, @CaptainEmail)";
using (SqlCommand cmd = new SqlCommand(query, con))
{
cmd.Connection = con;
cmd.Parameters.AddWithValue("@TeamName", footballTeam.TeamName);
cmd.Parameters.AddWithValue("@TeamCaptain", footballTeam.TeamCaptain);
cmd.Parameters.AddWithValue("@CaptainEmail", footballTeam.CaptainEmail);
con.Open();
int count=cmd.ExecuteNonQuery();
if(count>0)
{
return true;
}
con.Close();
}
}
return false;
}
public static bool UpdateTeam(FootballTeam footballTeam)
{
string query = "UPDATE FootballTeam SET TeamName = @TeamName, TeamCaptain = @TeamCaptain,CaptainEmail=@CaptainEmail Where Id =@Id";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
cmd.Parameters.AddWithValue("@TeamName", footballTeam.TeamName);
cmd.Parameters.AddWithValue("@TeamCaptain", footballTeam.TeamCaptain);
cmd.Parameters.AddWithValue("@CaptainEmail", footballTeam.CaptainEmail);
cmd.Parameters.AddWithValue("@Id", footballTeam.Id);
con.Open();
if(cmd.ExecuteNonQuery()>0)
{
return true;
}
con.Close();
}
}
return false;
}
public static bool DeleteTeam(int Id)
{
using (SqlConnection con = new SqlConnection(constr))
{
string query = "Delete FROM FootballTeam where Id='" + Id + "'";
using (SqlCommand cmd = new SqlCommand(query, con))
{
con.Open();
if (cmd.ExecuteNonQuery() > 0)
{
return true;
}
con.Close();
}
}
return false;
}
}
}
Step7.Implement Logic crud operation
Let’s write the logic for showing a list of teams in
AllTeams.razor
@page “/allteams” is used for defining the route of the page.
@page "/allteams"
@using BlazorAppTestApp.Data
@using Radzen
<h3>AllTeams</h3>
<a class="btn btn-primary" href="/addTeam">Add</a>
<table class="table datatable" id="datatable">
<thead>
<tr>
<th>Team Name</th>
<th>'s Name</th>
<th>Captain's Email</th>
<th width="150px">Action</th>
</tr>
</thead>
<tbody>
@if (footballTeams != null && footballTeams.Any())
{
foreach (var item in footballTeams)
{
<tr>
<td>@item.TeamName</td>
<td>@item.TeamCaptain</td>
<td>@item.CaptainEmail</td>
<td>
<a href="/updateteam/@item.Id">Edit</a>
<button @onclick="() => DeleteTeam(item.Id)">Delete</button>
</td>
</tr>
}
}
</tbody>
</table>
@code {
public List<FootballTeam> footballTeams { get; set; } = new List<FootballTeam>();
[Inject]
public NotificationService NotificationService { get; set; }
protected override async Task OnInitializedAsync()
{
await LoadData();
}
protected async Task LoadData()
{
footballTeams = DbCrudOption.GetAllHockeyTeams();
if (footballTeams == null)
{
footballTeams = new List<FootballTeam>();
}
}
protected async Task DeleteTeam(int Id)
{
if (DbCrudOption.DeleteTeam(Id))
{
await LoadData();
this.StateHasChanged();
NotificationService.Notify(new NotificationMessage { Severity = NotificationSeverity.Success, Summary = "deleted Successfully", Duration = 4000 });
}
else
{
NotificationService.Notify(new NotificationMessage { Severity = NotificationSeverity.Error, Summary = "Something went wrong please try again", Duration = 4000 });
}
}
}
add new option in side menu for showing list page.
open NavMenu.razor and add link for AllTeams.razor.
<div class="top-row ps-3 navbar navbar-dark">
<div class="container-fluid">
<a class="navbar-brand" href="">BlazorAppTestApp</a>
<button title="Navigation menu" class="navbar-toggler" @onclick="ToggleNavMenu">
<span class="navbar-toggler-icon"></span>
</button>
</div>
</div>
<div class="@NavMenuCssClass" @onclick="ToggleNavMenu">
<nav class="flex-column">
<div class="nav-item px-3">
<NavLink class="nav-link" href="" Match="NavLinkMatch.All">
<span class="oi oi-home" aria-hidden="true"></span> Home
</NavLink>
</div>
<div class="nav-item px-3">
<NavLink class="nav-link" href="counter">
<span class="oi oi-plus" aria-hidden="true"></span> Counter
</NavLink>
</div>
<div class="nav-item px-3">
<NavLink class="nav-link" href="allteams">
<span class="oi oi-plus" aria-hidden="true"></span> Team List
</NavLink>
</div>
<div class="nav-item px-3">
<NavLink class="nav-link" href="fetchdata">
<span class="oi oi-list-rich" aria-hidden="true"></span> Fetch data
</NavLink>
</div>
</nav>
</div>
@code {
private bool collapseNavMenu = true;
private string? NavMenuCssClass => collapseNavMenu ? "collapse" : null;
private void ToggleNavMenu()
{
collapseNavMenu = !collapseNavMenu;
}
}
Write logic for adding new record AddTeam.razor
@page "/addTeam"
@using BlazorAppTestApp.Data;
@using Radzen
@inject NavigationManager NavigationManager;
<h3>AddTeam</h3>
@if (addTeam != null)
{
<EditForm Model="@addTeam" OnValidSubmit="@CreateTeam">
<DataAnnotationsValidator />
<ValidationSummary />
<div class="row">
<div class="col-sm-12 mb-3">
<label class="form-label">Team Name</label>
<InputText class="form-control" placeholder="Team Name" @bind-Value="addTeam.TeamName" />
<ValidationMessage For="@(() => addTeam.TeamName)" />
</div>
<div class="col-sm-12 mb-3">
<label class="form-label">Team Captain</label>
<InputText class="form-control" placeholder="Team Name" @bind-Value="addTeam.TeamCaptain" />
<ValidationMessage For="@(() => addTeam.TeamCaptain)" />
</div>
<div class="col-sm-12 mb-3">
<label class="form-label">Captain Email</label>
<InputText class="form-control" placeholder="Captain Email" @bind-Value="addTeam.CaptainEmail" />
<ValidationMessage For="@(() => addTeam.CaptainEmail)" />
</div>
</div>
<button type="submit" class="btn btn-success btn-ui">Submit</button>
</EditForm>
}
@code {
public FootballTeam addTeam { get; set; } = new FootballTeam();
[Inject]
public NotificationService NotificationService{ get; set; }
protected async void CreateTeam()
{
try
{
if(DbCrudOption.CreateTeam(addTeam))
{
NotificationService.Notify(new NotificationMessage { Severity = NotificationSeverity.Success, Summary = "Data Saved Successfully",Duration = 4000 });
NavigationManager.NavigateTo("/allteams");
}
else
{
NotificationService.Notify(new NotificationMessage { Severity = NotificationSeverity.Error, Summary = "Something Went wrong while making entry", Duration = 4000 });
}
}
catch (Exception ex)
{
}
}
}
Write logic for updating new record UpdateTeam.razor
@page "/updateteam/{Id:int}"
@using BlazorAppTestApp.Data
@using Radzen
<h3>UpdateTeam</h3>
@if (updateTeamModel != null)
{
<EditForm Model="@updateTeamModel" OnValidSubmit="@UpdateTeamDetail">
<DataAnnotationsValidator />
<ValidationSummary />
<div class="row">
<div class="col-sm-12 mb-3">
<label class="form-label">Team Name</label>
<InputText class="form-control" placeholder="Team Name" @bind-Value="updateTeamModel.TeamName" />
<ValidationMessage For="@(() => updateTeamModel.TeamName)" />
</div>
<div class="col-sm-12 mb-3">
<label class="form-label">Team Captain</label>
<InputText class="form-control" placeholder="Team Name" @bind-Value="updateTeamModel.TeamCaptain" />
<ValidationMessage For="@(() => updateTeamModel.TeamCaptain)" />
</div>
<div class="col-sm-12 mb-3">
<label class="form-label">Captain Email</label>
<InputText class="form-control" placeholder="Captain Email" @bind-Value="updateTeamModel.CaptainEmail" />
<ValidationMessage For="@(() => updateTeamModel.CaptainEmail)" />
</div>
</div>
<button type="submit" class="btn btn-success btn-ui">Submit</button>
</EditForm>
}
@code {
[Parameter]
public int Id { get; set; }
public FootballTeam updateTeamModel { get; set; } = new FootballTeam();
[Inject]
public NotificationService NotificationService { get; set; }
[Inject]
public NavigationManager NavigationManager{ get; set; }
protected override async Task OnInitializedAsync()
{
await LoadData();
}
protected async Task LoadData()
{
updateTeamModel = DbCrudOption.GetTeamById(Id);
}
protected async void UpdateTeamDetail()
{
try
{
updateTeamModel.Id = Id;
if (DbCrudOption.UpdateTeam(updateTeamModel))
{
NotificationService.Notify(new NotificationMessage { Severity = NotificationSeverity.Success, Summary = "updated Successfully", Duration = 4000 });
NavigationManager.NavigateTo("/allteams");
}
else
{
NotificationService.Notify(new NotificationMessage { Severity = NotificationSeverity.Error, Summary = "Something Went wrong while making entry", Duration = 4000 });
}
}
catch (Exception ex)
{
}
}
}Now build and run blazor app and perform the operation
Thank you!