SQLite-net and SQLite-net extensions with Universal Apps

So I wanted to get my hands dirty with some SQLite in Universal Apps.
Turns out I had my hands full with trying to figure out why stuff wasn’t working… so here a small blog post to help others if they have the same problems.

Let me explain the setup.

* Univeral app template
* Installed SQLite SDK for Windows Runtime (Windows 8.1) and Windows Phone 8.1
* Installed SQLite-net nuget ( to be able to work with helper methods and attributes )
I used the PCL version https://github.com/oysteinkrog/SQLite.Net-PCL
* Installed SQLite-net extensions nuget ( to be able to handle relationships through attributes )

First problem, while using the SQLite-net in combination with SQLite-net extensions, the SQLConnection method needs an extra parameter to identify for what platform you want to run SQLite.
So what you’ll see in most tutorials:

is not correct, you’ll need to put:

But where do we find this SQLitePlatformWinRT implementation? Well there is a nuget for this too! BUT alas in Universal this won’t load because of the Windows Phone project that is also inside the shared solution.
Only way to circumvent this is by copy/pasting the source code, because the code is correct for both platforms in an Universal setup!
Thanks StackO for this: http://stackoverflow.com/questions/27506374/sqlite-net-platform-for-windows-phone-8-1
The source code can be found here: https://github.com/oysteinkrog/SQLite.Net-PCL/tree/master/src/SQLite.Net.Platform.WinRT

Second problem, when doing all this, everything works great – but I was unable to check if the database exists after I created it with the SQLConnection method!
Again all tutorial say, just check the local storage like this:

So if you’ll get an exception the file does not exists, so there would not be a DB. But even though I had no file, I was able to perform queries!
Seems SQLite-net uses a pre compiler directive NETFX_CORE that is being hit when I only supply a db name to the SQLConnection method. The code is in the SQLite.cs file here https://github.com/praeclarum/sqlite-net/blob/master/src/SQLite.cs

In other words the db was created in the temporaryfolder!
To fix this we need to supply a full path while first creating the db so change the SQLConnection method again to:

After this, the db will be inside the ApplicationData.Current.LocalFolder and so you can again use the GetFileAsync(dbName) method to verify if your db is available or not.

I hope this helps some people… happy coding

Migrating from SL8.0 ProtectData to RT8.1 PasswordVault

Back in the SilverLight days when you created a Windows Phone app that needed to store user credentials, there was only one good way to do this!
You had to use the ProtectData class with it’s Protect and Unprotect methods.

The reference post about how to do this was given by Rob Tiffany here http://robtiffany.com/encrypting-your-credentials-on-windows-phone-7-5/!

But of course you now want to create a new Universal app and you wonder what the new way of storing credentials in a safe way is today… Well it’s called PasswordVault!
Read about it here http://msdn.microsoft.com/en-us/library/windows/apps/xaml/windows.security.credentials.passwordvault.aspx.

Big advantage of using PassworVault, is that it will roam the settings across devices! So in other word, if you first installed the Windows Phone app and entered your credentials. Than download the Windows Store app, you’ll no longer need to enter your credentials because the app uses the same Vault and can already read the needed info out of it!

Now let me show you how you implement these 2 options, so you’ll have a clear overview of how to migrate from one to the other.

First up, the use of ProtectData.
What I tend to do is protect all account data in one go by first creating a json string from the given data and storing that as a protected byte array inside a file on the Windows Phone.
Small note: the Account class in the examples below is just a small POCO with UserName and Password properties.

When that is done, you’ll be able to read it out again when needed! Most of the time you’ll do this at app startup.

So you see it’s very easy to store a whole collection of account info in your isolated storage in a secure way!
Now when you upgrade your app to WP 8.1 RT, you’ll want to do this in a different way! By using the PasswordVault to ensure that your credentials are still stored in a secure way AND are roaming across devices.

When you want to use the PasswordVault in your app, you’ll need to select your own app key. Because everything stored and retrieved from the vault is done with that key! This is needed because otherwise, when linking your WP and Win Store app, you won’t be able to read out the same credentials.

Getting all credentials from the vault is done through the FindAllByResource method, supplying the app key. Do note that you’ll need to check for exceptions that can occur by doing this, because if the given app key is not yet present in the vault, you’ll get an exception!
After that, you’ll need to request the password for each credential that you got from the vault! Don’t forget this, because when using the FindAllByResource method the passwords will still be empty. Getting the password is done by using the RetrievePasword method.

So now we already know how to get hold of everything in the vault, but how do we store or remove accounts?
Well there are 2 methods for this, Add and Remove, so as easy as that. Do note there is no Update method.

So there you have it, everything you need to know to convert your apps!

Twitterate your Windows Phone app

Thanks Scott for helping with the Path control!

Well, the people of twitter have streamlined the look and feel of their mobile apps, so that it almost looks the same on each platform.
Currently on Windows Phone this means it’s like this:


So you will see a set of icons on top with a content body below and by selecting one of the images you will switch the content body.

But the special thing about this, is that the body content will ‘slide’ from one to another, meaning in Windows Phone development terms this is most likely a Pivot control. Weird thing though, the icons are not inline with the standard Pivot control header. If so they would not be all on the same page. 
How can we mimic this ourselves?

I have no doubt there would be several different possibilities, but let me give you mine…

Create a new windows phone app and in the MainPage.xaml add a ListBox called ImageBar.
This imagebar will host our 4 icons!
Each icon is represented with a custom Path control that contains XAML data ( more on this below ). To get nice looking icons, just download Metro Studio from Syncfusion! Open the program, look up a nice icon and copy paste the generated XAML data. The listbox XAML will be:

After this, also add a Pivot to the page, but don’t add a HeaderTemplate ( we don’t need one ). The one in our solution looks like this:

Now there are several things going on in here! You will see that both the listbox has a SelectedIndex bound to that of the Pivot in two way mode, these are needed to keep the image listbox and the pivot in sync on what is selected.

Now when you would run this, nothing will happen with your headers, because we need to give the selected icon a colour when it’s selected. This is done by giving the listbox a custom ItemContainerStyle in there we are going to use the visual states of the listbox to set the colour when an item is selected! In our example we the PhoneAccentColor for the selected state and PhoneInactiveColor for the unselected state…

For this trick to work we needed to create our custom Path Control! Because using a Style Template will try to set/change the ForeGround of a ContentControl, but a default Path control only has a Fill property! The custom control will use template binding to take the ForeGround value and use this as the Fill value. We do the same for the Content itself, this is used for the Data property of the Path control.

Do note in this style, we also set the HorizontalContentAlignment to Center so each icon takes center place in the list item…

But still, when you would run this, each icon will stick to the next! So how do we divide them evenly. You could calculate this and add margins or change something else… But in our example we again use a special control derived from Panel, called SplitPanel and use this as ItemsPanelTemplate on the listbox.

This control will calculate the total amount of space available and evenly change the width of each item to match this.

So when all is done, you should get following result: ( source code is up on my GitHub here… )


Fixing header when scrolling content on windows phone page

Thanks to Kévin Gosse @KooKiz for tweaking a part of the code to get it right 🙂

So, sometimes you want to try something different on Windows Phone and this time I wanted to tweak the scroll animation of a page.

The setup I was looking for visually: You have a content page ( for example in a news app ) that shows a nice article with an image and a title caption on top. But as you can see the text of the article is too large, so when the user scrolls down to view the text, we want to keep the header title in view! ( or something else depending on your taste )

TextPage TextPage2 TextPage3

You would think this would be easy, but it alas! Again we are faced with a stripped down XAML version in Windows Phone, so we don’t have the same ‘control’ over these events as in WPF.
Now with some small tricks, it’s possible to get a decent working version, so let’s get to it!

Just create a new Windows Phone app in Visual Studio, when doing so you’ll get a MainPage.xaml right away.

We are going to change the page, so delete everything inside the LayoutRoot grid and replace it with following xaml

If you did this correctly ( also there should be a bear.jpg image inside the Assets/Images folder 😉 ) you’ll get a nice page representing the article!


You’ll notice the page has a scrollviewer that contains everything except the header title! The trick we are going to use, is manipulating the location of the header title ourselves.

Do note that we need to se the ManipulationMode of the ScrollViewer to Control to get a smooth UI effect! ( see xaml above ) Otherwise the UI won’t be notified enough with the scrollviewer scroll values – is a performance optimization that we now need to bypass.

