Top 10 2014 Blog Posts

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));

Microsoft Introduces New Modular .NET Core Delivered by NuGet, Source on GitHub


Big changes are coming to the .NET platform that affect your development wherever you use .NET. The direction helps you develop applications (Web, Azure, Phone, Desktop. Windows Store, Linux, MacOS, iOs and Android) easier. So if you are going horizontal and targeting more than one variation of Windows, then this is for you.

For developers and architects, it provides keys to a new way to looking at how your code should be written. The new .NET implements the kinds of features we face every day. And the solutions are evolving from vertical solutions where each problem was a subset of some other bigger problem. Rather it becomes a set of contracts, where dependencies are clearly defined, where the contract can be implemented in different ways to meet specific needs.

Migrating the .NET base is no small task. Yet, the Microsoft teams have taken on the challenge to make it easier…

View original post 1,838 more words

Blog at

Up ↑