Working with SQLite on Windows Phone 8.1 - Do I ne

Question

I am working on a Windows Phone 8.1 app and need to use a database, I didn't that would be an issue until someone asked me what ORM I am using.

So I downloaded & installed the SQLite.net library from http://sqlite.org and added it to my project, I also added the nuget packages and this resolved the errors I was getting.

Now I have created a connection to my database as well as tables based on my models. My question is, where does the ORM come in? What am I not doing that I need to do?

My database is very simple by design, only two tables. The reason I need it is because I will later need to export the data using MS Office APIs.

Please help.

Answer 1

Object Relational Mapping(ORM) – library that allows you CRUD objects from a database without writing SQL statements. The SQLite.NET library is a very lite ORM. It seems that you are doing it right, but you can check it with the following step-by-step instruction:

1. Add a new project to you solution like YourProject.SQLite or just create a sub folder in one of existent appropriate projects.
2. Add sqlite-net dependency via nuget.
3. Install SQLite for Windows Phone extension.
4. Now you can:
   • Create a connection _connection = new SQLiteAsyncConnection(DatabaseName);
   • Create databases await _connection.CreateTablesAsync(typeof(SyncFile), typeof(TransferFile));
   • CRUD objects await _connection.InsertAsync(entity); _connection.UpdateAsync(entity); etc.

I would recommend you to use generic repository pattern as well. It can look like:

public class Repository<TEntity> : IRepository<TEntity> where TEntity : class, new()
{
    private readonly SQLiteAsyncConnection _connection;
    public Repository(SQLiteAsyncConnection connection)
    {
        _connection = connection;
    }
    public async Task<TEntity> GetByIdAsync(object id)
    {
        return await _connection.FindAsync<TEntity>(id);
    }
    public async Task<IReadOnlyCollection<TEntity>> GetAllAsync()
    {
        return await _connection.Table<TEntity>().ToListAsync();
    }
    public async Task<IReadOnlyCollection<TEntity>> GetWhereAsync(Expression<Func<TEntity, bool>> predicate)
    {
        return await _connection.Table<TEntity>().Where(predicate).ToListAsync();
    }
    public async Task AddAsync(TEntity entity)
    {
        await _connection.InsertAsync(entity);
    }
    public async Task UpdateAsync(TEntity entity)
    {
        await _connection.UpdateAsync(entity);
    }
    public async Task DeleteAsync(TEntity entity)
    {
        await _connection.DeleteAsync(entity);
    }
}