The Complete Beginners Guide to using SQLite SQL database in a Windows 8 App

[Writing this on the train home so may be a bit rushed – sorry :) ]

Install the Bits

  1. In Visual Studio 2012, go to Tools menu, Extensions and updates.
  2. Search for “SQLite for Windows Runtime” – install it.
  3. In your Windows Store app, install the NuGet package called “sqlite-net” – these are the LINQy wrappers over SQLite.

Use SQLite

Create an “entity” that you want to store in a table, e.g. Customer.

Create some plain properties, choose one to be your primary key, e.g.

[SQLite.PrimaryKey, SQLite.AutoIncrement]
public int Id { get; set; }
public string Name { get; set; }

 

Create a constant to be the filename of the SQLite db on disk:

private const string dbFileName = "data.dat";

Insert a Customer

var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, dbFileName);

var newCustomer = new Customer{Name="test"};

using (var db = new SQLiteConnection(dbPath))
{
    int autoGeneratedPrimaryKey = db.Insert(newCustomer);
}

Delete a Customer

using (var db = new SQLiteConnection(dbPath))
{
    int autoPrimaryKey = db.Insert(customerToDelete);
}

Load a Customer

This will get all customers:

var allCustomers = from c in db.Table<Customer>()
        select c;

Create Customer Table (will only do it if not already exists)

db.CreateTable<Customer>();

Making Your Solution Build

You cannot have your project/solution set to any cpu, otherwise it will fail to build. Instead choose a specific platform x86, x64 or ARM.

When you create a package for the store, I’m assuming you uncheck the all platform box and tick each individual platform to create multiple versions for each architecture (I haven’t done this yet…).

 

(If you’re developing a Windows 8 app and you’d like to learn more about how to choose features for version one of your app, etc check out my Designing Windows 8 Apps Pluralsight course.)

Add comment

Loading