Click here to Skip to main content
11,484,402 members (63,585 online)
Click here to Skip to main content

SQLite for Windows Phone 8

, 5 Sep 2013 CPOL 30K 13
Rate this:
Please Sign up or sign in to vote.
Using SQLite in Windows Phone. We will see the DLL references and Visual Studio extension for the same.

Introduction

In this tutorial we will see with an example how to use SQLite in Windows Phone 8. Note: this tip is just an introduction about the usage of the SQLite library in Windows Phone 8.

Setting up SQLite in a Windows Phone 8 Solution:

  1. Download SQLiteWinRTPhone project from http://sqlwinrt.codeplex.com/SourceControl/latest and compile it.
  2. You must download and install SQLite for Windows Phone Visual Studio Extension http://visualstudiogallery.msdn.microsoft.com/cd120b42-30f4-446e-8287-45387a4f40b7. It's a light weight self contained, zero configuration database engine.
  3. Create a Windows Phone 8 Project and reference SQLite for Windows Phone Extension as below and add a reference to “SQLiteWinRTPhone”.

Background

A little knowledge about the usage of SQLite.

Using the code

Below is the code block for the SQLite database and table creation.

private async void CreateDatabaseButtonClicked(object sender, RoutedEventArgs e)
{
  Database database = new Database(ApplicationData.Current.LocalFolder, "usertask.db");

  await database.OpenAsync();

  string query = "create table if not exists Tasks " +
                       "(Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
                       "Task varchar(100)" 
                       )";

  await database.ExecuteStatementAsync(query);
}

Below is the code block to add tasks.

private async void AddTasksButtonClicked(object sender, RoutedEventArgs e)
{
  Database database = new Database(ApplicationData.Current.LocalFolder, "usertask .db");

  await database.OpenAsync();

  string query = "INSERT INTO TASKS (Task) VALUES (@task)";
  Statement statement = await database.PrepareStatementAsync(query);
  statement.BindTextParameterWithName("@task", "Work on CodeProject Article");
       
  await statement.StepAsync();
}

Below is the code block to retrieve task information from the SQLite Tasks table.

private async void FetchTasksButtonClicked(object sender, RoutedEventArgs e)
{
  Database database = new Database(ApplicationData.Current.LocalFolder, "usertask .db");
  await database.OpenAsync();
  string query = "SELECT * FROM TASKS";
  Statement statement = await database.PrepareStatementAsync(query);
  while (await statement.StepAsync())
  {
      MessageBox.Show(statement.GetTextAt(0));
  }
} 

Points of Interest

It wasn't so easy or straightforward to make use of the SQLite library in Windows Phone. After doing some research on the topic I learned how to make use of it with no trouble. Hope my findings will help others too.

History

  • Version 1.0 - 08/25/2013 - Coded skeleton for using SQLite in Windows Phone.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Ranjan.D
Web Developer
United States United States
Profile

Around 10 years of professional software development experience in analysis, design, development, testing and implementation of enterprise web applications for healthcare domain with good exposure to object-oriented design, software architectures, design patterns, test-driven development and agile practices.

In Brief

Analyse and create High Level , Detailed Design documents.
Use UML Modelling and create Use Cases , Class Diagram , Component Model , Deployment Diagram, Sequence Diagram in HLD.

Area of Working : Dedicated to Microsoft .NET Technologies
Experience with : C# , J2EE , J2ME, Windows Phone 8, Windows Store App
Proficient in: C# , XML , XHTML, XML, HTML5, Javascript, Jquery, CSS, SQL, LINQ, EF

Software Development

Database: Microsoft SQL Server, FoxPro
Development Frameworks: Microsoft .NET 1.1, 2.0, 3.5, 4.5
UI: Windows Forms, Windows Presentation Foundation, ASP.NET Web Forms and ASP.NET MVC3, MVC4
Coding: WinForm , Web Development, Windows Phone, WinRT Programming, WCF, WebAPI

Healthcare Domain Experience

CCD, CCR, QRDA, HIE, HL7 V3, Healthcare Interoperability

Education

B.E (Computer Science)

CodeProject Contest So Far:

1. Windows Azure Developer Contest - HealthReunion - A Windows Azure based healthcare product , link - http://www.codeproject.com/Articles/582535/HealthReunion-A-Windows-Azure-based-healthcare-pro

2. DnB Developer Contest - DNB Business Lookup and Analytics , link - http://www.codeproject.com/Articles/618344/DNB-Business-Lookup-and-Analytics

3. Intel Ultrabook Contest - Journey from development, code signing to publishing my App to Intel AppUp , link - http://www.codeproject.com/Articles/517482/Journey-from-development-code-signing-to-publishin

4. Intel App Innovation Contest 2013 - eHealthCare

5. Grand Prize Winner of CodeProject HTML5 &CSS3 Article Contest 2014

6. Grand Prize Winner of CodeProject Android Article Contest 2014

7. Grand Prize Winner of IOT on Azure Contest 2015

Comments and Discussions

 
QuestionSystem.Data.SQLite, SQLite + LINQ, DataTable etc. for Windows Phone 8! Pin
maxtek21-Sep-14 5:34
membermaxtek21-Sep-14 5:34 
GeneralHow to Store data or variables using SQLite in WP App Pin
Nitin M. Patil18-Jul-14 20:35
professionalNitin M. Patil18-Jul-14 20:35 
QuestionThanks for this but i have one question Pin
adi_201212-Nov-13 17:19
memberadi_201212-Nov-13 17:19 
GeneralMy vote of 5 Pin
ridoy5-Sep-13 7:48
professionalridoy5-Sep-13 7:48 
GeneralRe: My vote of 5 Pin
Ranjan.D5-Sep-13 10:07
professionalRanjan.D5-Sep-13 10:07 
GeneralMy vote of 3 Pin
ridoy5-Sep-13 5:46
professionalridoy5-Sep-13 5:46 
GeneralRe: My vote of 3 Pin
Ranjan.D5-Sep-13 5:55
professionalRanjan.D5-Sep-13 5:55 
GeneralRe: My vote of 3 Pin
Pete O'Hanlon5-Sep-13 6:21
protectorPete O'Hanlon5-Sep-13 6:21 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150520.1 | Last Updated 5 Sep 2013
Article Copyright 2013 by Ranjan.D
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid