Blazor: Create and Use MS SQL Database with Entity Framework Core

This example uses .NET Core 3.1 but is compatible with .NET 5, .NET 6 and .NET 8. The NuGet package versions should match the target version of .NET.

Have SQL Server installed and running on the development machine or on the network and have Visual Studio 2019 open with a new WebAPI project or Blazor WebAssembly project created with the "ASP.NET Core hosted" option.

Install NuGet Packages

Make sure that the NuGet packages Microsoft.EntityFrameworkCore, Microsoft.EntityFrameworkCore.SqlServer and Microsoft.EntityFrameworkCore.Tools are installed in the server project.

Also, make sure that the NuGet package System.ComponentModel.Annotations is installed in the shared project.

SQL Server Connection String

Modify the server project's appsettings.json file to include a ConnectionStrings property. The database does not need to exist.

{
  "ConnectionStrings": {
	"Default": "Server=LOCALHOST\\SQLEXPRESS;Database=ACME;Trusted_Connection=True;MultipleActiveResultSets=True"
  },
  "Logging": {
	"LogLevel": {
	  "Default": "Information",
	  "Microsoft": "Warning",
	  "Microsoft.Hosting.Lifetime": "Information"
	}
  },
  "AllowedHosts": "*"
}

Add Customer Class to Project

In the root of the shared project, create a file named Customer.cs.

// Customer.cs
using System;
using System.ComponentModel.DataAnnotations;

namespace BlazorDatabase.Shared
{
	[Serializable]
	public class Customer
	{
		[Key]
		public Guid id { get; set; } // CAN USE AN INTEGER INSTEAD OF A GUID

		[Required]
		public string name { get; set; }

		[Required]
		public string address { get; set; }

		[Required]
		public string zip { get; set; }
	}
}

Create the Application Database Context

In the root of the server project, create a file named AppDbContext.cs.

// AppDbContext.cs
using Microsoft.EntityFrameworkCore;
using BlazorDatabase.Shared;

public class AppDbContext : DbContext
{
	public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }
	public DbSet<Customer> Customer { get; set; }
}

Modify Startup.cs

Modify Startup.cs as follows.

// Startup.cs
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.ResponseCompression;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using System.Linq;

using Microsoft.EntityFrameworkCore; // ########## THIS LINE ADDED ##########

namespace BlazorDatabase.Server
{
	public class Startup
	{
		public Startup(IConfiguration configuration)
		{
			Configuration = configuration;
		}

		public IConfiguration Configuration { get; }

		// This method gets called by the runtime. Use this method to add services to the container.
		// For more information on how to configure your application, visit https://go.microsoft.com/fwlink/?LinkID=398940
		public void ConfigureServices(IServiceCollection services)
		{

			// ########### FOLLOWING FOUR LINES ADDED ###########
			services.AddDbContext<AppDbContext>(options =>
			{
				options.UseSqlServer(Configuration.GetConnectionString("Default"));
			});
			// ########### END OF ADDED CODE ###########


			services.AddControllersWithViews();
			services.AddRazorPages();
		}

		// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
		public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
		{
			if (env.IsDevelopment())
			{
				app.UseDeveloperExceptionPage();
				app.UseWebAssemblyDebugging();
			}
			else
			{
				app.UseExceptionHandler("/Error");
			}

			app.UseBlazorFrameworkFiles();
			app.UseStaticFiles();

			app.UseRouting();

			app.UseEndpoints(endpoints =>
			{
				endpoints.MapRazorPages();
				endpoints.MapControllers();
				endpoints.MapFallbackToFile("index.html");
			});
		}
	}
}

Create/Update the SQL Server Database

Now, enough has been done to create the database. Open the Package Manager Console (Tools > NuGet Package Manager > Package Manager Console) and enter the following two PM commands. NOTE: If the following commands do not work on Windows 7 (with a ScriptHalted error) then the PowerShell version may be too old. Install the latest version from HERE. These two commands will create the database and the table for the customers.

PM> add-migration AddCustomerToDb
PM> update-database

The results should look something like this:

PM> add-migration AddCustomerToDb
Build started...
Build succeeded.
To undo this action, use Remove-Migration.
PM> update-database
Build started...
Build succeeded.
Done.

Create the Web API / RESTful API.

Add a new class file in the server project's Controllers folder named CustomersController.cs

// CustomersController.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using BlazorDatabase.Shared;
using Microsoft.EntityFrameworkCore.ChangeTracking;

namespace BlazorDatabase.Server.Controllers
{
	[ApiController]
	[Route("api/[controller]")]
	public class CustomersController : ControllerBase
	{
		private readonly AppDbContext db;

		public CustomersController(AppDbContext db)
		{
			this.db = db;
		}

		[HttpPut("{id}")]
		public async Task<Customer> Put(Guid id, [FromBody] Customer cust)
		{
			var edit = await db.Customer.FindAsync(id);
			if(edit != null)
			{
				edit.name = cust.name;
				edit.address = cust.address;
				edit.zip = cust.zip;
				await db.SaveChangesAsync();
			}
			return edit;
		}

		[HttpDelete("{id}")]
		public async Task<Customer> Delete(Guid id)
		{
			var delete = await db.Customer.FindAsync(id);
			if (delete != null)
			{
				db.Customer.Remove(delete);
				await db.SaveChangesAsync();
			}
			return delete;
		}

