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);
}
Now I can call methods in my Repository classes, through these objects. Here are some screenshots of the final application.
 |
| Add New Employee |
 |
| View/Update/Delete Employee |
I am uploading the full sample to my SkyDrive. Do check it out.
Appreciate your feedback.
Happy Coding.
Regards,
Jaliya
