A day with .Net

My day to day experince in .net

SQLite with Windows 8 apps

Posted by vivekcek on September 6, 2012

Installing the SQLite

The first thing you want to do is install the package.You can do this from within Visual Studio itself in all editions.
From the Tools menu, choose Extensions and Updates and then choose the Online section (on the left of the dialog) and
search for ‘sqlite’ in the search term. This will show you the SQLite for Windows Runtime package.

Using the new package in your C#

Now that you have the SQLite for Windows Runtime package installed in your Visual Studio environment, you want to use it. In a managed (.NET) app you would do the following steps.
First, create your app (e.g., a Blank XAML app is fine). Once within your app, use the Add Reference mechanism to get to
the next step. Now you will not be browsing for any DLL directly like you would in a traditional .NET.
What we are adding here is a reference to the Extension SDK…not the library itself, a small but important distinction.
Once in the Add Reference dialog choose the Windows\Extensions view (see on left) and you’ll see SQLite for Windows Runtime.

To correctly use this in a managed app you’ll need to select that *and* the C++ runtime as seen below:

<

Now with this involved you can grab a managed wrapper to call the SQLite APIs. I personally recommend the sqlite-net library (available via NuGet) to make this easier for you.

Using the sqlite-net library you can perform tasks using a model similar to LINQ2SQL where you can have types represent database entities:

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
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;
using SQLite;

namespace App1
{

    public sealed partial class MainPage : Page
    {
        public MainPage()
        {
            this.InitializeComponent();
            var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "person.sqlite");
            using (var db = new SQLite.SQLiteConnection(dbPath))
            {
                db.CreateTable<Person>();

                db.RunInTransaction(() =>
                     {
                         db.Insert(new Person() { Name = "vivek", Surname = "ps" });
                     });
            }

        }

    }

    public class Person
    {
        [PrimaryKey, AutoIncrement]
        public int Id { get; set; }

        [MaxLength(30)]
        public string Name { get; set; }

        [MaxLength(30)]
        public string Surname { get; set; }

    }

}

Now you just need to specify your architecture for your app (x86, x64, ARM) and when you build, the appropriate sqlite3.dll will be packaged in your app automatically. This also happens during the packaging step for the store so the right item is included for each architecture-specific package.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s