In Windows Store Apps there are variety of ways/methods to store your application data. Examples of such mechanisms would be Web Storage, IndexedDB etc. and even SkyDrive. Today I am going to write about my favorite data storage method in developing Windows Store Apps which is “SQLite”.

SQLite is a relational database management system contained in a small C programming library. SQLite stores the entire database as a single cross-platform file on a host machine. You can examine this file using tools such as SQLite Database Browser which you can download from here. SQLite does not support all the SQL features. Here is a list of features which are not supported in SQLite.

Now let’s see how we can use SQLite in a Windows Store App. I am creating a blank Windows Store App. Now I need to get SQLite binaries for Windows Runtime. You can download Precompiled Binaries for Windows Runtime from here.

0
Precompiled Binaries for Win RT


Once the vsix file has completed installing, then you need to add a reference to "SQLite for Windows Runtime" from your project.

1
Add Reference

One important thing. If you want to publish a app with SQLite to Windows Store, make sure to add reference to Microsoft Visual C++ Runtime package as well
. Now after adding the reference, when I compile the project, I am getting a error.

2
Platform Error

To resolve the error here, I am changing the projects target platform to “x64”.

3
Changing Target Plaform

Now I am going to add a wrapper for SQLite which was written using C#. So I can write my code fairly easily. I am going to Nuget and searching online for “sqlite-net” and I am adding it to my project.

4
Adding sqlite-net

Once the package has completed configuring, you can see two new classes (“SQLite.cs” and “SQLiteAsync.cs”) added to your project. Now I am almost done configuring the project for SQLite. Now let's move into writing some codes.

Here I am using the Repository Pattern. Repository Pattern is a software design pattern where the entity classes and the entity functionalities are separated. To my project I am adding following folders,
  • “DataModel” – Stores classes which model tables (entities) inside my SQLite database.
  • “Repository” – Stores classes which has CRUD methods of Entity models.
  • “DataAccess” – Stores classes for database connection.
First I am adding DbConnection.cs  and IDbConnection.cs to “DataAccess” folder.

IDbConnection.cs
using System.Threading.Tasks;
using SQLite;
 
namespace SQLiteModernApp.DataAccess
{
    public interface IDbConnection
    {
        Task InitializeDatabase();
        SQLiteAsyncConnection GetAsyncConnection();
    }
}

DbConnection.cs
using System.IO;
using System.Threading.Tasks;
using SQLite;
using SQLiteModernApp.DataModel;
 
namespace SQLiteModernApp.DataAccess
{
    public class DbConnection : IDbConnection
    {
        string dbPath;
        SQLiteAsyncConnection conn;        

        public DbConnection()
        {
            dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "MyTable.sqlite");
            conn = new SQLiteAsyncConnection(dbPath);
        }

        public async Task InitializeDatabase()
        {
            await conn.CreateTableAsync<Department>();
            await conn.CreateTableAsync<Employee>();
        }

        public SQLiteAsyncConnection GetAsyncConnection()
        {
            return conn;
        }
    }
}

Here I am getting the path of the current application and creating my SQLite database there. Now I am adding two classes to “DataModel” folder which are “Employee” and “Department”.

Department.cs
using SQLite;

namespace SQLiteModernApp.DataModel
{
    [Table("Department")]
    public class Department
    {
        [PrimaryKey]
        public int DepartmentId { get; set; }

        [MaxLength(30)]
        public string DepartmentName { get; set; }
    }
}
Employee.cs
using SQLite;

namespace SQLiteModernApp.DataModel
{
    [Table("Employee")]
    public class Employee
    {
        [PrimaryKey, AutoIncrement]
        public int EmployeeId { get; set; }

        [MaxLength(30)]
        public string FirstName { get; set; }
 
        public string LastName { get; set; }
 
        public string Email { get; set; }

        public int DepartmentId { get; set; }
    }
}

In my “Repository”, I am going to have CRUD operations for above two entities. Here I am only pasting the code for Employee entity.

IEmployeeRepository.cs
using System.Collections.Generic;
using System.Threading.Tasks;
using SQLiteModernApp.DataModel;

namespace SQLiteModernApp.Repository
{
    interface IEmployeeRepository
    {
        Task InsertEmployeeAsync(Employee employee);
        Task UpdateEmployeeAsync(Employee employee);
        Task DeleteEmployeeAsync(Employee employee);
        Task<List<Employee>> SelectAllEmployeesAsync();
        Task<List<Employee>> SelectEmployeesAsync(string query);
    }
}

EmployeeRepository.cs

In my EmployeeRepository class I am implementing the above IEmployeeRepository interface.
using System.Collections.Generic;
using System.Threading.Tasks;
using SQLite;
using SQLiteModernApp.DataAccess;
using SQLiteModernApp.DataModel;

