Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQLite.NET async BaseRepository
using System;
using System.Threading.Tasks;
using System.Collections.Generic;
using SQLite;
using System.Linq.Expressions;
using System.Linq;
namespace SQLite
{
public interface IBaseRepository<TEntity, TPrimaryKey>
{
Task CreateTable();
Task DropTable();
Task<List<TEntity>> GetAll();
Task<TEntity> Get(TPrimaryKey key);
Task InsertOrUpdate(TEntity entity);
Task Insert(TEntity entities);
Task InsertAll(IEnumerable<TEntity> entities);
Task InsertOrUpdateAll(IEnumerable<TEntity> entities);
Task Update(TEntity entity);
Task UpdateAll(IEnumerable<TEntity> entities);
Task Delete(TEntity entity);
Task DeleteAll(IEnumerable<TEntity> entities);
Task DeleteAll();
Task<List<TEntity>> Where(Expression<Func<TEntity, bool>> filter);
Task<TEntity> FirstOrDefault();
Task<TEntity> FirstOrDefault(Expression<Func<TEntity, bool>> filter);
Task<int> Count();
Task<int> Count(Expression<Func<TEntity, bool>> filter);
Task<TReturnType> GetMaxValue<TReturnType>(Expression<Func<TEntity, TReturnType>> property);
Task<TReturnType> GetMinValue<TReturnType>(Expression<Func<TEntity, TReturnType>> property);
}
public class BaseRepository<TEntity, TPrimaryKey> : IBaseRepository<TEntity, TPrimaryKey> where TEntity : class, new()
{
protected SQLiteAsyncConnection AsyncConnection { get; private set; }
public BaseRepository()
{
//Init your connection here
AsyncConnection = null;
}
public async Task DropTable()
{
await AsyncConnection.DropTableAsync<TEntity>();
}
public async Task CreateTable()
{
await AsyncConnection.CreateTableAsync<TEntity>();
}
public async Task<TEntity> Get(TPrimaryKey key)
{
return await AsyncConnection.GetAsync<TEntity>(key);
}
public virtual async Task<List<TEntity>> GetAll()
{
return await AsyncConnection.Table<TEntity>().ToListAsync();
}
public virtual async Task Insert(TEntity entity)
{
await AsyncConnection.InsertAsync(entity);
}
public virtual async Task InsertAll(IEnumerable<TEntity> entities)
{
await AsyncConnection.InsertAllAsync(entities);
}
public virtual async Task InsertOrUpdate(TEntity entity)
{
await AsyncConnection.InsertOrReplaceAsync(entity);
}
public virtual async Task InsertOrUpdateAll(IEnumerable<TEntity> entities)
{
foreach (var entity in entities)
{
await AsyncConnection.InsertOrReplaceAsync(entity);
}
}
public virtual async Task Update(TEntity entity)
{
await AsyncConnection.UpdateAsync(entity);
}
public virtual async Task UpdateAll(IEnumerable<TEntity> entities)
{
await AsyncConnection.UpdateAllAsync(entities);
}
public virtual async Task Delete(TEntity entity)
{
await AsyncConnection.DeleteAsync(entity);
}
public virtual async Task DeleteAll(IEnumerable<TEntity> entities)
{
foreach (var entity in entities)
{
await AsyncConnection.DeleteAsync(entity);
}
}
public virtual async Task DeleteAll()
{
await AsyncConnection.ExecuteAsync("DELETE from " + typeof(TEntity).Name);
}
public virtual async Task<List<TEntity>> Where(Expression<Func<TEntity, bool>> filter)
{
return await AsyncConnection.Table<TEntity>().Where(filter).ToListAsync();
}
public virtual async Task<TEntity> FirstOrDefault()
{
return await AsyncConnection.Table<TEntity>().FirstOrDefaultAsync();
}
public virtual async Task<TEntity> FirstOrDefault(Expression<Func<TEntity, bool>> filter)
{
return await AsyncConnection.Table<TEntity>().Where(filter).FirstOrDefaultAsync();
}
public virtual async Task<int> Count()
{
return await AsyncConnection.Table<TEntity>().CountAsync();
}
public virtual async Task<int> Count(Expression<Func<TEntity, bool>> filter)
{
return await AsyncConnection.Table<TEntity>().Where(filter).CountAsync();
}
public virtual async Task<TReturnType> GetMaxValue<TReturnType>(Expression<Func<TEntity, TReturnType>> property)
{
return await AsyncConnection.ExecuteScalarAsync<TReturnType>($"SELECT COALESCE(MAX({GetPropertyName(property)}), 0) FROM {typeof(TEntity).Name}");
}
public virtual async Task<TReturnType> GetMinValue<TReturnType>(Expression<Func<TEntity, TReturnType>> property)
{
return await AsyncConnection.ExecuteScalarAsync<TReturnType>($"SELECT COALESCE(MIN({GetPropertyName(property)}), 0) FROM {typeof(TEntity).Name}");
}
string GetPropertyName<TReturnType>(Expression<Func<TEntity, TReturnType>> property)
{
return (property.Body as MemberExpression ?? ((UnaryExpression)property.Body).Operand as MemberExpression).Member.Name;
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.