SQLite Databases in UWP Applications


Click here to change the theme.

SQLite is shipping with the Windows 10 Anniversary edition (yes, it is now included with Windows) and Microsoft recommendeds it for all Universal Windows Platform (UWP) application local data storage needs (see Data Access in Universal Windows Platform (UWP) Apps - Building Apps for Windows).

The following describes how to add SQLite to a blank UWP project and use the Entity Framework for a SQLite database. We are using Entity Framework Code First (as described in Introduction to Entity Framework) to create and use a model for the database. This article began as notes for myself. This is intended as a quick reminder of the procedure so if it is not complete enough then look at the original walkthrough at Getting Started with EF Core on Universal Windows Platform (UWP) with a New Database. Unlike that walkthrough, however, I do not use the NuGet Package Manager Console to install the packages.

Begin by creating a blank UWP project.

Upgrade and Install NuGet Packages

After creating the project, go to "Tools" | "NuGet Package Manager" | "Manage NuGet Packages for Solution". Click on "Microsoft.NETCore.UniversalWindowsPlatform". Entity Framework requires version 5.2.2 or greater. If the version shown in the right side is less than 5.2.2 then click the drop-down for version and choose version 5.2.2 or greater. Then click on "Install". It will take a minute or more to complete; there will be a period when nothing happens. It will probably be complete soon after you accept the licnese agreement.

Then, again in the NuGet Package Manager, click on "Browse" in the upper-left then enter "Microsoft.EntityFrameworkCore" into the search box. Install each of the following:

  • Microsoft.EntityFrameworkCore.Sqlite
  • Microsoft.EntityFrameworkCore.Tools

Be careful, since there are some packages with very similar names. Also, the version for them will probably default to 2.0.0 and that version might not work. At the time of the writing of this article, UWP was not yet updated to support .NET Standard 2.0. If they won't install then in the drop-down choose the version shown in the following table and install that version.

1.1.1Microsoft.EntityFrameworkCore.Tools
1.1.2Microsoft.EntityFrameworkCore.Sqlite

Version 2.0.0 of Microsoft.EntityFrameworkCore.Sqlite might install but then you will get an error later saying that UseSqlite is not found.

Create the Model

Most of what is done here is typical of Entity Framework Code First and other tutorials and references can be used for that. See Entity Framework Code First to a New Database for another description of what we are doing.

Add a using for Microsoft.EntityFrameworkCore wherever it is needed.

The code for Code First typically consists of a class derived from DbContext that corresponds to a database that has DbSet members for the tables. Each table is a public property, one for each table. The DbContext-derived class and the classes for each table would typically be in separate files but for these samples one file is used for the classes.

Create a class, such as BloggingContext, derived from DbContext. You can name the file Model.cs or whatever you want. The DbContext-derived class would often be refered to as the context. The context class has an "OnConfiguring" member as in the following:

public class BloggingContext : DbContext
{
	public DbSet<Blog> Blogs { get; set; }
	public DbSet<Post> Posts { get; set; }

	protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
	{
		optionsBuilder.UseSqlite("Data Source=blogging.db");
	}
}

The following are the classes for each of the two tables in the sample. These can be put in the same file as the BloggingContext class but in a larger application these would be in separate files.

public class Blog
{
	public int BlogId { get; set; }
	public string Url { get; set; }

	public List<Post> Posts { get; set; }
}

public class Post
{
	public int PostId { get; set; }
	public string Title { get; set; }
	public string Content { get; set; }

	public int BlogId { get; set; }
	public Blog Blog { get; set; }
}

Note that the Posts member of the Blog class is a list of Post instances; that creates a relationship in the database.

Create the Database

We use NuGet to execute Entity Framework to generate code that will create a database if it does not exist. In the NuGet Package Manager Console run:

Add-Migration MyFirstMigration

Entity Framework Migrations are not documneted very well; see Code First Migrations documentation. The name "Add-Migration" is a bit misleading; it is more of an "Apply-Migration" command. The name "MyFirstMigration" can be anything meaningful to you. In the future, when changes are made to the model in code, use the Add-Migration command to apply the changes to the database. The name would be a different name, whatever is meaningful to you. You might see articles and other things saying something about "Enable-Migration" and "Update-Database"; apparently they do not apply to UWP applications. If you try to use "Update-Database" in a UWP application then you will get a message saying not to do that; it says to use "DbContext.Database.Migrate()" as in the following.

Then add code to create the local database when the application starts the first time it runs. In the code for App.xaml add a using (for Microsoft.EntityFrameworkCore) then add the following (change "BloggingContext" to the name of your context) to the constructor:

using (var db = new BloggingContext())
    {
        try { db.Database.Migrate(); }
        catch (Exception ex)
        {
            // do something
        }
    }

The try/catch is important since if the migration fails then the debugger will be unhappy and its complaints will be very confusing. SQLite has many limitations (see SQLite EF Core Database Provider Limitations) so it is very possible you will have a migration that will fail.

Use the Model

In the XAML code-behind, use something as in the following for the Page_Loaded event to bind a table to the XAML, where "Blogs" is an item (such as a ListView) in your XAML:

private void Page_Loaded(object sender, RoutedEventArgs e)
{
    using (var db = new BloggingContext())
    {
        Blogs.ItemsSource = db.Blogs.ToList();
    }
}

You can now create an item in the XAML, such as a ListView, to show the data. See my Creatiung Grids With Evenly-Sized Columns for some help with that.

Something such as the following will add a record; this would typically be done in a button click event:

using (var db = new BloggingContext())
{
var blog = new Blog { Url = NewBlogUrl.Text };
db.Blogs.Add(blog);
db.SaveChanges();
Blogs.ItemsSource = db.Blogs.ToList();
}

References


Hosted by WinHost.com.