Monthly Archives: August 2016

Creating Complex Tables using SQLite.Net

Based on this earlier post, I returned to my test project to find that it didn’t actually pass anymore. The first issue was that the test couldn’t locate Sqllite3.dll.

Thanks to this question on every programmers favourite web site, I realised that I needed to add an extension for SQLite.

The next issue was when I tried to add a test to create a complex table:

complex1

Don’t know about [EntityName].

What I mean by a complex table, and the thing that CreateTable doesn’t like, is an entity that references another entity (the same is true if you try to reference the same entity).

Finally, you’re not allowed to use the System.Object type:

Complex2

A fix

It is my understanding that, if you can get it to work with SQLite, Entity Framework will solve this problem for you. I took more of a roll-your-own approach. The key issue here is that you want SQLite to not try to serialise the second object; and for this, you can use the [Ignore] attribute:

private ProductCategory _category;
 
[Ignore]
public ProductCategory Category
{
    get { return _category; }
    set
    {
        _category = value;
        RaisePropertyChanged();
    }
}

So, that stops it crashing. The next part is largely dependent on your data: if you have a basic primary / foreign key one-to-n mapping then this will work; however, anything more complex, and you’ll probably have to write a custom abstraction for the data. All that said, my solution starts with a base entity class:

public abstract class BaseDataEntity : INotifyPropertyChanged
{
    public event PropertyChangedEventHandler PropertyChanged;
 
    protected void RaisePropertyChanged([CallerMemberName] string propertyName = null)
    {
        var handler = PropertyChanged;
        if (handler != null)
            handler(this, new PropertyChangedEventArgs(propertyName));
    }
 
    public abstract string Key { get; set; }
}

The PropertyChanged goes without saying (it’s like the roads), but the interesting part is the Key. Based on the code above, each entity needs a uniquely identifiable key; in this case, it’s a string; however, this can be any primitive type. In the derived class, you’ll need to map the type to the data; for example:

 
[Ignore]
public ProductCategory Category
{
    get { return _category; }
    set
    {
        _category = value;
        RaisePropertyChanged();
    }
}
 
public string CategoryRef
{
    get { return Category.Key; }
    set
    {
        if (Category == null)
        {
            Category = new ProductCategory()
            {
                Key = value
            };
        }
    }
}        

I’ll explain CategoryRef later on.

The next thing is to put some logic into the data access (I simply created a DataAccessBase class to sit on top of the SQLite data manipulation. Here is the abstraction to add a record:

 
public void Add(Type objType, object t)
{
    _connection.Insert(t);
 
    // Look for any ignored properties
    var ignored = objType                
        .GetProperties().Where(p =>
            p.CustomAttributes.Any(a => a.AttributeType == typeof(SQLite.Net.Attributes.IgnoreAttribute)));
 
    // For each, look for a reference
    foreach (var p in ignored)
    {
        // Determine the type first and get the value
        Type propertyType = p.PropertyType;
        var propertyValue = p.GetValue(t);
 
        // Recursively call this function to add the reference
        Add(propertyType, propertyValue);
    }
}

As you can see, for each reference, it simply recursively calls itself to add that, too. This will spectacularly fall down if you have a circular reference (so don’t do that, or don’t use this if you do).

Next is the Get:

 

public T Get<T>(string key) where T : class
{
    T data = _connection.Get<T>(key);
 
    var ignored = typeof(T)
        .GetProperties()
        .Where(p => p.GetCustomAttributes(typeof(SQLite.Net.Attributes.IgnoreAttribute), inherit: true).Any())
        .ToList();
 
    // For each, look for a reference
    foreach (var p in ignored)
    {
        // Get the reference property
        string propName = $"{p.Name}Ref";
        PropertyInfo refProp = typeof(T).GetProperty(propName);
 
        // Get its value (we know it's a string)
        string val = refProp.GetValue(data).ToString();
 
        // Determine the type first and get the value
        Type propertyType = p.PropertyType;
        
        var mapping = _connection.TableMappings.First(m => m.MappedType == propertyType);
        var rec = _connection.Find(val, mapping);
 
        // Update the property
        p.SetValue(data, rec);
 
    }
}

Here, I’m using a convention based approach; so, where I reference a separate class, I also have separate property called [Class]Ref; which you can see earlier on in the example of the entity code.

Conclusion

And that’s it; I make no assertions about edge cases, but for basic data that references other basic data, this works fine.

Acknowledgements

I have a great deal of help in creating this, but mainly just from random search results; however, I did ask a question on Stack Overflow

MVVM Cross Upgrade to 4.2.2

Coming back to MVVMCross and trying to create a new project, I found that some of the documentation available for the new version (4.2.2 at the time of writing this) is no longer correct; for example, the ToDo file in the sample projects still looks like this:

The steps to get this Store UI working are:

1. Add a reference to your Core PCL project
2. Change App.Xaml.cs so that it creates a ‘new Setup(RootFrame)’ during its OnLaunched:

protected override void OnLaunched(LaunchActivatedEventArgs args)
{
var rootFrame = Window.Current.Content as Frame;

// Do not repeat app initialization when the Window already has content,
// just ensure that the window is active
if (rootFrame == null)
{
// Create a Frame to act as the navigation context and navigate to the first page
rootFrame = new Frame();

if (args.PreviousExecutionState == ApplicationExecutionState.Terminated)
{
//TODO: Load state from previously suspended application
}

// Place the frame in the current Window
Window.Current.Content = rootFrame;
}

if (rootFrame.Content == null)
{
// When the navigation stack isn’t restored navigate to the first page,
// configuring the new page by passing required information as a navigation
// parameter

var setup = new Setup(rootFrame);
setup.Initialize();

var start = MvvmCross.Core.Mvx.Resolve();
start.Start();
}
// Ensure the current window is active
Window.Current.Activate();
}

3. For Windows 8 – Add a views folder and a view – xaml.cs and .xaml based on BasicPage – this
will add 5 files to the Common folder.
– Change the Common/LayoutAwarePage.cs inheritance to MvvmCross.WindowsStore.Views.MvxStorePage
– Change the Common/LayoutAwarePage.cs – remove the OnNavigatedTo and OnNavigatedFrom handlers
– Add some content for your Xaml – e.g.

5. For Windows 8.1 – Add a views folder and a view based on the BasicPage template
– In the .xaml.cs – remove public NavigationHelper NavigationHelper and all referencing code
– In the .xaml.cs – remove the OnNavigatedTo and OnNavigatedFrom handlers
– Add some content for your Xaml – e.g.

This document was very useful. I was looking specifically at the Babel project in the above sample; this won’t compile under MvvmCross 4.2.2. I’ve listed here everything I needed to do to make it.

Mvx has now been replaced with MvxSimpleIoCContainer.Instance

In App.xaml.cs:

var start = MvxSimpleIoCContainer.Instance.Resolve<IMvxAppStart>();

Is now:

var start = MvxSimpleIoCContainer.Instance.Resolve<IMvxAppStart>();
start.Start();

In App.cs:

        private void InitializeText()
        {
            var builder = new TextProviderBuilder();
            Mvx.RegisterSingleton<IMvxTextProviderBuilder>(builder);
            Mvx.RegisterSingleton<IMvxTextProvider>(builder.TextProvider);
        }

Is now is a separate plug-in by the looks of things:

mvvmcrossupgrade

The new code is:

        private void InitializeText()
        {            
            var builder = new TextProviderBuilder();
            MvxSimpleIoCContainer.Instance.RegisterSingleton<IMvxTextProviderBuilder>(builder);
            MvxSimpleIoCContainer.Instance.RegisterSingleton<IMvxTextProvider>(builder.TextProvider);
        }