This topic is going to be sort of continuation of my previous post about SQLiteWrapperUWP-PCL – an SQLite Wrapper for Windows 10 UWP applications. I’m writing “sort of” because it’s actually an update to that post. The project part – setting up the solution – hasn’t changed but the code part has. Please understand that the SQLiteWrapperUWP-PCL library is being constantly developed and improved, hence I need to explain how things has changed and how one should implement their database logic with latest release of SQLiteWrapperUWP-PCL NuGet package BuildIt SQLite.

For those who are not very familiar with Windows 10 and UWP application development please, before you start going through what’s written in here, refer to my previous post about SQLite Wrapper for Windows 10 UWP applications, where you can find guidelines how to set up your solution / project.

For those who can’t be bothered reading and/or want to figure things out by themselves I recommend looking at the GitHub repository GitHub repository where you can find some short guidance / information about how things work with SQLiteWrapperUWP-PCL BuildIt (Sqlite). Also there’s a CRUD database operations example CRUD database operations example which could be a good place to start your explorations.

EDIT: Apologies to all that wanted to try out the SQLiteWrapperUWP-PCL and some of the links weren’t working, but SQLiteWrapperUWP-PCL evolved from its own existence, to become a part of a bigger set of libraries, called BuildIt. Those libraries are a foundation that we rely on, in almost every project that we create @BuiltToRoam, so besides working with SQLite check out others, I can bet you will like it and use it.

The Code

Let’s start from the Core project and setting up the DatabaseService.

The base class, that DatabaseService inherits from, is an abstract class BasicDatabaseService which is a exemplary implementation of BaseDatabaseService. Both of which sit in the SQLiteWrapperUWP-PCL library and expose the IBasicDatabaseService and IBaseDatabaseService interfaces. In this case, deriving from Basic service, one have to provide only the implementation of CreateDatabaseTables method, which is responsible for creating database tables, and provide some constructor parameters, about which in a second.

I think it’s worth noting that one is left with an option to derive directly from BaseDatabaseService and in that case it opens a path where one could provide their own SQLite database connection creation (implement CreateSQLiteConnection method) handling.

Let’s explain a bit about DatabaseService constructor parameters and its purpose.

  • ISqlitePlatformProvider consists of one child, and its purpose is to provide core SQLite platform functionality, basically it’s the “heart” or “spine” to the SQLite database.
  • IDatabaseNameProvider is nothing more but a “fancy” way of saying “if you want a database you need to give it a name it”. With this interface implementation you need to provide a database name, and that’s it – one string
  • Last but not least is the ILocalFileService which is necessary to provide a way of getting physical path – RetrieveNativePath method – where the database file will be saved (on a hard drive)

You can find exemplary implementation of all of those in the GitHub repository

NOTE: In almost all of my projects I use mvvm cross-platform framework MvvmCross, which I highly recommend, and with it, out of the box, comes Dependency Injection (IoC) mechanisms. Those mechanism under the hood, create and inject automatically singletons for mentioned above interfaces (constructor parameters). You can find out more about it in the N+1 days of MvvmCross blog posts and videos. For those who are familiar with it and to spare you some time looking for how it could be initialized, here’s a sample code from Setup.cs file

 

Important thing, that has been changed in the latest release, is BaseEntity class. It was redesign to consists the update of representation of the entity logic (update of the database record). For example:

There’re two things to note in the above code. First is to do with self-reference generic class in the class declaration. It was purely designed this way so we could handle the update logic inside of the Entity class, which is called from the BaseRepository every time the Update logic on the entity is executed. UpdateFromEntity method – second thing that one should be aware of – should be a place where entity update is being handled.

 

Assuming you have an instance of DatabaseService handy (e.g. created with MvvmCross DI mechanisms) and with all this setup / knowledge we can now start working with our database, and it couldn’t be any easier. To create new database record (entity) one could write something as follows:

To retrieve all the table records or just one:

To delete record:

Those are Base CRUD methods, but if you’d like to write more sophisticated queries you should look into BaseRepository and Table property. It will allow you to perform some LINQ queries on it. For example:

NOTE: As it’s not perfectly obvious, the CreateSQLiteConnection method won’t create a new one every time it’s being called, but it will take the cached one.

NOTE: Another thing is that not all LINQ queries will be valid in terms of translating them to what database understands, hence sometimes it’s better to grab all table records (entities) and perform some queries on the values stored in memory.

 

I would highly recommend checking out the GitHub repository and the sample that is in it, as it consists all of what I’ve just wrote about, and you can easily compile and debug that code.

 

NOTE: For inspecting SQLite database file(s) I recommend using SQLiteBrowser

The SQLiteWrapper story

