Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server / SQL Server 2008

Reading sql_variant in Entity Framework

Rate me:
Please Sign up or sign in to vote.
4.89/5 (10 votes)
8 Jan 2011CPOL3 min read 84.4K   819   9  
Working around an Entity Framework exclusion of sql_variant
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)


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions