[Guide] Basic CRUD with a Generic Controller for SQLite.

RaphaelSchindlerRaphaelSchindler USMember ✭✭✭

Hi Guys.

As I've seen a few posts latetly that are asking about using the SQLite component in Forms, I thought it would be a nice idea to set up a post which explains most of it.

This is a real handy implementation, because you can feed every Entity you want to it. And it's async which is great for mobile apps. If you don't need it to be async you have to alter my code a little bit. But I'm gonna explain this in detail in my post.

So to get ready for all of this you need to install this neat little NuGet into your Solutions (PCL, Android and iOS). I really really like this one. It's so easy to use. I'm currently using the Stable one but I'm gonna upgrade to the latest -pre in the near future.

Now let's get started. This is gonna be accessible in a DependencyService so our first move is to make a Interface for it.
I called it IDatabaseAccess which contains a single method.

public interface IDatabaseAccess
{
    SQLiteAsyncConnection GetConnection();
}

Thats it :) If you don't need the async functionality you have to use SQLiteConnection.

Let's head off to our Android project. Here we add the implementation so we can create the Database and get a Connection to it.
It's a simple one and I called it DatabaseAccess

[assembly: Dependency(typeof(DatabaseAccess))]

namespace YourNameSpace.Droid
{
    public class DatabaseAccess : IDatabaseAccess
    {
        public DatabaseAccess()
        {

        }

        public SQLiteAsyncConnection GetConnection()
        {
            var sqlDbFileName = "YourDBName.db3";
            var documentsPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
            var path = System.IO.Path.Combine(documentsPath, sqlDbFileName);

            var connection = new SQLiteAsyncConnection(path);

            return connection;
        }
    }
}

This is completly the same in your iOS project. So you just can drop this code into the file you're creating in your iOS project.

Now when you want to use the Database you have to drop this in the code where you need it:

var databaseConnection = DependencyService.Get<IDatabaseAccess>().GetConnection();

Pretty simple start right? Yeah now let's get to the more complex stuff.

Here we're gonna build the EntityController which will do all the CRUD operations and a little bit more ;)

But first we need to define a basic Entity. Since we are working on mobile and with a database I had 2 things in my mind here.
First we need some basic DB stuff like an ID, some Flags and DateTimes.
Second I wanted the GUI to automatically update when an Entity changed.

So this is how my basic Entity looks like.

public class Entity : INotifyPropertyChanged
{
    public event PropertyChangedEventHandler PropertyChanged;

    [PrimaryKey, AutoIncrement]
    public int? Id { get; set; }

    private DateTime _createdAt;

    public DateTime CreatedAt
    {
        get { return _createdAt; }
        set
        {
            if (value.Equals(_createdAt))
            {
                return;
            }
            _createdAt = value;
            OnPropertyChanged();
        }
    }

    private DateTime _modifiedAt;

    public DateTime ModifiedAt
    {
        get {return _modifiedAt; }
        set
        {
            if (value.Equals(_modifiedAt))
            {
                return;
            }
            _modifiedAt = value;
            OnPropertyChanged();
        }
    }

    private bool _deleted;

    public bool Deleted
    {
        get { return _deleted; }
        set
        {
            if (value.Equals(_deleted))
            {
                return;
            }
            _deleted = value;
            OnPropertyChanged();
        }
    }

    public void OnPropertyChanged([CallerMemberName] string propertyName = null)
    {
        var handler = PropertyChanged;
        handler?.Invoke(this, new PropertyChangedEventArgs(propertyName));
    }

    public Entity()
    {
        CreatedAt = DateTime.Now;
        ModifiedAt = DateTime.Now;
    }
}

Since we have a basic Entity we can go to implement the Controller for it.
I split this class into 2 parts. The first one is the defining Interface and the second is the implemantion for the methods. To keep it a little simple I'm gonna split this also into 2 parts here and explain them a little.

Off to the Interface then:

public interface IEntityController<T> where T : Entity, new()
{
    Task<List<T>> Get();

    Task<T> Get(int id);

    Task<ObservableCollection<T>> Get<TValue>(Expression<Func<T, bool>> predicate = null, Expression<Func<T, TValue>> orderBy = null);

    Task<T> Get(Expression<Func<T, bool>> predicate);

    AsyncTableQuery<T> AsQueryable();

    Task<int> Insert(T entity);

    Task<int> Update(T entity);

    Task<int> Delete(T entity);

    Task<int> Count(Expression<Func<T, bool>> predicate = null);
}

So what does this? Well it just defines the methods we need. Simple CRUD and some more complex methods like the Getter with Expression or the Count method with Expression. They will be used for filters. If you have further questions just leave a comment here.

Now we can define the methods which is obviously a little bit more code :)
Here's the code:

public class EntityController<T> : IEntityController<T> where T : Entity, new()
{
    private SQLiteAsyncConnection _db;

    public EntityController(SQLiteAsyncConnection db)
    {
        this._db = db;
    }

    public AsyncTableQuery<T> AsQueryable()
    {
        return _db.Table<T>();
    }

    public async Task<int> Count(Expression<Func<T, bool>> predicate = null)
    {
        var query = _db.Table<T>();

        if (predicate != null)
        {
            query = query.Where(predicate);
        }

        return await query.CountAsync();
    }

    public async Task<int> Delete(T entity)
    {
        return await _db.DeleteAsync(entity);
    }

    public async Task<List<T>> Get()
    {
        return await _db.Table<T>().ToListAsync();
    }

    public async Task<T> Get(Expression<Func<T, bool>> predicate)
    {
        return await _db.FindAsync<T>(predicate);
    }

    public async Task<T> Get(int id)
    {
        return await _db.FindAsync<T>(id);
    }

    public async Task<ObservableCollection<T>> Get<TValue>(Expression<Func<T, bool>> predicate = null, Expression<Func<T, TValue>> orderBy = null)
    {
        var query = _db.Table<T>();

        if (predicate != null)
        {
            query = query.Where(predicate);
        }
        if (orderBy != null)
        {
            query = query.OrderBy<TValue>(orderBy);
        }

        var collection = new ObservableCollection<T>();
        var items = await query.ToListAsync();
        foreach (var item in items)
        {
            collection.Add(item);
        }

        return collection;
    }

    public async Task<int> Insert(T entity)
    {
        return await _db.InsertAsync(entity);
    }

    public async Task<int> Update(T entity)
    {
        return await _db.UpdateAsync(entity);
    }
}

Yeah that's it. This is really all you need. And now I'm gonna explain a little how to use this.

So let's say you have two Entities you want to work with. In this example I'm gonna use Cat and Dog.

public class Cat : Entity
{
    private string _name;
    public string Name
    {
        get { return _name; }
        set
        {
            if(value.Equals(_name))
            {
                return;
            }
            _name = value;
            OnPropertyChanged();
        }
    }

    private int _furLength;
    public int FurLenght
    {
        get { return _furLength; }
        set
        {
            if(value.Equals(_furLength))
            {
                return;
            }
            _furLength = value;
            OnPropertyChanged();
        }
    }
}

public class Dog : Entity
{
    private string _name;
    public string Name
    {
        get { return _name; }
        set
        {
            if(value.Equals(_name))
            {
                return;
            }
            _name = value;
            OnPropertyChanged();
        }
    }

    private int _barkNoise;
    public int BarkNoise
    {
        get { return _barkNoise; }
        set
        {
            if(value.Equals(_barkNoise))
            {
                return;
            }
            _barkNoise = value;
            OnPropertyChanged();
        }
    }
}

You have to create 2 Controllers for them who aren't doing anything complex and can be easily extended if you need more methods for an Entity.

Note: I have a public static SQLiteAsyncConnection that I declare in my App.cs so I have access to the SqlConnection all over my app.

public class CatController : EntityController
{
    public CatController() : base(App.SqlConnection)
    {
        App.SqlConnection.CreateTableAsync<Cat>();
    }
}

public class DogController : EntityController
{
    public DogController() : base(App.SqlConnection)
    {
        App.SqlConnection.CreateTableAsync<Dog>();
    }
}

If you want to use the methods in your app it's pretty simple. Im gonna show a few ones but I think you can get the rest on your own, since this post is rather large already.

public class TestPage : ContentPage
{
    public TestPage()
    {
        var catController = new CatController();
        var dogController = new DogController();

        //Here we get all cats that aren't deleted and their name contains an 'a'
        var cats = catController.Get(cat => !cat.Deleted && cat.Name.Contains("a"));

        //Here we get all dogs who's BarkNoise is OVER 9000 and sort them
        var loudDogs = dogController.Get(dog => dog.BarkNoise > 9000, dog => dog.BarkNoise));
    }
}

So thats it. I hope someone will find this useful. If you have questions feel free to leave a comment.

Posts

  • JohnHardmanJohnHardman GBUniversity mod

    @RaphaelSchindler - looks a very useful guide. Nice. Have you extended this to include synchronisation with a cloud-based DB, such as using Microsoft Azure as a back end?

  • RaphaelSchindlerRaphaelSchindler USMember ✭✭✭
    edited May 2016

    @JohnHardman Not now, since my app is in a really early stage. But this will come on me in the near future. But I think it would be not that hard tbh. Instead of writing it to the local database in the methods you could hook the cloud-service inside of them. With a REST based WebService and JSON this shouldn't be that hard, I guess.

    Actually as I think of this I have something for this flying around somewhere. I'm gonna do a quick search ok?

  • RaphaelSchindlerRaphaelSchindler USMember ✭✭✭

    @JohnHardman Since I have no idea how the Azure backends work, I'm not sure if I can help you here. Could you give me a little insight to it? How is the API called and so

  • JohnHardmanJohnHardman GBUniversity mod

    @RaphaelSchindler - There are some existing samples available as to how to do this - I just wondered if you had already added this as the next step. The more samples the better IMHO :-)

    As a starting point, take a look at:
    https://azure.microsoft.com/en-gb/documentation/articles/app-service-mobile-offline-data-sync/

  • zasah16zasah16 ZAMember ✭✭

    I believe the SQLite Async functions are only fake Async functions. Read this:

    http://xleon.net/xamarin/dotnet/sqlite-net/async/2017/02/15/sqlite-net-async-vs-sync/

    What is the point of going to all this trouble?

  • MonteHansenMonteHansen USUniversity ✭✭
    edited September 2018

    Very nice Raphael. This was exactly what I was looking for. Abstraction is similar to JPA's EntityManager approach. Only thing I would add is to synchronize when I am touching the database. Also agree with sidebar on async for the sake of async, which ends up being a sweater thread that no one wants to tug on so we just conform and perpetuate.

Sign In or Register to comment.