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