Local Database with Windows Phone 8.1

Following on from the previous post, we now want to incorporate basic local database into our application which words and scores will be stored in. To begin we looked at the local database sample.

Model/ToDoDataContext.cs

First, we'll take a look at the ToDoDataContext class in the Model folder! This is where you define how your data will be stored. To start off, we added a new folder and C# class to our spelling bee project and called them Model and SpellingDataContext respectively. Then we had SpellingDataContext extend DataContext, if you need to add a reference to System.Data.Linq.dll in your project you may have selected a universal app initially. This means you may need to use SQLite or switch to a Windows Phone Silverlight app, if you chose a Windows Phone Silverlight app initially your references will appear as follows:



Now we're ready to define how our data will be stored, to start off design your schema. We decided on the following, using dbdsgnr:



Now that we know what we want, let's take another look at ToDoDataContext.cs. As you can see, we have a constructor that passes the connection string to the DataContext super class. We want to replicate this for SpellingDataContext:



Now we are going to define our tables, we can do this in the same way as ToDoDataContext. The [Table] statement basically specifies that the following code pertains to a table. The use of square brackets before a block of code denotes an attribute in C#. You can also see these before the public properties which have the attribute defining a column before them. The column attribute for ToDoItemId has parameters, the parameters which can be passed in and their meaning are defined on the Microsoft page on attribute-based mapping. That page also has information on the parameters which can be present when defining an association. An association in the case of our Spelling Bee app would be a foreign key constraint e.g. the one that associates a word with a spelling list.

Once all your tables, columns and associations are defined you must add a reference to the tables from your DataContext object, e.g. public Table<Word> Words; After this we can move onto defining our ViewModel. An example of defining a table is as follows:

    [Table]
    public class Word {
        private int _id;

        [Column(IsPrimaryKey = true, IsDbGenerated = true, 
         DbType = "INT NOT NULL Identity", CanBeNull = false,
         AutoSync = AutoSync.OnInsert)]
        public int Id
        {
            get { return _id; }
            set
            {
                if (_id != value)
                {
                    _id = value;
                }
            }
        }

        private string _name;

        [Column]
        public string Name
        {
            get { return _name; }
            set
            {
                if (_name != value)
                {
                    _name = value;
                }
            }
        }

        private bool _isCorrect;

        [Column]
        public bool IsCorrect
        {
            get { return _isCorrect; }
            set
            {
                if (_isCorrect != value)
                {
                    _isCorrect = value;
                }
            }
        }

        [Column]
        internal int _spellingListId;

        private EntityRef _spellingList;
        [Association(Storage = "_spellingList", 
         ThisKey = "_spellingListId", OtherKey = "Id", 
         IsForeignKey = true)]
        public SpellingList SpellingList
        {
            get { return _spellingList.Entity; }
            set
            {
                _spellingList.Entity = value;

                if (value != null)
                {
                    _spellingListId = value.Id;
                }
            }
        }
    }

ViewModel/ToDoViewModel.cs

Now for the ToDoViewModel, this is where we handle events relating to data and what we use to get data! First we created a ViewModel folder and a SpellingBeeViewModel C# file. Then we took a look at the ToDoViewModel, this has a private field toDoDB of the ToDoDataContext type which unsurprisingly provides a reference to the database we just specified. The toDoDB is initialised in the constructor. We did the same for our spelling database in our SpellingViewModel class, to arrive at something like this:



Next, we looked at the data available through the ToDoViewModel. It defined a private field and property for all the data required by the MainPage and NewPage, so we did the same for our SpellingViewModel. We decided we needed the following data:

• A collection of games. This can be added to when a new game is started, and is used for displaying scores.
• A collection of spelling lists. This can be added to from the menu bar and is used for selecting which spelling list you would like to try.
• A collection of words you have currently spelled correctly. This will be displayed on the home page.
• A collection of words you have currently spelling incorrectly. This will be displayed on the home page.

Therefore, we created corresponding private and public fields in our SpellingViewModel of type ObservationalCollection. ObservationalCollections were used to aid us in automatically updating the user interface. For example, if we bind a dropdown to a observational collection and it changes, the dropdown will automatically update to match the changes.



The above populates the properties we defined earlier. But what if we want our database to come pre-populated with defaults and where do we load the database from? The answer is from your App's C# code, for us it is App.xaml.cs.

App.xaml.cs

In the App constructor, we want to instantiate the SpellingDataContext and check if the database exists. If the database doesn't exist we want to create it and insert the initial values in it:



Once the data has been loaded, we want to create the view model which will be used throughout the app for populating views. Using the view model, we can then call our method which retrieves the data from the database and maps it to collections on the view model. Now you can store data easily on Windows Phone apps, and use it in whatever way you like. It's important to note you may encounter a few issues:

• If you cannot create your database your attributes on your columns may be a potential cause, in particular we had issues with auto-generating values for an entry based on an expression.
• Also as mentioned previously, if you have to add references for linq then you may have selected a universal app initially. In which case either SQLite will need to be used or you can manually switch to a Windows Phone Silverlight app. The decision between the two will depend on how much code you have written when you discover this problem.

Enjoy!