MARKUS

using Dapper;

using Npgsql;

using NpgsqlTypes;

using System;

using System.Collections.Generic;

using System.Data;

using System.Linq;

namespace Markus.Service.DataBase

{

    internal class MixParameters : SqlMapper.IDynamicParameters, SqlMapper.IParameterLookup

    {

        public MixParameters(DBMSType dbms)

        {

            DBMS = dbms;

        }

        private DBMSType DBMS = DBMSType.MSSQL;

        internal const DbType EnumerableMultiParameter = (DbType)(-1);

        private static readonly Dictionary<SqlMapper.Identity, Action<IDbCommand, object>> paramReaderCache = new Dictionary<SqlMapper.Identity, Action<IDbCommand, object>>();

        private readonly Dictionary<string, ParamInfo> _parameters = new Dictionary<string, ParamInfo>();

        private List<object> templates;

        /// <summary>

        ///     All the names of the param in the bag, use Get to yank them out

        /// </summary>

        public IEnumerable<string> ParameterNames

        {

            get { return _parameters.Select(p => p.Key); }

        }

        public int ParameterCount

        {

            get { return _parameters.Count; }

        }

        object SqlMapper.IParameterLookup.this[string name] =>

             _parameters.TryGetValue(name, out ParamInfo param) ? param.Value : null;

        internal static bool ShouldSetDbType(DbType? dbType)

            => dbType.HasValue && dbType.GetValueOrDefault() != EnumerableMultiParameter;

        internal static bool ShouldSetDbType(DbType dbType)

            => dbType != EnumerableMultiParameter; // just in case called with non-nullable

        void SqlMapper.IDynamicParameters.AddParameters(IDbCommand command, SqlMapper.Identity identity)

        {

            if (templates != null)

                foreach (var template in templates)

                {

                    var newIdent = identity.ForDynamicParameters(template.GetType());

                    Action<IDbCommand, object> appender;

                    lock (paramReaderCache)

                    {

                        if (!paramReaderCache.TryGetValue(newIdent, out appender))

                        {

                            appender = SqlMapper.CreateParamInfoGenerator(newIdent, false, true);

                            paramReaderCache[newIdent] = appender;

                        }

                    }

                    appender(command, template);

                }

            switch (DBMS)

            {

                case DBMSType.POSTGRESQL:

                case DBMSType.MSSQL:

                    msSqlAddParamaters(command, identity);

                    break;

                //case DBMSType.POSTGRESQL:

                //    npgAddParamaters(command, identity);

                    //break;

                default:

                    break;

            }

        }

        private void msSqlAddParamaters(IDbCommand command, SqlMapper.Identity identity)

        {

            foreach (var param in _parameters.Values)

            {

                if (param.CameFromTemplate) continue;

                var dbType = param.DbType;

                var val = param.Value;

                string name = Clean(param.Name);

                var isCustomQueryParameter = val is SqlMapper.ICustomQueryParameter;

                SqlMapper.ITypeHandler handler = null;

                if (dbType == null && val != null && !isCustomQueryParameter)

                {

#pragma warning disable 618

                    dbType = SqlMapper.LookupDbType(val.GetType(), name, true, out handler);

#pragma warning disable 618

                }

                if (isCustomQueryParameter)

                {

                    ((SqlMapper.ICustomQueryParameter)val).AddParameter(command, name);

                }

                else if (dbType == EnumerableMultiParameter)

                {

#pragma warning disable 612, 618

                    SqlMapper.PackListParameters(command, name, val);

#pragma warning restore 612, 618

                }

                else

                {

                    bool add = !command.Parameters.Contains(name);

                    IDbDataParameter p;

                    if (add)

                    {

                        p = command.CreateParameter();

                        p.ParameterName = name;

                    }

                    else

                    {

                        p = (IDbDataParameter)command.Parameters[name];

                    }

                    p.Direction = param.ParameterDirection;

                    if (handler == null)

                    {

#pragma warning disable 0618

                        p.Value = SqlMapper.SanitizeParameterValue(val);

#pragma warning restore 0618

                        if (ShouldSetDbType(dbType) && p.DbType != dbType.GetValueOrDefault())

                        {

                            p.DbType = dbType.GetValueOrDefault();

                        }

                        var s = val as string;

                        if (s?.Length <= DbString.DefaultLength)

                        {

                            p.Size = DbString.DefaultLength;

                        }

                        if (param.Size != null) p.Size = param.Size.Value;

                        if (param.Precision != null) p.Precision = param.Precision.Value;

                        if (param.Scale != null) p.Scale = param.Scale.Value;

                    }

                    else

                    {

                        if (ShouldSetDbType(dbType)) p.DbType = dbType.GetValueOrDefault();

                        if (param.Size != null) p.Size = param.Size.Value;

                        if (param.Precision != null) p.Precision = param.Precision.Value;

                        if (param.Scale != null) p.Scale = param.Scale.Value;

                        handler.SetValue(p, val ?? DBNull.Value);

                    }

                    if (add)

                    {

                        command.Parameters.Add(p);

                    }

                    param.AttachedParam = p;

                }

            }

            // note: most non-privileged implementations would use: this.ReplaceLiterals(command);

            //if (literals.Count != 0) SqlMapper.ReplaceLiterals(this, command);

        }

        private void npgAddParamaters(IDbCommand command, SqlMapper.Identity identity)

        { 

            foreach (var param in _parameters.Values)

            {

                if (((NpgsqlCommand)command).Parameters.Contains(param.Name))

                {

                    ((NpgsqlCommand)command).Parameters.Remove(param.Name);

                }

                var p = ParamInfoToNpgParam(param);

                p.ParameterName = param.Name;

                command.Parameters.Add(p);

                param.AttachedParam = p;

            }

        }

        private NpgsqlParameter ParamInfoToNpgParam(ParamInfo param)

        {

            NpgsqlParameter p = new NpgsqlParameter();

            var val = param.Value;

            p.Value = val ?? DBNull.Value;

            p.Direction = param.ParameterDirection;

            if (param.Size != null) p.Size = param.Size.Value;

            if (param.NpgsqlType != null)

            {

                p.NpgsqlDbType = param.NpgsqlType.Value;

            }

            else if (param.Value != null)

            {

                var npgType = dbTypeConvert.GetNpgsqlDbType(param.Value.GetType()).Value;

                if (npgType == NpgsqlDbType.Text)

                {

                    //npgType = NpgsqlDbType.Varchar;

                }

                p.NpgsqlDbType = npgType;

            }

            else if (param.DbType != null)

            {

                var npgType = dbTypeConvert.GetNpgsqlDbType(param.DbType.Value).Value;

                if (npgType == NpgsqlDbType.Text)

                {

                    //npgType = NpgsqlDbType.Varchar;

                }

                p.NpgsqlDbType = npgType;

            }

            else

            {

                throw new Exception($"{param.Name} npgSQL type을 알 수 없습니다.");

            }

            return p;

        }

        private static string Clean(string name)

        {

            if (!string.IsNullOrEmpty(name))

            {

                switch (name[0])

                {

                    case '@':

                    case ':':

                    case '?':

                        return name.Substring(1);

                }

            }

            return name;

        }

        internal void Add(string name,object value = null,NpgsqlTypes.NpgsqlDbType? npgsqlDbType = null, DbType? dbType = null, ParameterDirection? direction = null, int? size = null)

        {

            if(DBMS == DBMSType.MSSQL)

            {

                switch (direction)

                {

                    case ParameterDirection.Output:

                    case ParameterDirection.InputOutput:

                        if(size == null)

                        {

                            size = 50;

                            //throw new Exception($"{name}는 MSSQL인 경우 direction이 {direction.ToString()}이므로 size를 지정하여야 합니다.");

                        }

                    break;

                    default:

                        break;

                }

            }

            _parameters[Clean(name)] = new ParamInfo

            {

                Name = name,

                Value = value,

                ParameterDirection = direction ?? ParameterDirection.Input,

                DbType = dbType,

                NpgsqlType = npgsqlDbType,

                Size = size

            };

        }

        internal T Get<T>(string name)

        {

            var val = _parameters[name].AttachedParam.Value;

            if (val == DBNull.Value)

            {

                if (default(T) != null)

                    throw new ApplicationException("Attempting to cast a DBNull to a non nullable type!");

                return default(T);

            }

            return (T)val;

        }

        public NpgsqlParameter Get(string name)

        {

            return ParamInfoToNpgParam(_parameters[name]);

        }

        private class ParamInfo

        {

            public string Name { get; set; }

            public object Value { get; set; }

            public ParameterDirection ParameterDirection { get; set; }

            public DbType? DbType { get; set; }

            public NpgsqlTypes.NpgsqlDbType? NpgsqlType { get; set; }

            public int? Size { get; set; }

            public IDbDataParameter AttachedParam { get; set; }

            internal Action<object, DynamicParameters> OutputCallback { get; set; }

            internal object OutputTarget { get; set; }

            internal bool CameFromTemplate { get; set; }

            public byte? Precision { get; set; }

            public byte? Scale { get; set; }

        }

    }

}

using Dapper;

using System;

using System.Collections;

using System.Collections.Generic;

using System.Data;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Linq.Expressions;

using System.Reflection;

using System.Reflection.Emit;

using System.ComponentModel;

using System.Data.SqlTypes;

using System.Dynamic;

using System.Globalization;

using System.Text.RegularExpressions;

using System.Diagnostics;

namespace Markus.Service.DataBase

{

    public partial class QueryParameters : SqlMapper.IDynamicParameters

    {

        DBMSType DBMS;

        public QueryParameters(DBMSType dbms)

        {

            DBMS = dbms;

        }

        //////////////////////////////////////////////////////////////////////////////////////////////////// Field

        ////////////////////////////////////////////////////////////////////////////////////////// Private

        #region Field

        /// <summary>

        /// 동적 매개 변수 목록

        /// </summary>

        private readonly DynamicParameters dynamicParameters = new DynamicParameters();

        /// <summary>

        /// 오라클 매개 변수 리스트

        /// </summary>

        private readonly List<Npgsql.NpgsqlParameter> NpgsqlParameterList = new List<Npgsql.NpgsqlParameter>();

        #endregion

        //////////////////////////////////////////////////////////////////////////////////////////////////// Constructor

        ////////////////////////////////////////////////////////////////////////////////////////// Public

        #region 추가하기 - Add(name, value, dbType, direction, size)

        /// <summary>

        /// 추가하기

        /// </summary>

        /// <param name="name">명칭</param>

        /// <param name="value">값</param>

        /// <param name="dbType">DB 타입</param>

        /// <param name="direction">방향</param>

        /// <param name="size">크기</param>

        public void Add(string name, object value = null, DbType? dbType = null, ParameterDirection? direction = null, int? size = null)

        {

            if (DBMS == DBMSType.MSSQL)

            {

                this.dynamicParameters.Add(name, value, dbType, direction, size);

            }

            else

            {

                Npgsql.NpgsqlParameter parameter = new Npgsql.NpgsqlParameter(parameterName: name, parameterType: dbType)

                {

                    Direction = direction.GetValueOrDefault()

                };

                this.NpgsqlParameterList.Add(parameter);

            }

        }

        #endregion

        #region 추가하기 - Add(name, oracleDbType, direction)

        /// <summary>

        /// 추가하기

        /// </summary>

        /// <param name="name">명칭</param>

        /// <param name="oracleDbType">오라클 DB 타입</param>

        /// <param name="direction">방향</param>

        public void Add(string name, NpgsqlTypes.NpgsqlDbType dbType, ParameterDirection direction)

        {

            Npgsql.NpgsqlParameter parameter = new Npgsql.NpgsqlParameter(parameterName: name, parameterType: dbType)

            {

                Direction = direction

            };

            this.NpgsqlParameterList.Add(parameter);

        }

        #endregion

    }

}

using Dapper;

using System;

using System.Collections;

using System.Collections.Generic;

using System.Data;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Linq.Expressions;

using System.Reflection;

using System.Reflection.Emit;

using System.ComponentModel;

using System.Data.SqlTypes;

using System.Dynamic;

using System.Globalization;

using System.Text.RegularExpressions;

using System.Diagnostics;

namespace Markus.Service.DataBase

{

    public partial class QueryParameters : SqlMapper.IDynamicParameters, SqlMapper.IParameterLookup, SqlMapper.IParameterCallbacks

    {

        internal const DbType EnumerableMultiParameter = (DbType)(-1);

        private static readonly Dictionary<SqlMapper.Identity, Action<IDbCommand, object>> paramReaderCache = new Dictionary<SqlMapper.Identity, Action<IDbCommand, object>>();

        private readonly Dictionary<string, ParamInfo> parameters = new Dictionary<string, ParamInfo>();

        private List<object> templates;

        object SqlMapper.IParameterLookup.this[string name] =>

            parameters.TryGetValue(name, out ParamInfo param) ? param.Value : null;

        /// <summary>

        /// construct a dynamic parameter bag

        /// </summary>

        public DynamicParameters()

        {

            RemoveUnused = true;

        }

        /// <summary>

        /// construct a dynamic parameter bag

        /// </summary>

        /// <param name="template">can be an anonymous type or a DynamicParameters bag</param>

        public DynamicParameters(object template)

        {

            RemoveUnused = true;

            AddDynamicParams(template);

        }

        /// <summary>

        /// Append a whole object full of params to the dynamic

        /// EG: AddDynamicParams(new {A = 1, B = 2}) // will add property A and B to the dynamic

        /// </summary>

        /// <param name="param"></param>

        public void AddDynamicParams(object param)

        {

            var obj = param;

            if (obj != null)

            {

                if (obj is DynamicParameters subDynamic)

                {

                    if (subDynamic.parameters != null)

                    {

                        foreach (var kvp in subDynamic.parameters)

                        {

                            parameters.Add(kvp.Key, kvp.Value);

                        }

                    }

                    if (subDynamic.templates != null)

                    {

                        templates ??= new List<object>();

                        foreach (var t in subDynamic.templates)

                        {

                            templates.Add(t);

                        }

                    }

                }

                else

                {

                    if (obj is IEnumerable<KeyValuePair<string, object>> dictionary)

                    {

                        foreach (var kvp in dictionary)

                        {

                            Add(kvp.Key, kvp.Value, null, null, null);

                        }

                    }

                    else

                    {

                        templates ??= new List<object>();

                        templates.Add(obj);

                    }

                }

            }

        }

        /// <summary>

        /// Add a parameter to this dynamic parameter list.

        /// </summary>

        /// <param name="name">The name of the parameter.</param>

        /// <param name="value">The value of the parameter.</param>

        /// <param name="dbType">The type of the parameter.</param>

        /// <param name="direction">The in or out direction of the parameter.</param>

        /// <param name="size">The size of the parameter.</param>

        public void Add(string name, object value, DbType? dbType, ParameterDirection? direction, int? size)

