Windows 10 Universal App SQLite

Question

I'm currently trying to port a working Windows 8 JavaScript app to a Windows 10 UAP app. In my Windows 8 app, I heavily used this SQLite wrapper and library: https://github.com/doo/SQLite3-WinRT. However, after adding SQLite3-WinRT to my Windows 10 UAP app as per the setup instructions in the readme of the repo, I get a "WinJS is not defined" error coming from the SQLite3.js source file that I added to my /js directory in the app (the way it works fine in the windows 8 app). Am I doing something wrong here, or wild this SQLite3-WinRT not work with Win 10 UAP and is there some better way of using SQLite in a JavaScript Windows 10 UAP app? Thanks a lot!

Answer 1

I tried using https://github.com/doo/SQLite3-WinRT on Windows 10 and found VS2015 Community Edition couldn't even load the project. Every time I tried to load it, VS would hang with "unloading project" showing in the status bar. Killing it via task manager was the only way out.

I found this sample app which implements SQLite in a Universal App. This compiles and runs fine for me on Windows 10, although I did have to update the references to SQLite 3.8.4.3 with the version I had, SQLite 3.8.11.1

1. Download and unzip Universal JavaScript SQLite Sample
2. Open in Visual Studio
3. Click on the "Shared App" project group
4. Expand "SQLite.Windows" > "References"
5. Remove the reference to "SQLite.WinRT81, Version=3.8.4.3
6. Right-click > "Add Reference"
7. From Windows 8.1 > Extensions, select "SQLite for Windows Runtime (Windows 8.1)
8. Expand "SQLite.WindowsPhone" > "References"
9. Remove the reference to "SQLite.WP81, Version=3.8.5
10. Right-click > "Add Reference"
11. From Windows 8.1 > Extensions, select "SQLite for Windows Runtime (Windows 8.1)
12. Compile

Answer 2

If you are looking for a step by step procedure, you can take a look at this post.

You can use it with:

• Windows 10: SQLite for Universal App Platform
• Windows Phone 8.1: SQLite for Windows Phone 8.1
• Windows 8.1: SQLite for Windows Runtime (Windows 8.1)

After setting up the packages and extensions, you are able to create the database and the models classes using:

using System.IO;
using System.Threading.Tasks;
using SQLiteModernApp.DataModel;
using SQLite.Net.Async;
using System;
using SQLite.Net;
using SQLite.Net.Platform.WinRT;

namespace SQLiteModernApp.DataAccess
{
    public class DbConnection : IDbConnection
    {
        string dbPath;
        SQLiteAsyncConnection conn;

        public DbConnection()
        {
            dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "Sample.sqlite");

            var connectionFactory = new Func<SQLiteConnectionWithLock>(() => new SQLiteConnectionWithLock(new SQLitePlatformWinRT(), new SQLiteConnectionString(dbPath, storeDateTimeAsTicks: false)));
            conn = new SQLiteAsyncConnection(connectionFactory);
        }

        public async Task InitializeDatabase()
        {
            await conn.CreateTableAsync<Department>();
            await conn.CreateTableAsync<Employee>();
            await conn.CreateTableAsync<EmployeeDepartment>();
        }

        public SQLiteAsyncConnection GetAsyncConnection()
        {
            return conn;
        }
    }
}

Now you can create a CRUD, following the example:

using System.Collections.Generic;
using System.Threading.Tasks;
using SQLite.Net.Async;
using SQLiteModernApp.DataAccess;
using SQLiteModernApp.DataModel;
using SQLiteNetExtensionsAsync.Extensions;

namespace SQLiteModernApp.Repository
{
    public class EmployeeRepository : IEmployeeRepository
    {
        SQLiteAsyncConnection conn;

        public EmployeeRepository(IDbConnection oIDbConnection)
        {
            conn = oIDbConnection.GetAsyncConnection();
        }

        public async Task InsertEmployeeAsync(Employee employee)
        {
            await conn.InsertOrReplaceWithChildrenAsync(employee);
        }

        public async Task UpdateEmployeeAsync(Employee employee)
        {
            await conn.UpdateWithChildrenAsync(employee);
        }

        public async Task DeleteEmployeeAsync(Employee employee)
        {
            await conn.DeleteAsync(employee);
        }

        public async Task<List<Employee>> SelectAllEmployeesAsync()
        {
            return await conn.GetAllWithChildrenAsync<Employee>();
        }

        public async Task<List<Employee>> SelectEmployeesAsync(string query)
        {
            return await conn.QueryAsync<Employee>(query);
        }
    }
}