EntityFrameWork 5.0数据访问层封装

稍作整理, 留以后备用

1.数据访问层DAL 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Linq.Expressions;
using System.Data.Entity;

using System.Data.Linq;
using System.Data.Objects;
using System.Reflection;
using System.Threading;
using DiaryNodeApp.Model;

namespace DiaryNodeApp.DAL
{
    public class SysDbDAL: DbContext
    {
        DbContext entities = null;
        public SysDbDAL()
        {
            entities = new DiaryNodeDBEntities();
        }
        public SysDbDAL(string nameOrConnectionString)
        {
            //"name=DiaryNodeDBEntities"
            entities = new DbContext(nameOrConnectionString);
        }


        #region 根据SQL语句查询数据
        public IEnumerable ExecuteQuery( string query, params object[] parms)
        {
            try
            {
               return entities.Database.SqlQuery(query, parms);
               
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        public IEnumerable ExecuteQuery(string query)
        {
            return ExecuteQuery(string.Empty, query, new object[] { });
        }
        #endregion

        #region 执行操作类型SQl语句
        /// 

        /// 执行SQL命令
        /// 

        /// 
        /// 
        public int ExecuteSqlCommand(string sqlCommand, params object[]parms)
        {
            return entities.Database.ExecuteSqlCommand(sqlCommand, parms);
        }
        /// 

        /// 执行SQL命令
        /// 

        /// 
        /// 
        public void ExecuteSqlCommand(string sqlCommand)
        {
            ExecuteSqlCommand(sqlCommand);
        }
        #endregion


        #region 私有方法
        private DbSet GetTable() where T : class
        {
            try
            {
               
                DbSet customers = entities.Set();
                return customers;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        #endregion

        #region 统计指定条件的数据量
        /// 

        /// 统计指定条件的数据量
        /// 

        /// 
        /// 
        /// 
        public virtual int Count(Expression> query) where T : class
        {
            var table = GetTable();
            return (from t in table
                    select t).Where(query).Count();
        }
        #endregion


        #region 获取单个实体
        /// 

        /// 获取单个实体
        /// 

        /// 泛型类型参数
        /// 查询条件
        /// 
        public virtual T GetSingleEntity(Expression> query) where T : class
        {
            try
            {
                var table = GetTable();
                return (from t in table
                        select t).Where(query).FirstOrDefault();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion


        #region 更新实体
        /// 

        /// 更新实体
        /// 

        /// 泛型类型参数
        /// 更改后的实体
        public virtual bool UpdateEntitys(T entity) where T : class
        {
            object propertyValue = null;
            T entityFromDB = GetSingleEntity(s => s == entity);
            if (null == entityFromDB)
                return false;
            PropertyInfo[] properties = entityFromDB.GetType().GetProperties();
            foreach (PropertyInfo property in properties)
            {
                propertyValue = null;
                if (null != property.GetSetMethod())
                {
                    PropertyInfo entityProperty =
                          entity.GetType().GetProperty(property.Name);
                    if (entityProperty.PropertyType.BaseType ==
                        Type.GetType("System.ValueType") ||
                        entityProperty.PropertyType ==
                        Type.GetType("System.String"))

                        propertyValue = entity.GetType().GetProperty(property.Name).GetValue(entity, null);
                    if (propertyValue == null)
                    {
                        Thread.Sleep(50);
                        propertyValue = entity.GetType().GetProperty(property.Name).GetValue(entity, null);
                    }
                    if (null != propertyValue)
                        property.SetValue(entityFromDB, propertyValue, null);
                }
            }

            entities.SaveChanges();
            return true;
        }

        #endregion


        #region 获取相关的实体信息
        /// 

        /// 分页_获取指定页的数据集合
        /// 

        /// 泛型类型参数
        /// 查询条件
        /// 每页显示数量
        /// 当前页号
        /// 总页数
        /// 总数据量
        /// 
        public virtual List GetAllEntity(Expression> query, int pageSize, int pageNum, out int pageTotal, out int datasTotal) where T : class
        {
            var table = GetTable();

            datasTotal = (from t in table
                          select t).Where(query).Count();

            pageTotal = datasTotal / pageSize + 1;

            return (from t in table
                    select t).Where(query).Skip(pageSize * (pageNum - 1)).Take(pageSize).ToList();
        }
        /// 

        /// 分页_获取指定页的数据集合
        /// 

        /// 泛型类型参数
        /// 查询条件
        /// 每页显示数量
        /// 当前页号
        /// 总页数
        /// 总数据量
        /// 
        public virtual List GetAllEntity(Expression> query, Func orderByDesc, int pageSize, int pageNum, out int pageTotal, out int datasTotal) where T : class
        {
            var table = GetTable();

            datasTotal = (from t in table
                          select t).Where(query).Count();

            pageTotal = (int)Math.Ceiling((double)datasTotal / pageSize);
            return (from t in table
                    select t).Where(query).OrderByDescending(orderByDesc).Skip(pageSize * (pageNum - 1)).Take(pageSize).ToList();
        }
        /// 

        /// 获取指定条件的实体集合

        /// 

        /// 泛型类型参数
        /// 查询条件
        /// 
        public virtual List GetAllEntity(Expression> query) where T : class
        {
            var table = GetTable();
            return (from t in table
                    select t).Where(query).ToList();
        }

        /// 

        /// 获取指定条件的实体集合

        /// 

        /// 泛型类型参数
        /// 查询条件      
        ///        
        /// 
        public virtual List GetAllEntity(Expression> query, bool isAsc, Func order) where T : class
        {
            var table = GetTable();
            if (isAsc)
                return (from t in table
                        select t).Where(query).OrderBy(order).ToList();
            return (from t in table
                    select t).Where(query).OrderByDescending(order).ToList();
        }

        public virtual List GetAllEntity() where T : class
        {
            var table = GetTable();
            return (from t in table
                    select t).ToList();
        }
        #endregion


        #region 新增实体
        /// 

        /// 新增单个实体
        /// 

        /// 泛型类型参数
        /// 待插入的实体
        /// 
        public virtual void InsertEntitys(T entity) where T : class
        {
            var table = GetTable();

            table.Add(entity);
           
            entities.SaveChanges();
        }

        /// 

        /// 批量新增实体
        /// 

        /// 泛型类型参数
        /// 待添加的实体集合
        /// 
        public virtual void BatchInsertEntity(List entityList) where T : class
        {
            if (entityList.Count > 0)
            {
                var table = GetTable();
                foreach (var item in entityList)
                {
                    table.Add(item);
                   
                }
                entities.SaveChanges();
            }
        }
        #endregion

        #region 删除实体
        /// 

        /// 根据条件删除指定实体
        /// 

        /// 实体
        /// 条件
        /// bool
        public virtual void DeleteEntitys(Expression> query) where T : class
        {
            var table = GetTable();
            var toDeletedColl = table.Where(query);
            if (toDeletedColl != null && toDeletedColl.Count() > 0)
            {
                foreach (var item in toDeletedColl)
                {
                    table.Remove(item);
                   
                }
                entities.SaveChanges();
            }
        }
        #endregion
    }
}

View Code

 

2.业务逻辑BLL

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DiaryNodeApp.Model;
using DiaryNodeApp.DAL;
using System.Linq.Expressions;

namespace DiaryNodeApp.BLL
{
    public class SysDbBLL
    {
        private SysDbDAL dal { get; set; }
        public SysDbBLL()
        {
            dal = new SysDbDAL();
        }
        public SysDbBLL(string entityName)
        {
            dal = new SysDbDAL(entityName);
        }
        
        public bool Insert(T entity) where T : class
        {
            dal.InsertEntitys(entity);
            return true;
        }

        public bool Delete(Expression> query) where T : class
        {
            dal.DeleteEntitys(query);
            return true;
        }

        public bool Update(T entity) where T : class
        {
            dal.UpdateEntitys(entity);
            return true;
        }
        public T GetSingle(Expression> query) where T : class
        {
            return dal.GetSingleEntity(query);
          
        }

        public List GetAll(Expression> query) where T : class
        {
            return dal.GetAllEntity(query);
          
        }
    }
}

View Code