        {

            parameters[Clean(name)] = new ParamInfo

            {

                Name = name,

                Value = value,

                ParameterDirection = direction ?? ParameterDirection.Input,

                DbType = dbType,

                Size = size

            };

        }

        /// <summary>

        /// Add a parameter to this dynamic parameter list.

        /// </summary>

        /// <param name="name">The name of the parameter.</param>

        /// <param name="value">The value of the parameter.</param>

        /// <param name="dbType">The type of the parameter.</param>

        /// <param name="direction">The in or out direction of the parameter.</param>

        /// <param name="size">The size of the parameter.</param>

        /// <param name="precision">The precision of the parameter.</param>

        /// <param name="scale">The scale of the parameter.</param>

        public void Add(string name, object value = null, DbType? dbType = null, ParameterDirection? direction = null, int? size = null, byte? precision = null, byte? scale = null)

        {

            parameters[Clean(name)] = new ParamInfo

            {

                Name = name,

                Value = value,

                ParameterDirection = direction ?? ParameterDirection.Input,

                DbType = dbType,

                Size = size,

                Precision = precision,

                Scale = scale

            };

        }

        private static string Clean(string name)

        {

            if (!string.IsNullOrEmpty(name))

            {

                switch (name[0])

                {

                    case '@':

                    case ':':

                    case '?':

                        return name.Substring(1);

                }

            }

            return name;

        }

        void SqlMapper.IDynamicParameters.AddParameters(IDbCommand command, SqlMapper.Identity identity)

        {

            AddParameters(command, identity);

        }

        /// <summary>

        /// If true, the command-text is inspected and only values that are clearly used are included on the connection

        /// </summary>

        public bool RemoveUnused { get; set; }

        internal static bool ShouldSetDbType(DbType? dbType)

            => dbType.HasValue && dbType.GetValueOrDefault() != EnumerableMultiParameter;

        internal static bool ShouldSetDbType(DbType dbType)

            => dbType != EnumerableMultiParameter; // just in case called with non-nullable

        /// <summary>

        /// Add all the parameters needed to the command just before it executes

        /// </summary>

        /// <param name="command">The raw command prior to execution</param>

        /// <param name="identity">Information about the query</param>

        protected void AddParameters(IDbCommand command, SqlMapper.Identity identity)

        {

            var literals = SqlMapper.GetLiteralTokens(identity.sql);

            if (templates != null)

            {

                foreach (var template in templates)

                {

                    var newIdent = identity.ForDynamicParameters(template.GetType());

                    Action<IDbCommand, object> appender;

                    lock (paramReaderCache)

                    {

                        if (!paramReaderCache.TryGetValue(newIdent, out appender))

                        {

                            appender = SqlMapper.CreateParamInfoGenerator(newIdent, true, RemoveUnused, literals);

                            paramReaderCache[newIdent] = appender;

                        }

                    }

                    appender(command, template);

                }

                // The parameters were added to the command, but not the

                // DynamicParameters until now.

                foreach (IDbDataParameter param in command.Parameters)

                {

                    // If someone makes a DynamicParameters with a template,

                    // then explicitly adds a parameter of a matching name,

                    // it will already exist in 'parameters'.

                    if (!parameters.ContainsKey(param.ParameterName))

                    {

                        parameters.Add(param.ParameterName, new ParamInfo

                        {

                            AttachedParam = param,

                            CameFromTemplate = true,

                            DbType = param.DbType,

                            Name = param.ParameterName,

                            ParameterDirection = param.Direction,

                            Size = param.Size,

                            Value = param.Value

                        });

                    }

                }

                // Now that the parameters are added to the command, let's place our output callbacks

                var tmp = outputCallbacks;

                if (tmp != null)

                {

                    foreach (var generator in tmp)

                    {

                        generator();

                    }

                }

            }

            foreach (var param in parameters.Values)

            {

                if (param.CameFromTemplate) continue;

                var dbType = param.DbType;

                var val = param.Value;

                string name = Clean(param.Name);

                var isCustomQueryParameter = val is SqlMapper.ICustomQueryParameter;

                SqlMapper.ITypeHandler handler = null;

                if (dbType == null && val != null && !isCustomQueryParameter)

                {

#pragma warning disable 618

                    dbType = SqlMapper.LookupDbType(val.GetType(), name, true, out handler);

#pragma warning disable 618

                }

                if (isCustomQueryParameter)

                {

                    ((SqlMapper.ICustomQueryParameter)val).AddParameter(command, name);

                }

                else if (dbType == EnumerableMultiParameter)

                {

#pragma warning disable 612, 618

                    SqlMapper.PackListParameters(command, name, val);

#pragma warning restore 612, 618

                }

                else

                {

                    bool add = !command.Parameters.Contains(name);

                    IDbDataParameter p;

                    if (add)

                    {

                        p = command.CreateParameter();

                        p.ParameterName = name;

                    }

                    else

                    {

                        p = (IDbDataParameter)command.Parameters[name];

                    }

                    p.Direction = param.ParameterDirection;

                    if (handler == null)

                    {

#pragma warning disable 0618

                        p.Value = SqlMapper.SanitizeParameterValue(val);

#pragma warning restore 0618

                        if (ShouldSetDbType(dbType) && p.DbType != dbType.GetValueOrDefault())

                        {

                            p.DbType = dbType.GetValueOrDefault();

                        }

                        var s = val as string;

                        if (s?.Length <= DbString.DefaultLength)

                        {

                            p.Size = DbString.DefaultLength;

                        }

                        if (param.Size != null) p.Size = param.Size.Value;

                        if (param.Precision != null) p.Precision = param.Precision.Value;

                        if (param.Scale != null) p.Scale = param.Scale.Value;

                    }

                    else

                    {

                        if (ShouldSetDbType(dbType)) p.DbType = dbType.GetValueOrDefault();

                        if (param.Size != null) p.Size = param.Size.Value;

                        if (param.Precision != null) p.Precision = param.Precision.Value;

                        if (param.Scale != null) p.Scale = param.Scale.Value;

                        handler.SetValue(p, val ?? DBNull.Value);

                    }

                    if (add)

                    {

                        command.Parameters.Add(p);

                    }

                    param.AttachedParam = p;

                }

            }

            // note: most non-privileged implementations would use: this.ReplaceLiterals(command);

            if (literals.Count != 0) SqlMapper.ReplaceLiterals(this, command, literals);

        }

        /// <summary>

        /// All the names of the param in the bag, use Get to yank them out

        /// </summary>

        public IEnumerable<string> ParameterNames => parameters.Select(p => p.Key);

        /// <summary>

        /// Get the value of a parameter

        /// </summary>

        /// <typeparam name="T"></typeparam>

        /// <param name="name"></param>

        /// <returns>The value, note DBNull.Value is not returned, instead the value is returned as null</returns>

        public T Get<T>(string name)

        {

            var paramInfo = parameters[Clean(name)];

            var attachedParam = paramInfo.AttachedParam;

            object val = attachedParam == null ? paramInfo.Value : attachedParam.Value;

            if (val == DBNull.Value)

            {

                if (default(T) != null)

                {

                    throw new ApplicationException("Attempting to cast a DBNull to a non nullable type! Note that out/return parameters will not have updated values until the data stream completes (after the 'foreach' for Query(..., buffered: false), or after the GridReader has been disposed for QueryMultiple)");

                }

                return default;

            }

            return (T)val;

        }

        /// <summary>

        /// Allows you to automatically populate a target property/field from output parameters. It actually

        /// creates an InputOutput parameter, so you can still pass data in.

        /// </summary>

        /// <typeparam name="T"></typeparam>

        /// <param name="target">The object whose property/field you wish to populate.</param>

        /// <param name="expression">A MemberExpression targeting a property/field of the target (or descendant thereof.)</param>

        /// <param name="dbType"></param>

        /// <param name="size">The size to set on the parameter. Defaults to 0, or DbString.DefaultLength in case of strings.</param>

        /// <returns>The DynamicParameters instance</returns>

        public DynamicParameters Output<T>(T target, Expression<Func<T, object>> expression, DbType? dbType = null, int? size = null)

        {

            static void ThrowInvalidChain()

                => throw new InvalidOperationException($"Expression must be a property/field chain off of a(n) {typeof(T).Name} instance");

            // Is it even a MemberExpression?

#pragma warning disable IDE0019 // Use pattern matching - already complex enough

            var lastMemberAccess = expression.Body as MemberExpression;

#pragma warning restore IDE0019 // Use pattern matching

            if (lastMemberAccess == null

                || (!(lastMemberAccess.Member is PropertyInfo)

                    && !(lastMemberAccess.Member is FieldInfo)))

            {

                if (expression.Body.NodeType == ExpressionType.Convert

                    && expression.Body.Type == typeof(object)

                    && ((UnaryExpression)expression.Body).Operand is MemberExpression member)

                {

                    // It's got to be unboxed

                    lastMemberAccess = member;

                }

                else

                {

                    ThrowInvalidChain();

                }

            }

            // Does the chain consist of MemberExpressions leading to a ParameterExpression of type T?

            MemberExpression diving = lastMemberAccess;

            // Retain a list of member names and the member expressions so we can rebuild the chain.

            List<string> names = new List<string>();

            List<MemberExpression> chain = new List<MemberExpression>();

            do

            {

                // Insert the names in the right order so expression

                // "Post.Author.Name" becomes parameter "PostAuthorName"

                names.Insert(0, diving?.Member.Name);

                chain.Insert(0, diving);

#pragma warning disable IDE0019 // use pattern matching; this is fine!

                var constant = diving?.Expression as ParameterExpression;

                diving = diving?.Expression as MemberExpression;

#pragma warning restore IDE0019 // use pattern matching

                if (constant is object && constant.Type == typeof(T))

                {

                    break;

                }

                else if (diving == null

                    || (!(diving.Member is PropertyInfo)

                        && !(diving.Member is FieldInfo)))

                {

                    ThrowInvalidChain();

                }

            }

            while (diving != null);

            var dynamicParamName = string.Concat(names.ToArray());

            // Before we get all emitty...

            var lookup = string.Join("|", names.ToArray());

            var cache = CachedOutputSetters<T>.Cache;

            var setter = (Action<object, DynamicParameters>)cache[lookup];

            if (setter != null) goto MAKECALLBACK;

            // Come on let's build a method, let's build it, let's build it now!

            var dm = new DynamicMethod("ExpressionParam" + Guid.NewGuid().ToString(), null, new[] { typeof(object), GetType() }, true);

            var il = dm.GetILGenerator();

            il.Emit(OpCodes.Ldarg_0); // [object]

            il.Emit(OpCodes.Castclass, typeof(T));    // [T]

            // Count - 1 to skip the last member access

            for (var i = 0; i < chain.Count - 1; i++)

            {

                var member = chain[i].Member;

                if (member is PropertyInfo info)

                {

                    var get = info.GetGetMethod(true);

                    il.Emit(OpCodes.Callvirt, get); // [Member{i}]

                }

                else // Else it must be a field!

                {

                    il.Emit(OpCodes.Ldfld, (FieldInfo)member); // [Member{i}]

                }

            }

            var paramGetter = GetType().GetMethod("Get", new Type[] { typeof(string) }).MakeGenericMethod(lastMemberAccess.Type);

            il.Emit(OpCodes.Ldarg_1); // [target] [DynamicParameters]

            il.Emit(OpCodes.Ldstr, dynamicParamName); // [target] [DynamicParameters] [ParamName]

            il.Emit(OpCodes.Callvirt, paramGetter); // [target] [value], it's already typed thanks to generic method

            // GET READY

            var lastMember = lastMemberAccess.Member;

            if (lastMember is PropertyInfo property)

            {

                var set = property.GetSetMethod(true);

                il.Emit(OpCodes.Callvirt, set); // SET

            }

            else

            {

                il.Emit(OpCodes.Stfld, (FieldInfo)lastMember); // SET

            }

            il.Emit(OpCodes.Ret); // GO

            setter = (Action<object, DynamicParameters>)dm.CreateDelegate(typeof(Action<object, DynamicParameters>));

            lock (cache)

            {

                cache[lookup] = setter;

            }

            // Queue the preparation to be fired off when adding parameters to the DbCommand

            MAKECALLBACK:

            (outputCallbacks ??= new List<Action>()).Add(() =>

            {

                // Finally, prep the parameter and attach the callback to it

                var targetMemberType = lastMemberAccess?.Type;

                int sizeToSet = (!size.HasValue && targetMemberType == typeof(string)) ? DbString.DefaultLength : size ?? 0;

                if (parameters.TryGetValue(dynamicParamName, out ParamInfo parameter))

                {

                    parameter.ParameterDirection = parameter.AttachedParam.Direction = ParameterDirection.InputOutput;

                    if (parameter.AttachedParam.Size == 0)

                    {

                        parameter.Size = parameter.AttachedParam.Size = sizeToSet;

                    }

                }

                else

                {

                    // CameFromTemplate property would not apply here because this new param

                    // Still needs to be added to the command

                    Add(dynamicParamName, expression.Compile().Invoke(target), dbType, ParameterDirection.InputOutput, sizeToSet);

                }

                parameter = parameters[dynamicParamName];

                parameter.OutputCallback = setter;

                parameter.OutputTarget = target;

            });

            return this;

        }

        private List<Action> outputCallbacks;

        void SqlMapper.IParameterCallbacks.OnCompleted()

        {

            foreach (var param in from p in parameters select p.Value)

            {

                param.OutputCallback?.Invoke(param.OutputTarget, this);

            }

        }

    }

}

using System;

using System.Collections.Generic;

using System.Data;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace Markus.Service.DataBase

{

    public static class dbTypeConvert

    {

        private static readonly List<TypeItem> _typeMap;

        //private static Dictionary<Type, DbType> _typeMap;

        //private static Dictionary<Type, NpgsqlTypes.NpgsqlDbType> _npgSQLtypeMap;

        static dbTypeConvert()

        {

            _typeMap = new List<TypeItem>();

            _typeMap.Add(new TypeItem{ PostgreSQL = "int8",NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Bigint,DbType = DbType.Int64,Type = typeof(Int64)});

            _typeMap.Add(new TypeItem{ PostgreSQL = "bool",NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Boolean,DbType = DbType.Boolean,Type = typeof(Boolean)});

            _typeMap.Add(new TypeItem{ PostgreSQL = "bytea",NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Bytea,DbType = DbType.Binary,Type = typeof(Byte[])});

            _typeMap.Add(new TypeItem{ PostgreSQL = "date",NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Date,DbType = DbType.Date,Type = typeof(DateTime)});

            _typeMap.Add(new TypeItem{ PostgreSQL = "float8",NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Double,DbType = DbType.Double,Type = typeof(Double)});

            _typeMap.Add(new TypeItem{ PostgreSQL = "int4",NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer,DbType = DbType.Int32,Type = typeof(Int32)});

            _typeMap.Add(new TypeItem{ PostgreSQL = "money",NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Money,DbType = DbType.Decimal,Type = typeof(Decimal)});

            _typeMap.Add(new TypeItem{ PostgreSQL = "numeric",NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Numeric,DbType = DbType.Decimal,Type = typeof(Decimal)});

            _typeMap.Add(new TypeItem{ PostgreSQL = "float4",NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Real,DbType = DbType.Single,Type = typeof(Single)});

            _typeMap.Add(new TypeItem{ PostgreSQL = "int2",NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Smallint,DbType = DbType.Int16,Type = typeof(Int16)});

            _typeMap.Add(new TypeItem{ PostgreSQL = "text",NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Text,DbType = DbType.String,Type = typeof(String)});

            _typeMap.Add(new TypeItem{ PostgreSQL = "time",NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Time,DbType = DbType.Time,Type = typeof(DateTime)});

            _typeMap.Add(new TypeItem{ PostgreSQL = "timetz",NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Time,DbType = DbType.Time,Type = typeof(DateTime)});

            _typeMap.Add(new TypeItem{ PostgreSQL = "timestamp",NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Timestamp,DbType = DbType.DateTime,Type = typeof(DateTime)});

            _typeMap.Add(new TypeItem{ PostgreSQL = "timestamptz",NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.TimestampTz,DbType = DbType.DateTime,Type = typeof(DateTime)});

            _typeMap.Add(new TypeItem{ PostgreSQL = "interval",NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Interval,DbType = DbType.Object,Type = typeof(TimeSpan)});

            _typeMap.Add(new TypeItem{ PostgreSQL = "varchar",NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar,DbType = DbType.String,Type = typeof(String)});

            _typeMap.Add(new TypeItem{ PostgreSQL = "inet",NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Inet,DbType = DbType.Object,Type = typeof(System.Net.IPAddress) });

            _typeMap.Add(new TypeItem{ PostgreSQL = "bit",NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Bit,DbType = DbType.Boolean,Type = typeof(Boolean)});

            _typeMap.Add(new TypeItem{ PostgreSQL = "uuid",NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Uuid,DbType = DbType.Guid,Type = typeof(Guid)});

            _typeMap.Add(new TypeItem{ PostgreSQL = "array",NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Array,DbType = DbType.Object,Type = typeof(Array)});

            //DefaultMap();

            //npgSQLMap();

        }

        public static NpgsqlTypes.NpgsqlDbType? GetNpgsqlDbType(DbType dbType)

        {

            var type = _typeMap.Find(x => x.DbType == dbType);

            if (type == null)

            {

                return null;

            }

            else

            {

                return type.NpgsqlDbType;

            }

        }

        public static NpgsqlTypes.NpgsqlDbType? GetNpgsqlDbType(Type csharpType)

        {

            var type = _typeMap.Find(x => x.Type == csharpType);

            if (type == null)

            {

                return null;

            }

            else

            {

                return type.NpgsqlDbType;

            }

        }

        //private static void DefaultMap()

        //{ 

        //     _typeMap = new Dictionary<Type, DbType>();

        //    _typeMap[typeof(byte)] = DbType.Byte;

        //    _typeMap[typeof(sbyte)] = DbType.SByte;

        //    _typeMap[typeof(short)] = DbType.Int16;

        //    _typeMap[typeof(ushort)] = DbType.UInt16;

        //    _typeMap[typeof(int)] = DbType.Int32;

        //    _typeMap[typeof(uint)] = DbType.UInt32;

        //    _typeMap[typeof(long)] = DbType.Int64;

        //    _typeMap[typeof(ulong)] = DbType.UInt64;

        //    _typeMap[typeof(float)] = DbType.Single;

        //    _typeMap[typeof(double)] = DbType.Double;

        //    _typeMap[typeof(decimal)] = DbType.Decimal;

        //    _typeMap[typeof(bool)] = DbType.Boolean;

        //    _typeMap[typeof(string)] = DbType.String;

        //    _typeMap[typeof(char)] = DbType.StringFixedLength;

        //    _typeMap[typeof(Guid)] = DbType.Guid;

        //    _typeMap[typeof(DateTime)] = DbType.DateTime;

        //    _typeMap[typeof(DateTimeOffset)] = DbType.DateTimeOffset;

        //    _typeMap[typeof(byte[])] = DbType.Binary;

        //    _typeMap[typeof(byte?)] = DbType.Byte;

        //    _typeMap[typeof(sbyte?)] = DbType.SByte;

        //    _typeMap[typeof(short?)] = DbType.Int16;

        //    _typeMap[typeof(ushort?)] = DbType.UInt16;

        //    _typeMap[typeof(int?)] = DbType.Int32;

        //    _typeMap[typeof(uint?)] = DbType.UInt32;

        //    _typeMap[typeof(long?)] = DbType.Int64;

        //    _typeMap[typeof(ulong?)] = DbType.UInt64;

        //    _typeMap[typeof(float?)] = DbType.Single;

        //    _typeMap[typeof(double?)] = DbType.Double;

        //    _typeMap[typeof(decimal?)] = DbType.Decimal;

        //    _typeMap[typeof(bool?)] = DbType.Boolean;

        //    _typeMap[typeof(char?)] = DbType.StringFixedLength;

        //    _typeMap[typeof(Guid?)] = DbType.Guid;

        //    _typeMap[typeof(DateTime?)] = DbType.DateTime;

        //    _typeMap[typeof(DateTimeOffset?)] = DbType.DateTimeOffset;

        //    _typeMap[typeof(DBNull)] = DbType.String;

        //}

        //private static void npgSQLMap()

        //{

        //    _npgSQLtypeMap = new Dictionary<Type, NpgsqlTypes.NpgsqlDbType>();

        //    _npgSQLtypeMap[typeof(Int64)] = NpgsqlTypes.NpgsqlDbType.Bigint;

        //    _npgSQLtypeMap[typeof(bool)] =  NpgsqlTypes.NpgsqlDbType.Smallint;

        //    _npgSQLtypeMap[typeof(byte[])] = NpgsqlTypes.NpgsqlDbType.Bytea;

        //    _npgSQLtypeMap[typeof(DateTime)] = NpgsqlTypes.NpgsqlDbType.Date;

        //    _npgSQLtypeMap[typeof(double)] = NpgsqlTypes.NpgsqlDbType.Double;

        //    _npgSQLtypeMap[typeof(Int32)] = NpgsqlTypes.NpgsqlDbType.Integer;

        //    _npgSQLtypeMap[typeof(decimal)] = NpgsqlTypes.NpgsqlDbType.Money;

        //    _npgSQLtypeMap[typeof(decimal)] = NpgsqlTypes.NpgsqlDbType.Numeric;

        //    _npgSQLtypeMap[typeof(Single)] = NpgsqlTypes.NpgsqlDbType.Real;

        //    _npgSQLtypeMap[typeof(Int16)] = NpgsqlTypes.NpgsqlDbType.Smallint;

        //    _npgSQLtypeMap[typeof(string)] = NpgsqlTypes.NpgsqlDbType.Text;

        //    _npgSQLtypeMap[typeof(DateTime)] = NpgsqlTypes.NpgsqlDbType.Time;

        //    _npgSQLtypeMap[typeof(DateTime)] = NpgsqlTypes.NpgsqlDbType.Time;

        //    _npgSQLtypeMap[typeof(DateTime)] = NpgsqlTypes.NpgsqlDbType.Timestamp;

        //    _npgSQLtypeMap[typeof(DateTime)] = NpgsqlTypes.NpgsqlDbType.TimestampTz;

        //    _npgSQLtypeMap[typeof(TimeSpan)] = NpgsqlTypes.NpgsqlDbType.Interval;

        //    _npgSQLtypeMap[typeof(string)] = NpgsqlTypes.NpgsqlDbType.Varchar;

        //    _npgSQLtypeMap[typeof(System.Net.IPAddress)] = NpgsqlTypes.NpgsqlDbType.Inet;

        //    _npgSQLtypeMap[typeof(bool)] = NpgsqlTypes.NpgsqlDbType.Bit;

        //    _npgSQLtypeMap[typeof(Guid)] = NpgsqlTypes.NpgsqlDbType.Uuid;

        //    _npgSQLtypeMap[typeof(Array)] = NpgsqlTypes.NpgsqlDbType.Array;

        //}

        private class TypeItem

        {

            public string PostgreSQL { get; set; }

            public NpgsqlTypes.NpgsqlDbType NpgsqlDbType { get; set; }

            public System.Data.DbType DbType { get; set; }

            public Type Type { get; set;}

        }

    }

}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace Markus.Service.DataBase.Test

