Using SQLite in Windows 8 Store Apps

In episode 52 of my Visual Studio Toolbox show, I showed a sample Windows 8 app that uses SQLite. The app manages customers and their projects. In this post, I will show you how to build that app.

[11/28/2012]: I uploaded the sample to the MSDN Code Samples . You will have to add the references and get the SQLite-net package, but you won't have to write any of the code.

The first thing you need to do is get SQLite. Select Tools|Extensions and Updates. In the Extensions and Updates dialog, select Online. Type sqlite in the search box. You will see SQLite for Windows Runtime. This is the SQLite binaries packaged up by the SQLite team. Install this.

Picture1

Next, create a new C# Windows Store project. Select the Blank App template and name the project SQLiteDemo. Next, add a reference to both SQLite and the VC++ Runtime Package.

Picture2

After you add the references, you will notice yellow triangles in the Solution Explorer. This is never a good sign. Build the app and you will see the following errors:

Picture3

When you create a new C# or VB Windows Store project in Visual Studio, it supports all architectures (x86, x64 and ARM) by default. But since you added SQLite to the project, you can’t build one package that targets all architectures. You have to build one target for each. Select Build|Configuration Manager and select x86, x64 or ARM from the Platform drop-down list. Now the project builds.

NOTE: If you are going to put this app in the Windows Store, you will want to build a package for each architecture and upload them all to the Store. When a user installs the app, the Store will send the correct package to the user.

Before you write code to work with SQLite, you should add a NuGet package that will make your life easier. Select Tools|Library Package Manager|Manage NuGet Packages for Solution. In the Manage NuGet Packages dialog, select Online and type sqlite-net in the search text box. The sqlite-net package enables you to work with SQLite databases using a LINQ syntax. After you install it, you will see two new files in your project: SQLite.cs and SQLiteAsync.cs.

Picture4

NOTE: You can also install this package using the Package Manager Console. Select Tools|Library Package Manager|Package Manager Console and type install-package sqlite-net.

Now you are ready to work with SQLite.

Open App.xaml.cs and add the following properties to the App class:

 public string DBPath { get; set; }
public int CurrentCustomerId { get; set; }

DBPath will represent the location of the SQLite database and CurrentCustomerId will represent the customer you selected on the main page.

In the OnLaunched method, add the following code in bold:

  // Get a reference to the SQLite databasethis.DBPath = Path.Combine(<br>    Windows.Storage.ApplicationData.Current.LocalFolder.Path, "customers.sqlite");// Initialize the database if necessaryusing (var db = new SQLite.SQLiteConnection(this.DBPath))<br>{<br>    // Create the tables if they don't exist    db.CreateTable<Customer>();<br>    db.CreateTable<Project>();<br>} 



 // Place the frame in the current Window
Window.Current.Content = rootFrame;

A SQLite database is a single file. In this app, it will be called customers.sqlite. The file lives in the app’s local folder. To connect to the database, the code uses SQLite.SQLiteConnection. This will create the database first if it doesn’t exist.

Once you have a connection to the database, you can work with tables. Unlike SQL Server, you don’t define tables using a SQL-like syntax. Rather, you create classes. SQLite will construct the tables from the classes. This is just like the Code First model in the Entity Framework.

You will use a simple MVVM (Model-View-ViewModel) pattern in this app, so create a Models folder in the project and add classes for Customer and Project:

 public class Customer
{
    [SQLite.PrimaryKey]
    public int Id { get; set; }
    public string Name { get; set; }
    public string City { get; set; }
    public string Contact { get; set; }
}
 public class Project
{
    [SQLite.PrimaryKey]
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public DateTime DueDate { get; set; }
}

These are basic POCO classes. Notice that you have attributed the Id fields and identified them as primary keys.

Back in App.xaml.cs, add the following to the top of the code file:

 using SQLiteDemo.Models;

Add the following code in bold:

 using (var db = new SQLite.SQLiteConnection(this.DBPath))
{
 // Create the tables if they don't exist    db.CreateTable<Customer>();<br>    db.CreateTable<Project
 >(); 
}

The CreateTable method of the SQLConnection class takes a class as a parameter and creates a table in the database based on the class.

Download the SQLiteDemo.zip file attached to this post and extract the contents.

In the MVVM pattern, ViewModels represent data. They sit between the UI (views) and the actual data (models). Create a ViewModels folder in the project and add the contents of the ViewModels folder from the zip file.

