May 3, 2016 2:00 pm

Data Access in Universal Windows Platform (UWP) Apps

SQLite-Hero-FINAL

SQLite version 3.11.2 is shipping with the Windows 10 Anniversary edition as part of the Universal Windows Platform (UWP) and is recommended for all UWP local data storage needs. This represents the first time an open source, third-party library, such as SQLite, has shipped as part of the SDK.

SQLite has become a popular database for mobile apps because…

  • It’s self-contained. It is just a code library without any additional dependencies. Unlike most databases, once you have referenced the SQLite library, you don’t need to do any further configuration.

1_SQLite

  • It has no database server. The client and the server run in the same process.
  • It has no license model. The creators put it in the public domain, so you can use and distribute it via your product with no strings attached.
  • It doesn’t support multiple users. It’s a storage system that has just one user and one database instance.
  • It is cross-platform and cross-architecture. It works well on the full range of devices in the Windows 10 family and is already a go-to solution for developers on other platforms. In other words, SQLite takes architecture and compatibility out of the data access layer equation for migrating your app to UWP.

In this post, we will touch on why SQLite works well on mobile platforms, discuss various ways you can consume SQLite in your C++ and C# UWAs, and look at some of the data developer tools our developer community has built.

SQLite API overview

SQLite databases can be created, updated, and deleted with the SQLite C APIs (we’ll also look at using C# to work with SQLite in the next section). Details of the SQLite C API can be found at the SQLite.org  page.

To gain a sound understanding of how SQLite works, work backwards from the main task of the SQL database, which is to evaluate SQL statements. There are two objects to keep in mind:

There are six interfaces to perform database operations on these objects. All of SQLite’s power is delivered by these six interfaces. That’s it.

These sets of APIs give developers tremendous control over storing, retrieving, and manipulating data in a SQLite database with granular result codes. For instance, a prepare statement can return an OK, an error, or an indication of misuse, allowing the developer to take a specific action based on each of these specific return codes. Often, developers might feel that they want to develop for the general case. In this circumstance, an ORM might be a more convenient way to go instead of using the vanilla C API.

Entity Framework Core: An Object Relation Mapper (ORM) for UWP apps

Object Relational Mappers (ORMs) allow developers to use strongly typed, domain-specific objects to work with relational data. Since Entity Framework has been a big hit with the .NET community, the team has created Entity Framework Core (EF Core), which can talk to SQLite via the SQLite ADO.NET provider for UWP. You can use it in UWP apps written in C#.

2_SQLite

For developers tasked with migrating enterprise apps to UWP, EF Core enables you to literally copy code over with only minor changes to the database context class. For developers starting an app from scratch, EF Core can save you time and simplify your code by abstracting out the specific database calls.

To get started with EF, open your solution in Visual Studio and go to Tools -> NuGet Package Manager -> Package Manager Console. From there, run…



Install-Package EntityFramework.SQLite –Pre


You will also want to get the commands. To do so, run…



Install-Package EntityFramework.Commands –Pre 


Alternatively, you can also use the Nuget Package Manager to install the EntityFramework.SQLite and EntityFramework.Commands packages. Just be sure to select the Include prerelease checkbox.

3_SQLite

From here, we’ll use the example of a sensor data collection app. The app samples and displays current ambient temperature and humidity data from sensors placed around the home. The app also features a setup experience to add additional sensors from the app. We can get the end-to-end data layer working with four steps:

  1. Create a data model.
  2. Create a database file.
  3. Bind the UI and the data model.
  4. Integrate the UI with the database.

First, we’ll create a new file that contains the database context and entity classes that define our sensor data model. Let’s call this model.cs.

When creating a database context, we can specify the database type we wish to use (SQLite for our purposes).



using Microsoft.Data.Entity; 
using System.Collections.Generic; 

namespace EFGetStarted.UWP 
{ 
    public class SensorContext : DbContext
    {
        public DbSet<Sensor> Sensors { get; set; }
        public DbSet<Ambience> AmbientDataSample { get; set; }
        
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlite("Filename=Sensors.db");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // Make SensorId required
            modelBuilder.Entity<Sensor>()
                .Property(b => b.SensorId)
                .IsRequired();

            // Make TimeStamp required
            modelBuilder.Entity<Ambience>()
                .Property(b => b.AmbienceId)
                .IsRequired();
        }
    }

    // These classes can be declared in individual files. Shown here for simplicity.

    public class Sensor
    {
        public Guid SensorId { get; set; }
        public string Location { get; set; }
        public List<Ambience> CurrentAmbientData { get; set; }
    }

    public class Ambience
    {
        public int AmbienceId { get; set; }
        public int TimeStamp { get; set; }
        public int Temp { get; set; }
        public int Humidity { get; set; }
    }
} 


Next, we create a database at app launch time by adding the highlighted code to App.xaml.cs.



