CB.55128.CN/Code/cb.55128.cn/Data.Dapper/DatabaseCommon.cs

using Common;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;

namespace Data.Dapper
{
    public class DatabaseCommon<T> where T : class, new()
    {
        #region 拼接
        /// <summary>
        /// 拼接 查询 SQL语句，自定义条件
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where">条件</param>
        /// <param name="allFieid">是否查询所有字段</param>
        /// <returns></returns>
        public static StringBuilder SelectSql(string where, bool allFieid = false)
        {
            //表名
            string table = EntityAttributeHelper.GetEntityTable<T>();

            PropertyInfo[] props = EntityAttributeHelper.GetProperties(typeof(T));
            StringBuilder sbColumns = new StringBuilder();
            if (allFieid)
            {
                sbColumns.Append(" * ");
            }
            else
            {
                foreach (PropertyInfo prop in props)
                {
                    //string propertytype = prop.PropertyType.ToString();
                    sbColumns.Append(prop.Name + ",");
                }
                if (sbColumns.Length > 0) sbColumns.Remove(sbColumns.ToString().Length - 1, 1);
            }

            if (string.IsNullOrWhiteSpace(where)) where = " WHERE 1 = 1";

            string strSql = "SELECT {0} FROM {1} {2}";
            strSql = string.Format(strSql, sbColumns.ToString(), table + " ", where);
            return new StringBuilder(strSql);
        }

        /// <summary>
        /// 查询条数的sql
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public static StringBuilder SelectCountSql(string where)
        {
            //表名
            string table = EntityAttributeHelper.GetEntityTable<T>();

            PropertyInfo[] props = EntityAttributeHelper.GetProperties(typeof(T));
            StringBuilder sbColumns = new StringBuilder();
           
            if (string.IsNullOrWhiteSpace(where)) where = " WHERE 1 = 1";

            string strSql = $"SELECT COUNT(*) FROM {table} {where}";
            return new StringBuilder(strSql);
        }

        /// <summary>
        /// 拼接删除语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where"></param>
        /// <param name="allFieid"></param>
        /// <returns></returns>
        public static StringBuilder DeleteSql(string where, bool allFieid = false)
        {
            if (string.IsNullOrWhiteSpace(where)) where = " WHERE 1 = 1";
            //表名
            string table = EntityAttributeHelper.GetEntityTable<T>();
            var sql = $"DELETE FROM {table} {where}";
            return new StringBuilder(sql);
        }
        /// <summary>
        /// 拼接删除语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where"></param>
        /// <param name="allFieid"></param>
        /// <returns></returns>
        public static StringBuilder DeleteSqlById(int id, bool allFieid = false)
        {
            //表名
            string table = EntityAttributeHelper.GetEntityTable<T>();
            var sql = $"DELETE FROM {table} where id=@{id}";
            return new StringBuilder(sql);
        }

        /// <summary>
        /// 根据实体拼接新增sql语句
        /// </summary>
        /// <returns></returns>
        public static StringBuilder InsertSql()
        {
            string table = EntityAttributeHelper.GetEntityTable<T>();
            var values = GetInsertName();
            var sql = $"INSERT INTO {table}({values.Replace("@", "")}) values({values}) ";
            return new StringBuilder(sql);
        }

        /// <summary>
        /// 根据实体拼接修改sql语句
        /// </summary>
        /// <returns></returns>
        public static StringBuilder UpdateSql(T t)
        {
            string table = EntityAttributeHelper.GetEntityTable<T>();
            var values = GetInsertName();
            var key = EntityAttributeHelper.GetEntityKey<T>();
            var set = GetUpdateName(t, key);
            if (set.IsEmpty())
                return new StringBuilder();
            var sql = $"UPDATE  {table} SET {set} WHERE {key}=@{key}";
            return new StringBuilder(sql);
        }


        /// <summary>
        /// 根据实体拼接删除sql语句
        /// </summary>
        /// <returns></returns>
        public static StringBuilder DeleteSql(T t)
        {
            string table = EntityAttributeHelper.GetEntityTable<T>();
            var values = GetInsertName();
            var key = EntityAttributeHelper.GetEntityKey<T>();
            var sql = $"DELETE FROM {table} WHERE {key}=@{key}";
            return new StringBuilder(sql);
        }


        #endregion

        private static string GetInsertName()
        {

            var properties = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public);

            if (properties.Length <= 0)
            {
                return "";
            }

            var key = EntityAttributeHelper.GetEntityKey<T>();
            var list = new List<string>();
            foreach (System.Reflection.PropertyInfo item in properties)
            {
                if (key != item.Name)
                {
                    list.Add("@" + item.Name);
                }
            }

            return list.Join();
        }

        private static string GetUpdateName(T t, string key)
        {
            var str = "";
            var properties = t.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);

            if (properties.Length <= 0)
            {
                return "";
            }
            var keyValue = "";
            var list = new List<string>();
            foreach (System.Reflection.PropertyInfo item in properties)
            {
                if (key != item.Name)
                {
                    str += $"{item.Name}=@{item.Name},";
                }
                else
                {
                    keyValue = item.GetValue(t, null) != null ? item.GetValue(t, null).ToString() : "";
                }
            }
            if (keyValue.IsEmpty() || keyValue == "0")
                return string.Empty;
            return str.Substring(0, str.Length - 1);
        }
    }
}