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.%7Cutmccn%3D(direct)%7Cutmcmd%3D(none)%3B&utmu=qB~){kind=small}
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.
Installing SQLite
The first thing you’ll need to do is install the SQLite for Windows Store apps. This can be done by downloading the SQLite for Windows Runtime package
-
In Visual Studio, click the Tools menu, then click Extensions and Updates
- In the tree on the left of the Extensions and Updates window, click Online, then click Visual Studio Gallery.
- Next, type sqlite in the search box in the upper right hand corner and press Enter.
-
The SQLite for Windows Runtime package should appear. Click Download.
-
You will then be prompted to click Install. Do so.
-
Once the package is installed you will need to restart Visual Studio
| Android tip |
| The SQLite for Windows Runtime package in Visual Studio is similar to the android.database.sqlite package. |
Adding a Reference to SQLite
Now that SQLite is installed you need to add a reference to it from you project.
-
Right click the References folder in your Windows Store project and click Add Reference…
- In the tree on the left hand side of the Reference Manager windows, expand the Windows and the Extensions nodes.
-
Then select both the SQLite for Windows Runtime and the Microsoft Visual C++ Runtime Package and click OK.
-
You should now see the extensions appear under the References folder for you project.
| Visual Studio tip |
| After you add these references, your app’s project may not build or run. To fix this, 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. |
Getting Helper Classes
The last thing you’ll want to do is obtain some helper classes that make working with SQLite a bit easier. There are a number available for Windows Store applications. The ones I prefer to use come from the sqlite-net library.
The sqlite-net library can be obtained from NuGet via the following steps
-
Right click on the References folder in you Windows Store project and click Manage NuGet Packages…
- Expand the Online node in the left hand side of the Window.
- Enter sqlite in the search box in the upper right hand side of Window and press Enter.
-
Select sqlite-net and click Install.
-
Two source files will be added to your project: SQLite.cs and SQLiteAsync.cs.
- Build your project by pressing F6 to ensure it is set up correctly.
| 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. |
Using SQLite
In the last part of this section we’ll look at how to perform some basic tasks with SQLite in your Windows Store application.
Creating a Table
The first step you’ll need to take is to create a table that your application will use. For the sake of example, let’s say your application is storing blog posts in a SQLite table. Using the sqlite-net package you obtained in the last section, you can define the table by simply writing a class.
public class Post
{
[PrimaryKey]
public int Id { get; set; }
public string Title { get; set; }
public string Text { get; set; }
}
The PrimaryKey attributes come from the sqlite-net package. There are a number of attributes that the package provides that allow you to define the table’s schema.
Once the table is defined it needs to be created, which can be done like this:
private async void CreateTable()
{
SQLiteAsyncConnection conn = new SQLiteAsyncConnection("blog");
await conn.CreateTableAsync<Post>();
}
The “blog” parameter in the constructor for the SQLiteAsyncConnection class simply specifies the path to the SQLite database.
The Post type specified in the call to the CreateTableAsync method specifies the type of table that should be created. This maps back to the Post class created earlier.
| 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 )");
}
|
Inserting a Record
Now that the table is created, records can be added to it with the following code:
public async void InsertPost(Post post)
{
SQLiteAsyncConnection conn = new SQLiteAsyncConnection("blog");
await conn.InsertAsync(post);
}
| 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);
}
|
Retrieving Records
Retrieve a single records from the table with the following:
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 |
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;
}
|
Retrieve all record from the table with the following:
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 |
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;
}
|
Updating a Record
Updating a record requires the following code:
public async void UpdatePost(Post post)
{
SQLiteAsyncConnection conn = new SQLiteAsyncConnection("blog");
await conn.UpdateAsync(post);
}
| 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);
}
|
Deleting a Record
A record can be delete with the following:
public async void DeletePost(Post post)
{
SQLiteAsyncConnection conn = new SQLiteAsyncConnection("blog");
await conn.DeleteAsync(post);
}
| 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);
}
|
Next Steps
To learn more about how to work with SQLite, see these resources.
button on the toolbar between the designer and the markup view. This will move the markup view to the top and designer view to the bottom.
button on the toolbar between the two views. This will minimize the designer.
Hey there! My name is Adam, and I'm a Technical Evangelist at Microsoft where I spend time focusing on Windows, Windows Phone, and Windows Azure.