Abstract database access

Question

Let's say I have 2 models in my application and I want a table for each of them but my CRUD operations are the same for both. Is there a way to abstract the table name so I don't have to write the same code for both.

Exemple

    class User{
      int id,
      int name,
      int age
    }

   class Seller: User{
      List<string> products
    }

and i would access data like this

    public static async Task<List<User>> GetAll()
        {
            await Init();

            var users = await db.Table<User>().ToListAsync();

            return users;
        }

     public static async Task<List<Seller>> GetAll()
        {
            await Init();

            var users = await db.Table<Seller>().ToListAsync();

            return users;
        }

is there a way to write in abstract for

public static async Task<List<T>> GetAll()
        {
            await Init();

            var users = await db.Table<T>().ToListAsync();

            return users;
        }

EDIT I tried with this function

public static async Task<T> GetUser<T>(int id, bool isCustomer)
        {
            await Init();

            return await db.Table<T>().Where(x => x.id == id).FirstAsync();

        }

Errors: 'T' must be a non-abstract type with a public parameterless constructor in order to use it as parameter 'T' in the generic type or method 'SQLiteAsyncConnection.Table()'

'T' does not contain a definition for 'id' and no accessible extension method 'id' accepting a first argument of type 'T' could be found (are you missing a using directive or an assembly reference?)

Answer 1

You can achieve this if the classes implement the same interface e.g.

interface IDataRecord
{
  //record index
  int Id{get;set;}

  //deep clone/copy function
  object Clone();  
}

public abstract class BaseRecord<T> : IDataRecord
{
    [PrimaryKey]
    [AutoIncrement]
    public int Id { get; set; }

    public object Clone()
    {
        var newRecord = Activator.CreateInstance<T>();
        var props = newRecord.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance)
            .Where(p => p.CanRead);
        foreach (var p in props) p.SetValue(newRecord, p.GetValue(this));

        return newRecord;
    }
}

class User : BaseRecord<User>
{
  [NotNull]
  string Name{get;set;}

}

