How can I use sql Lite Database in IoT

Question

I normally use DataTables with SQl Lite in my C# desktop Apps.

Using Raspberry Pi 2 with Windows IoT and C# the DataTable does not exist anywhere in this framework (searched using Object Browser).

So, can any database repositories be used in this architecture like Sql lite and if not what are the equivalents?

Answer 1

Yes, you can use SQLite but you need to use a platform specific version of the DLL. This one is approved for Windows IoT usage and is stable.

You can install the Visual Studio extension for SQlite from http://sqlite.org/download.html which enables a Database visualiser.

But what you really need is to install the correct DLL or package from NuGet

1. Run the following commands in the Package Manager Console:
   • Install-Package SQLite.Net-PCL
2. Add the required references
   • SQLite for Universal App Platform

Sample code

using SQLite.Net.Attributes;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices.WindowsRuntime;
using Windows.Foundation;
using Windows.Foundation.Collections;
using Windows.UI.Xaml;
using Windows.UI.Xaml.Controls;
using Windows.UI.Xaml.Controls.Primitives;
using Windows.UI.Xaml.Data;
using Windows.UI.Xaml.Input;
using Windows.UI.Xaml.Media;
using Windows.UI.Xaml.Navigation;

// The Blank Page item template is documented at http://go.microsoft.com/fwlink/?LinkId=402352&clcid=0x409

namespace SQLitePiExample
{
    /// <summary>
    /// An empty page that can be used on its own or navigated to within a Frame.
    /// </summary>
    public sealed partial class MainPage : Page
    {
        string path;
        SQLite.Net.SQLiteConnection conn;

        public MainPage()
        {
            this.InitializeComponent();

            path = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db.sqlite");

            conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), path);

            conn.CreateTable<Message>();
        }

        private void Retrieve_Click(object sender, RoutedEventArgs e)
        {
            var query = conn.Table<Message>();

            string text = "";
            foreach (var message in query)
            {
                text = text + " " + message.Content;
            }
            textBlock.Text = text;
        }

        private void Add_Click(object sender, RoutedEventArgs e)
        {
            var s = conn.Insert(new Message()
            {
                Content = textBox.Text
            });


        }
    }
}

^{* Credits to Chris Briggs found at http://blog.chrisbriggsy.com/Using-SQLITE-in-Windows-10-IoT-Core-Insider-Preview/}