private static void ConvertListParameterByValue(IDataParameter parameter, IEnumerable list, IDbCommand command) { // we are going to replace the current parameter with a list of new parameters command.Parameters.Remove(parameter); string parameterName = parameter.ParameterName; var count = 0; bool isString = list is IEnumerable <string>; // add a parameter for each item foreach (var item in list) { count++; // create the parameter for the item var listParam = command.CreateParameter(); listParam.ParameterName = parameterName + count; listParam.Value = item ?? DBNull.Value; listParam.DbType = ColumnMapping.MapParameterDataType( isString ? typeof(string) : (item?.GetType() ?? typeof(object)), command, parameter, listParam.DbType); // if we are dealing with strings, add the length of the string if (isString && item != null) { listParam.Size = GetStringParameterLength(item as string, command.CommandType, listParam.DbType); } command.Parameters.Add(listParam); } if (count == 0) { command.CommandText = Regex.Replace(command.CommandText, _parameterPrefixRegex + Regex.Escape(parameterName), "NULL", RegexOptions.IgnoreCase); } else { command.CommandText = Regex.Replace( command.CommandText, _parameterPrefixRegex + Regex.Escape(parameterName), match => { var grp = match.Value; var sb = new StringBuilder(); // append the parameters sb.Append(grp).Append(1); for (int i = 2; i <= count; i++) { sb.Append(',').Append(grp).Append(i); } return(sb.ToString()); }, RegexOptions.IgnoreCase); } }
private static Action <IDbCommand, object> CreateClassInputParameterGenerator(IDbCommand command, Type type) { var provider = InsightDbProvider.For(command); var parameters = provider.DeriveParameters(command); // special case if the parameters object is an IEnumerable or Array // look for the parameter that is a Structured object and pass the array to the TVP // note that string supports ienumerable, so exclude atomic types var enumerable = type.GetInterfaces().FirstOrDefault(i => i.GetTypeInfo().IsGenericType&& i.GetGenericTypeDefinition() == typeof(IEnumerable <>)); if (enumerable != null && type != typeof(string) && parameters.OfType <IDataParameter>().Where(p => p.Direction.HasFlag(ParameterDirection.Input)).Count() == 1) { return((IDbCommand cmd, object o) => { // don't use the provider above. The command may be unwrapped by the time we get back here var tableParameter = InsightDbProvider.For(cmd).CloneParameter(cmd, parameters.OfType <IDataParameter>().Single(p => p.Direction.HasFlag(ParameterDirection.Input))); cmd.Parameters.Add(tableParameter); ListParameterHelper.ConvertListParameter(tableParameter, o, cmd); }); } // get the mapping of the properties for the type var mappings = ColumnMapping.MapParameters(type, command, parameters); // start creating a dynamic method Type typeOwner = type.HasElementType ? type.GetElementType() : type; var dm = new DynamicMethod(String.Format(CultureInfo.InvariantCulture, "CreateInputParameters-{0}", Guid.NewGuid()), null, new[] { typeof(IDbCommand), typeof(object) }, typeOwner, true); var il = dm.GetILGenerator(); // copy the parameters into the command object var parametersLocal = il.DeclareLocal(typeof(IDataParameter[])); StaticFieldStorage.EmitLoad(il, provider); il.Emit(OpCodes.Ldarg_0); StaticFieldStorage.EmitLoad(il, parameters); il.Emit(OpCodes.Call, typeof(InsightDbProvider).GetMethod("CopyParameters", BindingFlags.NonPublic | BindingFlags.Instance)); il.Emit(OpCodes.Stloc, parametersLocal); // go through all of the mappings for (int i = 0; i < mappings.Count; i++) { var mapping = mappings[i]; var dbParameter = parameters[i]; // if there is no mapping for the parameter if (mapping == null) { // sql will silently eat table parameters that are not specified, and that can be difficult to debug if (provider.IsTableValuedParameter(command, dbParameter)) { throw new InvalidOperationException(String.Format(CultureInfo.InvariantCulture, "Table parameter {0} must be specified", dbParameter.ParameterName)); } // unspecified input parameters get skipped if (dbParameter.Direction == ParameterDirection.Input) { parameters[i] = null; } continue; } var memberType = mapping.Member.MemberType; var serializer = mapping.Serializer; // get the parameter il.Emit(OpCodes.Ldloc, parametersLocal); il.Emit(OpCodes.Ldc_I4, i); il.Emit(OpCodes.Ldelem, typeof(IDataParameter)); // look up the best type to use for the parameter DbType sqlType = LookupDbType(memberType, serializer, dbParameter.DbType); // give the provider an opportunity to fix up the template parameter (e.g. set UDT type names) provider.FixupParameter(command, dbParameter, sqlType, memberType, mapping.Member.SerializationMode); // give a chance to override the best guess parameter DbType overriddenSqlType = sqlType; if (sqlType != DbTypeEnumerable) { overriddenSqlType = ColumnMapping.MapParameterDataType(memberType, command, dbParameter, sqlType); } /////////////////////////////////////////////////////////////// // We have a parameter, start handling all of the other types /////////////////////////////////////////////////////////////// if (overriddenSqlType != sqlType) { sqlType = overriddenSqlType; dbParameter.DbType = sqlType; } /////////////////////////////////////////////////////////////// // Get the value from the object onto the stack /////////////////////////////////////////////////////////////// il.Emit(OpCodes.Ldarg_1); if (type.GetTypeInfo().IsValueType) { il.Emit(OpCodes.Unbox_Any, type); } /////////////////////////////////////////////////////////////// // Special case support for enumerables. If the type is -1 (our workaround, then call the list parameter method) /////////////////////////////////////////////////////////////// if (sqlType == DbTypeEnumerable) { // we have the parameter and the value as object, add the command ClassPropInfo.EmitGetValue(type, mapping.PathToMember, il); il.Emit(OpCodes.Ldarg_0); il.Emit(OpCodes.Call, typeof(ListParameterHelper).GetMethod("ConvertListParameter", BindingFlags.Static | BindingFlags.NonPublic)); continue; } Label readyToSetLabel = il.DefineLabel(); ClassPropInfo.EmitGetValue(type, mapping.PathToMember, il, readyToSetLabel); // special conversions for timespan to datetime if ((sqlType == DbType.Time && dbParameter.DbType != DbType.Time) || (dbParameter.DbType == DbType.DateTime || dbParameter.DbType == DbType.DateTime2 || dbParameter.DbType == DbType.DateTimeOffset)) { IlHelper.EmitLdInt32(il, (int)dbParameter.DbType); il.Emit(OpCodes.Call, typeof(TypeConverterGenerator).GetMethod("ObjectToSqlDateTime")); } // if it's class type, boxed value type (in an object), or nullable, then we have to check for null var nullableUnderlyingType = Nullable.GetUnderlyingType(memberType); if (!memberType.GetTypeInfo().IsValueType || nullableUnderlyingType != null) { Label notNull = il.DefineLabel(); // check to see if it's not null il.Emit(OpCodes.Dup); il.Emit(OpCodes.Brtrue, notNull); // it's null. replace the value with DbNull il.Emit(OpCodes.Pop); il.Emit(OpCodes.Ldsfld, _dbNullValue); // value is set to null. ready to set the property. il.Emit(OpCodes.Br, readyToSetLabel); // we know the value is not null il.MarkLabel(notNull); } // some providers (notably npgsql > 4.0) don't convert enums to ints, so we do it for them if ((memberType != null && memberType.GetTypeInfo() != null && memberType.GetTypeInfo().IsEnum) || (nullableUnderlyingType != null && nullableUnderlyingType.GetTypeInfo() != null && nullableUnderlyingType.GetTypeInfo().IsEnum)) { var enumType = nullableUnderlyingType ?? memberType; // ClassPropInfo.EmitGetValue has the enum boxed, so unbox, cast, and re-box switch (dbParameter.DbType) { case DbType.Int16: il.Emit(OpCodes.Unbox_Any, enumType); il.Emit(OpCodes.Conv_I2); il.Emit(OpCodes.Box, typeof(Int16)); break; case DbType.Int32: il.Emit(OpCodes.Unbox_Any, enumType); il.Emit(OpCodes.Conv_I4); il.Emit(OpCodes.Box, typeof(Int32)); break; case DbType.Int64: il.Emit(OpCodes.Unbox_Any, enumType); il.Emit(OpCodes.Conv_I8); il.Emit(OpCodes.Box, typeof(Int64)); break; } } /////////////////////////////////////////////////////////////// // if this is a linq binary, convert it to a byte array /////////////////////////////////////////////////////////////// if (memberType == TypeHelper.LinqBinaryType) { il.Emit(OpCodes.Callvirt, TypeHelper.LinqBinaryToArray); } else if (memberType == typeof(XmlDocument)) { // we are sending up an XmlDocument. ToString just returns the classname, so use the outerxml. il.Emit(OpCodes.Callvirt, memberType.GetProperty("OuterXml").GetGetMethod()); } else if (memberType == typeof(XDocument)) { // we are sending up an XDocument. Use ToString. il.Emit(OpCodes.Callvirt, memberType.GetMethod("ToString", new Type[] { })); } else if (serializer != null && serializer.CanSerialize(memberType, sqlType)) { il.EmitLoadType(memberType); StaticFieldStorage.EmitLoad(il, serializer); il.Emit(OpCodes.Call, typeof(DbParameterGenerator).GetMethod("SerializeParameterValue", BindingFlags.NonPublic | BindingFlags.Static)); } /////////////////////////////////////////////////////////////// // p.Value = value /////////////////////////////////////////////////////////////// // push parameter is at top of method // value is above il.MarkLabel(readyToSetLabel); if (memberType == typeof(string)) { il.Emit(OpCodes.Call, typeof(DbParameterGenerator).GetMethod("SetParameterStringValue", BindingFlags.NonPublic | BindingFlags.Static)); } else if ((memberType == typeof(Guid?) || (memberType == typeof(Guid))) && dbParameter.DbType != DbType.Guid && command.CommandType == CommandType.StoredProcedure) { il.Emit(OpCodes.Call, typeof(DbParameterGenerator).GetMethod("SetParameterGuidValue", BindingFlags.NonPublic | BindingFlags.Static)); } else { il.Emit(OpCodes.Callvirt, _iDataParameterSetValue); } } il.Emit(OpCodes.Ret); return((Action <IDbCommand, object>)dm.CreateDelegate(typeof(Action <IDbCommand, object>))); }