The idea for SQLite Wrapper was purely dictated by the demand. We, at BuiltToRoam – company that I work at – are dealing with all types of universal application projects (Universal Apps for Windows 8.0-8.1 and Windows Phone 8.0-8.1 or UWP for Windows 10) that are required to use database(s). With every new application, we were basically re-using same code that was written for one of the previous apps. It was done in a really “savage” way, by copying and pasting core functionality and adjusting it accordingly to the needs of the app. Not good..something had to be done. The decision was made. We created a PCL (Portable Class Library) wrapper over SQLite.Net-PCL library that implements some basic functionality for Creating, Reading, Updating and Deleting (CRUD) operations on the database tables.

SQLiteWrapper came to life on a GitHub repository. From the beginning of it’s existence in the Open Source space I was tempted to create a NuGet package out of it. With NuGet package it would be much easier to distribute it and share it. On top of that, nothing would change in terms of GitHub repository, it would still be there so anyone could grab sources. Besides..I always wanted to have mine NuGet package, even though this one wouldn’t be strictly speaking mine. Even though it is a child that whole BuiltToRaom team gave birth to, I feel like at the moment I’m changing its dippers ;]. What’s more, creating this package could finally place myself on the map of .NET world!

I ended up reading some articles how to create a package, what should it consist in terms of information and description and a bit later..I present you SQLiteWrapperUWP-PCL! There’s still no information whatsoever about how to deal with it or how to use what’s inside of if. I will try to fill this gap by guiding you, reader, through the process of creating a really basic example of SQLiteWrapperUWP-PCL usage.

 

The SQLiteWrapperUWP-PCL example

 

The project

Let’s start with creating an empty solution in Visual Studio 2015 – I’m using Community edition.

Creating_Blank_VisualStudio_Solution

Next let’s add UWP project

Adding_UWP_Project

Next add a PCL (Portable Class Library)

Adding_PCL_Project

Adding_PCL_Project_Support_Selection

As you can see on the support selection screen (it appears after clicking OK on the dialog with Add New Project and choosing PCL), I’m selecting .NET Framework 4.6 and Windows Universal 10.0 (UWP).

After those steps you should end up, in your Solution Explorer, with something similar to this

SolutionExplorer_View

Having that done, we can grab NuGet packages and add it to the project. As SQLiteWrapperUWP-PCL depends on the SQLite.Net-PCL library we it will be installed along side SQLiteWrapperUWP-PCL

NuGet_Package_Manger_SQLiteWrapperUWP-PCL

New references should appear in both Core and UWP projects and in the NuGet Package Manger window green tick should appear next to libraries that were installed. Even after successful installation you still don’t seem to have SQLite.NET-PCL library referenced (in one or both of your projects) you should try installing it explicitly (by finding it in the NuGet Package Manager and installing it from there)

SQLiteWrapperUWP-PCL_Installed

You’re probably aware that to work with SQLite database one need an SQLite extension. In our case it’s going to be UWP extension. You can download one from Visual Studio, by going Tools –> Extensions and Updates and then selecting on the left side Online> Visual Studio Gallery and search for sqlite for universal

SQLite_UWP_Extension

After downloading SQLite for Universal App Platform you will be prompted with installer confirmation dialog

Confirm_SQLite_UWP_Extension_Installation

Remember to restart Visual Studio after installation has finished. Now you will be able to Add reference of this extension to your UWP project.

Adding_SQLite_Extension_To_UWP_Project

Then navigate – left side panel – to Universal Windows. SQLite for Universal App Platform should be listed in Extensions

Adding_SQLite_Extension_To_UWP_Project_Extension_Window

Click OK. Afterwards you should have your SQL extension visible under References in your UWP project

SQLite_Extension_Added

At some point you should add a reference to the Core (PCL) library in your UWP project. You can see that I already did that, look on the above screenshot, it sits just below the SQLite for Universal App Platform reference.

Last thing, that is not necessary but recommended is to install MvvmCross libraries. It can be done in the same way as we did with SQLiteWrapperUWP-PCL, which means that in the NuGet Package Manager find MvvmCross and install it in both projects. On the below screenshot you can see MvvmCross already installed

Adding_MvvmCross

Installation of MvvmCross framework will most likely create some folders and files in your project. To get familiar with MvvmCross and it’s structures, concepts and files I would recommend reading and/or watching some videos from the  N+1 days of MvvmCross blog posts.

In terms of preparing your project for this example that should be it, let’s proceed to the code part

The description, below, was for version of SQLiteWrapperUWP-PCL up to 1.0.0.3. For the new version you can find some information in my latest post on this subject

The code

Let’s start from the Core project and setting up the DatabaseService.

