Author: Jeremy Morgan
All Hands on Tech LIVE is happening NOW!
Follow us on Twitch!
Part Two: SQLite Database setup
In this portion of the tutorial, we’re going to create a database model, and set up the context. Then we’ll connect to it. Because we’re using Entity Framework Core, you won’t need to write a line of SQL for this. We’ll be writing C# classes to create the data structure. So let’s get started with creating a database model.
Part of a series:
Part One
- Set up a new Blazor project
- Create a SQLite database
Part Two (This page)
Part Three
- Add services
- Create the initial migration
- Seed the database
Part Four
- Build the CRUD operations
- Create
- Read
- Update
- Delete
- Summary
3. Creating the database model
We’re going to do a model-first (sometimes called code-first) migration for Entity Framework. This means we create what we want the database to look like in code. Then Entity Framework Core will turn that into SQL and Databases and all that fun stuff.
Creating databases first is fine as well. I did that for many years, but these days I have found building out the models in code to be faster and easier, especially if it’s a greenfield project (starting something brand new).
In our data folder, let’s create a new class. Name the file Employee.cs.
We want to store:
- First name
- Last name
- Email address
- Department
- Avatar (picture)
We can easily represent that in a class like this:
namespace Employees.Data
{
public class Employee
{
public int Id { get; set; }
public string? FirstName { get; set; }
public string? LastName { get; set; }
public string? Email { get; set; }
public string? Department { get; set; }
public string? Avatar { get; set; }
}
}
This POCO (Plain old Class Object) will store all this data for us. We’ll use Entity Framework Core to generate SQL tables for us. Keep reading to find out how. Next, we’ll create a database context.
4. Creating the database context
In Entity Framework Core, a database context is a fancy name for an object that controls access to the database. This object manages the life cycle of all the entities and generates SQL for you in the background. If you’ve used Entity Framework in the past, you know how awesome this is. If you haven’t, you’re about to see how easily tons of database code are compacted into something simple, elegant, and clean.
Create a new file named EmployeeContext.cs in the ‘Data’ folder.
We’ll add a reference to Entity Framework Core ()
And we’ll extend the DbContext class:
public class EmployeeDataContext : DbContext
Then we’ll drop in some code to read the configuration:
protected readonly IConfiguration Configuration;
public EmployeeDataContext(IConfiguration configuration)
{
Configuration = configuration;
}
This will read the connection string we’ll create in a few minutes.
Next, we need to create an OnConfiguring method. This method is called when the context is created and connects to our database. In this case we will set the option to connect to SQLite. We do this with an options.UseSqlite() call:
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlite(Configuration.GetConnectionString("EmployeeDB"));
}
Finally, we’ll create a DBSet as a temporary space to hold our Employee entities (instances of the Employee class we created above).
public DbSet<Employee> Employees{ get; set; }
The final class will look like this:
using Microsoft.EntityFrameworkCore;
namespace Employees.Data
{
public class EmployeeDataContext : DbContext
{
protected readonly IConfiguration Configuration;
public EmployeeDataContext(IConfiguration configuration)
{
Configuration = configuration;
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlite(Configuration.GetConnectionString("EmployeeDB"));
}
public DbSet<Employee> Employees { get; set; }
}
}
This will create the context we need to manipulate our data easily.
5. Connecting to the SQLite Database
Connecting to our SQLite database couldn’t be easier. We created a database earlier in Data\Employees.db.
Open up appsettings.json and add the following lines before or after the “Logging” section:
"ConnectionStrings": {
"EmployeeDB": "Data Source=Data\\Employees.db"
},
This will connect to our Employees DB with the context we just built.
Here’s what my appsettings.json looks like:
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
},
"ConnectionStrings": {
"EmployeeDB": "Data Source=Data\\Employees.db"
},
"AllowedHosts": "*"
}
This is all you’ll need to connect to your SQLite database! The implmentation is very similar to setting up a SQL server connection, except we are simply referencing a file and loading that.
Next up in Part three we’ll set up our application to load Entity Framework Core, create our first migration and seed the database with sample data.