Windows Store App with a SQLite Database

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.

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.

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.

Platform Error

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

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.

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