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.
- Create a Xamarin PCL or Shared project. It doesn’t matter if it’s Xamarin.Forms or native.
- Install this nuget package in your PCL or Shared project: https://www.nuget.org/packages/sqlite-net-pcl/
- Install the same nuget package in your Android, iOS, UWP, and any other project.
- 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; } } - 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>(); } } - 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); } } - Create a ProfileRepository Class:
public class ProfileRepository : GenericSqliteRepository<Post> { public ProfileRepository(DbContext context) : base(context) { } } - Set your file path in your platform code:
- Android
DbContext.LocalFilePath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
- 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;
- Android
- 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"); ... - 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!
Alex Dunn 