一些复合类型进行范式分解是没有必要的,尤其是一些统一模型的情况下
DECLARE @i TimeBalance
SET @i = CAST('0/102' AS TimeBalance)
SELECT @i
SELECT @i.ToString()
SELECT @i.ToTimeString()
SELECT @i.Distance()
SELECT @i.Distance2('mi')
SET @i = CAST('2015-1-1/2016-8-21' AS TimeBalance)
SELECT @i.ToString() string ,
@i.ToTimeString() timeString ,
@i.Distance() distance ,
@i.Distance2('m') distanceMonth
相关资料:
TSql vs. SQL CLR Performance Analysis
CLR 集成的性能
操作 UDT 数据
- /// <summary>
- /// 时间钱包余额类型
- ///
- /// </summary>
- /// <remarks>
- /// 基于长整型,结构如下:
- /// 起始UTC秒数4B,结束UTC秒数4B
- /// </remarks>
- /// <see cref="https://msdn.microsoft.com/en-us/library/ms131086.aspx"/>
- [Serializable]
- [Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
- public struct TimeBalance : INullable
- {
- public override string ToString()
- {
-
- return string.Format("{0}/{1}", startSecond, endSecond);
- }
-
- public string ToTimeString()
- {
- return string.Format("{0}/{1}", UTCSecondToDateTime(startSecond).ToString("yyyy-MM-dd HH:mm:ss"), UTCSecondToDateTime(endSecond).ToString("yyyy-MM-dd HH:mm:ss"));
- }
-
- public static TimeBalance Parse(SqlString s)
- {
- if (s.IsNull)
- return Null;
-
- TimeBalance u = new TimeBalance();
- if (s.Value.Contains("-"))
- {
- u.startSecond = DateTimeToUTCSecond(DateTime.Parse(s.Value.Split('/')[0]));
- u.endSecond = DateTimeToUTCSecond(DateTime.Parse(s.Value.Split('/')[1]));
- }else
- if (s.Value.Contains("/"))
- {
- u.startSecond = int.Parse(s.Value.Split('/')[0]);
- u.endSecond = int.Parse(s.Value.Split('/')[1]);
- }
- else
- {
- byte[] bs = BitConverter.GetBytes(long.Parse(s.Value));
- u.startSecond = BitConverter.ToInt32(bs, 0);
- u.endSecond = BitConverter.ToInt32(bs, 4);
-
- }
- return u;
- }
-
-
-
-
- public bool IsNull
- {
- get
- {
- // 在此处放置代码
- return _null;
- }
- }
-
- public static TimeBalance Null
- {
- get
- {
- TimeBalance h = new TimeBalance();
- h._null = true;
- return h;
- }
- }
-
-
- public double Distance2(SqlString type)
- {
- switch (type.Value.ToLower())
- {
- case "y":
- return UTCSecondToDateTime(endSecond).Year - UTCSecondToDateTime(startSecond).Year;
- case "m":
- return UTCSecondToDateTime(endSecond).Year * 12 + UTCSecondToDateTime(endSecond).Month - UTCSecondToDateTime(startSecond).Year * 12 - UTCSecondToDateTime(startSecond).Month;
- case "d":
- return new TimeSpan(0, 0, 0, endSecond - startSecond).TotalDays;
- case "h":
- return new TimeSpan(0, 0, 0, endSecond - startSecond).TotalHours;
- case "mi":
- return new TimeSpan(0, 0, 0, endSecond - startSecond).TotalMinutes;
-
- case "s":
- default:
- return endSecond - startSecond;
- }
- }
- public string Distance()
- {
- return new TimeSpan(0, 0, 0, endSecond - startSecond).ToString();
-
- }
- #region 获取UTC
-
- public static int DateTimeToUTCSecond(DateTime dateTime)
- {
- return (int)dateTime.ToUniversalTime().Subtract(UTC).TotalSeconds;
- }
-
- static DateTime UTCSecondToDateTime(int second)
- {
- return UTC.AddSeconds(second).ToLocalTime();
- }
- #endregion
-
- // 这是占位符成员字段
- public int startSecond;
- public int endSecond;
- public static readonly DateTime UTC = TimeZoneInfo.ConvertTimeToUtc(new DateTime(1970, 1, 1), TimeZoneInfo.Utc);
- // 私有成员
- private bool _null;
- }
原标题:使用用户自定义类型 CLR UDT
关键词: