SQLite Getting Ready for Windows 10 (Universal App Platform)

    Hello, dear All!

    I want to talk about a small library for working with SQLite in Windows Phone 8.0 Silverlight, Windows Phone 8.1, Windows 8.1, and now also for Windows 10 UAP. The library has been over a year old and since problems with her for all the time did not arise, then, I think, you can tell others about her.

    Why all this?
    The library within the framework of Windows Phone 8.0 Silverlight, Windows Phone 8.1, Windows 8.1 lasted more than a year. The question is: why now I decided to talk about it? The fact is that now is the time to port your applications for Windows 10, but there is no official SQLite SDK yet.
    Here there is, the SDK for Windows Phone 8 , Windows Phone 8.1 , the Windows 8 and8.1 the Windows . But not for Windows 10 .
    And most likely it will not be until the release of Windows 10. Therefore, it makes sense to look at this library.

    Let's go The
    library is called SQLite.WinRT .

    I am not its 100% author. I just gathered many pieces of code wandering around the internet together. Therefore, if you believe that I somehow violated your copyrights, then let me know - we will agree.

    Source codes can be found here .
    You can install the library using NuGet. There are 5 packages:

    For successful compilation, you will have to get rid of Any CPU and assemble the project separately for each platform: x86, x64, ARM.

    For Windows 10 UAP applications, you must add a link to the following SDKs:
    • Microsoft Visual C ++ 14 AppLocal Runtime Package for Windows UAP

    For Windows 8.1 applications, you must add a link to the following SDKs:
    • Microsoft Visual C ++ 2013 Runtime Package for Windows
    • SQLite for Windows Runtime (Windows 8.1)

    For Windows Phone 8.1 applications, you must add a link to the following SDKs:
    • Microsoft Visual C ++ 2013 Runtime Package for Windows Phone
    • SQLite for Windows Phone 8.1

    For Windows Phone 8.0 applications, you must add a link to the following SDKs:
    • SQLite for Windows Phone


    How to use the library?
    For an example, we will need the following classes:
        public sealed class DatabaseContext : BaseDatabaseContext
        {
            private DatabaseContext(SQLiteConnection connection): base(connection)
            {
            }
            public async static Task CreateContext()
            {
                const string dbName = "db.sqlite";
                var folder = CorePlatform.Current.LocalFolder;
                var connectionString = new SQLiteConnectionString(Path.Combine(folder, dbName), true);
                var connection = SQLiteConnectionPool.Shared.GetConnection(connectionString);
                var ctx = new DatabaseContext(connection);
                await ctx.CreateSchemeAsync();
                await ctx.UpdateSchemeAsync();
                return ctx;
            }
            public IEntityTable Items { get { return provider.GetTable(); } }
            public IEntityTable Categories { get { return provider.GetTable(); } }
        }
        [Table("Items")]
        public class Item
        {
            [PrimaryKey, AutoIncrement]
            public int ItemID { get; set; }
            public int CategoryID { get; set; }
            public string Title { get; set; }
        }
        [Table("Categories")]
        public class Category
        {
            [PrimaryKey, AutoIncrement]
            public int CategoryID { get; set; }
            public string Name { get; set; }
        }
    

    Add Record
    var category = new Category();
    category.Name = "category";
    await db.Categories.InsertAsync(category);
    

    Record Update
    category.Name = "category2";
    await db.Categories.UpdateAsync(category);
    

    All fields except the primary key are updated.
    If you really need it, you can do it like this:
    var count = db.Categories
      .Update()
      .Set(t => t.Name).EqualTo("test name")
      .Where(t => t.CategoryID).IsBetweenAnd(3, 4)
      .Execute();
    

    In this case, only that will be updated.
    Delete record
    var count = db.Items.Delete()
      .Where(t => t.CategoryID).IsLessThanOrEqualTo(3)
      .And(t => t.Title).IsEqualTo("item0")
      .Execute();
    

    or so:
    var category = ...;
    await db.Categories.DeleteAsync(category);
    

    Data Sample
    And here LINQ will help us:
    var query =
      from c in db.Categories
      join i in db.Items on c.CategoryID equals i.CategoryID
      select i;
    var items = await query.ToListAsync();
    

    Data Migration
    Attentive reader noticed 2 interesting challenges:
    await ctx.CreateSchemeAsync();
    await ctx.UpdateSchemeAsync();
    

    The first creates or updates tables based on the declared context properties. Columns in the table can only be added. Cannot be deleted.
    The second launches a list of migrations whose version is smaller than the database version.
    Migrations are declared like this:
        [DatabaseUpdate(typeof(DbChangeset1))]
        [DatabaseUpdate(typeof(DbChangeset2))]
        [DatabaseUpdate(typeof(DbChangeset3))]
        [DatabaseUpdate(typeof(DbChangeset4))]
        public sealed class DatabaseContext : BaseDatabaseContext { ... }
    

    Migration example:
        public class DbChangeset1 : IDatabaseChangeset
        {
            public int Version { get { return 1; } }
            public void Update(IEntityProvider provider)
            {
               // так как структура обновляется сама, то тут обновляем только данные.
            }
        }
    

    Something like that. More information can be found in unit tests.

    And now I am ready to listen to your comments, suggestions and objections.

    Also popular now: