你的位置:首页 > 数据库

[数据库]使用用户自定义类型 CLR UDT

  

一些复合类型进行范式分解是没有必要的,尤其是一些统一模型的情况下

  

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 数据

 

  

  1. /// <summary>
  2. /// 时间钱包余额类型
  3. ///
  4. /// </summary>
  5. /// <remarks>
  6. /// 基于长整型,结构如下:
  7. /// 起始UTC秒数4B,结束UTC秒数4B
  8. /// </remarks>
  9. /// <see cref="https://msdn.microsoft.com/en-us/library/ms131086.aspx"/>
  10. [Serializable]
  11. [Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
  12. public struct TimeBalance : INullable
  13. {
  14.     public override string ToString()
  15.     {
  16.   
  17.         return string.Format("{0}/{1}", startSecond, endSecond);
  18.     }
  19.   
  20.     public string ToTimeString()
  21.     {
  22.         return string.Format("{0}/{1}", UTCSecondToDateTime(startSecond).ToString("yyyy-MM-dd HH:mm:ss"), UTCSecondToDateTime(endSecond).ToString("yyyy-MM-dd HH:mm:ss"));
  23.     }
  24.   
  25.     public static TimeBalance Parse(SqlString s)
  26.     {
  27.         if (s.IsNull)
  28.             return Null;
  29.   
  30.         TimeBalance u = new TimeBalance();
  31.         if (s.Value.Contains("-"))
  32.         {
  33.             u.startSecond = DateTimeToUTCSecond(DateTime.Parse(s.Value.Split('/')[0]));
  34.             u.endSecond = DateTimeToUTCSecond(DateTime.Parse(s.Value.Split('/')[1]));
  35.         }else
  36.         if (s.Value.Contains("/"))
  37.         {
  38.             u.startSecond = int.Parse(s.Value.Split('/')[0]);
  39.             u.endSecond = int.Parse(s.Value.Split('/')[1]);
  40.         }
  41.         else
  42.         {
  43.             byte[] bs = BitConverter.GetBytes(long.Parse(s.Value));
  44.             u.startSecond = BitConverter.ToInt32(bs, 0);
  45.             u.endSecond = BitConverter.ToInt32(bs, 4);
  46.   
  47.         }
  48.         return u;
  49.     }
  50.   
  51.   
  52.   
  53.   
  54.     public bool IsNull
  55.     {
  56.         get
  57.         {
  58.             // 在此处放置代码
  59.             return _null;
  60.         }
  61.     }
  62.   
  63.     public static TimeBalance Null
  64.     {
  65.         get
  66.         {
  67.             TimeBalance h = new TimeBalance();
  68.             h._null = true;
  69.             return h;
  70.         }
  71.     }
  72.   
  73.   
  74.     public double Distance2(SqlString type)
  75.     {
  76.         switch (type.Value.ToLower())
  77.         {
  78.             case "y":
  79.                 return UTCSecondToDateTime(endSecond).Year - UTCSecondToDateTime(startSecond).Year;
  80.             case "m":
  81.                 return UTCSecondToDateTime(endSecond).Year * 12 + UTCSecondToDateTime(endSecond).Month - UTCSecondToDateTime(startSecond).Year * 12 - UTCSecondToDateTime(startSecond).Month;
  82.             case "d":
  83.                 return new TimeSpan(0, 0, 0, endSecond - startSecond).TotalDays;
  84.             case "h":
  85.                 return new TimeSpan(0, 0, 0, endSecond - startSecond).TotalHours;
  86.             case "mi":
  87.                 return new TimeSpan(0, 0, 0, endSecond - startSecond).TotalMinutes;
  88.   
  89.             case "s":
  90.             default:
  91.                 return endSecond - startSecond;
  92.         }
  93.     }
  94.     public string Distance()
  95.     {
  96.         return new TimeSpan(0, 0, 0, endSecond - startSecond).ToString();
  97.   
  98.     }
  99.     #region 获取UTC
  100.   
  101.     public static int DateTimeToUTCSecond(DateTime dateTime)
  102.     {
  103.         return (int)dateTime.ToUniversalTime().Subtract(UTC).TotalSeconds;
  104.     }
  105.   
  106.     static DateTime UTCSecondToDateTime(int second)
  107.     {
  108.         return UTC.AddSeconds(second).ToLocalTime();
  109.     }
  110.     #endregion
  111.   
  112.     // 这是占位符成员字段
  113.     public int startSecond;
  114.     public int endSecond;
  115.     public static readonly DateTime UTC = TimeZoneInfo.ConvertTimeToUtc(new DateTime(1970, 1, 1), TimeZoneInfo.Utc);
  116.     // 私有成员
  117.     private bool _null;
  118. }