The base class – BaseDatabaseService – is the one that sits in our wrapper and is responsible for database management (e.g. creation, opening or closing). To create either BaseDatabaseService or in this case DatabaseService one will have to specify a constructor parameter of ISqlitePlatformProvider, I will explain that one in a moment. In the above code besides the constructor you can see an override of the CreateAndOpenDb method. It allows as to “plug in” between the creation of the database and returning connection to it. It’s done this way to make sure that before someone will start operating on the database (SQLiteConnection) they will have the proper entities – database tables – defined in it. In this case PersonEntity table is being created and afterwards dbConnection returned. Invoking dbConnection.CreateTable<PersonEntity>(); will map the model of our class to the database table. For more information about creating tables and mapping in general I’ll refer you to the SQLite.NET-PCL git project site, where you can find some examples.

PersonEntity is just an example of how you should structure your database entities. They should inherit from BaseEntity class so that you could use BaseRepository implementation – both of those classes are in the wrapper and about the second one I will talk in a moment. You should know that BaseEntity has a Id property which is decorated with [PrimaryKey] attribute and as you may suspect it will be mapped as a database table PrimaryKey.

Before explaining ISqlitePlatformProvider and BaseRepository concept I’ll quickly show you how I dealt with PersonService and Inserting and Retrieving data from database. Let’s start with very simple IPersonService interface that our service will implement

As simple as two methods, Insert and RetrieveAll. Implementation will sit in the PersonService.

Constructor takes SQLiteConnection which is the product of database service CreateAndOpen() method call. There’s also Insert and RetrieveAll implementation. Both use same pattern, which is BaseRepository<TEntity>. Repository part of the wrapper is the “thing” that you will deal with the most of the time. In above snippet of code I’m simply creating, for both methods, new BaseRepository<PersonEntity>, within using statement – because BaseRepository implements IDisplosable interface – which allows me to operate on my table (e.g. personRepo.Insert(person) or personRepo.Table.ToList()). Explore BaseRepository more to get familiar with functions that we exposed (e.g. Get or Delete).

That’s about it when it goes for Core, let’s move to the UWP project. Starting from mentioned earlier ISqlitePlatformProvider

That one is pretty easy. It’s purely about platform specific implementation of SQLite.Net-PLC library and how it will handle all the “low level” SQL database stuff. In this case our platform will be SQLitePlatformWinRT.

Last but not least is to put all this together and insert and retrieve some actual database entries.

I hope the comments and my walk through will let you understand what’s happening in this snipped of code. If you still have doubts you should definitely try it yourself! Start from checking out this SQLiteWrapperUWP-PCL example project that you just read about.

 

NOTE: For inspecting SQLite database file(s) I recommend using SQLiteBrowser

TypeLoadException_ExceptionWindow

This issue stroke me suddenly and without any reason (at least I couldn’t find one). One day code that rotates picture, which was captured from camera, started to throw exceptions. I was really surprised, because I didn’t change much in the project since the time it was working just fine..Till this day, I have no idea what was the cause of it

TypeLoadException_RotatingPictureCode

What I figured out after some research on the Internet and looking in the generated *.appx package files (appx ~ zip – by changing extension of your *.appx to *.zip you can check what’s in it)  I discovered that for some reason the AppxManifest.xml was lacking entry with the title library mentioned in the title of this blog post (highlighted entry is the one that was missing and had to be added manually)

I couldn’t work it out how I can force mechanism standing behind creating packages (*.appx) to include this in the AppxManifest.xml, so the only solution was to insert it after package was made. To automate this process, of unpacking files from *.appx package, making amendments to the AppxManifest.xml and then repacking again all the files into *.appx pckage, I wrote a pretty ‘nasty’ PowerShell script which uses makeappx.exe (to unpack and repack) and some other PowerShell ‘hacks’ ;]

In the end, all this script does is inserting one line of code into your AppxManifest.xml file, which is

After executing this script, your package should be ready to go, and your Lumia’s (old Nokia’s) libraries shouldn’t fail again

Another blog post about struggle with weird compilation errors of universal app solution.

MdilXapCompile code 1004 - error windows cropped

This one occurs only when you’re compiling in Release configuration, otherwise it’s not bothering you. I couldn’t find any solution online (at that time) and we were helpless for about a week. Finally we overcame this issue, when my boss stumbled upon, totally by coincidence, on one of his subscribed feeds (or forums or something else) on a thread with similar description and gave me the solution.

What I had to do, was to remove all the resources files from the packages (nuget packages) folder in the solution. I searched them by the resources phrase and literally selected all the files that were ending with resources and deleted them

Resource files in packages folder - important part cropped

After deleting all the resources (I advice no to use shfit + del, but just del, so that in case of deleting something important you can recover it from the bin) problem was gone, and I was able to compile in Release mode again.

 

Some links, that might be useful on this matter, after quick googling:

Deploying to Windows Phone fails when using Release build

RateMyApp 1.2.4 alpha prevents Windows Phone Universal App from being deployed to device when compiled in Release

