//Build 2017 – EF Core 2.0 / UWP streamable install / WinForms-WPF

Entity Framework Core 2.0: data, mobile, server, IoT, and more
Rowan Miller

This session presented the new features brought by EF Core 2.0, which is out as Preview 1 right now.

Like .NET Core, EF Core is built against .NET Standard 2.0 which means that it can be used on any platform: Windows, Xamarin, HoloLens, IoT, Linux, etc.

Here’s the summary of the new features demonstrated

  • Global Query Filters
  • Flexible Mapping
  • Context Pooling
  • Compiled Queries
  • Some currently in development features were also shown like complex type mapping (which already exists in EF6) and better migration with « SeedData ».

    Global Query Filters allows to define filters at the DbContext level.
    For example in soft delete scenario, instead of excluding « IsDeleted = True » in every queries, we can define in the configuration of the DbContext to exclude it globally.
    This is done with the following code:

    modelBuilder.Entity<Blog>().HasQueryFilter(p => !p.IsDeleted)

    The interesting part of that line of code is that the QueryFilter part is evaluated at every query.
    This allows to do things like this.

    public class MyDbContext : DbContext
    {
        public string TenantId {get; set; }
    
        public void OnConfigure(...)
        {
            modelBuilder.Entity<Blog>().HasQueryFilter(b => b.TenantId == TenantId);
        }
    }
    

    Flexible Mapping allows more possibilities when mapping a C# object to a table.
    One possibility is mapping a private field to a column.

    modelBuilder.Entity<Blog>.Property<string>("TenantId").HasField("_tenantId")
    

    When we want to retrieve the value of the column, we can write that line

    var value = EF.Property<string>(row, "TenantId")
    

    Context Pooling allows the use a pool of already instantiated DbContext (like a ThreadPool) when working with DI.
    Enabling it is pretty straightforward, just replace « .AddDbContext() » by « .AddDbContextPool() ».
    It has some restrictions because the DbContext needs to be cleaned before each use.

    Compiled Queries are now available in EF Core.

    var query = EF.CompiledQuery((DbContext dbContext, int id) => dbContext.Table.First(a => a.Id == id))
    var result = query(dbContext, id);
    

    During the session, the speaker demonstrated the « EF Everywhere » by running a UWP application with EF Core on a Raspberry Pi named « Unicorn Launcher » that launched a small rocket into the air.

    https://docs.efproject.net
    https://github.com/aspnet/entityframework
    https://github.com/rowanmiller/demo-efcore

    XAML custom controls for UWP: Start to finish
    John Bristowe, Nikola Metulev

    This was a disappointing session.
    I tried my chances with this session based on its description which promised to explain the best pratices and UX design patterns for accessibility and input methods (mouse, keyboard, game controller, HoloLens) when creating custom controls in XAML.

    But in fact, it was a plain simple session on how to use and create controls in XAML (that is the same thing since the introduction of WPF 10 years ago).

    This session was more of an advertisement for UWP Community Toolkit and UI for Telerik.

    Here’s what we saw

    • 1st party controls: Button, TextBox, etc.
    • 3rd party controls: UWP community toolkit, Telerik, etc.
    • Restyled controls (Style, Template)
    • Extended controls (Inheriting controls to add behaviors)
    • Custom controls

    Nextgen UWP app distribution: Building extensible, stream-able, componentized apps
    John Vintzel, Sandeep George

    This session talked about some new features for UWP concerning the distribution of the apps.

    First, we were shown the « App streaming installation » feature that allows to download only a « required » part of the application and run it even before its full download. This can be compared to how Xbox games can be partially downloaded and run.

    This is available right now in the Creators Update.

    All the magic lies in a manifest file named « AppxContentGroupMap.xml » that is included in the UWP app. The Windows Store then uses this file to know how to install it.
    This feature comes with all the needed API to finely manage how to application reacts to missing parts, prioritizes parts to download and gets feedback on the progression of the download.

    APIs are also available to debug the app to simulate a state of partial download.

    For more information, see https://aka.ms/uwpstreaminginstall

    Coming in the Fall Creators Update, optional packages for UWP will be available.
    We can see it as DLC or plugins for existing UWP apps.
    It extends an existing app (or many) by adding contents and/or features and it can be sold separately.
    A UWP app that allows for optional packages can also receive packages from other publishers.

    For more information, see https://aka.ms/uwpoptionalpackages

    Modernize WinForms and WPF apps with maximum code reuse, cross-platform reach, and efficient DevOps
    Joshua Weber, Mike Battista

    This session was also non-surprising.
    It was aimed at those stuck with WinForms/WPF and who didn’t followed the other technologies in the meantime.

    To start, we were explained how to leverage the Desktop Bridge to take advantage of all the new APIs of Windows 10.
    Then, by moving the codebase of an existing app to a .NET Standard 2.0 lib, the code was reused into a Xamarin app.
    To end this session, we were presented Visual Studio Mobile Center to setup CI/CD for the Xamarin app.

//Build 2017 – Visual Studio Mobile Center / Fluent Design

Visual Studio Mobile Center: Ship mobile apps faster
Keith Ballinger, Thomas Dohmke

This session presented the new Visual Studio Mobile Center that aims to bring into a single place every aspect of continuous integration and delivery as well as every services used in mobile development like authentication and push notifications.

In a few simple steps, we can quickly setup a continuous build on each push to a git repository (hosted in GitHub, VSTS, BitBucket). Then UI tests will be run by Xamarin Test Cloud, and finally the app can be distributed by Hockey App.
Also we are given 2 lines of code to add to our apps to enable Application Insights.
All that without leaving Mobile Center.

It supports Xamarin.iOS, Xamarin.Android, Objective-C, Swift, Java, ReactNative and UWP apps.
Cordova is not supported for the moment.

The build agent is hosted in the cloud, which means that we don’t need to have a Mac to compile iOS apps.

New features were announced

  • Partial support of UWP
  • Automated store distribution for UWP
  • More supported git hosting services (VSTS, BitBucket)
  • Push notifications campaign with analytics data-based targeting

This service is still in preview and free for the moment.
No pricing available as of now.

If you want to checkout Visual Studio Mobile Center, here’s the url: https://mobile.azure.com

Build Amazing Apps with Fluent Design
James Clarke, Paul Gusmorino

Another session packed with lots of sweet things, especially for me who loves XAML/UWP and UX design.

I recommend that you watch the replay as it’s a really visual session, difficult to transcribe by words.

This session presented all the new concepts of Fluent Design and how to implement them in XAML for UWP.

Fluent Design is based on 5 concepts:

  • Light: subtly highlight elements on the UI to give users feedback and make for discoverability
  • Depth: play with different levels of depth (like a parallax) to give a dynamic feeling to the app
  • Material: apply different kinds of « materials » on some parts of the application, rather than just putting flat colors, to make the app prettier
  • Motion: place animations in the app so that it feels fluid, especially when navigating from page to page
  • Scale: adapt to the space available, mostly true when making UWP apps that works on PC and on HoloLens

In XAML, some new features were shown to support for those concepts:

  • Reveal Highlight: dynamic lightning on controls when approaching the cursor and/or clicking (<Button Style= »{ThemeResource ButtonRevealStyle} »/>)
  • Commanding in collections: add support of Context Menu and swipe into collections like ListView

New controls for UWP were also shown:

  • NavigationView: full-featured navigation panel (burger menu) with its content pane.Can be seen like a SplitView++
  • RatingsControl
  • ColorPicker
  • Repeater: Repeat a group of UI elements
  • ParallaxView: Scroll its content in sync with another scrollable control to give parallax effect
  • 360° video support in UWP
  • SVG support in XAML

The demo app can be found at http://aka.ms/buildcast

Documentation on fluent design can be found at the following links
http://design.windows.com
http://fluent.microsoft.com

//Build 2017 – Xamarin: The future of mobile app development

Lots of great announcements in this crowded session.

Xamarin Live Player
Announced at the morning keynote, Xamarin Live Player seems to be an awesome tool for developing mobile apps.
Directly on real devices (both iPhone and Android), it adds live reloading of both the UI and the C# code, without even saving the files in Visual Studio!
That way, we don’t waste time when we want to make a change (big or small). No need to stop debugging, make a small change, recompile, and restart debugging.

It works both on Visual Studio and Visual Studio for Mac with an alpha version of the Xamarin SDK.

On the devices, an application « Xamarin Live Player » has to be installed. After that we only need to pair our device to Visual Studio.
A specific debug option will appear in Visual Studio to launch a debug session through the Xamarin Live Player that will interpret our app rather than install it the classic way, enabling live reloading.

This works both for Xamarin.Forms and classic Xamarin.
A really great side effect of the interpretation part of Xamarin Live Player is that when developing on Windows, no Mac is needed for debugging (it still is required for release)!

iOS provisioning wizard
One thing that is really really bothering when developing iOS apps is that we need to declare lots of things in the Apple Developer portal: one certificate per build/developer machine, one registration per device, one App ID declaration per application and finally one provisioning profile which links all the previous items.

That means when starting a new app, it really is complicated to setup everything.
Even when working on an already setup project, if there is a new developer or a new device, we need to update the developer portal and revoke certificates, and so on.
This results in lot of lost time.

Today Microsoft announced the upcoming of an integrated wizard inside VS for Mac that handles all that declaration part just by a handful of clicks:
– No certificate on the Mac? Click, done.
– No App ID matching the bundle name? Click, done.
– Device not registered in the portal? Click, done.
– Device not part of the existing provisioning profile? Click, done.
This will really simplify developing for iOS.

Embeddinator 4000
Just like we can use native libs (Objective-C, Swift, Java) inside a .NET Xamarin app, Microsoft announced the ability to use .NET libs inside native apps written in ObjC, Swift and Java.

It is even possible to write UI code in .NET and use it in a native app!

More info at: https://github.com/mono/embeddinator-4000

And lots of other things…
I won’t get into the details, but here are some of the other announcements

  • Easier native bindings: CocoaPods importer, SwiftNetifier
  • Multi process debugging for iOS and Android
  • New integrated SDK Manager for Android inside Visual Studio
  • Xamarin.Forms for MacOS, Linux and WPF
  • Xamarin.Forms pages embedding inside native apps
  • AOT compilation for Android/MacOS; Hybrid mode compilation (AOT+JIT) for Android/MacOS
  • SkiaSharp for cross-platform 2D
  • UrhoSharp for cross-platform 3D

If you want to know more, be sure to watch the replay on Channel9!

//Build 2017 – .NET Core / Standard / ASP.NET Core 2 / Cognitive Services

For this first day at Build, I attended several sessions revolving around .NET Core and the Cognitive Services running on Azure.

Three Runtimes, one standard… .NET Standard: All in Visual Studio 2017
Scott Hanselman, Scott Hunter

This session was more about a global overview of all the major frameworks available in Visual Studio than an in-depth talk on .NET Standard.

We were reminded that Microsoft recently put reference resources for Architecture in .NET on https://dot.net/architecture

To start, the Scotts explained us what are the available .NET Platforms (.NET FX, .NET Core, Mono) and how .NET Standard helps for easier code sharing across platforms.
One sentence that I found particularly relevant for describing .NET Standard is « .NET Standard is a contract that .NET Platforms must comply to »

Nothing was announced for the availability of .NET Standard 2.0 (that is currently in preview) but as said during the Keynote, .NET Core 2.0 and ASP.NET Core 2.0 are available today as preview also.

Microsoft estimates that currently 70% of the NuGet Packages are .NET Standard 2.0 compliant due to the large new API surface covered by this new version of .NET Standard.
Those packages don’t need to be recompiled to be used on any platforms!
The other 30% are mostly framework-specific like WPF libs or ASP.NET libs, so they won’t ever be .NET Standard compliant.

When VS2017 was first introduced, it came with a Live Unit Testing feature that were only usable with .NET FX projects.
It was announced that the upcoming VS2017 Update 3 (currently in preview) will add this feature to .NET Core projects!
This update will also come with support for C# 7.1.

After that we were given a brief tour of Visual Studio for Mac and how it come closer and closer to the classic Visual Studio: same tooling, same project templates, and so on.

A conference named « .NET Conf » will take place in September 2017 for 3 days and will cover any kind of subject about .NET.

Introducing ASP.NET Core 2.0
Daniel Roth, Maria Naggaga Nakanwagi, Scott Hanselman

This session was about the differences and new features introduced by ASP.NET Core 2.0 preview compared to ASP.NET Core 1.1.
I recommend that you watch the replay as there was a lot of technical goodness that I can’t really transcribe here.

If you want to develop ASP.NET Core 2.0 apps, you will need VS2017.3 currently in preview.
Microsoft says that ASP.NET Core 2.0 is 25% faster than 1.1.

The first thing that we saw was the dotnet cli and how easy and quick it is to bootstrap an ASP.NET Core app without ever going into Visual Studio.
It can be resumed by those two commands : dotnet new, dotnet run.
The first one creates a sample ASP.NET Core project and the second one compiles and runs it.

« dotnet new » will bootstrap an ASP.NET Core project based on the version of the dotnet tooling.
This version can be set by a « global.json » file placed either in your user folder (to globally define the dotnet tooling version) or per folder.
That way you can have dotnet 1.1 running inside a folder, and dotnet 2.0 running inside another one.

One great thing that was announced is Microsoft resolved several shortcomings of the new « all NuGet packages » strategy.
.NET Core introduced a lot of small packages especially for ASP.NET Core.
For example, you had one packages for servicing Static Files, one for MVC, one for Configuration, and so on.
This resulted in a lot of packages to download on our machines and significantly large packages to publish in production (every dlls that were used were uploaded along our web app)

Those shortcomings were resolved by a single new NuGet package named « Microsoft.AspNetCore.All », this package includes all the small packages used almost all the time like MVC.
And to avoid downloading gigabytes of packages, this « Microsoft.AspNetCore.All » is already included in the dotnet tooling version 2.0.
That means that when publishing our web app, we only need to deploy our own dll, resulting in way smaller package.

ASP.NET Core 2.0 introduces a new startup pattern.
DI configuration is not done in the Startup.cs file anymore, but in the Program.cs file.

ASP.NET Core 2.0 also introduces a new version of Web Pages.
We can now create Razor pages (cshtml) that don’t need to be backed by a controller.
For that, just create a folder named « Pages » and create cshtml files inside it.

Those cshtml files need to have the « @page » tag defined inside them to be treated as Pages.

Then if you call the url « http://localhost/mypage &raquo;, it will resolve to the /Pages/mypage.cshtml. No need for a controller that would only return a view.

More complex scenario are enabled by defining inlined « PageModel » which is a class that represents the « @page » tag.
You can then define behaviors for GET,PUT,POST,etc. verbs and also read parameters passed by url.
Like for this url « http://localhost/mypage/5 &raquo;, you can define the following @page « {id: int} ».
The PageModel instance of the page will then have access to the id passed by the url.

It is no more necessary to add the Application Insights package when creating ASP.NET Core apps.
When deploying to Azure, Azure will recognize the type of application and will propose to automatically inject Application Insights.
This also enables the new « Snap points » feature for debugging live production apps directly from Visual Studio.

Using Microsoft Cognitive Services to bring the power of speech recognition to your apps
Ivo Santos, Khuram Shahid, Panos Periorellis

This session talked about the speech recognition services available in Cognitive Services, especially TTS/STT, Custom Speech Service and Speaker Recognition.

Speech API is rather classic and enables Text-to-Speech and Speech-to-Text scenario.
Using it is simple:

Custom Speech Services is interesting as it allows to finely customized speech recognition based on our business requirements.
An example that was given is a kiosk in an airport where customers would ask for the next flight to a city in a noisy environment (announcements, chattering, etc)
Custom Speech Services offers a portal where we can train the speech recognition AI with sample acoustic data, and we can also upload vocabularies applied to our business.

Using Custom Speech Services in code is exactly the same as Speech API, you just need to specify an url given by the portal that represent your trained AI.

