Windows Phone 8.1 (appx) Database solution

Question

I'm trying to find the best solution to have a local database in my WP8.1 application.

I'm using the standard WP8.1 (non-SL) and Visual Studio 2013.

I've looked into SQLite but I couldn't manage to get it to work on my application/Visual Studio.

If I can use SQLite, I need someone to point me out the way to go. Else, please refer me the best solution.

Thanks in advance!

Answer 1

Here's a repository class that leverages SQLite:

public class ContactsRepository : IContactsRepository
{
    SQLiteAsyncConnection _connection = null;
    static ContactsRepository _repository = null;
    private ContactsRepository()
    {
    }

    public async Task Initialize()
    {
        _connection = new SQLiteAsyncConnection(Constants.DATABASE_FILE_NAME);

        await EnsureTableExist<ContactReference>(_connection);
    }

    public static ContactsRepository Instance
    {
        get
        {
            if (_repository == null)
            {
                _repository = new ContactsRepository();
            }

            return _repository;
        }
    }
    public async Task Add(Category category, Contact contact)
    {
        var result =  await _connection.Table<ContactReference>().Where(c => c.ContactId == contact.Id).FirstOrDefaultAsync();

        if (result != null)
        {
            result.CategoryName = category.Name;
            await _connection.UpdateAsync(result);
        }
        else
        {
            await _connection.InsertAsync(new ContactReference()
            {
                CategoryName = category.Name,
                ContactId = contact.Id
            });
        }
    }

    public async Task Update(Category category, Contact contact)
    {
        var result = await _connection.Table<ContactReference>().Where(c => c.ContactId == contact.Id).FirstOrDefaultAsync();
        Debug.Assert(result != null);

        if (result == null)
        {
            throw new Exception("Unable to update category. Candidate not found");
        }

        if (result != null)
        {
            result.CategoryName = category.Name;
            await _connection.UpdateAsync(result);
        }
    }

    public async Task<ObservableCollection<Contact>> Get(string categoryName)
    {
        var result = new ObservableCollection<Contact>();

        var query = _connection.Table<ContactReference>().Where(c => c.CategoryName == categoryName);
        var queryResult = await query.ToListAsync();

        foreach(var contact in queryResult)
        {
            var phoneContacts = ResourceLocator.Instance[typeof(ObservableCollection<Contact>)] as ObservableCollection<Contact>;

            var phoneContact = phoneContacts.Where(c => c.Id == contact.ContactId).FirstOrDefault();
            Debug.Assert(phoneContact != null);

            if (phoneContact != null)
            {
                result.Add(phoneContact);
            }
        }

        return result;
    }

    public async Task<ObservableCollection<ContactReference>> Get()
    {
        var result = new ObservableCollection<ContactReference>();

        var query = _connection.Table<ContactReference>();
        var queryResult = await query.ToListAsync();

        foreach (var contact in queryResult)
        {
            result.Add(contact);
        }

        return result;
    }

    private async Task EnsureTableExist<T>(SQLiteAsyncConnection connection) where T : new()
    {
        bool noTableExists = false;

        try
        {
            var query = await connection.Table<T>().FirstOrDefaultAsync();
        }
        catch (SQLiteException ex)
        {
            if (ex.Message.Contains("no such table"))
            {
                noTableExists = true;
            }
        }

        if (noTableExists)
        {
            await connection.CreateTableAsync<T>();
        }
    }
}