MikaltoResort/ManagementApp/Dapper.AExtentions/DapperCommand.cs

using Dapper;
using System.Data.Common;
using System.Reflection;

namespace ManagementApp.Dapper.AExtentions
{
    public static class DapperCommand
    {
        public static async Task Insert<T>(this DbConnection connection, T obj, bool identityInsert = true)
        {
            var type = typeof(T);
            var tableName = TableMapper.GetTableName(type);
            var allProperties = PropertiesCache.TypePropertiesCache(type);
            var keyProperties = PropertiesCache.KeyPropertiesCache(type);
            var computedProperties = PropertiesCache.ComputedPropertiesCache(type);
            var columns = PropertiesCache.GetColumnNamesCache(type);

            var insertProperties = allProperties.Except(computedProperties).ToList();
            var insertPropertiesString = string.Empty;
            if (identityInsert)
            {
                insertProperties = insertProperties.Except(keyProperties).ToList();
                insertPropertiesString = GetColumnsString(insertProperties, columns);
                var insertedId = await connection.QueryFirstAsync<int>($@"
                INSERT INTO {FormatTableName(tableName)} ({insertPropertiesString}) VALUES ({GetColumnsString(insertProperties, columns, "@")});
                SELECT LAST_INSERT_ID()", obj);
                keyProperties[0].SetValue(obj, insertedId);
                
            }
            else
            {
                insertPropertiesString = GetColumnsString(insertProperties, columns);
                await connection.QueryFirstAsync($@"
                INSERT INTO {FormatTableName(tableName)}({insertPropertiesString}) 
                VALUES ({GetColumnsString(insertProperties, columns, "@")})", obj);
            }
        }

        public static async Task Update<T>(this DbConnection connection, T obj)
        {
            var type = typeof(T);
            var tableName = TableMapper.GetTableName(type);
            var allProperties = PropertiesCache.TypePropertiesCache(type);
            var keyProperties = PropertiesCache.KeyPropertiesCache(type);
            var computedProperties = PropertiesCache.ComputedPropertiesCache(type);
            var columns = PropertiesCache.GetColumnNamesCache(type);

            T obj1 = Activator.CreateInstance<T>();
            var uProperties = allProperties.Except(computedProperties).Except(keyProperties).ToList();
            var diffProperties = ComparePropertiesValue<T>(obj, obj1, uProperties);

            await connection.QueryAsync($@"
                UPDATE {FormatTableName(tableName)} Set {GetUpdateString(diffProperties, columns, " , ")}
                WHERE {GetUpdateString(keyProperties, columns, " and ")}", obj);
        }
        private static List<PropertyInfo> ComparePropertiesValue<T>(T obj1, T obj2, IEnumerable<PropertyInfo> properties)
        {
            List<PropertyInfo> propertiesDiff = new List<PropertyInfo>();
            foreach (PropertyInfo property in properties) { 
                if (property.GetValue(obj1, null) != property.GetValue(obj2, null))
                {
                    propertiesDiff.Add(property);
                }
            }
            return propertiesDiff;
        }



        private static string GetUpdateString(IEnumerable<PropertyInfo> properties, IReadOnlyDictionary<string, string> columnNames, string prefix)
        {
            return string.Join(prefix, properties.Select(p => $"{columnNames[p.Name]} = @{p.Name}"));
        }
        private static string GetColumnsString(IEnumerable<PropertyInfo> properties, IReadOnlyDictionary<string, string> columnNames, string tablePrefix = null)
        {
            if (tablePrefix == "target.")
            {
                return string.Join(", ", properties.Select(property => $"{tablePrefix}{columnNames[property.Name]} as {property.Name}"));
            }
            else if(tablePrefix == "@")
            {
                return string.Join(", ", properties.Select(property => $"{tablePrefix}{property.Name}"));
            }
            return string.Join(", ", properties.Select(property => $"{tablePrefix}{columnNames[property.Name]}"));
        }
        private static string FormatTableName(string table)
        {
            if (string.IsNullOrEmpty(table))
            {
                return table;
            }

            var parts = table.Split('.');

            if (parts.Length == 1)
            {
                return $"{table}";
            }

            var tableName = "";
            for (int i = 0; i < parts.Length; i++)
            {
                tableName += $"{parts[i]}";
                if (i + 1 < parts.Length)
                {
                    tableName += ".";
                }
            }

            return tableName;
        }
    }
}