ASP.NET Core 2: Using SQLite as a light weight database

SQLite370.svg.pngAzure is a great place to store your data of any size, but sometimes that may not be possible for a number of reasons. In this article I’ll show you how to use Microsoft Entity Framework Core (EF) and SQLite to create and store a small amount of data.

This code is a bare-bones example. It doesn’t include important features like error handling or DI.

Note: There is no database file. EF Core will create the SQLite database file for you.

Step 1: Create any simple ASP.NET Core site via the (File | New) menu

Step 2: Add these NuGet packages

  1. Microsoft.EntityFrameworkCore
  2. Microsoft.EntityFrameworkCore.Sqlite
  3. Microsoft.EntityFrameworkCore.Sqlite.Core

Step 3: DbContext

All of the magic takes place in a single simple data class. Add this to your solution:

public class SimpleDataStorageDbContext
{
    public SimpleDataStorage(){}
 
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        //Set the filename of the database to be created
        optionsBuilder.UseSqlite("Data Source=db.sqlite");
    }
 
    public DbSet<Cat> Cats { getset; }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
 
        //Define the Table(s) and References to be created automatically
        modelBuilder.Entity<Cat>(b =>
        {
            b.HasKey(e => e.Id);
            b.Property(e => e.Id).ValueGeneratedOnAdd();
            b.Property(e => e.Name).IsRequired().HasMaxLength(255);
            b.ToTable("Cats");
        });
    }
}

What’s very interesting here is that the database doesn’t exist, the class SimpleDataStorage tells EF how and where to create it.

Step 4: Create the database and tables in Startup.cs

public class Startup
{
   public Startup()
    {
        using (var client = new SimpleDataStorage())
        {
            //Create the database file at a path defined in SimpleDataStorage
            client.Database.EnsureCreated();
            //Create the database tables defined in SimpleDataStorage
            client.Database.Migrate();
        }
    }

Step 5: Add Cats to the database!

We can now access the database anywhere in code

public IActionResult Index(SimpleDataStorage db)
{
    for (int i = 0; i < 1000; i++)
    {
        db.Cats.Add(new Cat() {Name = "Caty McCat: " + i}); 
    }
    db.SaveChanges(true);
    return View();
}


Resources: A good SQLite database editor

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s