Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » ADO.NET » Downloads
 
Add your own
alternative version

Reading sql_variant in Entity Framework

, 8 Jan 2011 CPOL
Working around an Entity Framework exclusion of sql_variant
SqlVariant.zip
using System;

namespace SqlValueProto {

    partial class SqlVariant {

        private static readonly DateTime Epoch = new DateTime(1900, 1, 1, 0, 0, 0, DateTimeKind.Unspecified);

        private static readonly DateTime YearOne = new DateTime(1, 1, 1, 0, 0, 0, DateTimeKind.Unspecified);

        private static readonly long[] TimeToTicMultiplier = new long[] {
            -1, -1, 100000, 10000, 1000, 100, 10, 1
        };

        private static readonly int[] TimePartOffset = new[] {
            -1, -1, 6, 7, 7, 8, 8, 8
        };

        public object Converted {
            get {
                if (Representation == null || BaseType == null) {
                    return null;
                }
                switch (BaseType) {
                    case "uniqueidentifier":
                        return new Guid(Representation);
                    case "char":
                    case "varchar":
                        return GetString(Representation);
                    case "nvarchar":
                    case "nchar": 
                        return GetNlString(Representation);
                    case "int":
                        return GetInt(Representation);
                    case "smallint":
                        return GetShort(Representation);
                    case "bigint":
                        return GetLong(Representation);
                    case "decimal":
                    case "numeric":
                        return GetDecimal(Representation);
                    case "bit":
                        return GetBool(Representation);
                    case "float":
                        return GetDouble(Representation);
                    case "real":
                        return GetFloat(Representation);
                    case "date":
                        return GetDate(Representation);
                    case "time":
                        return GetTime(Representation);
                    case "datetime":
                        return GetDateTime(Representation);
                    case "datetime2":
                        return GetDateTime2(Representation);
                    case "datetimeoffset":
                        return GetDateTimeOffset(Representation);
                }
                throw new InvalidOperationException("Unsupported SQL type: '" + BaseType + "'");
            }
        }

        private static long GetLong(byte[] src) {
            AssertLength(src, 8, "bigint");
            Array.Reverse(src);
            return BitConverter.ToInt64(src, 0);
        }

        private static int GetInt(byte[] src) {
            AssertLength(src, 4, "int");
            Array.Reverse(src);
            return BitConverter.ToInt32(src, 0);
        }

        private static short GetShort(byte[] src) {
            AssertLength(src, 2, "smallint");
            Array.Reverse(src);
            return BitConverter.ToInt16(src, 0);
        }

        private static bool GetBool(byte[] src) {
            AssertLength(src, 1, "bit");
            return src[0] == 1;
        }

        private static string GetNlString(byte[] src) {
            if (src.Length % 2 != 0) {
                throw new InvalidOperationException("NLS format is invalid.");
            }
            var buf = new char[src.Length / 2];
            for (var pos = 0 ; pos != src.Length ; pos += 2) {
                buf[pos/2] = BitConverter.ToChar(src, pos);
            }
            return new string(buf);
        }

        private static string GetString(byte[] src) {
            var res = new char[src.Length];
            for (var i = 0 ; i != src.Length ; i++) {
                res[i] = (char)src[i];
            }
            return new string(res);
        }

        private static decimal GetDecimal(byte[] src) {
            if (src.Length < 8) {
                throw new InvalidOperationException("decimal format is invalid.");
            }
            return new decimal(
                BitConverter.ToInt32(src, 4)
            ,   src.Length >= 12 ? BitConverter.ToInt32(src, 8) : 0
            ,   src.Length >= 16 ? BitConverter.ToInt32(src, 12) : 0
            ,   src[3] != 1
            ,   src[1]
            );
        }

        private static double GetDouble(byte[] src) {
            AssertLength(src, 8, "float");
            Array.Reverse(src);
            return BitConverter.ToDouble(src, 0);
        }

        private static float GetFloat(byte[] src) {
            AssertLength(src, 4, "real");
            Array.Reverse(src);
            return BitConverter.ToSingle(src, 0);
        }

        private static DateTime GetDate(byte[] src) {
            AssertLength(src, 3, "date");
            long dayNumber = GetInt12(src, 0);
            return YearOne.AddDays(dayNumber);
        }
        
        private static TimeSpan GetTime(byte[] src) {
            var secFraction = 0L;
            for (var i = src.Length-1 ; i != 0 ; i--) {
                secFraction <<= 8;
                secFraction += src[i];
            }
            return new TimeSpan(secFraction * TimeToTicMultiplier[src[0]]);
        }

        private static DateTime GetDateTime(byte[] src) {
            AssertLength(src, 8, "datetime");
            Array.Reverse(src);
            var res = Epoch.AddDays(BitConverter.ToInt32(src, 4));
            var fraction = BitConverter.ToInt32(src, 0);
            if (fraction == 0) {
                return res;
            }
            var millis = (fraction % 300 * 10) / 3;
            var seconds = fraction / 300;
            return res.AddSeconds(seconds).AddMilliseconds(millis);
        }

        private static DateTime GetDateTime2(byte[] src) {
            if (src.Length < 7 || src[0] > 7) {
                throw new InvalidOperationException("datetime2 format is invalid.");
            }
            var offset = TimePartOffset[src[0]];
            long dayNumber = GetInt12(src, offset-2);
            var res = YearOne.AddDays(dayNumber);
            var secFraction = 0L;
            for (var i = offset - 3 ; i != 0 ; i--) {
                secFraction <<= 8;
                secFraction += src[i];
            }
            return res.AddTicks(secFraction*TimeToTicMultiplier[src[0]]);
        }

        private static DateTimeOffset GetDateTimeOffset(byte[] src) {
            var dt = GetDateTime2(src);
            var offsetTicks = ((sbyte)src[src.Length - 1] << 8 | src[src.Length - 2]) * 60 * 10000000L;
            var offset = new TimeSpan(offsetTicks);
            return new DateTimeOffset(dt.Add(offset), offset);
        }

        private static int GetInt12(byte[] src, int offset) {
            return src[offset+2] << 16 | src[offset+1] << 8 | src[offset];
        }

        private static void AssertLength(byte[] src, int length, string name) {
            if (src.Length != length) {
                throw new InvalidOperationException(name + " format is invalid");
            }
        }

    }

}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

dasblinkenlight

United States United States
No Biography provided

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150327.1 | Last Updated 8 Jan 2011
Article Copyright 2010 by dasblinkenlight
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid