Category

Visual Studio

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

blend_for_visual_studio_design_view_unavailable

I know..it annoys me the same way it annoys you, but I don’t think there’s any way to get rid of it, permanently. There is a quick cure though, because it happens only when you open a project in Visual Studio that has its configuration set to x64 or ARM, so you can easily undo it with setting it to something that Blend does not windge about.

configuration_manager_ARM_build

In my case I end up with this frustrating information presented to my face, when I try to deploy my app to the device (phone). I set up my build configuration to ARM, I finish my day of work, close everything, restart my machine, go home, and the next day I show up in the office, I open up VS and then Blend and then start..Boom!

Design view is unavailable for x64 and ARM target platforms.

How to get rid of it ? Close both, Blend and Visual Studio, but before you close Visual Studio, go to the configuration manager of the project and change its settings to e.g. Any CPU or just something that is not x64 or ARM

configuration_manager_Any_CPU_build

Now, re-open your project in Visual Studio and after VS is up and running re-open your project in Blend. Now your nightmare should be gone, and you should be able to design some cool stuff for your app in Blend for Visual Studio.

Have you found yourself frustrated about Visual Studio acting retarded on the values specified in search dialog ? I sure did. It was so frustrating when I wanted to search through whole project, no matter what file extensions and without ‘Word match’ option turned on. What Visual Studio has to say on this topic ? No can do mister..You specified few days ago, in one of your searches, that you want narrow your results to *.cs files and the search phrase have to match exactly the word, so from that moment on, I assume you want to search for it EVERY SINGLE TIME you open the search dialog..

Yes Visual Studio, you are right, I specified that, but you are just a retard to assume that I wanted those setting to be treated as a default

Anyhow..I was just tired of seeing this window, every time I clicked CTRL + SHIFT + F to search through the whole project, and having to clear those preserved and unwanted settings, that for some, unknown for me, reasons Visual Studio keeps them and treats as default. So annoying..

visual_studio_search_dialog

Unfortunately there’s no easy way of removing those settings. What you have to do is to open the registry and change some values. I’m opening Registry Editor with the shortcut of WINDOW BUTTON + R (opens a Run window) and typing, in it, regedit and hitting enter

run_window_with_regedit

after that you should be prompted with this window

registry_editor

Now you have to navigate to (bear in mind that your version of Visual Studio might be different)

HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\12.0\Find

registry_editor_visual_studio_find

Locate the Filter # item(s) and DialogOptions. Those are the ones that are responsible for your misery and they need to ‘vanish’. What you want to do is to clear the values of the Filter # ones (set to empty string) and for the DialogOptions you have to change (in my case) WholeWord flag to be 0. BEFORE you start doing changes in the registry ALWAYS make a backup by Exporting.. the registry to a file

registry_backup

Second thing BEFORE making changes to those items (Filter # and DialogOptions) is to close all the Visual Studio instances that you’re currently running. If you won’t close them the settings will keep coming back. My guess is that when you close Visual Studio it updates those values in registry with whatever it has in cache, hence your changes will be overridden. So..having that done we can start making amendments in the registry entries.

modifying_registry_entry

registry_entry_edition

After making those changes, your registry view should be something like so (blanks for Filter # and changed flags for DialogOptions)

registry_edition_finished

Now, your search dialog should get back to normal (default)