Open the CustomerViewModel.cs file. Notice there is a property for each field in the Customer class. The GetCustomer method retrieves a customer and sends back an instance of the CustomerViewModel class. This instance represents the customer. Notice the code to query the Customer table in the database. Notice the LINQ syntax. Thank you sqlite-net NuGet package!

 public CustomerViewModel GetCustomer(int customerId)
{
    var customer = new CustomerViewModel();
    using (var db = new SQLite.SQLiteConnection(app.DBPath))
    {
        var _customer = (db.Table<Customer>().Where(
            c => c.Id == customerId)).Single();
        customer.Id = _customer.Id;
        customer.Name = _customer.Name;
        customer.City = _customer.City;
        customer.Contact = _customer.Contact;
    }
    return customer;
}

The SaveCustomer method takes an instance of the CustomerViewModel class as an argument. If this represents an existing customer, the method updates the database using SQLiteConnection.Update. Otherwise, the method adds a new customer using SQLiteConnection.Insert.

 public string SaveCustomer(CustomerViewModel customer)
{
    string result = string.Empty;
    using (var db = new SQLite.SQLiteConnection(app.DBPath))
    {
        string change = string.Empty;
        try
        {
            var existingCustomer = (db.Table<Customer>().Where(
                c => c.Id == customer.Id)).SingleOrDefault();

            if (existingCustomer != null)
            {
                existingCustomer.Name = customer.Name;
                existingCustomer.City = customer.City;
                existingCustomer.Contact = customer.Contact;
                int success = db.Update(existingCustomer);
            }
            else
            {
                int success = db.Insert(new Customer()
                {
                    Id = customer.id,
                    Name = customer.Name,
                    City = customer.City,
                    Contact = customer.Contact
                 });
            }
            result = "Success";
        }
        catch (Exception ex)
        {
            result = "This customer was not saved.";
        }
    }
    return result;
}

The DeleteCustomer method takes a customer Id as an argument. If the customer exists, the method deletes the customer’s projects and the customer using the SQLiteConnection.Delete method.

 public string DeleteCustomer(int customerId)
{
    string result = string.Empty;
    using (var db = new SQLite.SQLiteConnection(app.DBPath))
    {
        var projects = db.Table<Project>().Where(
            p => p.CustomerId == customerId);
        foreach (Project project in projects)
        {
            db.Delete(project);
        }
        var existingCustomer = (db.Table<Customer>().Where(
            c => c.Id == customerId)).Single();

        if (db.Delete(existingCustomer) > 0)
        {
            result = "Success";
        }
        else
        {
            result = "This customer was not removed";
        }
    }
    return result;
}

Open the CustomersViewModel.cs file. You will use this ViewModel to work with collections of customers. It has a GetCustomers method which retrieves all of the customers in the database and orders them by name. The method returns an ObservableCollection of CustomerViewModel.

 public ObservableCollection<CustomerViewModel> GetCustomers()
{
    customers = new ObservableCollection<CustomerViewModel>();
    using (var db = new SQLite.SQLiteConnection(app.DBPath))
    {
        var query = db.Table<Customer>().OrderBy(c => c.Name);
        foreach (var _customer in query)
        {
            var customer = new CustomerViewModel()
            {
                Id = _customer.Id,
                Name = _customer.Name,
                City = _customer.City,
                Contact = _customer.Contact
            };
            customers.Add(customer);
        }
    }
    return customers;
}

The ProjectViewModel and ProjectsViewModel classes have similar code.

In the MVVM pattern, Views represent the user interface. Before you add views, you need to add some plumbing to the project. Add a new item to the project and use the Basic Page template. You can call it BasicPage1, because you are going to delete it immediately. Click Yes when you see the following dialog.

Picture5

Notice that Visual Studio added a number of files to the Common folder. The XAML files you will add next rely on these.

Picture6

Delete the BasicPage1.xaml and MainPage.xaml files from the project. Create a Views folder in the project and add the contents of the Views folder from the zip file.

Open the MainPage.xaml.cs file. When you run the app, this page gets loaded and the OnNavigatedTo method gets called. This method creates a new instance of CustomersViewModel and calls the GetCustomers method to retrieve the customers from the database. The code then populates the CustomersViewSource with the customers. The CustomersGridView on the page is bound to that view source and will display customers.

The MainPage XAML uses a converter to display dates, so create a Converters folder in the project and add the contents of the Converters folder from the zip file.

The various XAML pages use AppBars to display commands such as Add, Edit and Delete. The StandardStyles.xaml file in the Common folder defines a large number of AppBar button styles you can use. This saves you from having to create icons for AppBar buttons. Notice however, that they are all commented out. This is to prevent them from all being loaded into memory. Locate and uncomment the EditAppBarButtonStyle, SaveAppBarButtonStyle, DeleteAppBarButtonStyle and AddAppBarButtonStyle styles.

Open the App.xaml.cs file and add the following to the top of the file. This is needed because MainPage.xaml is now in the Views folder instead of the project’s main folder.

 using SQLiteDemo.Views;

Finally, add the following method to add some customers and projects to the database. This code uses SQLiteConnection.DeleteAll to empty the Customer and Project tables and then add sample data to them.

 private void ResetData()
{
    using (var db = new SQLite.SQLiteConnection(this.DBPath))
    {
        // Empty the Customer and Project tables 
        db.DeleteAll<Customer>();
        db.DeleteAll<Project>();

        // Add seed customers and projects
        db.Insert(new Customer()
        {
            Id = 1,
            Name = "Adventure Works",
            City = "Bellevue",
            Contact = "Mu Han"
        });
        db.Insert(new Customer()
        {
            Id = 2,
            Name = "Contoso",
            City = "Seattle",
            Contact = "David Hamilton"
        });
        db.Insert(new Customer()
        {
            Id = 3,
            Name = "Fabrikam",
            City = "Redmond",
            Contact = "Guido Pica"
        });
        db.Insert(new Customer()
        {
            Id = 4,
            Name = "Tailspin Toys",
            City = "Kent",
            Contact = "Michelle Alexander"
        });

        db.Insert(new Project()
        {
            Id = 1,
            CustomerId = 1,
            Name = "Windows Store app",
            Description = "Expense reports",
            DueDate = DateTime.Today.AddDays(4)
        });
        db.Insert(new Project()
        {
            Id = 2,
            CustomerId = 1,
            Name = "Windows Store app",
            Description = "Time reporting",
            DueDate = DateTime.Today.AddDays(14)
        });
        db.Insert(new Project()
        {
            Id = 3,
            CustomerId = 1,
            Name = "Windows Store app",
            Description = "Project management",
            DueDate = DateTime.Today.AddDays(24)
        });
        db.Insert(new Project()
        {
            Id = 4,
            CustomerId = 2,
            Name = "Windows Phone app",
            Description = "Soccer scheduling",
            DueDate = DateTime.Today.AddDays(6)
        });
        db.Insert(new Project()
        {
            Id = 5,
            CustomerId = 3,
            Name = "MVC4 app",
            Description = "Catalog",
            DueDate = DateTime.Today.AddDays(30)
        });
        db.Insert(new Project()
        {
            Id = 6,
            CustomerId = 3,
            Name = "MVC4 app",
            Description = "Expense reports",
            DueDate = DateTime.Today.AddDays(-3)
        });
        db.Insert(new Project()
        {
            Id = 7,
            CustomerId = 3,
            Name = "Windows Store app",
            Description = "Expense reports",
            DueDate = DateTime.Today.AddDays(45)
        });
        db.Insert(new Project()
        {
            Id = 8,
            CustomerId = 4,
            Name = "Windows Store app",
            Description = "Kids game",
            DueDate = DateTime.Today.AddDays(60)
        });

    }
}

Now add a call to ResetData to the OnLaunched method.

 using (var db = new SQLite.SQLiteConnection(this.DBPath))
{
    // Create the tables if they don't exist
    db.CreateTable<Customer>();
    db.CreateTable<Project>();
}
ResetData(); 

Run the app and you should see customers on the main page.

Picture7

Try out the various features of the app:

  • On the main page, with no customers selected, swipe up or right-click and you should see an Add button in the AppBar. Tap that and you can add a customer.
  • On the main page, select a customer and the AppBar should appear with an Edit button. Tap that and you can edit or delete that customer.
  • On the main page, tap a customer and you should see the projects for that customer.
  • On the projects page, with no projects selected, swipe up or right-click and you should see an Add button in the AppBar. Tap that and you can add a project.
  • On the projects page, select a project and the AppBar should appear with an Edit button. Tap that and you can edit or delete that project.
  • If you tap the Back button, the app will return you to the last page you viewed.
  • If you add, edit or delete a customer, the app returns you to the main page. If you add, edit or delete a project, the app returns you to the projects page.

And that, my friends, is how you can add SQLite to a Windows Store app.