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.1 | Microsoft.EntityFrameworkCore.Tools |
1.1.2 | Microsoft.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