SqlSugarDBHelper

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using SqlSugar;
using System.Configuration;
using System.Linq.Expressions;
using System.Data;
using Newtonsoft.Json;

namespace CYSoft.WebMain_X2.Areas.Tools.SqlSugarTools
{
    public sealed class SqlSugarDBHelper
    {
        public static SqlSugarClient GetContext(bool isWithNoLockQuery = true)
        {
            var moreSettings = new ConnMoreSettings();
            moreSettings.IsAutoRemoveDataCache = true;
            moreSettings.IsWithNoLockQuery = isWithNoLockQuery;
            var context = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = ConfigurationManager.ConnectionStrings["DefaultDBConnectionString"].ToString(),
                DbType = SqlSugar.DbType.SqlServer,
                IsAutoCloseConnection = false,
                InitKeyType = InitKeyType.SystemTable,
                MoreSettings = moreSettings
            });
            return context;
        }

        /// <summary>
        /// 对单表单条记录新增或更新
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="entity">实体</param>
        /// <param name="key">主键字段值</param>
        /// <returns></returns>
        public static bool AddOrUpdateEntity<T>(T entity, string keyValue) where T : class, new()
        {
            using (var Db = GetContext())
            {
                var model = Db.Queryable<T>().InSingle(keyValue);
                if (model != null)
                {
                    return Db.Updateable(entity).ExecuteCommand() > 0;
                }
                return Db.Insertable(entity).ExecuteCommand() > 0;
            }

        }

        /// <summary>
        /// 对单表多条记录进行新增或更新
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entities"></param>
        /// <param name="keyName">主键字段名</param>
        /// <returns></returns>
        public static bool AddOrUpdateRange<T>(List<T> entities, string keyName) where T : class, new()
        {
            if (entities != null && entities.Any())
            {
                using (var Db = GetContext())
                {
                    var updateList = new List<T>();
                    var insertList = new List<T>();

                    //start--获取传入的子表的主键值集合，根据此集合从数据库中拉取子表数据，若subEntities包含取出的数据为要更新的子表数据，否则为要新增的子表数据
                    List<string> keyValue = new List<string>();

                    //keyValue = entities.Select(p => p.GetType().GetProperty(keyName).GetValue(p).ToString()).ToList();

                    foreach (var item in entities)
                    {
                        keyValue.Add(item.GetType().GetProperty(keyName).GetValue(item, null).ToString());
                    }
                    var models = Db.Queryable<T>().In(keyName, keyValue).Select(keyName).ToList();
                    if (models != null)
                    {
                        updateList.AddRange(entities.Where(p => models.Select(g => g.GetType().GetProperty(keyName).GetValue(g, null)).Contains(p.GetType().GetProperty(keyName).GetValue(p, null).ToString())));
                        insertList.AddRange(entities.Where(p => !models.Select(g => g.GetType().GetProperty(keyName).GetValue(g, null)).Contains(p.GetType().GetProperty(keyName).GetValue(p, null).ToString())));
                    }
                    else
                    {
                        insertList.AddRange(entities);
                    }
                    //end

                    var result = Db.Ado.UseTran(() =>
                    {
                        if (insertList.Count > 0)
                            Db.Insertable(insertList.ToArray()).ExecuteCommand();
                        if (updateList.Count > 0)
                            Db.Updateable(updateList).ExecuteCommand();
                    });
                    return result.Data;
                }
            }
            return false;
        }

        /// <summary>
        /// 对主从表进行新增或修改
        /// </summary>
        /// <typeparam name="T1">主表类型</typeparam>
        /// <typeparam name="T2">子表类型</typeparam>
        /// <param name="superEntity">主表实体集合</param>
        /// <param name="superKeyValue">主表主键值</param>
        /// <param name="subEntities">子表实体集合</param>
        /// <param name="subKeyName">子表主键名</param>
        /// <returns></returns>
        public static bool AddOrUpdateSuperAndSubEntities<T1, T2>(List<T1> superEntity, string superKeyValue, List<T2> subEntities, string subKeyName)
            where T1 : class, new()
            where T2 : class, new()
        {
            using (var Db = GetContext())
            {
                //根据主表主键从数据库拉取数据，包含数据库中取出的数据加入updateListMain，否则加入insertListMain
                var updateListMain = new List<T1>();
                var insertListMain = new List<T1>();
                List<string> mainkeyValue = new List<string>();
                foreach (var item in superEntity)
                {
                    mainkeyValue.Add(item.GetType().GetProperty(superKeyValue).GetValue(item, null).ToString());
                }
                var mainModels = Db.Queryable<T2>().In(superKeyValue, mainkeyValue).Select(superKeyValue).ToList();
                updateListMain.AddRange(superEntity.Where(p => mainModels.Select(g => g.GetType().GetProperty(superKeyValue).GetValue(g, null)).Contains(p.GetType().GetProperty(superKeyValue).GetValue(p, null).ToString())));
                insertListMain.AddRange(superEntity.Where(p => !mainModels.Select(g => g.GetType().GetProperty(superKeyValue).GetValue(g, null)).Contains(p.GetType().GetProperty(superKeyValue).GetValue(p, null).ToString())));
                //主表结束
                //根据子表主键从数据库拉取数据，包含数据库中取出的数据加入updateList，否则加入insertList
                var updateList = new List<T2>();
                var insertList = new List<T2>();
                List<string> keyValue = new List<string>();
                foreach (var item in subEntities)
                {
                    keyValue.Add(item.GetType().GetProperty(subKeyName).GetValue(item, null).ToString());
                }
                var models = Db.Queryable<T2>().In(subKeyName, keyValue).Select(subKeyName).ToList();
                if (models != null)
                {
                    updateList.AddRange(subEntities.Where(p => models.Select(g => g.GetType().GetProperty(subKeyName).GetValue(g, null)).Contains(p.GetType().GetProperty(subKeyName).GetValue(p, null).ToString())));
                    insertList.AddRange(subEntities.Where(p => !models.Select(g => g.GetType().GetProperty(subKeyName).GetValue(g, null)).Contains(p.GetType().GetProperty(subKeyName).GetValue(p, null).ToString())));
                }
                else
                {
                    insertList.AddRange(subEntities);
                }
                //子表结束
                var result = Db.Ado.UseTran(() =>
                {
                    if (updateListMain.Count > 0)
                        Db.Updateable(updateListMain).ExecuteCommand();
                    if (insertListMain.Count > 0)
                        Db.Insertable(insertListMain.ToArray()).ExecuteCommand();
                    if (updateList.Count > 0)
                        Db.Updateable(updateList).ExecuteCommand();
                    if (insertList.Count > 0)
                        Db.Insertable(insertList.ToArray()).ExecuteCommand();

                });
                return result.Data;
            }
        }
        /// <summary>
        /// 对主从表进行新增或修改
        /// </summary>
        /// <typeparam name="T1">主表类型</typeparam>
        /// <typeparam name="T2">子表类型</typeparam>
        /// <param name="superEntity">主表实体</param>
        /// <param name="superKeyValue">主表主键值</param>
        /// <param name="subEntities">子表实体集合</param>
        /// <param name="subKeyName">子表主键名</param>
        /// <returns></returns>
        public static bool AddOrUpdateSuperAndSub<T1, T2>(T1 superEntity, string superKeyValue, List<T2> subEntities, string subKeyName)
            where T1 : class, new()
            where T2 : class, new()
        {
            using (var Db = GetContext())
            {
                //根据主表主键从数据库拉取数据，包含数据库中取出的数据加入updateListMain，否则加入insertListMain
                var mainModel = Db.Queryable<T1>().InSingle(superKeyValue);
                //主表结束
                //根据子表主键从数据库拉取数据，包含数据库中取出的数据加入updateList，否则加入insertList
                var updateList = new List<T2>();
                var insertList = new List<T2>();
                List<string> keyValue = new List<string>();
                foreach (var item in subEntities)
                {
                    keyValue.Add(item.GetType().GetProperty(subKeyName).GetValue(item, null).ToString());
                }
                var models = Db.Queryable<T2>().In(subKeyName, keyValue).Select(subKeyName).ToList();
                if (models != null)
                {
                    updateList.AddRange(subEntities.Where(p => models.Select(g => g.GetType().GetProperty(subKeyName).GetValue(g, null)).Contains(p.GetType().GetProperty(subKeyName).GetValue(p, null).ToString())));
                    insertList.AddRange(subEntities.Where(p => !models.Select(g => g.GetType().GetProperty(subKeyName).GetValue(g, null)).Contains(p.GetType().GetProperty(subKeyName).GetValue(p, null).ToString())));
                }
                else
                {
                    insertList.AddRange(subEntities);
                }
                //子表结束
                var result = Db.Ado.UseTran(() =>
                {
                    if (mainModel != null)
                    {
                        Db.Updateable(superEntity).ExecuteCommand();
                    }
                    else
                    {
                        Db.Insertable(superEntity).ExecuteCommand();

                    }
                    if (updateList.Count > 0)
                        Db.Updateable(updateList).ExecuteCommand();
                    if (insertList.Count > 0)
                        Db.Insertable(insertList.ToArray()).ExecuteCommand();

                });
                return result.Data;
            }
        }
        /// <summary>
        /// 根据主键删除单条记录
        /// </summary>
        /// <param name="keyValue"></param>
        /// <returns></returns>
        public static bool DeleteEntity<T>(string keyValue) where T : class, new()
        {
            using (var Db = GetContext())
            {
                return Db.Deleteable<T>().In(keyValue).ExecuteCommand() > 0;
            }
        }

        /// <summary>
        /// 根据主键删除多条记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="keyValues"></param>
        /// <returns></returns>
        public static bool DeleteEntities<T>(string[] keyValues) where T : class, new()
        {
            using (var Db = GetContext())
            {
                return Db.Deleteable<T>().In(keyValues).ExecuteCommand() > 0;
            }
        }

        /// <summary>
        /// 根据主键删除主从表信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <typeparam name="T1"></typeparam>
        /// <param name="superKey"></param>
        /// <param name="subKeys"></param>
        /// <returns></returns>
        public static bool DeleteSuperAndSubEntities<T, T1>(string[] superKey, string[] subKeys)
            where T : class, new()
            where T1 : class, new()
        {
            using (var Db = GetContext())
            {
                var result = Db.Ado.UseTran(() =>
                {
                    Db.Deleteable<T>().In(superKey).ExecuteCommand();
                    Db.Deleteable<T1>().In(subKeys).ExecuteCommand();
                });
                return result.Data;
            }
        }
        /// <summary>
        /// 根据主键删除主表信息,并根据主表ID删除从表信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <typeparam name="T1"></typeparam>
        /// <param name="superKey"></param>
        /// <param name="mainID">主表主键字段名</param>
        /// <returns></returns>
        public static bool DeleteSuperAndSubEntities<T, T1>(string[] superKey, string superKeyName)
            where T : class, new()
            where T1 : class, new()
        {
            using (var Db = GetContext())
            {
                var result = Db.Ado.UseTran(() =>
                {
                    Db.Deleteable<T>().In(superKey).ExecuteCommand();
                    foreach (string keyValue in superKey)
                    {
                        var sql = string.Format("sub.{0}=@superKey ", superKeyName);
                        var subList = Db.Queryable<T1>("sub").Where(sql).AddParameters(new { superKey = keyValue }).ToList();
                        Db.Deleteable<T1>(subList).ExecuteCommand();
                    }
                });
                return result.Data;
            }
        }

        /// <summary>
        /// 单表分页查询
        /// </summary>
        /// <typeparam name="T">对应表实体</typeparam>
        /// <param name="exp">查询条件</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">每页显示条数</param>
        /// <param name="totalCount">总记录数</param>
        /// <returns></returns>
        public static List<T> FindBy<T>(Expression<Func<T, bool>> exp, string orderBy, int pageIndex, int pageSize, ref int totalCount, int orderByType = 0) where T : class, new()
        {
            using (var Db = GetContext())
            {
                var list = Db.Queryable<T>().Where(exp).OrderBy(orderBy + " " + (orderByType == 0 ? "Asc" : "Desc")).ToPageList(pageIndex, pageSize, ref totalCount);
                return list;
            }
        }

        public static List<T> FindBy<T>(Expression<Func<T, bool>> exp, string orderBy, int orderByType = 0) where T : class, new()
        {
            using (var Db = GetContext())
            {
                var list = Db.Queryable<T>().Where(exp).OrderBy(orderBy + " " + (orderByType == 0 ? "Asc" : "Desc")).ToList();
                return list;
            }
        }

        public static T FindOne<T>(Expression<Func<T, bool>> exp) where T : class,new()
        {
            using (var Db = GetContext())
            {
                var model = Db.Queryable<T>().First(exp);
                return model;
            }
        }

        /// <summary>
        /// 判断记录是否存在
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="exp"></param>
        /// <returns></returns>
        public static bool Exsits<T>(Expression<Func<T, bool>> exp) where T : class, new()
        {
            using (var Db = GetContext())
            {
                return Db.Queryable<T>().Any(exp);
            }
        }

        #region 执行SQL语句/存储过程，返回表/实体

        /// <summary>
        ///  执行SQL语句，返回实体
        /// </summary>
        /// <typeparam name="OutResult">返回的结果集</typeparam>
        /// <param name="sql">SQL语句</param>
        /// <param name="par">参数</param>
        /// <returns></returns>
        public static List<OutResult> getDateBySQL<OutResult>(string sql, List<SugarParameter> par)
        {
            using (var Db = GetContext())
            {
                List<OutResult> dt = Db.Ado.SqlQuery<OutResult>(sql, par);
                return dt;
            };
        }
        //执行SQL语句，返回表
        public static DataTable getDateBySQL(string sql, List<SugarParameter> par)
        {
            using (var Db = GetContext())
            {
                DataTable dt = Db.Ado.GetDataTable(sql, par);
                return dt;
            };
        }

        /// <summary>
        /// 执行存储过程，返回表
        /// </summary>
        /// <param name="pro">过程名称</param>
        /// <param name="par">参数</param>
        /// <returns></returns>
        public static DataTable getDateByPro(string pro, List<SugarParameter> par)
        {
            using (var Db = GetContext())
            {
                DataTable dt = Db.Ado.UseStoredProcedure().GetDataTable(pro, par);
                return dt;
            };
        }
        // 执行存储过程，返回实体
        public static List<OutResult> getDateByPro<OutResult>(string pro, List<SugarParameter> par)
        {
            using (var Db = GetContext())
            {
                string sql = @"exec " + pro;
                foreach (SugarParameter sp in par)
                {
                    sql += @" " + sp.ParameterName + "='" + sp.Value + "',";
                }
                sql = sql.Remove(sql.LastIndexOf(",")); 
                List<OutResult> dt = Db.Ado.SqlQuery<OutResult>(sql, par);
                return dt;
            };
        }

        #endregion

        /// <summary>
        /// 分页查询，返回实体集
        /// 表转成dynamic
        /// string str = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
        /// List<dynamic> list = Newtonsoft.Json.JsonConvert.DeserializeObject<dynamic>(str).ToObject<List<dynamic>>();
        /// List<dynamic>转成表。字符串格式必须是：[{“key”：“value”}，{“key”：“value”}]
        /// string strd = Newtonsoft.Json.JsonConvert.SerializeObject(list);
        /// DataTable dts=Newtonsoft.Json.JsonConvert.DeserializeObject<DataTable>( strd ); 
        /// 或者DataTable dts=Newtonsoft.Json.JsonConvert.DeserializeObject<DataTable>( "["+strd +"]"); 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="ExecuteType">执行类型。0 SQL语句，1 存储过程，2 视图</param>
        /// <param name="sqlName">对的SQL，存储过程名称，视图名称</param>
        /// <param name="par">对应的SQL参数</param>
        /// <param name="orderBy">排序字段名</param>
        /// <param name="pageIndex">当前页数，从0页开始</param>
        /// <param name="pageSize">每一页的数据行数</param>
        /// <param name="totalCount">总页数</param>
        /// <returns></returns>
        public static List<T> getDataByPage<T>(int ExecuteType, string sqlName, List<SugarParameter> par, string orderBy, int pageIndex, int pageSize, ref int totalCount)
        {
            using (var Db = GetContext())
            {
                //得到所有数据集
                List<T> listT = new List<T>();
                if (ExecuteType == 0)
                    listT = getDateBySQL<T>(sqlName, par);
                else if (ExecuteType == 1)
                    listT = getDateByPro<T>(sqlName, par);
                else if (ExecuteType == 1)
                    listT = getDateByPro<T>("select * from " + sqlName, par);
                //开始分页                
                int count = listT.Count();//总行数
                var Page = (int)Math.Ceiling((Decimal)count / pageSize);//总页数
                totalCount = Page;
                var Skip = pageIndex * pageSize;//当前页从第一行开始行数
                var Take = (pageIndex + 1) * pageSize;//当前页的结尾行数
                if (pageIndex * pageSize > count / 2)//页码大于一半用倒序
                {
                    listT = listT.OrderByDescending(i => i.GetType().GetProperty(orderBy).GetValue(i, null)).ToList<T>();
                    var Mod = count % pageSize;//总页数，取整                    
                    if (pageIndex * pageSize >= count)
                    {
                        Skip = 0; Take = Mod == 0 ? pageSize : Mod;
                    }
                    else
                    {
                        Skip = (Page - pageIndex - 1) * pageSize + Mod;
                    }
                }
                else
                {
                    listT = listT.OrderBy(i => i.GetType().GetProperty(orderBy).GetValue(i, null)).ToList<T>();//升序
                }
                List<T> list = listT.Skip(Skip).ToList<T>();//取前Skip条数据之后的所有数据
                list = list.Take(pageSize).ToList<T>();//取前pageSize条数据                
                return list;
            }
        }
    }
}