public App() 
{ 
Microsoft.ApplicationInsights.WindowsAppInitializer.InitializeAsync( 
                Microsoft.ApplicationInsights.WindowsCollectors.Metadata | 
                Microsoft.ApplicationInsights.WindowsCollectors.Session); 
       this.InitializeComponent(); 
       this.Suspending += OnSuspending; 
 
// Before running the app for the first time, follow these steps:
// 1- Build -> Build the Project
// 2- Tools –> NuGet Package Manager –> Package Manager Console
// 3- Run "Add-Migration MyFirstMigration" to scaffold a migration to create the initial set of tables for your model
// See here for more information https://docs.efproject.net/en/latest/platforms/uwp/getting-started.html#create-your-database

   using (var database = new SensorContext())
   {
     database.Database.Migrate();
   }
} 
 

Now we bind the data models to the UI in MainPage.xaml.



<Page 
    x:Class="EFGetStarted.UWP.MainPage" 
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" 
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" 
    xmlns:local="using:EFGetStarted.UWP" 
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008" 
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" 
    mc:Ignorable="d" 
    Loaded="Page_Loaded"> 
 
<Grid Background="{ThemeResource ApplicationPageBackgroundThemeBrush}">
        <StackPanel>
            <TextBox PlaceholderText="Add a sensor"/>
            <Button Content="Update Database" Click="Add_Click"/>
            <ListView Name="Sensors">
                <ListView.ItemTemplate>
                    <DataTemplate>
                        <TextBlock Text="{Binding Location}" />
                    </DataTemplate>
                </ListView.ItemTemplate>
            </ListView>
        </StackPanel>
    </Grid>
</Page>  


Finally, we are ready to update our data based on user interaction. Specifically, we’ll hook-up the sensor addition experience in MainPage.xaml.cs.



public MainPage()
        {
            this.InitializeComponent();
            Loaded += MainPage_Loaded;
        }

        private void MainPage_Loaded(object sender, RoutedEventArgs e)
        {
            using (var database = new SensorContext())
            {
                Sensors.ItemsSource = database.Sensors.ToList();
            }
        }

        private void Add_Click(object sender, RoutedEventArgs e)
        {
            using (var database = new SensorContext())
            {
                var sensor = new Sensor
                {
                    SensorId = Guid.NewGuid(),
                    Location = $"Room1 {DateTime.Now}"
                };

                // Note how only two lines of code update and save changes to 
                // the data source and how there’s no reference to ‘SQLite’
                database.Sensors.Add(sensor);
                database.SaveChanges();

                //Update the ItemsSource of the ListView
                Sensors.ItemsSource = database.Sensors.ToList();
            }
        }

Research data collection and analysis

Tablets and phones are increasingly being used as data collection devices. A health app, like Fitbit, is essentially a data collection and analysis app for time series data collected from the wearable. A simpler example may be a survey app that collects responses from research participants via tablet or phone.

A client/server model database solution requires the developer to set up a cloud service. This might be overkill if all that is needed is a table of data for analysis in Excel. The data collection app can upload the single SQLite database file to a cloud storage location, like OneDrive, or maybe even email it to the analysts. Analysts can then use the SQLite Command Line tool (also shipped in Windows) to convert the SQLite database file to a CSV file for processing in Excel.

Note: It is also possible to use Python or R scripts to access the database file directly.

Exporting a SQLite database to CSV is as simple as launching the SQLite command line tool and entering:



sqlite> .header on
sqlite> .mode csv
sqlite> .once c:/docs/researchData/participant101.csv
sqlite> SELECT * FROM sqliteTableFromParticipant101;


What about tooling?

With more than half a million downloads, @ErikEJ’s SQLCE/SQLite Toolbox deserves a special mention. The tool scans the solution for SQLite files and automatically adds them to the Toolbox. It also provides a simple UI to explore all database objects and script out schemas. Import/Export features allow users to port a SQL Server database to SQLite and vice versa. Check out this Channel 9 video to learn more.

Wrapping up

We have philosophized about what makes a great data access layer and how SQLite can help get you closer to that utopia, and we have ended with information on how to ship your own SQLite and a note about tooling. To learn more, get step-by-step instructions, or review platform support details, you can check out the UWP How-To for Data Access.

Updated May 12, 2016 1:05 pm

Join the conversation

  1. Also, thanks to the beauty of emscripten, SQLite is also available compiled to ASMJS optimized javascript. I’ve had it running in Edge on a four year old Lumia 920 without any problems. It maintains binary compatibility for db files, so you can create isomorphic data applications that are offline capable in modern browsers that support service workers or the older html5 manifest standards.

    See: https://github.com/kripken/sql.js

    It’s also available on NPM, but not sure why you’d use it server-side there as you could just use the native binary.

  2. > “This represents the first time an open source, third-party library, such as SQLite, has shipped as part of the SDK”

    This isn’t true. You’ve been shipping the AllJoyn C Library as part of the SDK since 10.0.10240.

  3. Very good… unless you compile in release mode.. using EF in the last build with .Native resulted in more than 1GB of compilation temporary data (for a 3mb app..) and unknown native errors during app launch.

    I had to abandon EF and use only SQLite (for an app that was already finished and worked in debug mode).

    • We were burned by the exact same issue… shame on us for trying to use pre-release software though. 🙁
      We have switched over to using SQLite.PCL for now, but have written/abstracted our code in such a way that we should be able to plugin a different “Database” technology at any point.
      It would have been nice for the EF team to throw some big red flags stating the Release mode scenario was broken, and that the next release was delayed.

  4. Any plans to bring LINQ to SQL to UWP? It’s a strange that it is not in UWP despite it’s available for Windows Phone Silverlight apps.

  5. What’s the story with working with EF core asynchronously? There’s no mention here, in the UWP docs or in the EF core docs about working with EF core off of the UI thread.

  6. I don’t find DatabaseContext object and Migrate method in Microsoft.Data.Entity.
    So, I replace respectively with DbContext object and EnsureCreated method.

    It’s work better with it.

    Nice job

  7. Can’t we have a ‘string’ key type? As currently adding a string type throws an error as
    “The entity type ‘NameOfModel’ requires a key to be defined.” Whereas adding an int type as key works fine, although this results in including a lambda expression to check if an item is already entered before Inserting any data in database.

  8. Unfortunately the “v7.0.0-rc1-final”-Version is not compilable with the “.NET Native tool chain” (Release Version).
    I wanted to publish my app to the windows store but I run into the same problem which is already posted here: http://stackoverflow.com/questions/34748389/uwp-net-native-tool-chain-compilation-error/34751468
    I also experimented with EF Core 1.0 (https://www.myget.org/F/aspnetvnext) where I got other compilation problems.
    The CHANGE from EF7 to EF Core 1.0 is quite CONFUSING and nobody knows the final release date. It’s somewhat disappointing for developers of UWP-Apps because they can’t deploy their app to the store.

  9. Where can we find more information about this ADO.Net for UWP spec? Is this open to other client side database engines? I am thinking of things like SQL CE and VistaDB. (not a fan of SQLITE, sorry)
    Client side database is the one big missing thing in modern apps. VB6 exploded partly because it was easy to build line of business apps using local databases like Access. Would be nice to see this barrier go away in UWP.

  10. Erik, can you help with the “The type or namespace ‘DatabaseContext’ could not be found” issue (and not the only one not found)?
    Is it possible to make the solution available for download so that we have a working example?

    • DbContext / DbSet respectively… with RC1.Final… its old bits but they work, but debug is the only mode at the moment… RC2 should compile better in .native when release…(hopefully)

  11. Nice article! I’m having trouble with Windows IoT. It allows me to install the package, but “DatabaseContext” is not resolving. Should this work on Windows IoT?

  12. Nice article but I get stuck on the migrate call with “The navigation ‘ManifestModule’ on entity type ‘System.Reflection.Assembly’ has not been added to the model, or ignored, or target entityType ignored.” error

    • Any chance to have this post fixed? Shouldn’t tutorials be correct, Windows Apps Team?

    • Hi,

      I’ve got a problem in the example at this page and at the link you mentioned.
      If try to run the App this line :
      using (var database = new SensorContext())
      {
      Sensors.ItemsSource = database.Sensors.ToList();
      }
      is giving me the following Exception:

      An exception of type ‘Microsoft.Data.Sqlite.SqliteException’ occurred in EntityFramework.Core.dll but was not handled in user code
      Additional information: SQLite Error 1: ‘no such table: Blog’

      Did I miss something? I tried the examples several times.

  13. Or replace the whole ting with a redirect to the updated version? Why would people waste time with something buggy?

  14. I really like SQLite. One interesting thing you can do with it is implement your own storage (virtual tables). You can expose non-database data to the SQLite engine (e.g. you could expose the file system as a database table and query it with SQL). I used this mechanism to build a SQL IDE for Excel (www.thingiequery.com) – shameless plug, but an interesting use case too. SQLite also lets developers register their own functions. Very cool stuff from such a tiny piece of software!

  15. The article says “… the single SQLite database file … even email it to the analysts”. How do you get the SQLite database file out/in an UWP app?

  16. Is there any way in which I can make retrieval of data from database as asynchronous.
    Since currently the following results in UI deadlock as my command fetches data synchronously from database any way I can make it asynchronous so that I have the facility to Update the UI once data is received.
    public List retrieveBrands()
    {
    List brandList = new List();
    using (var db = new CarDataContext())
    {
    brandList = db.Brands.ToList();
    }
    return brandList;
    }

  17. Hi Develop
    I have a problem when query db sqlite with special characters sqlite no return data?
    example : select * from mytable where key like ‘hoàng’
    with key query contain special characters ” é è ê ã…”
    If you have any solution please help me
    Thanks