namespace SQLiteModernApp.Repository
{
    public class EmployeeRepository : IEmployeeRepository
    {
        SQLiteAsyncConnection conn;

        public EmployeeRepository(IDbConnection oIDbConnection)
        {
            conn = oIDbConnection.GetAsyncConnection();
        }
 
        public async Task InsertEmployeeAsync(Employee employee)
        {
            await conn.InsertAsync(employee);
        }

        public async Task UpdateEmployeeAsync(Employee employee)
        {
            await conn.UpdateAsync(employee);
        }
 
        public async Task DeleteEmployeeAsync(Employee employee)
        {
            await conn.DeleteAsync(employee);
        }

        public async Task<List<Employee>> SelectAllEmployeesAsync()
        {
            return await conn.Table<Employee>().ToListAsync();
        }
 
        public async Task<List<Employee>> SelectEmployeesAsync(string query)
        {
            return await conn.QueryAsync<Employee>(query);
        }
    }
}
Now I am moving to the UI. In my MainPage.xaml, I have a stack panel which contain controls for user to insert data. And then I have a list view to show data stored in the database.
<Grid Background="{StaticResource ApplicationPageBackgroundThemeBrush}">
    <StackPanel Name="sPanelEmployee" Orientation="Vertical" Margin="10,11,1040,23">
        <TextBlock Text="First Name" Margin="2 2 2 2" FontSize="14"/>
        <TextBox Name="txtFirstName" Text="{Binding FirstName, Mode=TwoWay}"/>
        <TextBlock Text="Last Name" Margin="2 2 2 2" FontSize="14"/>
        <TextBox Name="txtLastName" Text="{Binding LastName, Mode=TwoWay}"/>
        <TextBlock Text="Email" Margin="2 2 2 2" FontSize="14"/>
        <TextBox Name="txtEmail" Text="{Binding Email, Mode=TwoWay}"/>
        <TextBlock Text="Department" Margin="2 2 2 2" FontSize="14" />
        <ComboBox Name="cboDepartment" ItemsSource="{Binding Department}" DisplayMemberPath="DepartmentName" SelectedValuePath="DepartmentId" SelectedValue="{Binding DepartmentId, Mode=TwoWay}" />
        <StackPanel Orientation="Horizontal" HorizontalAlignment="Right" Margin="0 10 0 0"> 
            <Button x:Name="btnCreate" Content="Create" Click="btnCreate_Click" />
            <Button x:Name="btnUpdate" Content="Update" Click="btnUpdate_Click" />
            <Button x:Name="btnDelete" Content="Delete" Click="btnDelete_Click" />
        </StackPanel>
    </StackPanel>

    <ListView Name="lstViewEmployees" ItemsSource="{Binding}" Margin="399,11,10,10" SelectionChanged="lstViewEmployees_SelectionChanged" >
        <ListView.ItemTemplate>
            <DataTemplate>
                <StackPanel Orientation="Vertical"  Margin="4">
                    <StackPanel Orientation="Horizontal">
                        <TextBlock Text="Employee Id" Width="300"></TextBlock>
                        <TextBlock Text="{Binding EmployeeId}" ></TextBlock>
                    </StackPanel>
                    <StackPanel Orientation="Horizontal">
                        <TextBlock Text="First Name" Width="300"></TextBlock>
                        <TextBlock Text="{Binding FirstName}" ></TextBlock>
                    </StackPanel>
                    <StackPanel Orientation="Horizontal">
                        <TextBlock Text="Last Name" Width="300"></TextBlock>
                        <TextBlock Text="{Binding LastName}"></TextBlock>
                    </StackPanel>
                    <StackPanel Orientation="Horizontal">
                        <TextBlock Text="Email" Width="300"></TextBlock>
                        <TextBlock Text="{Binding Email}" ></TextBlock>
                    </StackPanel>
                    <StackPanel Orientation="Horizontal" >
                        <TextBlock Text="Department" Width="300"></TextBlock>
                        <TextBlock Text="{Binding DepartmentId}" ></TextBlock>
                    </StackPanel>
                </StackPanel>
            </DataTemplate>
        </ListView.ItemTemplate>
    </ListView>
</Grid>
Now in my code behind, first I am initializing the database.
DepartmentRepository oDepartmentRepository;
EmployeeRepository oEmployeeRepository;
 
private async Task InitializeDatabase()
{
    DbConnection oDbConnection = new DbConnection();
    await oDbConnection.InitializeDatabase();
    oDepartmentRepository = new DepartmentRepository(oDbConnection);
    oEmployeeRepository = new EmployeeRepository(oDbConnection);
}
Now I can call methods in my Repository classes, through these objects. Here are some screenshots of the final application.

5
Add New Employee
6
View/Update/Delete Employee

I am uploading the full sample to my SkyDrive. Do check it out.

Appreciate your feedback.

Download Sample

Happy Coding.

Regards,
Jaliya