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!