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