{

    public class TestBaseMSSQL

    {

        public string ConnectionStr = "Data Source=cloud.devdoftech.co.kr,7777;Initial Catalog=markus;Persist Security Info=True;User ID=doftech;Password=dof1073#";

        public Markus.Service.DataBase.DBMSType dbtype = DBMSType.MSSQL;

        public string ProjectNo = "000000";

        public string docUri = "test://test";

    }

}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace Markus.Service.DataBase.Test

{

    public class TestBasePG

    {

        public string ConnectionStr = "Host=192.168.0.37;Port=49164;Database=markus;User ID=doftech;Password=dof1073#";

        public Markus.Service.DataBase.DBMSType dbtype = DBMSType.POSTGRESQL;

        public string ProjectNo = "000000";

        public string docUri = "test://test";

    }

}

<?xml version="1.0" encoding="utf-8"?>

<!--

    This file is automatically generated by Visual Studio .Net. It is 

    used to store generic object data source configuration information.  

    Renaming the file extension or editing the content of this file may   

    cause the file to be unrecognizable by the program.

-->

<GenericObjectDataSource DisplayName="ConvertDoc" Version="1.0" xmlns="urn:schemas-microsoft-com:xml-msdatasource">

   <TypeInfo>Markus.Service.DataBase.Entities.ConvertDoc, Markus.Service.DataBase.Dapper, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null</TypeInfo>

</GenericObjectDataSource>