nuget-3ie/WebApiUtils/BusinessManager/SqlServerManager.cs

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Linq.Expressions;
using System.Runtime.Serialization;
using iiie.Logs.DBO;
using iiie.WebApiUtils.DBO;

namespace iiie.WebApiUtils.BusinessManager
{
    /// <summary>
    /// Helper for SQL Server data access
    /// </summary>
    public abstract class SqlServerManager<TDbObject, TDboGet, TDboAdd, TDboEdit, TEntities, TThis>
        : SqlServerBasicManager<TDbObject, TEntities, TThis>
        where TDbObject : class
        where TEntities : DbContext, new()
        where TThis : SqlServerManager<TDbObject, TDboGet, TDboAdd, TDboEdit, TEntities, TThis>, new()
    {

        /// <summary>
        /// Convert a DB object to DBO
        /// </summary>
        /// <param name="obj">The object to be converted</param>
        /// <returns>The DBO</returns>
        public abstract TDboGet DbToDboGet(TDbObject obj);

        /// <summary>
        /// Convert a DBO to DB object
        /// </summary>
        /// <param name="obj">The object to be converted</param>
        /// <param name="add">The object to be added</param>
        /// <returns>The DB object</returns>
        public abstract OpResult<bool> DboAddToDb(TDboAdd obj, TDbObject add);

        /// <summary>
        /// Convert a DBO to DB object
        /// </summary>
        /// <param name="obj">The object to be converted</param>
        /// <param name="edit">The object beeing edited</param>
        /// <returns>The DB object</returns>
        public abstract OpResult<bool> DboEditToDb(TDboEdit obj, TDbObject edit);

        /// <summary>
        /// Return a global predicate that will be applied to all selects
        /// </summary>
        /// <returns>The expression</returns>
        public virtual Expression<Func<TDbObject, bool>> GetGlobalSelectPredicate()
        {
            return x => true;
        }

        /// <summary>
        /// Helper to get real select predicate
        /// </summary>
        /// <returns>The global predicate</returns>
        public static Expression<Func<TDbObject, bool>> GetGlobalSelectPredicateStatic()
        {
            var obj = new TThis();
            return obj.GetGlobalSelectPredicate();
        }

        /// <summary>
        /// Helper to convert DB object to DBO
        /// </summary>
        /// <param name="res">The result to convert</param>
        /// <returns>The DBO object</returns>
        public static TDboGet DbToDboGetStatic(TDbObject res)
        {
            var obj = new TThis();
            return obj.DbToDboGet(res);
        }
        
        /// <summary>
        /// Helper to convert DBO object to DB
        /// </summary>
        /// <param name="res">The result to convert</param>
        /// <param name="add">The object to be added</param>
        /// <returns>The DB object</returns>
        public static OpResult<bool> DboAddToDbStatic(TDboAdd res, TDbObject add)
        {
            var obj = new TThis();
            return obj.DboAddToDb(res, add);
        }

        /// <summary>
        /// Helper to convert DBO object to DB
        /// </summary>
        /// <param name="res">The result to convert</param>
        /// <param name="edit">The object beeing edited</param>
        /// <returns>The DB object</returns>
        public static OpResult<bool> DboEditToDbStatic(TDboEdit res, TDbObject edit)
        {
            var obj = new TThis();
            return obj.DboEditToDb(res, edit);
        }

        /// <summary>
        /// Get a single DB object matching the predicate
        /// </summary>
        /// <param name="table">The table to search in</param>
        /// <param name="predicate">The predicate</param>
        /// <returns>The object that match</returns>
        public static OpResult<TDbObject> GetSingleDbObject(DbSet<TDbObject> table, Expression<Func<TDbObject, bool>> predicate)
        {
            var o = table.Where(GetGlobalSelectPredicateStatic()).FirstOrDefault(predicate);
            if (o == null)
            {
                return OpResult<TDbObject>.Error(ResultStatus.NotFound, typeof(TThis).Name + ": Value not found", "");
            }
            return OpResult<TDbObject>.Ok(o);
        }

        /// <summary>
        /// Get a single DBO matching the predicate
        /// </summary>
        /// <param name="predicate">The predicate</param>
        /// <returns>The object that match, or null</returns>
        public static OpResult<TDboGet> GetSingle(Expression<Func<TDbObject, bool>> predicate)
        {
            return Execute((db, table) =>
            {
                var obj = GetSingleDbObject(table, predicate);
                if (!obj)
                    return obj.To<TDboGet>();
                return OpResult<TDboGet>.Ok(DbToDboGetStatic(obj.Data));
            });
        }

        /// <summary>
        /// Get an object by its primary key(s)
        /// </summary>
        /// <param name="keys">The key names and values</param>
        /// <returns>The object</returns>
        public static OpResult<TDboGet> GetSingleByKeys(params KeyValuePair<string, object>[] keys)
        {
            return GetSingle(GetExpression(keys));
        }

        /// <summary>
        /// Get an object by its id
        /// </summary>
        /// <param name="id">The id of the object</param>
        /// <returns>The object</returns>
        public static OpResult<TDboGet> GetSingleById(long id)
        {
            return GetSingleByKeys(new KeyValuePair<string, object>("id", id));
        }

        /// <summary>
        /// Get all DB object matching the predicate
        /// </summary>
        /// <param name="predicate">The predicate</param>
        /// <param name="orderBy">The order by function</param>
        /// <param name="page">The page numeber (0 based)</param>
        /// <param name="perPage">The maximum number of items par page</param>
        /// <returns>All matching objects</returns>
        public static OpResult<DboGetMultiple<TDboGet>> GetMultiple<TKey>(Expression<Func<TDbObject, bool>> predicate,
            Expression<Func<TDbObject, TKey>> orderBy, int page = 0, int perPage = Int32.MaxValue)
        {
            return Execute((db, table) =>
            {
                var globalPredicate = GetGlobalSelectPredicateStatic();
                var count = table.Where(globalPredicate).Count(predicate);
                var results = table.OrderBy(orderBy).Where(globalPredicate).Where(predicate)
                    .Skip(page * perPage).Take(perPage).Select(DbToDboGetStatic).ToList();
                var result = new DboGetMultiple<TDboGet>
                {
                    Count = count,
                    Data = results
                };
                return OpResult<DboGetMultiple<TDboGet>>.Ok(result);
            });
        }

        /// <summary>
        /// Get all DB object matching the predicate built from keys
        /// </summary>
        /// <param name="page">The page numeber (0 based)</param>
        /// <param name="perPage">The maximum number of items par page</param>
        /// <param name="orderBy">The order by function</param>
        /// <param name="keys">The key names and values</param>
        /// <returns>All matching objects</returns>
        public static OpResult<DboGetMultiple<TDboGet>> GetMultipleByKeys<TKey>(Expression<Func<TDbObject, TKey>> orderBy,
            int page = 0, int perPage = Int32.MaxValue, params KeyValuePair<string, object>[] keys)
        {
            return GetMultiple(GetExpression(keys), orderBy, page, perPage);
        }

        /// <summary>
        /// Add an entry in the database
        /// </summary>
        /// <param name="obj">The object to be added</param>
        /// <param name="returnFunc">Used to return some data</param>
        /// <returns>The result defined by returnFunc</returns>
        public static OpResult<T> Add<T>(TDboAdd obj, Expression<Func<TDbObject, T>> returnFunc)
        {
            return Execute((db, table) =>
            {
                var add = table.Create();
                var item = DboAddToDbStatic(obj, add);
                if (!item)
                    return item.To<T>();
                table.Add(add);
                db.SaveChanges();
                var res = returnFunc.Compile().DynamicInvoke(add);
                return OpResult<T>.Ok((T)res);
            });
        }

        /// <summary>
        /// Add an entry in the database
        /// </summary>
        /// <param name="obj">The object to be added</param>
        /// <returns>Always true or an OpResult error</returns>
        public static OpResult<bool> Add(TDboAdd obj)
        {
            return Add(obj, x => true);
        }

        /// <summary>
        /// Add an entry in the database
        /// </summary>
        /// <param name="obj">The object to be added</param>
        /// <returns>The DBO or an OpResult error</returns>
        public static OpResult<TDboGet> AddDbo(TDboAdd obj)
        {
            return Add(obj, x => DbToDboGetStatic(x));
        }

        /// <summary>
        /// Add an entry in the database
        /// </summary>
        /// <param name="obj">The object to be added</param>
        /// <returns>The DBO or an OpResult error</returns>
        public static OpResult<long> AddId(TDboAdd obj)
        {
            var param = Expression.Parameter(typeof(TDbObject), "x");
            var exp = Expression.Property(param, "id");
            var lambda = Expression.Lambda<Func<TDbObject, long>>(exp, param);
            return Add(obj, lambda);
        }

        /// <summary>
        /// Edit an entry in the database
        /// </summary>
        /// <param name="obj">The object to be added</param>
        /// <param name="predicate">The predicate to be used to select data</param>
        /// <returns>Always true or an OpResult error</returns>
        public static OpResult<bool> Edit(TDboEdit obj, Expression<Func<TDbObject, bool>> predicate)
        {
            return Execute((db, table) =>
            {
                var edit = GetSingleDbObject(table, predicate);
                if (!edit)
                    return edit.To<bool>();
                var res = DboEditToDbStatic(obj, edit.Data);
                if (!res)
                    return res.To<bool>();
                db.Entry(edit.Data).CurrentValues.SetValues(edit.Data);
                db.SaveChanges();
                return OpResult<bool>.Ok(true);
            });
        }

        /// <summary>
        /// Edit an entry in the database
        /// </summary>
        /// <param name="obj">The object to be added</param>
        /// <param name="keys">The keys to be used to select data</param>
        /// <returns>Always true or an OpResult error</returns>
        public static OpResult<bool> Edit(TDboEdit obj, params KeyValuePair<string, object>[] keys)
        {
            return Edit(obj, GetExpression(keys));
        }

        /// <summary>
        /// Edit an entry in the database
        /// </summary>
        /// <param name="obj">The object to be added</param>
        /// <param name="id">The id to be edited</param>
        /// <returns>Always true or an OpResult error</returns>
        public static OpResult<bool> EditById(TDboEdit obj, long id)
        {
            return Edit(obj, new KeyValuePair<string, object>("id", id));
        }

        /// <summary>
        /// Delete an entry in the database
        /// </summary>
        /// <param name="predicate">The predicate to be used to delete data</param>
        /// <returns>Always true or an OpResult error</returns>
        public static OpResult<bool> Delete(Expression<Func<TDbObject, bool>> predicate)
        {
            return Execute((db, table) =>
            {
                var del = GetSingleDbObject(table, predicate);
                if (!del)
                    return del.To<bool>();
                table.Remove(del.Data);
                db.SaveChanges();
                return OpResult<bool>.Ok(true);
            });
        }

        /// <summary>
        /// Delete an entry in the database
        /// </summary>
        /// <param name="keys">The keys to be used to delete data</param>
        /// <returns>Always true or an OpResult error</returns>
        public static OpResult<bool> Delete(params KeyValuePair<string, object>[] keys)
        {
            return Delete(GetExpression(keys));
        }

        /// <summary>
        /// Delete an entry in the database
        /// </summary>
        /// <param name="id">The id to be deleted</param>
        /// <returns>Always true or an OpResult error</returns>
        public static OpResult<bool> DeleteById(long id)
        {
            return Delete(new KeyValuePair<string, object>("id", id));
        }
    }
}