Table of Contents

 
 
 

Introduction

How to use SQLite with a Windows Phone 8 application

Building the Sample

To build this sample you will need Visual Studio 2012 on a computer running Windows 8 professional x64.  The computer will also need to have a microprocessor that support SLAT (Second Layer Address Layer)

Description

To start with you will need to install SQLite for windows phone in visual studio 2012 extensions and updates.  You will find it in the tools menu of Visual Studio

Second you will need to go to the GITHUB website and  download the code for SQLite-net-wp8.   This is a c++ project you will need to access a sqlite database.

 

https://github.com/peterhuene/sqlite-net-wp8

(warning if the sqlite-net-wp8 does not use the same version of SQLite for windows phone you will not be able to open the app)

 

So let's create a new Windows Phone app.

 

You will need to right click on the solution file and select add existing solution.   You will need to add the Sqlite-net-wp8 solution downloaded from github. 

 

Now you need to add the NuGet package Sqlite-net

 

This will add 2 C# files to the solution.  Finally before we start coding add a reference to the sqlite-net-wp8 project in your solution.  You also need to add a reference to SQLite.

 

 

 The 2 files added are Sqlite.cs andSqliteAsync.cs.

 

To interact with the sqlite database you will need to create a class which defines the table.  For this example we will create a person class: 

C#
public class Person
 {
     [SQLite.PrimaryKey, SQLite.AutoIncrement]
     public int Id { get; set; }
 
     public string FirstName { get; set; }
 
     public string LastName { get; set; }
 }
public class Person
 {
     [SQLite.PrimaryKey, SQLite.AutoIncrement]
     public int Id { get; set; }
 
     public string FirstName { get; set; }
 
     public string LastName { get; set; }
 }

 The Id property is marked as the Primary Key and will auto increment as values are added to the table.

 

In the app.xaml class lets create a database.  In the constructor we check if the database exists and if it does not we create it.  Since there is no file exists method on the windows phone we try and try and open the file.  If the file does not exist we get an exception.  

C#
public App()
    {
        // Global handler for non-caught exceptions.
        UnhandledException += Application_UnhandledException;
 
        // Standard XAML initialization
        InitializeComponent();
 
        // Phone-specific initialization
        InitializePhoneApplication();
 
        // Language display initialization
        InitializeLanguage();
 
        // Show graphics profiling information while debugging.
        if (Debugger.IsAttached)
        {
            // Display the current frame rate counters.
            Application.Current.Host.Settings.EnableFrameRateCounter = true;
 
            // Show the areas of the app that are being redrawn in each frame.
            //Application.Current.Host.Settings.EnableRedrawRegions = true;
 
            // Enable non-production analysis visualization mode,
            // which shows areas of a page that are handed off to GPU with a colored overlay.
            //Application.Current.Host.Settings.EnableCacheVisualization = true;
 
            // Prevent the screen from turning off while under the debugger by disabling
            // the application's idle detection.
            // Caution:- Use this under debug mode only. Application that disables user idle detection will continue to run
            // and consume battery power when the user is not using the phone.
            PhoneApplicationService.Current.UserIdleDetectionMode = IdleDetectionMode.Disabled;
        }
 
        //  check if database exists and create it if needed
 
        string dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db.sqlite");
        if (!FileExists("db.sqlite").Result)
        {
            using (var db = new SQLiteConnection(dbPath))
            {
                db.CreateTable<Person>();
            }
        }
 
}
 
 
 
 
    private async Task<bool> FileExists(string fileName)
    {
        var result = false;
        try
        {
            var store = await Windows.Storage.ApplicationData.Current.LocalFolder.GetFileAsync(fileName);
            result =true;
        }
        catch
        {
        }
 
        return result;
 
    }
 public App()
    {
        // Global handler for uncaught exceptions.
        UnhandledException += Application_UnhandledException;
 
        // Standard XAML initialization
        InitializeComponent();
 
        // Phone-specific initialization
        InitializePhoneApplication();
 
        // Language display initialization
        InitializeLanguage();
 
        // Show graphics profiling information while debugging.
        if (Debugger.IsAttached)
        {
            // Display the current frame rate counters.
            Application.Current.Host.Settings.EnableFrameRateCounter = true;
 
            // Show the areas of the app that are being redrawn in each frame.
            //Application.Current.Host.Settings.EnableRedrawRegions = true;
 
            // Enable non-production analysis visualization mode,
            // which shows areas of a page that are handed off to GPU with a colored overlay.
            //Application.Current.Host.Settings.EnableCacheVisualization = true;
 
            // Prevent the screen from turning off while under the debugger by disabling
            // the application's idle detection.
            // Caution:- Use this under debug mode only. Application that disables user idle detection will continue to run
            // and consume battery power when the user is not using the phone.
            PhoneApplicationService.Current.UserIdleDetectionMode = IdleDetectionMode.Disabled;
        }
 
        //  check if database exists and create it if needed
 
        string dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db.sqlite");
        if (!FileExists("db.sqlite").Result)
        {
            using (var db = new SQLiteConnection(dbPath))
            {
                db.CreateTable<Person>();
            }
        }
 
}
 
 
 
 
    private async Task<bool> FileExists(string fileName)
    {
        var result = false;
        try
        {
            var store = await Windows.Storage.ApplicationData.Current.LocalFolder.GetFileAsync(fileName);
            result =true;
        }
        catch
        {
        }
 
        return result;
 
    }
 

 In the MainPage.xaml lets add 3 buttons. These buttons will be used to add a record to the database, edit it, and delete it.

 

