locked
SQLite on Windows Phone 8.1 RRS feed

  • Question

  • I am working on a Windows Phone 8.1 app and need to use a database,

    So I downloaded & installed the SQLite.net library from SQLite.org and added it to my project, I also added the nugget 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.

    Friday, November 21, 2014 11:50 AM

Answers

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

    • Marked as answer by KevinPro Saturday, November 22, 2014 5:16 AM
    Saturday, November 22, 2014 5:11 AM