then you could have something like (redacted from my code but essence is there)

    /// <summary>
    /// This is the base class upon which all data tables shall be based.
    /// </summary>
    public class AccessDataTableBaseSqLite<T> :  where T: IDataRecord, new()
    {
        public static SQLiteAsyncConnection DBConnection;

        /// <summary>
        /// Lock object to prevent multi-thread interruption of code segment.
        /// </summary>
        public static readonly object CollisionLock = new object();

        /// <summary>
        /// Constructor
        /// </summary>
        public AllAccessDataTableBaseSqLite()
        {
            lock (CollisionLock)
            {
                if (DBConnection != null)
                {
                    DBConnection.CreateTableAsync<T>().Wait();

                    return;
                }

                try
                {
                    string directory;

                    if (DeviceInfo.Platform != DevicePlatform.Unknown)
                    {
                        directory = FileSystem.AppDataDirectory;
                    }
                    else
                    {
                        directory = "DataStore";
                        var directoryInfo = Directory.CreateDirectory(directory);
                        directory = directoryInfo.FullName;
                    }

                    var path = Path.Combine(directory, $"{typeof(T).Name}.db");
                    if (!File.Exists(path))
                    {
                        using var fileStream = File.Create(path);

                        fileStream.Close();
                    }

                    DBConnection = new SQLiteAsyncConnection(path);
                    DBConnection.CreateTableAsync<T>().Wait();
                }
                catch (Exception ex)
                {
                    if (ex is UnauthorizedAccessException)
                    {

                    }
                }
            }
        }

        /// <summary>
        /// Create the data table
        /// </summary>
        /// <returns></returns>
        public async Task<CreateTableResult> CreateTableAsync()
        {
            if (DBConnection != null)
            {
                return await DBConnection.CreateTableAsync<T>();
            }

            return CreateTableResult.Migrated;
        }

        /// <summary>
        /// Create a new record entry
        /// </summary>
        /// <param name="entity">Data entity to enter</param>
        /// <param name="user">Current User information</param>
        /// <returns>New entry record if successful</returns>
        public async Task<T> CreateAsync(T entity)
        {
            if (entity == null)
            {
                return default(T);
            }

            if (DBConnection == null)
            {
                return default(T);
            }

            entity.Id = 0;
            try
            {
                await DBConnection.InsertAsync(entity);

            }
            catch (SQLiteException e)
            {
                if (e.Message == "Constraint")
                {
                    throw new InvalidConstraintException(e.Message, e.InnerException);
                }
            }            
            var result = entity;

            return result;
        }

        /// <summary>
        /// Update a collection of new entities of type T to the data table.
        /// All entities should be present within the data table
        /// </summary>
        /// <param name="entityList">Entity collection</param>
        /// <returns>ID of entities successfully updated or added</returns>
        public async Task<int> UpdateAllAsync(IEnumerable<T> entityList)
        {
            var result = 0;
            foreach (var t in entityList)
            {
                if (null != await UpdateAsync(t))
                {
                    result++ ;
                }
            }

            return result;
        }

        /// <summary>
        /// Obtain the data record with the given Id
        /// </summary>
        /// <param name="id">Id value to select the record by</param>
        /// <returns>A valid record if found otherwise null</returns>
        public async Task<T> GetById(int id)
        {
            if (DBConnection == null)
            {
                return default(T);
            }

            return await DBConnection.Table<T>().Where(i => i.Id == id).FirstOrDefaultAsync();
        }

        /// <summary>
        /// This function returns all database entries that are not marked deleted or changed
        /// Warning: The data set may be very large
        /// </summary>
        /// <returns>A list of entries</returns>
        public async Task<List<T>> GetAll()
        {
            if (DBConnection != null)
            {
                return await DBConnection.Table<T>().ToListAsync();

            }
            return new List<T>();
        }

        /// <summary>
        /// This function is used to update the supplied record entry within the database.
        /// If the supplied record does not have a non-zero value Id field it is assumed to be a
        /// new record to be inserted into the database.
        /// </summary>
        /// <param name="entity">Record to update</param>
        /// <returns></returns>
        public async Task<T> UpdateAsync(T entity)
        {
            if (DBConnection == null)
            {
                return default(T);
            }

            if (entity == null)
            {
                return default(T);
            }

            var newRecord = (T) ((entity) as BaseRecord<T>)?.Clone();

            if (null == newRecord)
            {
                return default(T);
            }

            //if Id is zero assume that the record is new and to be added
            if (newRecord.Id == 0)
            {
                newRecord.Id = await DBConnection.InsertAsync(newRecord);
                return newRecord;
            }

            // Id is not zero and thus a new record should be created linked to the old record.
            var oldRecord = await GetById(newRecord.Id);
            try
            {
                var result = await DBConnection.UpdateAsync(oldRecord);

            }
            catch (Exception e)
            {

               Debug.WriteLine($"UpdateAsync {e.Message}");
            }           
            
            newRecord.PreviousRecordId = oldRecord.Id;
            newRecord.Id = 0;
            
            return await CreateAsync(newRecord);

        }

        public async Task<int> DeleteAsync(T entity)
        {
            if (DBConnection == null)
            {
                return -1;
            }
            return await DBConnection.DeleteAsync(entity);
        }

        public async Task DeleteAll()
        {
            await DBConnection.DropTableAsync<T>();
            await CreateTableAsync();
        }

        public async Task<PagedResult<T>> GetAllPagedResult(int recordId, uint maxResults = 100)
        {
            if (DBConnection == null)
            {
                return null;
            }

            List<T> list;
            
            if (maxResults == 0)
            {
                list = await GetAll();
            }
            else
            {
                list = await DBConnection.Table<T>().Where(x => (x.Id >= recordId)).ToListAsync();
                if (list.Count() > maxResults)
                {
                    list = list.GetRange(0, (int) maxResults);
                }
            }

            return new PagedResult<T>(list, list.Count());

        }

        public async Task<IEnumerable<T>> FindAsyncOrdered<TValue>(Expression<Func<T, bool>> predicate = null,
            Expression<Func<T, TValue>> orderBy = null)
        {
            var query = DBConnection.Table<T>();
            if (predicate != null)
            {
                query = query.Where(predicate);
            }

            if (orderBy != null)
            {
                query = query.OrderBy<TValue>(orderBy);
            }

            return await query.ToListAsync();
        }

        /// <inheritdoc />
        public async Task<T> FindFirst(Expression<Func<T, bool>> predicate) => await DBConnection.FindAsync(predicate);
    }