XAML
<Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">
     <StackPanel >
         <Button Name="btnInsert" Click="BtnInsert_OnClick" Content ="Insert" ></Button>
         <Button Click="BtnUpdate_OnClick"  Name="btnUpdate" Content="Update"></Button>
         <Button Name="btnDelete" Click="BtnDelete_OnClick" Content="Delete" ></Button>
     </StackPanel>
 </Grid>
<Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">              <StackPanel >                  <Button Name="btnInsert" Click="BtnInsert_OnClick" Content ="Insert" ></Button>
         <Button Click="BtnUpdate_OnClick"  Name="btnUpdate" Content="Update"></Button>
         <Button Name="btnDelete" Click="BtnDelete_OnClick" Content="Delete" ></Button>
     </StackPanel>
 </Grid>
 

 Here is the code to add, delete and modify records in the Sqlite database

C#
   string dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db.sqlite");
 
 
    private void BtnUpdate_OnClick(object sender, RoutedEventArgs e)
    {
        using (var db = new SQLiteConnection(dbPath))
        {
            var existing = db.Query<Person>("select * from Person").FirstOrDefault();
            if (existing != null)
            {
                existing.FirstName = "Denis";
                db.RunInTransaction(() =>
                    {
                        db.Update(existing);
                    });
            }
        }
    }
 
    private void BtnDelete_OnClick(object sender, RoutedEventArgs e)
    {
        using (var db = new SQLiteConnection(dbPath))
        {
            var existing = db.Query<Person>("select * from Person").FirstOrDefault();
            if (existing != null)
            {
                db.RunInTransaction(() =>
                {
                    db.Delete(existing);
                });
            }
        }
    }
 
    private void BtnInsert_OnClick(object sender, RoutedEventArgs e)
    {
        using (var db = new SQLiteConnection(dbPath))
        {
            db.RunInTransaction(() =>
            {
                db.Insert(new Person() { FirstName = "Ken", LastName = "Tucker" });
            });
        }
    }
}
   string dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db.sqlite");
 
 
    private void BtnUpdate_OnClick(object sender, RoutedEventArgs e)
    {
        using (var db = new SQLiteConnection(dbPath))
        {
            var existing = db.Query<Person>("select * from Person").FirstOrDefault();
            if (existing != null)
            {
                existing.FirstName = "Denis";
                db.RunInTransaction(() =>
                    {
                        db.Update(existing);
                    });
            }
        }
    }
 
    private void BtnDelete_OnClick(object sender, RoutedEventArgs e)
    {
        using (var db = new SQLiteConnection(dbPath))
        {
            var existing = db.Query<Person>("select * from Person").FirstOrDefault();
            if (existing != null)
            {
                db.RunInTransaction(() =>
                {
                    db.Delete(existing);
                });
            }
        }
    }
 
    private void BtnInsert_OnClick(object sender, RoutedEventArgs e)
    {
        using (var db = new SQLiteConnection(dbPath))
        {
            db.RunInTransaction(() =>
            {
                db.Insert(new Person() { FirstName = "Ken", LastName = "Tucker" });
            });
        }
    }
}

Before you compile you will need to add some conditional compilation constants in the application

 

web analytics

See Also

Another important place to find a huge amount of Windows Phone related articles is the TechNet Wiki itself. The best entry point is Windows Phone Resources on the TechNet Wiki.