To manipulate the location of the header title, we need to keep track of the scroll offset of our scrollbar. But the scrollviewer itself doesn’t give use enough info, the way to get this info is by hooking into the ValueChanged event of the VerticalScrollBar that is inside the ScrollViewer !

So first we need to get hold of this VerticalScrollBar, this is done by using the VisualTreeHelper on our ScrollViewer and hook onto the ValueChanged event.

Inside our ValueChangedHandler we will animate our header title to the corresponding vertical offset of our ScrollBar, but we will stop animating when the vertical offset is higher than the top position of our header title when the page was launched!! Because if our vertical offset has reached that value this means that the header title will be positioned at the top of our page!

The top value of the header title is called _borderTop and is calculated at page load.

With that in place all is looking great, except for one thing: what to do when the user compresses the ScrollViewer by dragging the content down when the page is at it’s initial position. This behaviour is sometimes used in listboxes to get that ‘pull to refresh effect’. When someone does this on our page, the header title will stay at it’s position while the rest is dragged down… not a nice effect indeed. Let’s fix this.

This can be solved by hooking into 2 other events of the ScrollViewer: MouseMove and MouseLeftButtonUp. MouseMove to detect the compression and MouseLeftButtonUp to know when the user stops the compression.

When the compression occurs, you’ll notice a positive TranslateY value on the CompositeTransform of the ScrollViewer content, we’ll use this to also animate our header title. When the compression is stopper, we check if we had a positive TranslateY value and if so, we reset the position of the header title.

If all goes well, the end result should look like this


To not mis anything, the demo project is up on my github here https://github.com/Depechie/FixedTextScrollDemo

Facebook like settings pane with gestures – windows phone

In my last post I showed you how to mimic the settings pane used in the Facebook app on windows phone through use of Visual States ( read it here… ).
But that solution was not 100% the same as in the Facebook app, it missed drag gestures.

So, let’s add those 🙂

First you’ll need to add the Windows Phone Toolkit nuget ( http://phone.codeplex.com/ )! Because it has a GestureListener, that you can hook up in XAML to almost any control. It will give you events for all possible windows phone gestures.

You’ll notice that with this, we are hooking into the DragDelta and DragCompleted events. We are going to use these events to animate our page.

In the OnDragDelta event we will check to see if there has been a Horizontal gesture detected and if so, depending on what pane is already open, show the animation on screen! Take a look at the code below and you’ll notice some tricks to get this done! Basically we calculate how far the user is dragging the content and if he get’s passed DragDistanceToOpen ( or ToClose ), we will start the rest of the animation.

If the user stops the drag gesture before reaching the given distance variable, we animate the screen to the given distance… ( through use of the SetHorizontalOffset method )

All this does is just animating the screen ‘while’ dragging.

Now next, we use the OnDragCompleted to finalize our animation, it’s almost the same code as above. The only difference now is that if the user has stopped his gesture and he has not passed the given distance, we will now reset the content to it’s original position. Giving us a ‘snap back’ animation.

To do the actual animation of the pages, we use the Animate method on the given grids ( RootGrid and SettingGrid ), here the OpenSettings example…

Now… if you’ll try this in your code you will see that some methods are missing! This because we are using some extension methods. I’m not taking credit for this, they are created by Colin Eberhardt, you can follow him on twitter https://twitter.com/ColinEberhardt or take a look at his blog http://www.scottlogic.com/blog/ceberhardt/ !

The extension methods used are: SetHorizontalOffset, GetHorizontalOffset and Animate the actual code is in the example provided below.

So with all this in place you will get a nice settings pane example with drag gestures enabled, just like the facebook app… if they do it the same way? I doubt it 😉


Everything is up on my github, just take a look here https://github.com/Depechie/SettingsPageAnimation


Facebook like settings pane – windows phone

So, we all took a good look at the ‘new design’ we got with the new Facebook app for Windows Phone and what is one of the most remarkable changes? That the settingspane now slides in/out from the left… Take a look at it here:


So not a different page like we are used too in Windows Phone. So let’s get started at creating our own version!

Just fire up your Visual Studio and create a new Databound Windows Phone app, it will already contain a MainPage with some data on it.

2013-07-22 23_06_51-Start Page - Microsoft Visual Studio

Open up this MainPage.xaml and we are going to add a second Grid that is placed outside the viewingfield on the left side. So we will set it’s margin to –480. The complete xaml looks like this: