markus / ConvertService / ServiceBase / Markus.Service.DataBase.Dapper / MixParameters.cs @ ab7fe8c0
이력 | 보기 | 이력해설 | 다운로드 (10.8 KB)
| 1 | d952e1d5 | taeseongkim | using Dapper; |
|---|---|---|---|
| 2 | using Npgsql; |
||
| 3 | using NpgsqlTypes; |
||
| 4 | using System; |
||
| 5 | using System.Collections.Generic; |
||
| 6 | using System.Data; |
||
| 7 | using System.Linq; |
||
| 8 | |||
| 9 | namespace Markus.Service.DataBase |
||
| 10 | {
|
||
| 11 | internal class MixParameters : SqlMapper.IDynamicParameters, SqlMapper.IParameterLookup |
||
| 12 | {
|
||
| 13 | public MixParameters(DBMSType dbms) |
||
| 14 | {
|
||
| 15 | DBMS = dbms; |
||
| 16 | } |
||
| 17 | |||
| 18 | private DBMSType DBMS = DBMSType.MSSQL; |
||
| 19 | |||
| 20 | internal const DbType EnumerableMultiParameter = (DbType)(-1); |
||
| 21 | private static readonly Dictionary<SqlMapper.Identity, Action<IDbCommand, object>> paramReaderCache = new Dictionary<SqlMapper.Identity, Action<IDbCommand, object>>(); |
||
| 22 | private readonly Dictionary<string, ParamInfo> _parameters = new Dictionary<string, ParamInfo>(); |
||
| 23 | private List<object> templates; |
||
| 24 | |||
| 25 | /// <summary> |
||
| 26 | /// All the names of the param in the bag, use Get to yank them out |
||
| 27 | /// </summary> |
||
| 28 | public IEnumerable<string> ParameterNames |
||
| 29 | {
|
||
| 30 | get { return _parameters.Select(p => p.Key); }
|
||
| 31 | } |
||
| 32 | |||
| 33 | public int ParameterCount |
||
| 34 | {
|
||
| 35 | get { return _parameters.Count; }
|
||
| 36 | } |
||
| 37 | |||
| 38 | object SqlMapper.IParameterLookup.this[string name] => |
||
| 39 | _parameters.TryGetValue(name, out ParamInfo param) ? param.Value : null; |
||
| 40 | |||
| 41 | internal static bool ShouldSetDbType(DbType? dbType) |
||
| 42 | => dbType.HasValue && dbType.GetValueOrDefault() != EnumerableMultiParameter; |
||
| 43 | |||
| 44 | internal static bool ShouldSetDbType(DbType dbType) |
||
| 45 | => dbType != EnumerableMultiParameter; // just in case called with non-nullable |
||
| 46 | |||
| 47 | void SqlMapper.IDynamicParameters.AddParameters(IDbCommand command, SqlMapper.Identity identity) |
||
| 48 | {
|
||
| 49 | if (templates != null) |
||
| 50 | foreach (var template in templates) |
||
| 51 | {
|
||
| 52 | var newIdent = identity.ForDynamicParameters(template.GetType()); |
||
| 53 | Action<IDbCommand, object> appender; |
||
| 54 | |||
| 55 | lock (paramReaderCache) |
||
| 56 | {
|
||
| 57 | if (!paramReaderCache.TryGetValue(newIdent, out appender)) |
||
| 58 | {
|
||
| 59 | appender = SqlMapper.CreateParamInfoGenerator(newIdent, false, true); |
||
| 60 | paramReaderCache[newIdent] = appender; |
||
| 61 | } |
||
| 62 | } |
||
| 63 | |||
| 64 | appender(command, template); |
||
| 65 | } |
||
| 66 | |||
| 67 | switch (DBMS) |
||
| 68 | {
|
||
| 69 | case DBMSType.POSTGRESQL: |
||
| 70 | case DBMSType.MSSQL: |
||
| 71 | msSqlAddParamaters(command, identity); |
||
| 72 | break; |
||
| 73 | //case DBMSType.POSTGRESQL: |
||
| 74 | // npgAddParamaters(command, identity); |
||
| 75 | //break; |
||
| 76 | default: |
||
| 77 | break; |
||
| 78 | } |
||
| 79 | } |
||
| 80 | |||
| 81 | private void msSqlAddParamaters(IDbCommand command, SqlMapper.Identity identity) |
||
| 82 | {
|
||
| 83 | foreach (var param in _parameters.Values) |
||
| 84 | {
|
||
| 85 | if (param.CameFromTemplate) continue; |
||
| 86 | |||
| 87 | var dbType = param.DbType; |
||
| 88 | var val = param.Value; |
||
| 89 | bede677a | GeunHo Song | string name = param.Name.ToLower(); |
| 90 | |||
| 91 | if (command.CommandType == CommandType.StoredProcedure) |
||
| 92 | name = Clean(param.Name); |
||
| 93 | |||
| 94 | var isCustomQueryParameter = val is SqlMapper.ICustomQueryParameter; |
||
| 95 | d952e1d5 | taeseongkim | |
| 96 | SqlMapper.ITypeHandler handler = null; |
||
| 97 | if (dbType == null && val != null && !isCustomQueryParameter) |
||
| 98 | {
|
||
| 99 | #pragma warning disable 618 |
||
| 100 | dbType = SqlMapper.LookupDbType(val.GetType(), name, true, out handler); |
||
| 101 | #pragma warning disable 618 |
||
| 102 | } |
||
| 103 | if (isCustomQueryParameter) |
||
| 104 | {
|
||
| 105 | ((SqlMapper.ICustomQueryParameter)val).AddParameter(command, name); |
||
| 106 | } |
||
| 107 | else if (dbType == EnumerableMultiParameter) |
||
| 108 | {
|
||
| 109 | #pragma warning disable 612, 618 |
||
| 110 | SqlMapper.PackListParameters(command, name, val); |
||
| 111 | #pragma warning restore 612, 618 |
||
| 112 | } |
||
| 113 | else |
||
| 114 | {
|
||
| 115 | bool add = !command.Parameters.Contains(name); |
||
| 116 | IDbDataParameter p; |
||
| 117 | if (add) |
||
| 118 | {
|
||
| 119 | p = command.CreateParameter(); |
||
| 120 | p.ParameterName = name; |
||
| 121 | } |
||
| 122 | else |
||
| 123 | {
|
||
| 124 | p = (IDbDataParameter)command.Parameters[name]; |
||
| 125 | } |
||
| 126 | |||
| 127 | p.Direction = param.ParameterDirection; |
||
| 128 | if (handler == null) |
||
| 129 | {
|
||
| 130 | #pragma warning disable 0618 |
||
| 131 | p.Value = SqlMapper.SanitizeParameterValue(val); |
||
| 132 | #pragma warning restore 0618 |
||
| 133 | if (ShouldSetDbType(dbType) && p.DbType != dbType.GetValueOrDefault()) |
||
| 134 | {
|
||
| 135 | p.DbType = dbType.GetValueOrDefault(); |
||
| 136 | } |
||
| 137 | var s = val as string; |
||
| 138 | if (s?.Length <= DbString.DefaultLength) |
||
| 139 | {
|
||
| 140 | p.Size = DbString.DefaultLength; |
||
| 141 | } |
||
| 142 | if (param.Size != null) p.Size = param.Size.Value; |
||
| 143 | if (param.Precision != null) p.Precision = param.Precision.Value; |
||
| 144 | if (param.Scale != null) p.Scale = param.Scale.Value; |
||
| 145 | } |
||
| 146 | else |
||
| 147 | {
|
||
| 148 | if (ShouldSetDbType(dbType)) p.DbType = dbType.GetValueOrDefault(); |
||
| 149 | if (param.Size != null) p.Size = param.Size.Value; |
||
| 150 | if (param.Precision != null) p.Precision = param.Precision.Value; |
||
| 151 | if (param.Scale != null) p.Scale = param.Scale.Value; |
||
| 152 | handler.SetValue(p, val ?? DBNull.Value); |
||
| 153 | } |
||
| 154 | |||
| 155 | if (add) |
||
| 156 | {
|
||
| 157 | command.Parameters.Add(p); |
||
| 158 | } |
||
| 159 | param.AttachedParam = p; |
||
| 160 | } |
||
| 161 | } |
||
| 162 | |||
| 163 | // note: most non-privileged implementations would use: this.ReplaceLiterals(command); |
||
| 164 | //if (literals.Count != 0) SqlMapper.ReplaceLiterals(this, command); |
||
| 165 | } |
||
| 166 | |||
| 167 | private void npgAddParamaters(IDbCommand command, SqlMapper.Identity identity) |
||
| 168 | {
|
||
| 169 | foreach (var param in _parameters.Values) |
||
| 170 | {
|
||
| 171 | if (((NpgsqlCommand)command).Parameters.Contains(param.Name)) |
||
| 172 | {
|
||
| 173 | ((NpgsqlCommand)command).Parameters.Remove(param.Name); |
||
| 174 | } |
||
| 175 | |||
| 176 | var p = ParamInfoToNpgParam(param); |
||
| 177 | p.ParameterName = param.Name; |
||
| 178 | |||
| 179 | command.Parameters.Add(p); |
||
| 180 | |||
| 181 | param.AttachedParam = p; |
||
| 182 | } |
||
| 183 | } |
||
| 184 | |||
| 185 | private NpgsqlParameter ParamInfoToNpgParam(ParamInfo param) |
||
| 186 | {
|
||
| 187 | NpgsqlParameter p = new NpgsqlParameter(); |
||
| 188 | |||
| 189 | var val = param.Value; |
||
| 190 | p.Value = val ?? DBNull.Value; |
||
| 191 | p.Direction = param.ParameterDirection; |
||
| 192 | if (param.Size != null) p.Size = param.Size.Value; |
||
| 193 | if (param.NpgsqlType != null) |
||
| 194 | {
|
||
| 195 | p.NpgsqlDbType = param.NpgsqlType.Value; |
||
| 196 | } |
||
| 197 | else if (param.Value != null) |
||
| 198 | {
|
||
| 199 | var npgType = dbTypeConvert.GetNpgsqlDbType(param.Value.GetType()).Value; |
||
| 200 | |||
| 201 | if (npgType == NpgsqlDbType.Text) |
||
| 202 | {
|
||
| 203 | //npgType = NpgsqlDbType.Varchar; |
||
| 204 | } |
||
| 205 | |||
| 206 | p.NpgsqlDbType = npgType; |
||
| 207 | } |
||
| 208 | else if (param.DbType != null) |
||
| 209 | {
|
||
| 210 | var npgType = dbTypeConvert.GetNpgsqlDbType(param.DbType.Value).Value; |
||
| 211 | |||
| 212 | if (npgType == NpgsqlDbType.Text) |
||
| 213 | {
|
||
| 214 | //npgType = NpgsqlDbType.Varchar; |
||
| 215 | } |
||
| 216 | |||
| 217 | p.NpgsqlDbType = npgType; |
||
| 218 | } |
||
| 219 | else |
||
| 220 | {
|
||
| 221 | throw new Exception($"{param.Name} npgSQL type을 알 수 없습니다.");
|
||
| 222 | } |
||
| 223 | |||
| 224 | return p; |
||
| 225 | } |
||
| 226 | |||
| 227 | private static string Clean(string name) |
||
| 228 | {
|
||
| 229 | if (!string.IsNullOrEmpty(name)) |
||
| 230 | {
|
||
| 231 | switch (name[0]) |
||
| 232 | {
|
||
| 233 | case '@': |
||
| 234 | case ':': |
||
| 235 | case '?': |
||
| 236 | return name.Substring(1); |
||
| 237 | } |
||
| 238 | } |
||
| 239 | return name; |
||
| 240 | } |
||
| 241 | internal void Add(string name,object value = null,NpgsqlTypes.NpgsqlDbType? npgsqlDbType = null, DbType? dbType = null, ParameterDirection? direction = null, int? size = null) |
||
| 242 | {
|
||
| 243 | if(DBMS == DBMSType.MSSQL) |
||
| 244 | {
|
||
| 245 | switch (direction) |
||
| 246 | {
|
||
| 247 | case ParameterDirection.Output: |
||
| 248 | case ParameterDirection.InputOutput: |
||
| 249 | if(size == null) |
||
| 250 | {
|
||
| 251 | size = 50; |
||
| 252 | //throw new Exception($"{name}는 MSSQL인 경우 direction이 {direction.ToString()}이므로 size를 지정하여야 합니다.");
|
||
| 253 | } |
||
| 254 | break; |
||
| 255 | default: |
||
| 256 | break; |
||
| 257 | } |
||
| 258 | } |
||
| 259 | |||
| 260 | _parameters[Clean(name)] = new ParamInfo |
||
| 261 | {
|
||
| 262 | Name = name, |
||
| 263 | Value = value, |
||
| 264 | ParameterDirection = direction ?? ParameterDirection.Input, |
||
| 265 | DbType = dbType, |
||
| 266 | NpgsqlType = npgsqlDbType, |
||
| 267 | Size = size |
||
| 268 | }; |
||
| 269 | } |
||
| 270 | |||
| 271 | internal T Get<T>(string name) |
||
| 272 | {
|
||
| 273 | var val = _parameters[name].AttachedParam.Value; |
||
| 274 | if (val == DBNull.Value) |
||
| 275 | {
|
||
| 276 | if (default(T) != null) |
||
| 277 | throw new ApplicationException("Attempting to cast a DBNull to a non nullable type!");
|
||
| 278 | return default(T); |
||
| 279 | } |
||
| 280 | |||
| 281 | return (T)val; |
||
| 282 | } |
||
| 283 | |||
| 284 | |||
| 285 | public NpgsqlParameter Get(string name) |
||
| 286 | {
|
||
| 287 | return ParamInfoToNpgParam(_parameters[name]); |
||
| 288 | } |
||
| 289 | |||
| 290 | private class ParamInfo |
||
| 291 | {
|
||
| 292 | public string Name { get; set; }
|
||
| 293 | public object Value { get; set; }
|
||
| 294 | public ParameterDirection ParameterDirection { get; set; }
|
||
| 295 | public DbType? DbType { get; set; }
|
||
| 296 | |||
| 297 | public NpgsqlTypes.NpgsqlDbType? NpgsqlType { get; set; }
|
||
| 298 | public int? Size { get; set; }
|
||
| 299 | public IDbDataParameter AttachedParam { get; set; }
|
||
| 300 | internal Action<object, DynamicParameters> OutputCallback { get; set; }
|
||
| 301 | internal object OutputTarget { get; set; }
|
||
| 302 | internal bool CameFromTemplate { get; set; }
|
||
| 303 | |||
| 304 | public byte? Precision { get; set; }
|
||
| 305 | public byte? Scale { get; set; }
|
||
| 306 | } |
||
| 307 | } |
||
| 308 | } |
