SQLite programming (Android versus Windows Store apps)

Article
10/14/2015

[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:

In Microsoft Visual Studio, on the Tools menu, click Extensions and Updates.
Expand Online. If Visual Studio Gallery is not already selected, click it.
In the Search Visual Studio Gallery box, type "SQLite".
Next to SQLite for Windows Runtime, click Download, like this.
Click Install.
Restart Visual Studio.

Android tip
The SQLite for Windows Runtime package in Visual Studio is similar to the android.database.sqlite package.

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:

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.
Expand Windows and click Extensions.
Check the Microsoft Visual C++ Runtime Package and SQLite for Windows Runtime boxes like this, and click OK.

Visual Studio tip
After you add these references, your app's project may not build or run. To fix this, in Visual Studio, on the Build menu, click Configuration Manager. In the Active solution platform box, click your specific target platform, such as ARM, x64, or x86. Then click Close.

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:

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.
Expand Online.
In the Search Online box, type sqlite-net.
Next to sqlite-net, click Install, like this.
Click Close.

Visual Studio tip
NuGet is a Visual Studio extension that makes it easier to install and update third-party libraries and tools in Visual Studio. To learn more about NuGet, see the NuGet Gallery. After you get familiar with NuGet, you may find it easier to use the command-line version of NuGet. To get to it in Visual Studio, on the Tools menu, click Library Package Manager > Package Manager Console.

NuGet is a Visual Studio extension that makes it easier to install and update third-party libraries and tools in Visual Studio. To learn more about NuGet, see the NuGet Gallery.

After you get familiar with NuGet, you may find it easier to use the command-line version of NuGet. To get to it in Visual Studio, on the Tools menu, click Library Package Manager > Package Manager Console.

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.

Android tip

Android tip
In Android apps, you create a table that extends the SQLiteOpenHelper class with code like this. `public void onCreate(SQLiteDatabase db) { db.execSQL("CREATE TABLE Post ( Id INTEGER PRIMARY KEY, Title TEXT, Text TEXT )"); }`

In Android apps, you create a table that extends the SQLiteOpenHelper class with code like this.

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);
}

Android tip

Android tip
In Android apps, you can add a record with code like this. `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); }`

In Android apps, you can add a record with code like this.

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();
}

Android tip

Android tip
In Android apps, you could return a Cursor object containing a single record with code like this. `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; }`

In Android apps, you could return a Cursor object containing a single record with code like this.

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;
}

Android tip

Android tip
In Android apps, you could return a Cursor object containing all records with code like this. `public Cursor getPosts(SQLiteDatabase db){ String[] projection = { "Id", "Title", "Text" }; Cursor c = db.query( "Post", projection, null, null, null, null, null ); return c; }`

In Android apps, you could return a Cursor object containing all records with code like this.

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);
}

Android tip

Android tip
In Android apps, you can update a record with code like this. `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); }`

In Android apps, you can update a record with code like this.

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);
}

Android tip

Android tip
In Android apps, you can delete a record with code like this. `public void deletePost(SQLiteDatabase db, Integer id ) { String selection = "Id LIKE ?"; String[] selelectionArgs = { String.valueOf(id) }; db.delete("Post", selection, selectionArgs); }`

In Android apps, you can delete a record with code like this.

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.

Top

Partager via

SQLite programming (Android versus Windows Store apps)

Introduction

Install SQLite

Reference SQLite

Get a SQLite helper library

Create a table

Create a record

Read records

Update a record

Delete a record

Next steps

Ressources supplémentaires