using SQLite;
{
public interface IDbConnection
Task InitializeDatabase();
SQLiteAsyncConnection GetAsyncConnection();
}
using System.IO;
using System.Threading.Tasks;
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;
namespace SQLiteModernApp.DataModel
[Table("Department")]
public class Department
[PrimaryKey]
public int DepartmentId { get; set; }
[MaxLength(30)]
public string DepartmentName { get; set; }
[Table("Employee")]
public class Employee
[PrimaryKey, AutoIncrement]
public int EmployeeId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Email { get; set; }
using System.Collections.Generic;
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);
using SQLiteModernApp.DataAccess;
public class EmployeeRepository : IEmployeeRepository
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);
<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>
<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>
<TextBlock Text="First Name" Width="300"></TextBlock>
<TextBlock Text="{Binding FirstName}" ></TextBlock>
<TextBlock Text="Last Name" Width="300"></TextBlock>
<TextBlock Text="{Binding LastName}"></TextBlock>
<TextBlock Text="Email" Width="300"></TextBlock>
<TextBlock Text="{Binding Email}" ></TextBlock>
<StackPanel Orientation="Horizontal" >
<TextBlock Text="Department" Width="300"></TextBlock>
<TextBlock Text="{Binding DepartmentId}" ></TextBlock>
</DataTemplate>
</ListView.ItemTemplate>
</ListView>
</Grid>
DepartmentRepository oDepartmentRepository;
EmployeeRepository oEmployeeRepository;
private async Task InitializeDatabase()
DbConnection oDbConnection = new DbConnection();
await oDbConnection.InitializeDatabase();
oDepartmentRepository = new DepartmentRepository(oDbConnection);
oEmployeeRepository = new EmployeeRepository(oDbConnection);