Abstract Database Access

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 h

Solution 1:

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

interfaceIDataRecord
{
  //record indexint Id{get;set;}

  //deep clone/copy functionobjectClone();  
}

publicabstractclassBaseRecord<T> : IDataRecord
{
    [PrimaryKey]
    [AutoIncrement]
    publicint Id { get; set; }

    publicobjectClone()
    {
        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;
    }
}

classUser : 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>publicclassAccessDataTableBaseSqLite<T> :  whereT: IDataRecord, new()
    {
        publicstaticSQLiteAsyncConnectionDBConnection;

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

        /// <summary>/// Constructor/// </summary>publicAllAccessDataTableBaseSqLite()
        {
            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 isUnauthorizedAccessException)
                    {

                    }
                }
            }
        }

        /// <summary>/// Create the data table/// </summary>/// <returns></returns>publicasyncTask<CreateTableResult> CreateTableAsync()
        {
            if (DBConnection!= null)
            {
                returnawaitDBConnection.CreateTableAsync<T>();
            }

            returnCreateTableResult.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>publicasyncTask<T> CreateAsync(T entity)
        {
            if (entity == null)
            {
                returndefault(T);
            }

            if (DBConnection== null)
            {
                returndefault(T);
            }

            entity.Id=0;
            try
            {
                awaitDBConnection.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>publicasyncTask<int> UpdateAllAsync(IEnumerable<T> entityList)
        {
            var result =0;
            foreach (var t in entityList)
            {
                if (null !=awaitUpdateAsync(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>publicasyncTask<T> GetById(int id)
        {
            if (DBConnection== null)
            {
                returndefault(T);
            }

            returnawaitDBConnection.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>publicasyncTask<List<T>> GetAll()
        {
            if (DBConnection!= null)
            {
                returnawaitDBConnection.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>publicasyncTask<T> UpdateAsync(T entity)
        {
            if (DBConnection== null)
            {
                returndefault(T);
            }

            if (entity == null)
            {
                returndefault(T);
            }

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

            if (null == newRecord)
            {
                returndefault(T);
            }

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

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

            }
            catch (Exception e)
            {

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

        }

        publicasyncTask<int> DeleteAsync(T entity)
        {
            if (DBConnection== null)
            {
                return-1;
            }
            returnawaitDBConnection.DeleteAsync(entity);
        }

        publicasyncTaskDeleteAll()
        {
            awaitDBConnection.DropTableAsync<T>();
            awaitCreateTableAsync();
        }

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

            List<T> list;
            
            if (maxResults ==0)
            {
                list =awaitGetAll();
            }
            else
            {
                list =awaitDBConnection.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());

        }

        publicasyncTask<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);
            }

            returnawait query.ToListAsync();
        }

        /// <inheritdoc />publicasyncTask<T> FindFirst(Expression<Func<T, bool>> predicate) =>awaitDBConnection.FindAsync(predicate);
    }