Xamarin.Tips – Super Simple Sqlite

Thinking about locally storing data and entity structures can be intimidating. You might have a complex data structure in your backend server structure and are trying to match that type of data in your mobile apps. Perhaps you’ve just never done it before and need a solution quickly. You might be over thinking it!

Let’s break it down into some straightforward steps.

  1. Create a Xamarin PCL or Shared project. It doesn’t matter if it’s Xamarin.Forms or native.
  2. Install this nuget package in your PCL or Shared project: https://www.nuget.org/packages/sqlite-net-pcl/
  3. Install the same nuget package in your Android, iOS, UWP, and any other project.
  4. Create your model in your PCL or Shared project. In this case, we will use a basic example model:
    public class Profile
    {
        public string Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Handle { get; set; }
        public DateTime CreatedDate { get; set; }
    }
    
  5. Create a DbContext class in your PCL or Shared project:
    public class DbContext
    {
        public static string LocalFilePath; // Set this before creating from platform project
        public SQLiteAsyncConnection Database { get; }
        /// <summary>
        /// Initialized a new DbContext
        /// </summary>
        public DbContext()
        {
            Database = new SQLiteAsyncConnection(LocalFilePath + "localdb.db3");
        }
    
        /// <summary>
        /// Creates a table for a given type in sql lite
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public async Task<CreateTablesResult> CreateTableAsync<T>() where T : new()
        {
        return await Database.CreateTableAsync<T>();
        }
    
        /// <summary>
        /// Gets a table by it's type from the db.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public AsyncTableQuery<T> Set<T>() where T : new()
        {
            return Database.Table<T>();
        }
    }
    
  6. Create a GenericRepository Class:
    public class GenericSqliteRepository<T> : IGenericRepository<T> where T : new()
    {
        protected readonly DbContext _context;
        public GenericSqliteRepository(DbContext context)
        {
            _context = context;
        }
        public virtual async Task InitializeAsync()
        {
            await _context.CreateTableAsync<T>();
        }
        public virtual async Task AddAsync(T entity)
        {
            await _context.Database.InsertOrReplaceAsync(entity);
        }
        public virtual async Task AddRangeAsync(IEnumerable<T> entities)
        {
            await _context.Database.InsertAllAsync(entities);
        }
    
        public virtual async Task<T> FindAsync(Expression<Func<T, bool>> predicate)
        {
            return await _context.Set<T>().Where(predicate).FirstOrDefaultAsync();
        }
    
        public virtual async Task<IEnumerable<T>> GetAsync(Expression<Func<T, bool>> predicate)
        {
            return await _context.Set<T>().Where(predicate).ToListAsync();
        }
    
        public virtual async Task<IEnumerable<T>> GetAsync(int skip, int take)
        {
            return await _context.Set<T>().Skip(skip).Take(take).ToListAsync();
        }
    
        public virtual async Task<IEnumerable<T>> GetAsync(Expression<Func<T, bool>> predicate, int skip, int take)
        {
            return await _context.Set<T>().Where(predicate).Skip(skip).Take(take).ToListAsync();
        }
    
        public virtual async Task RemoveAsync(T entity)
        {
            await _context.Database.DeleteAsync(entity);
        }
    
        public virtual async Task UpdateAsync(T entity)
        {
            await _context.Database.UpdateAsync(entity);
        }
    }
    
  7. Create a ProfileRepository Class:
    public class ProfileRepository : GenericSqliteRepository<Post>
    {
        public ProfileRepository(DbContext context) : base(context)
        {
        }
    }
    
  8. Set your file path in your platform code:
    1. Android
      DbContext.LocalFilePath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
      
    2. iOS
      var docFolder = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
      string libFolder = Path.Combine(docFolder, "..", "Library", "Databases");
      
      if (!Directory.Exists(libFolder))
      {
          Directory.CreateDirectory(libFolder);
      }
      DbContext.LocalFilePath = libFolder;
      
  9. Use your repository in your shared code or platform code or wherever you want.
    var repo = new ProfileRepository(new DbContext());
    await repo.InitializeAsync();
    ...
    await repo.AddAsync(new Profile {...});
    ...
    var profiles = await repo.GetAsync(0, 10);
    ...
    var profile = await repo.FindAsync(p => p.Id == "foo");
    ...
    
  10. Start storing your things!

This is obviously a simple situation that doesn’t cover all needs, but it’s a place to start for complex data models. Build repositories for each of your types. Control your queries that are very specific in those model-specific repositories.

Look out for a follow up post about some patterns and tips to use for complex and large data sets!

Advertisements

6 thoughts on “Xamarin.Tips – Super Simple Sqlite”

  1. Also, can you please detail a bit what you mean by this:
    “This is obviously a simple situation that doesn’t cover all needs”

    I’m interested about scenarios which are not covered.
    Thanks!

    Like

  2. So one of the situations it doesn’t cover is handling thread safety (as well as properly handling relationships, multiple keys, etc etc.). We know that sqlite-net will lock access to a db table while writing, and thus, if you try to hit it at the same time from two threads it will throw an exception. I answered this sort of question on SO or the Xamarin forums (struggling to find a link), but there are some ways around this.
    1. Only use one repository to talk to a single table (like this post does)
    2. Only use one instance of that repository
    3. Use a private field on the repo that handles checking if it is writing to the db or not. If it is, handle it by returning a safe error, or waiting and trying again.
    4. Advanced capability on top of #3 is to add a queue for requests and simply add items to the queue for the repository to pull off and process one at a time so there are no threading issues. Basically taking your multi threading and tying them into a knot to create one place for the repo to processes each request.

    I will talk about some of those strategies in some posts later when I talk about complex data sets and applications. This post was intended just to get people up and running with storing basic data in sqlite, hence the “super simple”. Keep an eye out for more posts in the coming weeks! And always feel free to comment and ask more questions! 🙂

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s