Custom CLRType to SqlDbType Mapping

Right now I’m working on implementing our own ‘LocalDate’ object because the built in ‘DateTime’  is causing several issues for us – it has a time component, Mongo DB automatically converts it to Utc when you save. All of that prompted us to come up with a custom object ‘LocalDate’.

The first requirement is that it’s recognised and saved correctly by Mongo DB, which was a relatively painless process. Then I had to make it behave similarly to the ‘DateTime’ object – create some explicit cast functions, implement various operators etc. Then followed binary and xml serialisation – so far, easy.

The problem arose when I tried to write a unit test to save it to SQL Db and I immediately got the following exception:
“No mapping exists from object type LocalDate to a known managed provider native type.”

I thought, fair enough, ADO.NET doesn’t know what to do with the type, so I must somehow specify how to convert it to a SqlDbType.

The problem is that the mapping is hardcoded and cannot be changed, unless I come up with some hacky solution.

So, for the time being the users have to call ‘ToSqlDbType()’ before saving it to SQL.

The code below is responsible for the problem. As you can see there is no easy solution to this 😦

 static private MetaType GetMetaTypeFromValue(Type dataType, object value, bool inferLen, bool streamAllowed) {
            switch (Type.GetTypeCode(dataType)) {
                case TypeCode.Empty:     throw ADP.InvalidDataType(TypeCode.Empty);
                case TypeCode.Object:
                    if (dataType == typeof(System.Byte[])) {
                        // mdac 90455 must not default to image if inferLen is false ...
                        //
                        if (!inferLen || ((byte[]) value).Length <= TdsEnums.TYPE_SIZE_LIMIT) {
                            return MetaVarBinary;
                        }
                        else {
                            return MetaImage;
                        }
                    }
                    else if (dataType == typeof(System.Guid)) {
                        return MetaUniqueId;
                    }
                    else if (dataType == typeof(System.Object)) {
                        return MetaVariant;
                    } // check sql types now
                    else if (dataType == typeof(SqlBinary))
                        return MetaVarBinary;
                    else if (dataType == typeof(SqlBoolean))
                        return MetaBit;
                    else if (dataType == typeof(SqlByte))
                        return MetaTinyInt;
                    else if (dataType == typeof(SqlBytes))
                        return MetaVarBinary;
                    else if (dataType == typeof(SqlChars))
                        return  MetaNVarChar; // MDAC 87587
                    else if (dataType == typeof(SqlDateTime))
                        return MetaDateTime;
                    else if (dataType == typeof(SqlDouble))
                        return MetaFloat;
                    else if (dataType == typeof(SqlGuid))
                        return MetaUniqueId;
                    else if (dataType == typeof(SqlInt16))
                        return MetaSmallInt;
                    else if (dataType == typeof(SqlInt32))
                        return MetaInt;
                    else if (dataType == typeof(SqlInt64))
                        return MetaBigInt;
                    else if (dataType == typeof(SqlMoney))
                        return MetaMoney;
                    else if (dataType == typeof(SqlDecimal))
                        return MetaDecimal;
                    else if (dataType == typeof(SqlSingle))
                        return MetaReal;
                    else if (dataType == typeof(SqlXml))
                        return MetaXml;                
                    else if (dataType == typeof(SqlString)) {
                        return ((inferLen && !((SqlString)value).IsNull) ? PromoteStringType(((SqlString)value).Value) : MetaNVarChar); // MDAC 87587
                    }
                    else if (dataType == typeof(IEnumerable<DbDataRecord>) || dataType == typeof(DataTable)) {
                        return MetaTable;
                    } else if (dataType == typeof(TimeSpan)) {
                        return MetaTime;
                    }
                    else if (dataType == typeof(DateTimeOffset)) {
                        return MetaDateTimeOffset;
                    }
                    else { 
                        // UDT ?
                        SqlUdtInfo attribs = SqlUdtInfo.TryGetFromType(dataType);
                        if (attribs != null) {
                            return MetaUdt;
                        }
                        if (streamAllowed) {
                            // Derived from Stream ?
                            if (typeof(Stream).IsAssignableFrom(dataType)) {
                                return MetaVarBinary;
                            }
                            // Derived from TextReader ?
                            if (typeof(TextReader).IsAssignableFrom(dataType)) {
                                return MetaNVarChar;
                            }
                            // Derived from XmlReader ? 
                            if (typeof(System.Xml.XmlReader).IsAssignableFrom(dataType)) {
                                return MetaXml;
                            }
                        }
                    }
                    throw ADP.UnknownDataType(dataType);

                case TypeCode.DBNull:    throw ADP.InvalidDataType(TypeCode.DBNull);
                case TypeCode.Boolean:   return MetaBit;
                case TypeCode.Char:      throw ADP.InvalidDataType(TypeCode.Char);
                case TypeCode.SByte:     throw ADP.InvalidDataType(TypeCode.SByte);
                case TypeCode.Byte:      return MetaTinyInt;
                case TypeCode.Int16:     return MetaSmallInt;
                case TypeCode.UInt16:    throw ADP.InvalidDataType(TypeCode.UInt16);
                case TypeCode.Int32:     return MetaInt;
                case TypeCode.UInt32:    throw ADP.InvalidDataType(TypeCode.UInt32);
                case TypeCode.Int64:     return MetaBigInt;
                case TypeCode.UInt64:    throw ADP.InvalidDataType(TypeCode.UInt64);
                case TypeCode.Single:    return MetaReal;
                case TypeCode.Double:    return MetaFloat;
                case TypeCode.Decimal:   return MetaDecimal;
                case TypeCode.DateTime:  return MetaDateTime;
                case TypeCode.String:    return (inferLen ? PromoteStringType((string)value) : MetaNVarChar);
                default:                 throw ADP.UnknownDataTypeCode(dataType, Type.GetTypeCode(dataType));
            }
        }

Advertisement