Lastly we were demoed the new Speaker Recognition API that recognizes who speak.
This is particularly promising as it is an on-the-fly trained AI which differentiates talking people at first, and then is capable of recognizing the same person throughout the conversation.
The demo was a bot running a trivia game, and which was attributing points to the person answering just by recognizing who talked. But it didn’t really worked.

//Build 2017 – AI Immersion Workshop

Ahead of the three days of the Build, Microsoft organized a few whole day sessions.
I attended one of them : the « AI Immersion Workshop » session

As the title gives in, it was focused on AI running on Azure.

It started with a keynote presenting all the available services for AI and Machine Learning on Azure.
To begin, we were presented the Cognitive Services (face recognition, image description, and so on).
You can find more on this here: https://azure.microsoft.com/en-us/services/cognitive-services/
The source code of a sample application using Cognitive Services can be found here: https://github.com/Microsoft/Cognitive-Samples-IntelligentKiosk

After that, the keynote focused on machine learning and image recognition.
A sample app on GitHub (https://github.com/Azure/Embarrassingly-Parallel-Image-Classification) shows how to use machine learning to analyze aerial images and determine what kind of terrain it is.

There was also a few customer stories about machine learning : lung cancer detection and electric pole inspection by drones.

We were reminded that SQL Server 2017 was available for a few weeks now, and that it supports AI stored procedures written in R or Python.
This is especially interesting as it is a best practice in AI/Machine Learning to put the computation logic close to the data.

After this keynote, there were a handful of workshops.
I chose to attend « Building intelligent SaaS applications »

In this workshop, we deployed a multitenant ASP.NET website that sell venue tickets, and we configured machine learning for it to answer questions like :

  • What venues are the least performing?
  • Will my future venue sellout if placed in Seattle?
  • How much a discount should I give for my venue to sellout?
  • Is there customers that go to multiple venues in a given area?

Everything we did in that workshop can be found on GitHub : https://github.com/Microsoft/WingtipSaaS

#UWPXAML – Compiled Binding – What’s new in the Anniversary Update?

Coming this summer, Windows 10 Anniversary Update will be more or less a Windows 10 v2.0.
With it comes a lot of new things for the windows app development, and a lot of new features and fixes for Compiled Binding.

Today we’ll see what those new features are and how to use them.

If you want to try them out yourself, you’ll need a Windows Insider build of Windows 10 with its Windows SDK and create a UWP project targeting a min version at least equals to Build 14383. Do not install it on your main work station, it will break any non-Anniversary Update apps.

For this post, I’ll use Windows 10 Build 14383 (available on Fast Ring) with Visual Studio 2015 Update 3.
The Windows SDK for Build 14383 can be found here: https://insider.windows.com/

In case you missed it, I wrote a few posts about compiled binding in the past.
Feel free to read them (again).

 

Function binding

Previously, it was possible to directly bind events to methods which followed specific rules regarding their parameters, but we were unable to bind the result of a method to a property without a converter which was calling the method for us.

Now, it’s possible to bind methods directly to properties without the need for a converter!
You can provide whatever method you like as long as it has a public accessor and you provide the parameters.

MainPage.xaml.cs

public sealed partial class MainPage : Page
{
    public MainPage()
    {
        this.InitializeComponent();
    }

    public string GetHelloWorld()
    {
        return "Hello World";
    }

    public Person Roberts = new Person()
    {
        Firstname = "Dread Pirate",
        Lastname = "Roberts"
    };
}

Person.cs

public class Person
{
    public string Firstname { get; set; }
    public string Lastname { get; set; }

    public string GetFullname()
    {
        return $"{Firstname} {Lastname}";
    }
}

MainPage.xaml

<TextBlock Text="{x:Bind GetHelloWorld()}" />
<TextBlock Text="{x:Bind Roberts.GetFullname()}" />

The method can have parameters.
You can provide them by setting constants in the XAML or by using data from your ViewModel.

MainPage.xaml.cs

public sealed partial class MainPage : Page
{
    public MainPage()
    {
        this.InitializeComponent();
    }

    public DateTime Today = DateTime.Now;

    public Person Someone = new Person()
    {
        Firstname = "Some",
        Lastname = "One"
    };        

    public string GetPersonFullname(Person person)
    {
        return person.GetFullname();
    }
}

MainPage.xaml

<TextBlock Text="{x:Bind Today.ToString('d', {x:Null})}" />
<TextBlock Text="{x:Bind GetPersonFullname(Someone)}" />

Does the binding is automatically updated when the data is updated?
Well, it is said to work if you set the Mode of the compiled binding to OneWay/TwoWay but I wasn’t able to compile it under VS2015. A few not really understandable compile errors appeared when I used TwoWay binding.

Along that, you may notice some errors appearing in the Error list when using parameters inside your binded functions stating « A value does not fall within the expected range ». It’s a bug of VS2015, but it compiles and runs fine anyway.

I hope those annoying errors will be fixed in the release version.
 

Dictionary indexers

As part of supporting most use cases without the need for a converter, x:Bind now supports access to a specific key in a dictionary.

MainPage.xaml.cs

public sealed partial class MainPage : Page
{
    public MainPage()
    {
        this.InitializeComponent();
    }

    public Dictionary<string, string> Dictionary = new Dictionary<string, string>()
    {
        { "A", "Alpha" },
        { "B", "Beta" },
        { "C", "Charlie" },
    };
}

MainPage.xaml

<TextBlock Text="{x:Bind Dictionary['A']}" />
<TextBlock Text="{x:Bind Dictionary['B']}" />
<TextBlock Text="{x:Bind Dictionary['C']}" />

It works great but there is a big shortcoming: You can only use constant strings directly set in the XAML.
Using anything else than a string, like an integer, will result in a compile error.
Even string data from your ViewModel won’t work.
 

Explicit value cast

Contrary to classic Databinding which uses duck typing to check if the binding is valid, compiled binding checks at compile time that the given data matches the property to which it is binded to.
In other words, if the value you’re trying to bind has no implicit cast to the type of the property, you can’t bind it using x:Bind without a converter.
Even if the value’s type declares an explicit cast…

As of the Anniversary Update, you will be able to declare an explicit cast inside a compiled binding, C#-style.

MainPage.xaml.cs

public sealed partial class MainPage : Page
{
    public MainPage()
    {
        this.InitializeComponent();
    }

    public bool? IsThreeStateNull = null;
}

MainPage.xaml

<CheckBox Content="IsThreeState = False" IsThreeState="{x:Bind (x:Boolean)IsThreeStateNull}" />

When casting explicitly, you’ll need to refer to the class type by its XAML namespace.
In this case, Boolean is part of the « x: » namespace declaration.
 

Implicit Visibility conversion

Who don’t know the legendary BooleanToVisibilityConverter?
Everyone, since the first time WPF came around, implemented it over and over in every projects.

x:Bind no longer requires that, it now converts booleans to Visibility by itself! Hurray!
You just need to bind a boolean to a property like Visibility.

MainPage.xaml.cs

public sealed partial class MainPage : Page
{
    public MainPage()
    {
        this.InitializeComponent();
    }

    public bool IsVisibleFalse = false;
    public bool IsVisibleTrue = true;
}

MainPage.xaml

<TextBlock Text="I'm not visible!" Visibility="{x:Bind IsVisibleFalse}" />
<TextBlock Text="I'm visible!" Visibility="{x:Bind IsVisibleTrue}" />

 

Conclusion

You can find a sample project on GitHub regrouping all the examples we saw in this post: http://github.com/TimLariviere/CompiledBinding-AnniversarySample

Xamarin – Using SQLite.NET Async with PCL

With Xamarin, it is now easier than ever to share most of the logic between your apps.
Most today’s apps use a lot of data, so deciding how to store them locally can prove a challenge.
SQLite provides a great way to store and query those easily and does support Xamarin. Hurray!

In this post, we’ll see how to setup SQLite in a portable project (PCL) shared by two Xamarin projects (Android, iOS) and a UWP project in Visual Studio.
It should be more or less the same with Xamarin Studio.

If you’re interested in using SQLite.NET with Xamarin.Forms, see the Xamarin.Forms documentation instead.

Portable class library and NuGet packages

First of all, we’ll need to create a portable class library supporting Android, iOS and Windows.
Conveniently, Visual Studio provides a template which exactly fits our needs.

PCL.PNG

If for any reason you can’t or won’t use this template, you can use the generic « Class Library (Portable) » template instead.
See Xamarin’s Introduction to Portable Class Libraries for more information on it.

Then, we’ll need to add references to SQLite.NET via the NuGet package SQLite.NET.Async-PCL v3.1.1 which comes along with its dependency SQLite.Net.Core-PCL.

Nuget1.PNG

That’s all we need to setup the PCL in order to use SQLite.NET.

Using SQLite.NET

Now for the tricky part.

We can now use SQLite in the PCL and thus write it once and be able to use it on each platform. But in order to do so, we’ll need a connection to the SQLite database (and need to create it if it’s not the case).

As we’re targeting three different platforms, each with its own rules on how to access the storage space, SQLite.NET needs several things in order to work : a path to where SQLite should store the database file and a custom implementation of the wrapper around the native sqlite3.

For that, as we’re using SQLite.NET Async, we need to create a SQLiteAsyncConnection.
It’s fairly simple.

public class SQLiteDatabase
{
    public static SQLiteAsyncConnection GetConnection(string path, ISQLitePlatform sqlitePlatform)
    {
        var connectionFactory = new Func<SQLiteConnectionWithLock>(() => new SQLiteConnectionWithLock(sqlitePlatform, new SQLiteConnectionString(path, storeDateTimeAsTicks: false)));
        return new SQLiteAsyncConnection(connectionFactory);
    }
}

See what we did there?
We have created a connection to the SQLite database but all the platform-specific parts are passed as parameters, allowing us to have a common method in the PCL project.

The path parameter is a file path which SQLite will use to store its db file.
The ISQLitePlatform parameter is described in the SQLite NuGet package and represents an internal implementation of SQLite for a given platform. Don’t worry, it has already been done for us for Android, iOS and UWP.

Of course, you can use dependency injection or any architecture that fits your needs instead. The key point here is to leave the implementation detail to the output projects.

We’ll come back shortly after to these platform-specific parameters.

For now enjoy!

public class MyEntityRepository
{
    private SQLiteAsyncConnection _connection;

    public async Task InitializeAsync(string path, ISQLitePlatform sqlitePlatform)
    {
        _connection = SQLiteDatabase.GetConnection(path, sqlitePlatform);

        // Create MyEntity table if need be
        await _connection.CreateTableAsync<MyEntity>();
    }

    public async Task<MyEntity> CreateAsync(string name)
    {
        var entity = new MyEntity()
        {
            Name = name
        };
        var count = await _connection.InsertAsync(entity);
        return (count == 1) ? entity : null;
    }

    public async Task<IEnumerable<MyEntity>> GetAllAsync()
    {
        var entities = await _connection.Table<MyEntity>().OrderBy(m => m.Name).ToListAsync();
        return entities;
    }
}

 

Android / iOS / UWP projects

Xamarin.Android and Xamarin.iOS will both need the exact same configuration regarding SQLite.NET, but UWP will need one extra small step.

Along with the obvious reference to the PCL project, we’ll need to reference the SQLite.NET-PCL v3.1.1 NuGet package as well.
Warning : it is not the same package as the one we used earlier.

This one automatically includes the previously seen ISQLitePlatform implementation of the targeted platform.
When referenced in an output project, it brings another dependency with it where all the platform-specific implementation is present.

  • SQLite.Net.Platform.XamarinAndroid when referenced in a Xamarin.Android project
  • SQLite.Net.Platform.XamarinIOS in a Xamarin.iOS project
  • SQLite.Net.Platform.WinRT in a UWP project

Last step is to use our MyEntityRepository by giving it all the platform specific parameters.

Android

private string GetDbPath()
{
    string documentsPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
    return Path.Combine(documentsPath, "Todo.db3");
}

protected override async void OnCreate(Bundle bundle)
{
    base.OnCreate(bundle);

    // Set our view from the "main" layout resource
    SetContentView(Resource.Layout.Main);
    var addButton = FindViewById<Button>(Resource.Id.AddButton);
    var todoListView = FindViewById<ListView>(Resource.Id.TodoListView);
    addButton.Click += OnAddButtonClicked;

    _todoRepository = new TodoRepository();

    var path = GetDbPath();

    await _todoRepository.InitializeAsync(path, new SQLitePlatformAndroid());
    var items = await _todoRepository.GetAllAsync();

    todoListView.Adapter = new ArrayAdapter<string>(this, global::Android.Resource.Layout.SimpleListItem1, items.Select(i => i.Text).ToList());
}

private async void OnAddButtonClicked(object sender, EventArgs e)
{
    var todoEditText = FindViewById<EditText>(Resource.Id.TodoEditText);
    var text = todoEditText.Text;
    todoEditText.Text = string.Empty;

    var todoItem = await _todoRepository.CreateAsync(text);
            
    var todoListView = FindViewById<ListView>(Resource.Id.TodoListView);
    var adapter = todoListView.Adapter as ArrayAdapter<string>;
    adapter.Add(todoItem.Text);
}

 

iOS

private string GetDbPath()
{
    string documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
    return Path.Combine(documentsPath, "..", "Library", "Todo.db3");
}

public override async void ViewDidLoad()
{
    base.ViewDidLoad();
    AddButton.TouchUpInside += OnAddButtonTouchUpInside;
            
    _todoRepository = new TodoRepository();

    var path = GetDbPath();

    await _todoRepository.InitializeAsync(path, new SQLitePlatformIOS());
    var items = await _todoRepository.GetAllAsync();
            
    TodoTable.Source = new TodoTableDataSource(items, TodoTable);
    TodoTable.ReloadData();
}

private async void OnAddButtonTouchUpInside(object sender, EventArgs e)
{
    var text = TodoTextField.Text;
    TodoTextField.Text = string.Empty;

    var todoItem = await _todoRepository.CreateAsync(text);

    var source = TodoTable.Source as TodoTableDataSource;
    source.Add(todoItem);
}

 

Universal Windows Platform

Remember that one extra small step ? Now is the time to take care of it.

Windows, contrary to Android and iOS, doesn’t have SQLite3 installed by default. So we need to bring sqlite3.dll along in our appx package otherwise we’ll get an exception « Unable to load sqlite3.dll ».
To do so, we’ll need to install the Visual Studio extension for UWP which can be found on the SQLite official website.

Then we just have to add two references in our UWP project.

  • SQLite for Universal Windows Platform (that’s our sqlite3.dll added by the VSIX)
  • Visual C++ 2015 Runtime for Universal Windows Platform (it’s a dependency of the former)

 
Capture.PNG
And that’s all for that extra small step.

Now we can use SQLite like in the Android and iOS projects.

public ObservableCollection<TodoItem> Items { get; set; } = new ObservableCollection<TodoItem>();

private async Task<string> GetDbPathAsync()
{
    var folder = Windows.Storage.ApplicationData.Current.LocalFolder;
    var file = await folder.CreateFileAsync("Todo.db3", CreationCollisionOption.OpenIfExists);
    return file.Path;
}

protected override async void OnNavigatedTo(NavigationEventArgs e)
{
    _todoRepository = new TodoRepository();

    var path = await GetDbPathAsync();

    await _todoRepository.InitializeAsync(path, new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT());

    var items = await _todoRepository.GetAllAsync();
    foreach (var todoItem in items)
    {
        Items.Add(todoItem);
    }
}

private async void OnAddTodoItemButtonClicked(object sender, RoutedEventArgs e)
{
    var text = TodoTextBox.Text;
    TodoTextBox.Text = string.Empty;

    var item = await _todoRepository.CreateAsync(text);
    Items.Add(item);
}

 
And there we are! That’s all for today.
Go make some happy users with great apps using SQLite.NET and Xamarin!
 
I’ve put up a sample project on GitHub, it’s a simple Todo list app working on Android, iOS and UWP : https://github.com/TimLariviere/XamarinSQLite