WorkFlow/WorkFlowApi/WorkFlowApi..../BaseBusiness...

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using WorkFlowApi.DB;
using WorkFlowApi.Utility;

namespace WorkFlowApi.Business
{
    /// <summary>
    /// 描述：业务处理基类
    /// </summary>
    /// <typeparam name="T">泛型约束（数据库实体）</typeparam>
    public abstract class BaseBusiness<T> where T : class, new()
    {
        #region 构造函数

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="db">注入数据库</param>
        public BaseBusiness()
        {
            type= typeof(T);
            Prop = type.GetProperties();
            KeyProp = Prop.Where(p => p.Name == KeyField).FirstOrDefault();
        }

        #endregion

        #region 私有成员
        /// <summary>
        /// 主键
        /// </summary>
        protected virtual string KeyField { get; } = "F_Id";
        /// <summary>
        /// 类
        /// </summary>
        protected Type type { get; set; }
        /// <summary>
        /// 属性
        /// </summary>
        protected PropertyInfo[] Prop { get; set; }
        /// <summary>
        /// 主键属性
        /// </summary>
        protected PropertyInfo KeyProp { get; set; }
        #endregion

        #region 存在数据
        /// <summary>
        /// 是否存在
        /// </summary>
        /// <param name="entity">实体对象</param>
        /// <param name="field">存在字段</param>
        /// <returns></returns>
        public bool Exists(T entity, string field)
        {
            PropertyInfo pi = Prop.Where(p => p.Name == field).FirstOrDefault();
            string sql = string.Format("select count(1) from [{0}] where [{1}]={2} ", type.Name, field, string.Format("@{0}", field));
            int keyValue = KeyProp.GetValue(entity, null)?.ToString().ToInt() ?? 0;
            if (keyValue > 0) { sql += " and [" + KeyField + "]!=" + keyValue; }
            SqlParameter[] ps = {
                    new SqlParameter(string.Format("@{0}", field), pi.GetValue(entity, null) ?? DBNull.Value)
            };

            object obj = DbHelperSQL.GetSingle(sql, ps.ToArray());
            if (obj == null)
            {
                return false;
            }
            else
            {
                return Convert.ToInt32(obj) > 0;
            }
        }
        #endregion

        #region 增加数据

        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="entity">实体对象</param>
        public int Insert(T entity)
        {
            string fields = string.Empty;
            string values = string.Empty;
            List<SqlParameter> ps = new List<SqlParameter>();
            foreach (var p in Prop.Where(p => p.Name.ToLower() != KeyField.ToLower()))
            {
                if (string.IsNullOrEmpty(fields)) { fields = string.Format("[{0}]", p.Name); }
                else { fields += string.Format(",[{0}]", p.Name); }
                if (string.IsNullOrEmpty(values)) { values = string.Format("@{0}", p.Name); }
                else { values += string.Format(",@{0}", p.Name); }
                ps.Add(new SqlParameter(string.Format("@{0}", p.Name), p.GetValue(entity, null) ?? DBNull.Value));
            }
            string sql = string.Format("insert into [{0}]({1}) values({2});select @@identity", type.Name, fields, values);

            object obj = DbHelperSQL.GetSingle(sql, ps.ToArray());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }


        public int InsertExe(T entity)
        {
            string fields = string.Empty;
            string values = string.Empty;
            List<SqlParameter> ps = new List<SqlParameter>();
            foreach (var p in Prop)
            {
                if (string.IsNullOrEmpty(fields)) { fields = string.Format("[{0}]", p.Name); }
                else { fields += string.Format(",[{0}]", p.Name); }
                if (string.IsNullOrEmpty(values)) { values = string.Format("@{0}", p.Name); }
                else { values += string.Format(",@{0}", p.Name); }
                ps.Add(new SqlParameter(string.Format("@{0}", p.Name), p.GetValue(entity, null) ?? DBNull.Value));
            }
            string sql = string.Format("insert into [{0}]({1}) values({2})", type.Name, fields, values);

            object obj = DbHelperSQL.GetSingle(sql, ps.ToArray());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 添加多条数据
        /// </summary>
        /// <param name="entities">实体对象集合</param>
        public int InsertList(List<T> entities)
        {
            List<CommandInfo> cmdList = new List<CommandInfo>();
            var pps = Prop.Where(p => p.Name.ToLower() != KeyField.ToLower());
            foreach (var entity in entities)
            {
                string fields = string.Empty;
                string values = string.Empty;
                List<SqlParameter> ps = new List<SqlParameter>();
                foreach (var p in pps)
                {
                    if (string.IsNullOrEmpty(fields)) { fields = string.Format("[{0}]", p.Name); }
                    else { fields += string.Format(",[{0}]", p.Name); }
                    if (string.IsNullOrEmpty(values)) { values = string.Format("@{0}", p.Name); }
                    else { values += string.Format(",@{0}", p.Name); }
                    ps.Add(new SqlParameter(string.Format("@{0}", p.Name), p.GetValue(entity, null) ?? DBNull.Value));
                }

                string sql = string.Format("insert into [{0}]({1}) values({2});", type.Name, fields, values);

                cmdList.Add(new CommandInfo(sql, ps.ToArray()));
            }
            return DbHelperSQL.ExecuteSqlTran(cmdList);
        }

        public int InsertListExe(List<T> entities)
        {
            List<CommandInfo> cmdList = new List<CommandInfo>();
            foreach (var entity in entities)
            {
                string fields = string.Empty;
                string values = string.Empty;
                List<SqlParameter> ps = new List<SqlParameter>();
                foreach (var p in Prop)
                {
                    if (string.IsNullOrEmpty(fields)) { fields = string.Format("[{0}]", p.Name); }
                    else { fields += string.Format(",[{0}]", p.Name); }
                    if (string.IsNullOrEmpty(values)) { values = string.Format("@{0}", p.Name); }
                    else { values += string.Format(",@{0}", p.Name); }
                    ps.Add(new SqlParameter(string.Format("@{0}", p.Name), p.GetValue(entity, null) ?? DBNull.Value));
                }

                string sql = string.Format("insert into [{0}]({1}) values({2});", type.Name, fields, values);

                cmdList.Add(new CommandInfo(sql, ps.ToArray()));
            }
            return DbHelperSQL.ExecuteSqlTran(cmdList);
        }

        #endregion

        #region 删除数据

        /// <summary>
        /// 删除指定主键数据
        /// </summary>
        /// <param name="key"></param>
        public int Delete(string key)
        {
            string sql = string.Format("delete from [{0}] where [{1}]={2};", type.Name,KeyField, string.Format("@{0}", KeyField));
            SqlParameter[] ps = {
                    new SqlParameter(string.Format("@{0}", KeyField), key)
            };
            return DbHelperSQL.ExecuteSql(sql, ps);
        }

        public int DeleteExe(string key,string Field)
        {
            string sql = string.Format("delete from [{0}] where [{1}]={2};", type.Name, Field, string.Format("@{0}", Field));
            SqlParameter[] ps = {
                    new SqlParameter(string.Format("@{0}", Field), key)
            };
            return DbHelperSQL.ExecuteSql(sql, ps);
        }

        /// <summary>
        /// 通过主键删除多条数据
        /// </summary>
        /// <param name="keys"></param>
        public int DeleteList(List<string> keys)
        {
            string fields = string.Empty;
            List<SqlParameter> ps = new List<SqlParameter>();
            for (int i = 0; i < keys.Count; i++)
            {
                if (string.IsNullOrEmpty(fields)) { fields = string.Format("@{0}" + i, KeyField); }
                else { fields += string.Format(",@{0}" + i, KeyField); }
                ps.Add(new SqlParameter(string.Format("@{0}"+i, KeyField), keys[i]));
            }
            string sql = string.Format("delete from [{0}] where [{1}] in ({2});", type.Name, KeyField, fields);
            return DbHelperSQL.ExecuteSql(sql,ps.ToArray());
        }

        /// <summary>
        /// 删除单条数据
        /// </summary>
        /// <param name="entity">实体对象</param>
        public int DeleteEntity(T entity)
        {
            string sql = string.Format("delete from [{0}] where [{1}]={2};", type.Name, KeyField, string.Format("@{0}", KeyField));
            SqlParameter[] ps = {
                    new SqlParameter(string.Format("@{0}", KeyField), KeyProp?.GetValue(entity, null)??null)
            };
            return DbHelperSQL.ExecuteSql(sql, ps);
        }

        /// <summary>
        /// 删除多条数据
        /// </summary>
        /// <param name="entities">实体对象集合</param>
        public int DeleteEntityList(List<T> entities)
        {
            string fields = string.Empty;
            List<SqlParameter> ps = new List<SqlParameter>();
            for (int i = 0; i < entities.Count; i++)
            {
                if (string.IsNullOrEmpty(fields)) { fields = string.Format("@{0}" + i, KeyField); }
                else { fields += string.Format(",@{0}" + i, KeyField); }
                ps.Add(new SqlParameter(string.Format("@{0}" + i, KeyField), KeyProp?.GetValue(entities[i], null) ?? null));
            }
            string sql = string.Format("delete from [{0}] where [{1}] in ({2});", type.Name, KeyField, fields);

            return DbHelperSQL.ExecuteSql(sql);
        }

        #endregion

        #region 更新数据

        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <param name="entity">实体对象</param>
        public int Update(T entity)
        {
            string fieldvalues = string.Empty;
            List<SqlParameter> ps = new List<SqlParameter>();
            foreach (var p in Prop.Where(p => p.Name.ToLower() != KeyField.ToLower()))
            {
                if (string.IsNullOrEmpty(fieldvalues)) { fieldvalues = string.Format("[{0}]=@{1}", p.Name, p.Name); }
                else { fieldvalues += string.Format(",[{0}]=@{1}", p.Name, p.Name); }

                ps.Add(new SqlParameter(string.Format("@{0}", p.Name), p.GetValue(entity, null) ?? DBNull.Value));
            }

            string sql = string.Format("update [{0}] set {1} where [{2}]={3};", type.Name, fieldvalues, KeyField, KeyProp?.GetValue(entity, null) ?? null);

            return DbHelperSQL.ExecuteSql(sql, ps.ToArray());
        }

        public int UpdateExe(T entity)
        {
            string fieldvalues = string.Empty;
            List<SqlParameter> ps = new List<SqlParameter>();
            foreach (var p in Prop.Where(p => p.Name.ToLower() != KeyField.ToLower()))
            {
                var value = p.GetValue(entity, null) ?? DBNull.Value;
                if (value != null)
                {
                    if (value.ToString() != "")
                    {
                        if (string.IsNullOrEmpty(fieldvalues)) { fieldvalues = string.Format("[{0}]=@{1}", p.Name, p.Name); }
                        else { fieldvalues += string.Format(",[{0}]=@{1}", p.Name, p.Name); }

                        ps.Add(new SqlParameter(string.Format("@{0}", p.Name), p.GetValue(entity, null) ?? DBNull.Value));
                    }
                }
                   
            }

            string sql = string.Format("update [{0}] set {1} where [{2}]='{3}';", type.Name, fieldvalues, KeyField, KeyProp?.GetValue(entity, null) ?? null);

            return DbHelperSQL.ExecuteSql(sql, ps.ToArray());
        }

        /// <summary>
        /// 更新多条数据
        /// </summary>
        /// <param name="entities">数据列表</param>
        public int UpdateList(List<T> entities)
        {
            List<CommandInfo> cmdList = new List<CommandInfo>();
            var pps = Prop.Where(p => p.Name.ToLower() != KeyField.ToLower());
            foreach (var entity in entities)
            {
                string fieldvalues = string.Empty;
                List<SqlParameter> ps = new List<SqlParameter>();
                foreach (var p in pps)
                {
                    if (string.IsNullOrEmpty(fieldvalues)) { fieldvalues = string.Format("[{0}]=@{1}", p.Name, p.Name); }
                    else { fieldvalues += string.Format(",[{0}]=@{1}", p.Name, p.Name); }

                    ps.Add(new SqlParameter(string.Format("@{0}", p.Name), p.GetValue(entity, null) ?? DBNull.Value));
                }

                string sql = string.Format("update [{0}] set {1} where [{2}]={3};", type.Name, fieldvalues, KeyField, KeyProp?.GetValue(entity, null) ?? null);

                cmdList.Add(new CommandInfo(sql, ps.ToArray()));
            }
            return DbHelperSQL.ExecuteSqlTran(cmdList);
        }

        #endregion

        #region 查询数据

        /// <summary>
        /// 获取实体
        /// </summary>
        /// <param name="keyValue">主键</param>
        /// <returns></returns>
        public T GetEntity(string keyValue)
        {
            string fields =string.Join(",", Prop.Select(p=> string.Format("[{0}]", p.Name)));
            string sql = string.Format("select top 1 {0} from [{1}] with(nolock) where [{2}]={3};", fields,type.Name, KeyField, string.Format("@{0}", KeyField));
            SqlParameter[] ps = {
                    new SqlParameter(string.Format("@{0}", KeyField), keyValue)
            };

            DataTable dt = DbHelperSQL.Query(sql, ps).Tables[0];

            return dt.ConvertToList<T>().FirstOrDefault();
        }

        /// <summary>
        /// 获取表的所有数据
        /// </summary>
        /// <returns></returns>
        public List<T> GetAllList()
        {
            string fields = string.Join(",", Prop.Select(p => string.Format("[{0}]", p.Name)));
            string sql = string.Format("select  {0} from [{1}] with(nolock);", fields, type.Name);

            DataTable dt = DbHelperSQL.Query(sql).Tables[0];
            return dt.ConvertToList<T>();
        }

        /// <summary>
        /// 查询列表
        /// </summary>
        /// <param name="WhereStr"></param>
        /// <param name="OrderStr"></param>
        /// <returns></returns>
        public List<T> GetList(string WhereStr, string OrderStr)
        {
            string fields = string.Join(",", Prop.Select(p => string.Format("[{0}]", p.Name)));
            string sql = string.Format("select  {0} from [{1}] with(nolock) ", fields, type.Name);
            if (!string.IsNullOrEmpty( WhereStr.Trim()))
            {
                sql += " where " + WhereStr;
            }
            if (!string.IsNullOrEmpty(OrderStr.Trim()))
            {
                sql += " order by " + OrderStr;
            }

            DataTable dt = DbHelperSQL.Query(sql).Tables[0];
            return dt.ConvertToList<T>();
        }

        /// <summary>
        /// 分页列表返回DataTable
        /// </summary>
        /// <param name="WhereStr"></param>
        /// <param name="OrderStr"></param>
        /// <param name="PageSize"></param>
        /// <param name="PageIndex"></param>
        /// <param name="RecordCount"></param>
        /// <returns></returns>
        public DataTable GetDataTablePage(string WhereStr, string OrderStr, int PageSize, int PageIndex, out int RecordCount)
        {
            string fields = string.Join(",", Prop.Select(p => string.Format("[{0}]", p.Name)));
            return DbHelperSQL.RunPagination(type.Name, KeyField, fields, WhereStr, OrderStr, PageSize, PageIndex, true, out RecordCount);
        }

        /// <summary>
        /// 分页列表返回List
        /// </summary>
        /// <param name="WhereStr"></param>
        /// <param name="OrderStr"></param>
        /// <param name="PageSize"></param>
        /// <param name="PageIndex"></param>
        /// <param name="RecordCount"></param>
        /// <returns></returns>
        public List<T> GetListPage(string WhereStr, string OrderStr, int PageSize, int PageIndex, out int RecordCount)
        {
            return GetDataTablePage(WhereStr, OrderStr, PageSize, PageIndex,out RecordCount).ConvertToList<T>();
        }

        #endregion
    }
}