SQLite programming (Android versus Windows Store apps)

[This article is for Windows 8.x and Windows Phone 8.x developers writing Windows Runtime apps. If you’re developing for Windows 10, see the latest documentation]

Learn how to write code in Windows Store apps for Windows 8 that works with structured data in SQLite—a popular database that's familiar to Android app developers.

Introduction

Your app may need to work with data that's more complex than simple key-value pairs—for example, a complex sales order or a survey with comments. For these kinds of data, you may want to use a database. One popular choice is SQLite—a self-contained, zero-configuration, relational, transactional database engine—which Android and Windows Store apps both support.

Here's how to:

• Install SQLite.
• Reference it from a Windows Store app.
• Get a helper library to make your coding easier.
• Write code to create a database table.
• Add, get, change, and remove the table’s records.

Install SQLite

First, install the SQLite for Windows Runtime package in Microsoft Visual Studio 2013:

1. In Microsoft Visual Studio, on the Tools menu, click Extensions and Updates.

2. Expand Online. If Visual Studio Gallery is not already selected, click it.

3. In the Search Visual Studio Gallery box, type "SQLite".

4. Next to SQLite for Windows Runtime, click Download, like this.

   

5. Click Install.

6. Restart Visual Studio.

Top

Reference SQLite

After you install the SQLite for Windows Runtime package, set a reference to it from your Windows Store app project in Visual Studio:

1. With your app's project open in Visual Studio, in the Solution Explorer window, right-click the References folder and click Add Reference, like this.

   

2. Expand Windows and click Extensions.

3. Check the Microsoft Visual C++ Runtime Package and SQLite for Windows Runtime boxes like this, and click OK.

   

Top

Get a SQLite helper library

Before you start writing code, you may want to install a SQLite helper library to make your coding easier. There are many of these helper libraries available. For example, you can install the sqlite-net library. Here's how to get it:

1. With your app's project still open in Visual Studio, in the Solution Explorer window, right-click the References folder and click Manage NuGet Packages. If you don't see Manage NuGet Packages, here's how to fix it:

   1. In Visual Studio, on the Tools menu, click Extensions and Updates.
   2. Expand Online. If Visual Studio Gallery is not already selected, click it.
   3. In the Search Visual Studio Gallery box, type NuGet.
   4. Next to NuGet Package Manager, click Download.
   5. Click Install.
   6. Restart Visual Studio.
   7. If your app’s project doesn't open automatically, open it.
   8. Try again: in the Solution Explorer window, right-click the References folder and click Manage NuGet Packages.

2. Expand Online.

3. In the Search Online box, type sqlite-net.

4. Next to sqlite-net, click Install, like this.

   

5. Click Close.

After you install a SQLite helper library, you're ready to write some code to create a table and to add, get, change, and remove the table's records.

Top

Create a table

For this example, let's say your Windows Store app works with blog posts. These blog posts are represented as records in a SQLite database table. By using the sqlite-net package that you installed, you can define the table by coding a class that represents each blog post. In this case, each blog post has a unique ID, a title, and the post's text, like this.

public class Post
{
    [PrimaryKey]
    public int Id { get; set; }
    public string Title { get; set; }
    public string Text { get; set; }
}

The PrimaryKey attribute is defined in the sqlite-net package.

After you define the table, you create it using code like this.

private async void CreateTable()
{
    SQLiteAsyncConnection conn = new SQLiteAsyncConnection("blog");
    await conn.CreateTableAsync<Post>();
}

The SQLiteAsyncConnection method's "blog" parameter specifies the path to the SQLite database.

The CreateTableAsync method's type corresponds to a table of type Post that was coded earlier as a class.

public void onCreate(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE Post ( Id INTEGER PRIMARY KEY, Title TEXT, Text TEXT )");
}

Top

Create a record

After you create a table, you can add a record to it with code like this.

public async void InsertPost(Post post)
{
    SQLiteAsyncConnection conn = new SQLiteAsyncConnection("blog");
    await conn.InsertAsync(post);
}

public void insertPost(SQLiteDatabase db, String title, String text ) {
    ContentValues values = new ContentValues();
    values.put("Title", title);
    values.put("Text", text);
    long newRowId;
    newRowId = db.insert("Post", null, values);
}

Top

Read records

To read a single record, use code like this.

public async Task<Post> GetPost(int id)
{
    SQLiteAsyncConnection conn = new SQLiteAsyncConnection("blog");

    var query = conn.Table<Post>().Where(x => x.Id == id);
    var result = await query.ToListAsync();

    return result.FirstOrDefault();
}

public Cursor getPost(SQLiteDatabase db, Integer id){
    String[] projection = {"Id", "Title", "Text" };
    String selection = "Id LIKE ?";
    String[] selelectionArgs = { String.valueOf(id) };
    Cursor c = db.query(
        "Post",
        projection,
        selection,
        selectionArgs,
        null,
        null,
        null
    );
    return c;
}

To read all records, use code like this.

public async Task<List<Post>> GetPosts()
{
    SQLiteAsyncConnection conn = new SQLiteAsyncConnection("blog");

    var query = conn.Table<Post>();
    var result = await query.ToListAsync();

    return result;
}

public Cursor getPosts(SQLiteDatabase db){
    String[] projection = { "Id", "Title", "Text" };
    Cursor c = db.query(
        "Post",
        projection,
        null,
        null,
        null,
        null,
        null
    );
    return c;
}

Top

Update a record

To update a record, use code like this.

public async void UpdatePost(Post post)
{
    SQLiteAsyncConnection conn = new SQLiteAsyncConnection("blog");
    await conn.UpdateAsync(post);
}

public void updatePost(SQLiteDatabase db, Integer id, String title, String text ) {
    ContentValues values = new ContentValues();
    
    values.put("Title", title);
    values.put("Text", text);
 
    String selection = "Id LIKE ?";
    String[] selelectionArgs = { String.valueOf(id) };
 
    int count = db.update(
        "Post,
        values,
        selection,
        selectionArgs);
}

Top

Delete a record

To delete a record, use code like this.

public async void DeletePost(Post post)
{
    SQLiteAsyncConnection conn = new SQLiteAsyncConnection("blog");
    await conn.DeleteAsync(post);
}

public void deletePost(SQLiteDatabase db, Integer id ) {
    String selection = "Id LIKE ?";
    String[] selelectionArgs = { String.valueOf(id) };
    
    db.delete("Post", selection, selectionArgs);
}

Top

Next steps

To learn more about how to work with SQLite, see these resources.

• Using SQLite in Windows 8 Store Apps
• SQLite with Windows 8 apps
• sqlite-net Readme
• Books About SQLite

Top