EntityFramework6写的数据访问框架之二BaseDAL

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using LinqKit;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections;

namespace MyTest
{
    public abstract class BaseDAL
    {
        public BaseDAL()
        {
        }
        public abstract BaseDb<T> GetDbContext<T>() where T : class;

        #region 通用增删改查
        #region 非原始sql语句方式
        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="entity">实体</param>
        /// <returns>返回受影响行数</returns>
        protected bool Add<T>(T entity) where T : class
        {
            using (BaseDb<T> db = this.GetDbContext<T>())
            {
                db.Entry<T>(entity).State = EntityState.Added;
                return db.SaveChanges() > 0;
            }
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="entity">实体</param>
        /// <returns>返回受影响行数</returns>
        protected bool Update<T>(T entity) where T : class
        {
            using (BaseDb<T> db = this.GetDbContext<T>())
            {
                db.Set<T>().Attach(entity);
                db.Entry<T>(entity).State = EntityState.Modified;
                return db.SaveChanges() > 0;
            }
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="entity">实体</param>
        /// <returns>返回受影响行数</returns>
        protected bool Delete<T>(T entity) where T : class
        {
            using (BaseDb<T> db = this.GetDbContext<T>())
            {
                db.Set<T>().Attach(entity);
                db.Entry<T>(entity).State = EntityState.Deleted;
                return db.SaveChanges() > 0;
            }
        }

        /// <summary>
        /// 根据条件删除
        /// </summary>
        /// <param name="deleWhere">删除条件</param>
        /// <returns>返回受影响行数</returns>
        protected bool DeleteByConditon<T>(Expression<Func<T, bool>> deleWhere) where T : class
        {
            using (BaseDb<T> db = this.GetDbContext<T>())
            {
                List<T> entitys = db.Set<T>().Where(deleWhere).ToList();
                entitys.ForEach(m => db.Entry<T>(m).State = EntityState.Deleted);
                return db.SaveChanges() > 0;
            }
        }

        /// <summary>
        /// 查找单个
        /// </summary>
        /// <param name="id">主键</param>
        /// <returns></returns>
        protected T GetSingleById<T>(int id) where T : class
        {
            using (BaseDb<T> db = this.GetDbContext<T>())
            {
                return db.Set<T>().Find(id);
            }
        }

        /// <summary>
        /// 查找单个
        /// </summary>
        /// <param name="seleWhere">查询条件</param>
        /// <returns></returns>
        protected T GetSingle<T>(Expression<Func<T, bool>> seleWhere) where T : class
        {
            using (BaseDb<T> db = this.GetDbContext<T>())
            {
                return db.Set<T>().AsExpandable().FirstOrDefault(seleWhere);
            }
        }

        /// <summary>
        /// 获取所有实体集合
        /// </summary>
        /// <returns></returns>
        protected List<T> GetAll<T>() where T : class
        {
            using (BaseDb<T> db = this.GetDbContext<T>())
            {
                return db.Set<T>().AsExpandable().ToList<T>();
            }
        }

        /// <summary>
        /// 获取所有实体集合(单个排序)
        /// </summary>
        /// <returns></returns>
        protected List<T> GetAll<T, Tkey>(Expression<Func<T, Tkey>> orderWhere, bool isDesc) where T : class
        {
            using (BaseDb<T> db = this.GetDbContext<T>())
            {
                return CommonSort(db.Set<T>().AsExpandable(), orderWhere, isDesc).ToList<T>();
            }
        }

        /// <summary>
        /// 获取所有实体集合(多个排序)
        /// </summary>
        /// <returns></returns>
        protected List<T> GetAll<T>(params OrderModelField[] orderByExpression) where T : class
        {
            using (BaseDb<T> db = this.GetDbContext<T>())
            {
                return CommonSort(db.Set<T>().AsExpandable(), orderByExpression).ToList();
            }
        }

        /// <summary>
        /// 单个排序通用方法
        /// </summary>
        /// <typeparam name="Tkey">排序字段</typeparam>
        /// <param name="data">要排序的数据</param>
        /// <param name="orderWhere">排序条件</param>
        /// <param name="isDesc">是否倒序</param>
        /// <returns>排序后的集合</returns>
        protected IQueryable<T> CommonSort<T, Tkey>(IQueryable<T> data, Expression<Func<T, Tkey>> orderWhere, bool isDesc) where T : class
        {
            if (isDesc)
            {
                return data.OrderByDescending(orderWhere);
            }
            else
            {
                return data.OrderBy(orderWhere);
            }
        }

        /// <summary>
        /// 多个排序通用方法
        /// </summary>
        /// <typeparam name="Tkey">排序字段</typeparam>
        /// <param name="data">要排序的数据</param>
        /// <param name="orderWhereAndIsDesc">字典集合(排序条件,是否倒序)</param>
        /// <returns>排序后的集合</returns>
        protected IQueryable<T> CommonSort<T>(IQueryable<T> data, params OrderModelField[] orderByExpression) where T : class
        {
            //创建表达式变量参数
            var parameter = Expression.Parameter(typeof(T), "o");

            if (orderByExpression != null && orderByExpression.Length > 0)
            {
                for (int i = 0; i < orderByExpression.Length; i++)
                {
                    //根据属性名获取属性
                    var property = typeof(T).GetProperty(orderByExpression[i].PropertyName);
                    //创建一个访问属性的表达式
                    var propertyAccess = Expression.MakeMemberAccess(parameter, property);
                    var orderByExp = Expression.Lambda(propertyAccess, parameter);

                    string OrderName = "";
                    if (i > 0)
                    {
                        OrderName = orderByExpression[i].IsDESC ? "ThenByDescending" : "ThenBy";
                    }
                    else
                        OrderName = orderByExpression[i].IsDESC ? "OrderByDescending" : "OrderBy";

                    MethodCallExpression resultExp = Expression.Call(typeof(Queryable), OrderName, new Type[] { typeof(T), property.PropertyType },
                  data.Expression, Expression.Quote(orderByExp));

                    data = data.Provider.CreateQuery<T>(resultExp);
                }
            }
            return data;
        }

        /// <summary>
        /// 根据条件查询实体集合
        /// </summary>
        /// <param name="seleWhere">查询条件 lambel表达式</param>
        /// <returns></returns>
        protected List<T> GetList<T>(Expression<Func<T, bool>> seleWhere) where T : class
        {
            using (BaseDb<T> db = this.GetDbContext<T>())
            {
                return db.Set<T>().AsExpandable().Where(seleWhere).ToList();
            }
        }

        /// <summary>
        /// 根据条件查询实体集合
        /// </summary>
        /// <param name="seleWhere">查询条件 lambel表达式</param>
        /// <returns></returns>
        protected List<T> GetList<T, TValue>(Expression<Func<T, TValue>> seleWhere, IEnumerable<TValue> conditions) where T : class
        {
            using (BaseDb<T> db = this.GetDbContext<T>())
            {

                return db.Set<T>().AsExpandable().WhereIn<T, TValue>(seleWhere, conditions).ToList();
            }
        }

        /// <summary>
        /// 根据条件查询实体集合(单个字段排序)
        /// </summary>
        /// <param name="seleWhere">查询条件 lambel表达式</param>
        /// <returns></returns>
        protected List<T> GetList<T, Tkey>(Expression<Func<T, bool>> seleWhere, Expression<Func<T, Tkey>> orderWhere, bool isDesc) where T : class
        {
            using (BaseDb<T> db = this.GetDbContext<T>())
            {
                return CommonSort(db.Set<T>().AsExpandable().Where(seleWhere), orderWhere, isDesc).ToList();
            }
        }

        /// <summary>
        /// 根据条件查询实体集合(多个字段排序)
        /// </summary>
        /// <param name="seleWhere">查询条件 lambel表达式</param>
        /// <returns></returns>
        protected List<T> GetList<T>(Expression<Func<T, bool>> seleWhere, params OrderModelField[] orderByExpression) where T : class
        {
            using (BaseDb<T> db = this.GetDbContext<T>())
            {
                return CommonSort(db.Set<T>().AsExpandable().Where(seleWhere), orderByExpression).ToList();
            }
        }

        /// <summary>
        /// 获取分页集合(无条件无排序)
        /// </summary>
        /// <returns></returns>
        protected List<T> GetListPaged<T, Tkey>(int pageIndex, int pageSize, out int totalcount) where T : class
        {
            using (BaseDb<T> db = this.GetDbContext<T>())
            {
                totalcount = db.Set<T>().AsExpandable().Count();//获取总数
                //需要增加AsExpandable(),否则查询的是所有数据到内存，然后再排序  AsExpandable是linqkit.dll中的方法
                return db.Set<T>().AsExpandable().Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            }
        }

        /// <summary>
        /// 获取分页集合(无条件单个排序)
        /// </summary>
        /// <returns></returns>
        protected List<T> GetListPaged<T, Tkey>(int pageIndex, int pageSize, Expression<Func<T, Tkey>> orderWhere, bool isDesc, out int totalcount) where T : class
        {
            using (BaseDb<T> db = this.GetDbContext<T>())
            {
                totalcount = db.Set<T>().AsExpandable().Count();//获取总数
                //需要增加AsExpandable(),否则查询的是所有数据到内存，然后再排序  AsExpandable是linqkit.dll中的方法
                return CommonSort(db.Set<T>().AsExpandable(), orderWhere, isDesc).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            }
        }

        /// <summary>
        /// 获取分页集合(无条件多字段排序)
        /// </summary>
        /// <returns></returns>
        protected List<T> GetListPaged<T>(int pageIndex, int pageSize, out int totalcount, params OrderModelField[] orderByExpression) where T : class
        {
            using (BaseDb<T> db = this.GetDbContext<T>())
            {
                totalcount = db.Set<T>().AsExpandable().Count();//获取总数
                //需要增加AsExpandable(),否则查询的是所有数据到内存，然后再排序  AsExpandable是linqkit.dll中的方法
                return CommonSort(db.Set<T>().AsExpandable(), orderByExpression).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            }
        }

        /// <summary>
        /// 获取分页集合(有条件无排序)
        /// </summary>
        /// <returns></returns>
        protected List<T> GetListPaged<T, Tkey>(int pageIndex, int pageSize, Expression<Func<T, bool>> seleWhere, out int totalcount) where T : class
        {
            using (BaseDb<T> db = this.GetDbContext<T>())
            {
                totalcount = db.Set<T>().AsExpandable().Where(seleWhere).Count();//获取总数
                //需要增加AsExpandable(),否则查询的是所有数据到内存，然后再排序  AsExpandable是linqkit.dll中的方法
                return db.Set<T>().AsExpandable().Where(seleWhere).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            }
        }

        /// <summary>
        /// 获取分页集合(有条件单个排序)
        /// </summary>
        /// <returns></returns>
        protected List<T> GetListPaged<T, Tkey>(int pageIndex, int pageSize, Expression<Func<T, bool>> seleWhere,
            Expression<Func<T, Tkey>> orderWhere, bool isDesc, out int totalcount) where T : class
        {
            using (BaseDb<T> db = this.GetDbContext<T>())
            {
                totalcount = db.Set<T>().AsExpandable().Where(seleWhere).Count();//获取总数
                //需要增加AsExpandable(),否则查询的是所有数据到内存，然后再排序  AsExpandable是linqkit.dll中的方法
                return CommonSort(db.Set<T>().AsExpandable().Where(seleWhere), orderWhere, isDesc).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            }
        }

        /// <summary>
        /// 获取分页集合(有条件多字段排序)
        /// </summary>
        /// <returns></returns>
        protected List<T> GetListPaged<T>(int pageIndex, int pageSize, Expression<Func<T, bool>> seleWhere,
            out int totalcount, params OrderModelField[] orderModelFiled) where T : class
        {
            using (BaseDb<T> db = this.GetDbContext<T>())
            {
                totalcount = db.Set<T>().AsExpandable().Where(seleWhere).Count();//获取总数
                //需要增加AsExpandable(),否则查询的是所有数据到内存，然后再排序  AsExpandable是linqkit.dll中的方法
                return CommonSort(db.Set<T>().AsExpandable().Where(seleWhere), orderModelFiled).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            }
        }
        #endregion

        #endregion
        #region sql查询
        protected int ProcedureQuery<T>(string sp, params SqlParameter[] parameters) where T : class
        {
            using (var context = this.GetDbContext<T>())
            {
                using (context.Database.Connection)
                {
                    context.Database.Connection.Open();
                    var cmd = context.Database.Connection.CreateCommand();
                    cmd.CommandText = sp;
                    cmd.CommandType = CommandType.StoredProcedure;
                    if (parameters != null && parameters.Count() > 0)
                    {
                        cmd.Parameters.AddRange(parameters);
                    }
                    return cmd.ExecuteNonQuery();
                }
            }
        }
        protected object ProcedureToScalar<T>(string sql, params SqlParameter[] parameters) where T : class
        {
            using (var context = this.GetDbContext<T>())
            {
                using (context.Database.Connection)
                {
                    context.Database.Connection.Open();
                    var cmd = context.Database.Connection.CreateCommand();
                    cmd.CommandText = sql;
                    cmd.CommandType = CommandType.StoredProcedure;
                    if (parameters != null && parameters.Count() > 0)
                    {
                        cmd.Parameters.AddRange(parameters);
                    }
                    return cmd.ExecuteScalar();
                }
            }
        }

        /// <summary>
        /// 执行存储过程 带返回总数以及结果集
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected DataSet ProcedureDataSet<T>(string storedProcName, params IDataParameter[] parameters) where T : class
        {
            using (var context = this.GetDbContext<T>())
            {
                using (context.Database.Connection)
                {
                    context.Database.Connection.Open();
                    var cmd = context.Database.Connection.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = storedProcName;
                    cmd.CommandTimeout = 60 * 5;
                    if (parameters != null)
                    {
                        foreach (SqlParameter parameter in parameters)
                        {
                            if (parameter != null)
                            {
                                // 检查未分配值的输出参数,将其分配以DBNull.Value.
                                if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                                    (parameter.Value == null))
                                {
                                    parameter.Value = DBNull.Value;
                                }
                                cmd.Parameters.Add(parameter);
                            }
                        }
                    }
                    SqlDataAdapter sdap = new SqlDataAdapter();
                    sdap.SelectCommand = (SqlCommand)cmd;
                    DataSet set = new DataSet();
                    sdap.Fill(set);
                    return set;
                }
            }
        }
        protected List<T> ProcedureToList<T>(string sql, params SqlParameter[] parameters) where T : class,new()
        {
            using (var context = this.GetDbContext<T>())
            {
                using (context.Database.Connection)
                {
                    context.Database.Connection.Open();
                    var cmd = context.Database.Connection.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = sql;
                    if (parameters != null && parameters.Count() > 0)
                    {
                        cmd.Parameters.AddRange(parameters);
                    }
                    SqlDataAdapter sdap = new SqlDataAdapter();
                    sdap.SelectCommand = cmd as SqlCommand;
                    DataTable dt = new DataTable();
                    sdap.Fill(dt);
                    return dt.ToList<T>();
                }
            }
        }

        /// <summary>
        /// 执行SQL语句,返回执行结果（int）
        /// </summary>
        /// <typeparam name="TElement"></typeparam>
        /// <param name="sp"></param>
        /// <param name="parameters">new SqlParameter() </param>
        /// <returns></returns>
        protected int ExecuteSql<T>(string strsql) where T : class
        {
            SqlParameter[] parameters = new SqlParameter[0];
            return ExecuteSql<T>(strsql, parameters);

        }
        /// <summary>
        /// 执行SQL语句,返回执行结果（int）
        /// </summary>
        /// <typeparam name="TElement"></typeparam>
        /// <param name="sp"></param>
        /// <param name="parameters">new SqlParameter() </param>
        /// <returns></returns>
        protected int ExecuteSql<T>(string strsql, params SqlParameter[] parameters) where T : class
        {
            using (var context = this.GetDbContext<T>())
            {
                using (context.Database.Connection)
                {
                    context.Database.Connection.Open();
                    using (var cmd = context.Database.Connection.CreateCommand())
                    {
                        cmd.CommandText = strsql;
                        cmd.CommandType = CommandType.Text;
                        if (parameters != null && parameters.Count() > 0)
                        {
                            cmd.Parameters.AddRange(parameters);
                        }
                        return cmd.ExecuteNonQuery();
                    }
                }
            }
        }
        protected object ExecuteSqlScalar<T>(string sql, params SqlParameter[] parameters) where T : class
        {
            using (var context = this.GetDbContext<T>())
            {
                using (context.Database.Connection)
                {
                    context.Database.Connection.Open();
                    var cmd = context.Database.Connection.CreateCommand();
                    cmd.CommandText = sql;
                    cmd.CommandType = CommandType.Text;
                    if (parameters != null && parameters.Count() > 0)
                    {
                        cmd.Parameters.AddRange(parameters);
                    }
                    return cmd.ExecuteScalar();
                }
            }
        }
        protected DataTable SQLQuery<T>(string sql) where T : class
        {
            using (var context = this.GetDbContext<T>())
            {
                using (context.Database.Connection)
                {
                    context.Database.Connection.Open();
                    var cmd = context.Database.Connection.CreateCommand();
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sql;
                    SqlDataAdapter sdap = new SqlDataAdapter();
                    sdap.SelectCommand = cmd as SqlCommand;
                    DataTable dt = new DataTable();
                    sdap.Fill(dt);
                    return dt;
                }
            }
        }

        protected DataTable SQLQuery<T>(string sql, params SqlParameter[] parameters) where T : class
        {
            using (var context = this.GetDbContext<T>())
            {
                using (context.Database.Connection)
                {
                    context.Database.Connection.Open();
                    var cmd = context.Database.Connection.CreateCommand();
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sql;
                    if (parameters != null && parameters.Count() > 0)
                    {
                        cmd.Parameters.AddRange(parameters);
                    }
                    SqlDataAdapter sdap = new SqlDataAdapter();
                    sdap.SelectCommand = cmd as SqlCommand;
                    DataTable dt = new DataTable();
                    sdap.Fill(dt);
                    return dt;
                }
            }
        }
        #endregion
        protected DataSet ManySQLQuery<T>(ArrayList arrayList) where T : class
        {
            using (var context = this.GetDbContext<T>())
            {
                using (context.Database.Connection)
                {
                    context.Database.Connection.Open();
                    var cmd = context.Database.Connection.CreateCommand();
                    SqlDataAdapter sdap = new SqlDataAdapter();
                    string table = "table";
                    DataSet set = new DataSet();
                    for (int i = 0; i < arrayList.Count; ++i)
                    {
                        cmd.CommandText = arrayList[i].ToString();
                        sdap.SelectCommand = (SqlCommand)cmd;
                        sdap.Fill(set, table + i);
                    }
                    return set;
                }
            }
        }
        private string FormatSqlString(object obj)
        {
            if (obj.GetType() == typeof(System.Boolean))
            {
                return ((bool)obj ? "1" : "0");
            }
            if (obj.GetType() == typeof(System.Guid))
            {
                return "'" + obj.ToString() + "'";
            }
            if (obj.GetType() == typeof(System.Int16) || obj.GetType() == typeof(System.Int32) || obj.GetType() == typeof(System.Int64) || obj.GetType() == typeof(System.Decimal) || obj.GetType() == typeof(System.Double))
            {
                return obj.ToString();
            }
            return "'" + obj.ToString() + "'";
        }



    }
    public struct OrderModelField
    {

        public bool IsDESC { get; set; }
        public string PropertyName { get; set; }
    }
}