		[HttpPost]
		public async Task<Customer> Post([FromBody] Customer create)
		{
			create.id = Guid.NewGuid();
			EntityEntry<Customer> cust = await db.Customer.AddAsync(create);
			await db.SaveChangesAsync();
			return cust.Entity;
		}

		[HttpGet]
		public async Task<IEnumerable<Customer>> Get(string name)
		{
			return await Task.Factory.StartNew<IEnumerable<Customer>>(() =>
			{
				if (string.IsNullOrEmpty(name))
					return db.Customer;
				else
					return db.Customer.Where(x => x.name.Contains(name));
			});
		}

		[HttpGet("{id}")]
		public async Task<Customer> Get(Guid id)
		{
			return await db.Customer.FindAsync(id);
		}
	}
}

Create a Data Repeater Component

In the client project, create a data repeater component in the Components folder named DataRepeaterComponent.razor. Create a Components folder if necessary.

@typeparam TItem

@if (Items != null)
{
	@foreach (var item in Items)
	{
		@Row(item)
	}
}

@code {

	[Parameter]
	public RenderFragment<TItem> Row { get; set; }

	[Parameter]
	public List<TItem> Items { get; set; }
}

Create the Customers.razor Page

In the client project, create a new page in the Pages folder named Customers.razor.

@page "/customers"
@inject HttpClient Http
@using BlazorDatabase.Shared
@using BlazorDatabase.Client.Components
@using System.Reflection

<h1>Customers</h1>

<form onsubmit="return false;">
	<div class="input-group input-group-md mb-2">
		<span class="input-group-text">Search by Name</span>
		<input type="text" class="form-control" @bind-value="filter" />
		<button class="btn btn-primary" @onclick="Search">Search</button>
	</div>
</form>

@if (custs == null)
{
	<p><em>Loading...</em></p>
}

<div class="table-responsive">
	<table class="table table-hover table-striped">
		<thead>
			<tr><th>name</th><th>address</th><th colspan="2">zip</th></tr>
		</thead>
		<tbody>
			<DataRepeaterComponent Items="custs">
				<Row Context="cust">
					<tr>
						<td>
							<input type="text" class="form-control" value="@cust.name" @onchange="@((ChangeEventArgs e) => Save(e, cust, "name"))" />
						</td>
						<td>
							<input type="text" class="form-control" value="@cust.address" @onchange="@((ChangeEventArgs e) => Save(e, cust, "address"))" />
						</td>
						<td>
							<input type="text" class="form-control" value="@cust.zip" @onchange="@((ChangeEventArgs e) => Save(e, cust, "zip"))" />
						</td>
						<td>
							<button class="btn btn-sm btn-danger" @onclick="@(() => Delete(cust.id))">delete</button>
						</td>
					</tr>
				</Row>
			</DataRepeaterComponent>
		</tbody>
	</table>
</div>

<form class="mt-5" onsubmit="return false;">
	<div class="input-group input-group-md mb-2">
		<span class="input-group-text">Name</span>
		<input type="text" class="form-control" autocomplete="off" required @bind-value="newcust.name" />
	</div>
	<div class="input-group input-group-md mb-2">
		<span class="input-group-text">Address</span>
		<input type="text" class="form-control" autocomplete="off" required @bind-value="newcust.address" />
	</div>
	<div class="input-group input-group-md mb-2">
		<span class="input-group-text">Zip</span>
		<input type="text" class="form-control" autocomplete="off" required @bind-value="newcust.zip" />
		<button class="btn btn-success" @onclick="Add">Add</button>
	</div>
</form>

@code {

	private List<Customer> custs;
	private Customer newcust = new Customer();
	private string filter;

	protected override async Task OnInitializedAsync()
	{
		await base.OnInitializedAsync();

		custs = await Http.GetFromJsonAsync<List<Customer>>("/api/customers");
	}

	private async Task Search()
	{
		custs = await Http.GetFromJsonAsync<List<Customer>>($"/api/customers?name={Uri.EscapeDataString(filter)}");
	}

	private async Task Add()
	{
		using (var msg = await Http.PostAsJsonAsync<Customer>("/api/customers", newcust, System.Threading.CancellationToken.None))
		{
			if (msg.IsSuccessStatusCode)
			{
				custs.Add(await msg.Content.ReadFromJsonAsync<Customer>());
				newcust.name = newcust.address = newcust.zip = null;
			}
		}
	}

	private async Task Save(ChangeEventArgs e, Customer cust, string propField)
	{
		PropertyInfo pinfo = typeof(Customer).GetProperty(propField);
		pinfo.SetValue(cust, e.Value);

		using (var msg = await Http.PutAsJsonAsync<Customer>($"/api/customers/{cust.id}", cust, System.Threading.CancellationToken.None))
		{
			if (msg.IsSuccessStatusCode)
			{
			}
			else
			{
				// DO SOMETHING
			}
		}
	}

	private async Task Delete(Guid id)
	{
		using (var msg = await Http.DeleteAsync($"/api/customers/{id}"))
		{
			if (msg.IsSuccessStatusCode)
			{
				int i;
				for (i = 0; i < custs.Count && custs[i].id != id; i++) ;
				custs.RemoveAt(i);
			}
		}
	}
}

Try the Customers Page

Run the application and navigate to the /customers url to add, edit, delete and search customers in the newly-created database.

Coding Video

https://youtu.be/NXA5e5IIFqk

Comment