Recently I was struggling with trying to set images (being a byte[] and/or being an Asset file and/or being an IsolatedStorage file) as an Image.Source. I didn’t know what should be the URI in case of dealing with Assets and IsolatedStorage, and I wasn’t sure what to do with a byte array aka byte[] as well. After a bit of research and some advices from my work colleagues I’ve manage to get it to work

If you are curious what Microsoft have to say about it, just go here and check official Image control site, scroll down to Remarks to learn how to set up a images from web URL and from relative URI (e.g. your app Assets). You can also learn from it, that Images are being cached. Every another time, same image, is referenced, it’s being taken from cache. It’s worth having a glimpse, at least on the remarks and examples. But going back to the subject

 

Image.Source as byte array aka byte[] (using async and await)

I assume, in this example, that you already have a byte array of image data prepared to be displayed on the screen. If not, this is how you can turn your StorageFile into byte array. Now, you can’t just simply assign Image.Source to array of bytes, XAML will not know what to do with it, and how to display transform it to a bitmap an show it on the screen. It will fail. Hopefully, there’s such thing as Converters, which come with help. As for an example, XAML and ViewModel are pretty straight forward and could look like this

The real ‘magic’ happens in the Converter, which looks like this

Few things to notice in this code. First is that we’re using WritableBitmap, which allows us to assign Source with it (empty) and return it from Converter, which releases UI thread, and transform our byte array, inside a separate task, into stream, which then is being set as source of earlier mentioned WritableBitmap. In more deatils, loading of the image is handled in the Task.Run() context, notice lack of await key word in front of the Task.Run() call, which means it will be done in another thread but, app won’t wait until it’s done, hence UI thread won’t be bothered, and app will respond properly on users actions. Another thing to bear in mind here is the dispatcher. Remember, that if you want to make any changes on the screen, in the UI, you need to do them on the UI thread. Dispatcher is the key to do just that. Call RunAsync() method on it, and you’re safe from getting and exception

The application called an interface that was marshalled for a different thread

Last but not least, is that after setting the source SetSource(ms) you need to call Invalidate() method, which will force a redraw of entire bitmap. Oh, and remember that SetSource(ms) method has to be called before you close and/or dispose your InMemoryRandomAccessStream (in this case ms), that’s why it’s being called inside of the using statement.

 

Image.Source as project file (Assets file)

This one seems to be the easiest one, but it made me sweat a bit when I tried setting relative UriSource of a BitmapImage and assign it as a Image.Source from the code behind. Even though, it’s well documented on the official, msdn, site that I already pointed out in my short prologue (for convenience), and its mechanism is pretty straightforward, it gave me a little headache. Anyway..All you need to do, in your XAML file, is to set a relative path to your project file (usually taken from Assets) like so

and image appears on the screen! Easy! It’s not that simple, though, when you try setting your project file as Image.Source from the code behind. At least not for me, and those who decided not to read the documentation and thought it’s too easy to waste their time. Wrong! It was a mistake, I should have read it, at least just have a look. To spare you the trouble…What I did, thinking that when I set same URI as I did in XAML, which is “Assets/image.png” as a UriSource of a BitmapImage I would get same results. Nope..because what happens in XAML, according to documentation, is that specified URI as the Source of the Image control is being processed in a specific manner

..the string as a URI, and calls the equivalent of the BitmapImage(Uri) constructor

Following this information I wrote:

Wrong again! What actually happens, and you can read about it, some paragraphs below, in the same document (third time’s the charm), that what actually happens, under the hood, with Image.Source URI is that it’s being treated as a relative path suffix, and in the end it looks something like this (depends on the page file – XAML – location in the project)

ms-appx:///Assets/image.png

That lead me to use this code instead, which finally worked

If you want to refer something from you AppData folder you will have to use ms-appdata:/// scheme prefix instead of ms-appx://. You can find more information about it here

 

Image.Source as IsolatedStorageFile

In this case you don’t have to do much. Just set the image URI as Image.Source and you’re done

The ‘hard’ part is to save and, later on, get the actual ImageUri. In my case, I’m using MvvmCross libraries, which, by the way, I strongly recommend as a general Mvvm framework for Windows RT (Universal apps) and cross platform development, it was pretty easy. After saving it on the device, I had to call NativePath(path) method, which is a part of IMvxFileStore and my URI was ready to use. I’m not getting into more specific on this matter, because it’s not the topic of this post, but you can check MvvmCross File Plugin out, and whole framework, by yourself, just use NuGet and start exploring.

An example of how native URI could look like:

C:\Data\Users\DefApps\APPDATA\Local\Packages\9e8374f5-6abb-4e02-bde7-c0fd586c936e_hnp1ad7aancnj\LocalState\Images\image.png