Sergey Barskiy's Blog

I mentioned months before my WinRT database project.  I was going to stop working on it, but unfortunately as of now there is no good alternative to structured storage in WinRT applications.  SQLite team is working on WinRT version, but there is no projected date as far as I know.

So, upon some thinking I decided to maintain the project.  I took time to update the code to Windows 8 Consumer Preview and VS 11 beta.  I added unit test project with basic tests and a sample project called WinRTDbQuickStart to the solution on CodePlex.  If you download source code, you can get a quick tutorial by looking at the sample project.

I added a utility class to help with data binding.  It can serve as base class for your object that you store in database.  Here is full code for the helper class:

using System.ComponentModel;
using System.Runtime.CompilerServices;

namespace WinRTDatabase
{
    /// <summary>
    /// Base class for objects that can be put inside tables
    /// </summary>
    public class PropertyChangedCore : INotifyPropertyChanged
    {
        /// <summary>
        /// Set property value
        /// </summary>
        /// <typeparam name="T">Type of property</typeparam>
        /// <param name="value">New value</param>
        /// <param name="backingField">Backing field for property</param>
        /// <param name="propertyName">Name of property, leave blank</param>
        protected void SetProperty<T>(T value, ref T backingField, [CallerMemberName] string propertyName = "")
        {
            if (backingField == null && value != null)
            {
                backingField = value;
                OnPropertyChanged(propertyName);
            }
            else if (backingField != null && value == null)
            {
                backingField = value;
                OnPropertyChanged(propertyName);
            }
            else if (backingField != null && value != null && !backingField.Equals(value))
            {
                backingField = value;
                OnPropertyChanged(propertyName);
            }
        }

        /// <summary>
        /// Property changed event
        /// </summary>
        public event PropertyChangedEventHandler PropertyChanged;

        /// <summary>
        /// Raises <see cref="PropertyChanged"/> event/>
        /// </summary>
        /// <param name="propertyName">Name of property to raise event for</param>
        protected void OnPropertyChanged(string propertyName)
        {
            if (PropertyChanged != null)
            {
                PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
            }
        }
    }
}

The code is similar to template that is used in Metro style applications project templates distributed with VS 11.  However, I made an enhancement to it, comparing old value with new value for each property, and only raising property changed event if new value is different.  I had to do that because I ran into an issue with data binding, when property setter was invoked by data binding, event though nothing was changed.  Maybe it is a but in WinRT?

There is also facility that maintains state in database, so that you always check IsDirty flag on database to find out if you need to save.  I demonstrate the use of that property in my sample project by disabling Save button.

Most interesting code is in ViewModel class.  Here is the code for it.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WinRTDatabase;

namespace WinRTDbQuickStart
{
    public class MainViewModel : PropertyChangedCore
    {
        public string DatabaseName = "TestDb";
        private Database _database;

        public MainViewModel()
        {
            DeleteCommand = new SimpleCommand<Person>(OnDelete);
            AddCommand = new SimpleCommand<Person>(OnAdd, CanAdd);
            SaveCommand = new SimpleCommand<object>(OnSave, CanSave);
            Initialise();
        }

        public async void Initialise()
        {
            //await Database.DeleteDatabase(DatabaseName, StorageLocation.Local);
            var exists = await Database.DoesDatabaseExistsAsync(DatabaseName, StorageLocation.Local);
            if (!exists)
            {
                _database = await Database.CreateDatabaseAsync(DatabaseName, StorageLocation.Local);
                _database.CreateTable<Person>();
                var table = await _database.Table<Person>();
                await _database.SaveAsync();
                People = table;
            }
            else
            {
                _database = await Database.OpenDatabaseAsync(DatabaseName, true, StorageLocation.Local);
                People = await _database.Table<Person>();
            }

            SaveCommand.RaiseCanExecuteChanged();
            AddCommand.RaiseCanExecuteChanged();
            if (People.Count > 0)
            {
                SelectedPerson = People.First();
            }
            _database.PropertyChanged += new PropertyChangedEventHandler(_database_PropertyChanged);
        }

        void _database_PropertyChanged(object sender, PropertyChangedEventArgs e)
        {
            SaveCommand.RaiseCanExecuteChanged();
            AddCommand.RaiseCanExecuteChanged();
        }

        private Table<Person> people;

        public Table<Person> People
        {
            get { return people; }
            set
            {
                people = value; OnPropertyChanged("People");
                if (people.Count > 0)
                {
                    SelectedPerson = people.First<Person>();
                }
            }
        }

        private Person selectedPerson;

        public Person SelectedPerson
        {
            get { return selectedPerson; }
            set
            {
                selectedPerson = value;
                OnPropertyChanged("SelectedPerson");
            }
        }

        public SimpleCommand<Person> DeleteCommand { get; set; }

        public void OnDelete(Person parameter)
        {
            if (parameter != null)
            {
                People.Remove(parameter);
                if (People.Count > 0)
                {
                    SelectedPerson = People.First();
                }
            }
        }

        public SimpleCommand<Person> AddCommand { get; set; }

        public void OnAdd(Person parameter)
        {
            People.Add(new Person { PersonID = Guid.NewGuid(), FirstName=" ", LastName = " " });
            SelectedPerson = People.Last();
        }
        public bool CanAdd(object parameter)
        {
            return (_database != null && People != null);
        }

        public SimpleCommand<object> SaveCommand { get; set; }

        public void OnSave(object parameter)
        {
            if (_database != null)
            {
                _database.SaveAsync();
            }
        }
        public bool CanSave(object parameter)
        {
            return (_database != null && !_database.IsBusy && _database.IsDirty);
        }
    }
}

Code is pretty simple, although I wrote a simple command to help me along that implements ICommand.

using System;
using System.Windows.Input;

namespace WinRTDbQuickStart
{
    public class SimpleCommand<T> : ICommand
    {
        private Action<T> _executeMethod;
        private Func<T, bool> _canExecuteMethod;

        public SimpleCommand(Action<T> executemethod, Func<T, bool> canExecuteMethod)
        {
            _executeMethod = executemethod;
            _canExecuteMethod = canExecuteMethod;
        }

        public SimpleCommand(Action<T> executemethod)
        {
            _executeMethod = executemethod;
        }

        public bool CanExecute(object parameter)
        {
            if (_canExecuteMethod == null)
            {
                return true;
            }
            else
            {
                return _canExecuteMethod((T)parameter);
            }
        }

        public event EventHandler CanExecuteChanged;
        protected void OnCanExecuteChanged()
        {
            if (CanExecuteChanged != null)
            {
                CanExecuteChanged(this, EventArgs.Empty);
            }
        }

        public void Execute(object parameter)
        {
            _executeMethod((T)parameter);
        }

        public void RaiseCanExecuteChanged()
        {
            OnCanExecuteChanged();
        }

    }
}

As you can see, I am binding ListBox control directly to the data stored in a table.

Another important thing to notice.  All store and retrieve operations as async.  You need to make sure you are following this pattern and are using await keyword as needed.

Please let me know if you have any questions or suggestions.

Thanks.