使用用户自定义类型 CLR UDT

时间：2021-09-10 09:42:03

      

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

     

  1. SET NOCOUNT ON
  2. DECLARE @i TimeBalance
  3. SET @i = CAST('D/2015-1-1/7' AS TimeBalance)
  4. SELECT @i
  5. SELECT @i.ToString()
  6. SELECT @i.ToTimeString()
  7. SELECT @i.Distance() AS Distance;
  8.  
  9. WITH a AS ( SELECT '100' s ,
  10.                         '' m
  11.                UNIONALL
  12.                SELECT '281474976710693' s ,
  13.                         '37次' m
  14.                UNIONALL
  15.                SELECT 'Y/2015-1-5/1' s ,
  16.                         '包年' m
  17.                UNIONALL
  18.                SELECT 'M/2015-1-5/11' s ,
  19.                         '包个月' m
  20.                UNIONALL
  21.                SELECT 'D/2015-1-1/7' s ,
  22.                         '包天' m
  23.                UNIONALL
  24.                SELECT '1500438729850887' s ,
  25.                         '包天' m
  26.                UNIONALL
  27.                SELECT 'H/2015-1-5 8:30/10' s ,
  28.                         '包小时' m
  29.              )
  30.     SELECT m + '' + s ,
  31.             t.ToString() string ,
  32.             t.ToHex() hex ,
  33.             t.ToTimeString() timeString ,
  34.             t.ToValue() value ,
  35.             t.Distance() distance
  36.     FROM ( SELECT s ,
  37.                         m ,
  38.                         CAST (s AS TimeBalance) t
  39.               FROM A
  40.             ) tt

 

  

      

使用用户自定义类型 CLR UDT

      

    

相关资料：

    

TSql vs. SQL CLR Performance Analysis

    

CLR 集成的性能

操作 UDT 数据

    

  1.      using System;
  2. using System.Data;
  3. using System.Data.SqlClient;
  4. using System.Data.SqlTypes;
  5. using Microsoft.SqlServer.Server;
  6.  
  7. /// <summary>
  8. /// 时间钱包余额类型
  9. ///
  10. /// </summary>
  11. /// <remarks>
  12. /// 基于长整型，结构如下:
  13. /// 包时类型2B，起始时间UTC秒数4B，时间总长2B（单位同类型决定）
  14. /// <p>包时类型：0不包时1秒2分3时4日5月6年</p>
  15. /// </remarks>
  16. /// <see cref="https://msdn.microsoft.com/en-us/library/ms131086.aspx"/>
  17. [Serializable]
  18. [Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
  19. publicstruct TimeBalance : INullable
  20. {
  21.     publicoverridestring ToString()
  22.     {
  23.         switch (type)
  24.         {
  25.             case 0:
  26.             case 1:
  27.                 return ToValue().ToString();
  28.             default:
  29.                 returnstring.Format("{0}/{1}/{2}", (TimeEnum)type, UTCSecondToDateTime(startSecond).ToString("yyyy-MM-dd HH:mm:ss"), value);
  30.         }
  31.  
  32.  
  33.     }
  34.     publicstring ToHex()
  35.     {
  36.         return type.ToString("X4") +"" + startSecond.ToString("X8") + value.ToString("X4");
  37.     }
  38.     publicstring ToTimeString()
  39.     {
  40.  
  41.         switch (type)
  42.         {
  43.             case 0:
  44.             case 1:
  45.                 return ToValue().ToString();
  46.             default:
  47.                 TimeEnum typeEnum = (TimeEnum)type;
  48.                 string format = "";
  49.                 switch (typeEnum)
  50.                 {
  51.                     case TimeEnum.S:
  52.                     default:
  53.                         format = "HH:mm:ss";
  54.                         break;
  55.                     case TimeEnum.Mi:
  56.                         format = "dd HH:mm";
  57.                         break;
  58.                     case TimeEnum.H:
  59.                         format = "MM-dd HH";
  60.                         break;
  61.                     case TimeEnum.D:
  62.                         format = "yyyy-MM-dd";
  63.                         break;
  64.                     case TimeEnum.M:
  65.                         format = "yyyy-MM";
  66.                         break;
  67.                     case TimeEnum.Y:
  68.                         format = "yyyy";
  69.                         break;
  70.  
  71.  
  72.                 }
  73.                 returnstring.Format("{0}/{1}/{2}", (TimeEnum)type, UTCSecondToDateTime(startSecond).ToString(format), EndTime().ToString(format));
  74.  
  75.         }
  76.     }
  77.  
  78.     publiclong ToValue()
  79.     {
  80.         switch (type)
  81.         {
  82.             case 0:
  83.             case 1:
  84.                 returnvalue;
  85.  
  86.             default:
  87.                 return ((long)type << 48) + ((long)startSecond << 16) + value;
  88.         }
  89.     }
  90.  
  91.     publicstatic TimeBalance Parse(SqlString s)
  92.     {
  93.         if (s.IsNull)
  94.             return Null;
  95.         try
  96.         {
  97.             TimeBalance u = new TimeBalance();
  98.  
  99.             if (s.Value.Contains("/"))
  100.             {
  101.                 u.type = (short)(TimeEnum)Enum.Parse(typeof(TimeEnum), (s.Value.Split('/')[0]));
  102.                 u.startSecond = DateTimeToUTCSecond(DateTime.Parse(s.Value.Split('/')[1]));
  103.                 u.value = long.Parse(s.Value.Split('/')[2]);
  104.             }
  105.             else
  106.             {
  107.  
  108.                 byte[] bs = BitConverter.GetBytes(long.Parse(s.Value));
  109.                 u.type = BitConverter.ToInt16(bs, 6);
  110.                 switch (u.type)
  111.                 {
  112.                     case 0:
  113.                         u.value = long.Parse(s.Value);
  114.                         break;
  115.                     case 1:
  116.                         u.value = BitConverter.ToInt16(bs, 0);
  117.                         break;
  118.                     default:
  119.  
  120.                         u.startSecond = BitConverter.ToInt32(bs, 2);
  121.                         u.value = BitConverter.ToInt16(bs, 0);
  122.                         break;
  123.                 }
  124.             }
  125.  
  126.             return u;
  127.         }
  128.         catch (Exception ex)
  129.         {
  130.             thrownew Exception(s.Value + "\r\n" + ex.ToString());
  131.         }
  132.     }
  133.  
  134.  
  135.  
  136.  
  137.     publicbool IsNull
  138.     {
  139.         get
  140.         {
  141.             // 在此处放置代码
  142.             return _null;
  143.         }
  144.     }
  145.  
  146.     publicstatic TimeBalance Null
  147.     {
  148.         get
  149.         {
  150.             TimeBalance h = new TimeBalance();
  151.             h._null = true;
  152.             return h;
  153.         }
  154.     }
  155.  
  156.  
  157.     publiclong Distance()
  158.     {
  159.         returnvalue;
  160.  
  161.     }
  162.  
  163.     public DateTime EndTime()
  164.     {
  165.         short v = (short)(value & 0xffff);
  166.         TimeEnum typeEnum = (TimeEnum)type;
  167.         switch (typeEnum)
  168.         {
  169.             // case TimeEnum.N:
  170.  
  171.             case TimeEnum.S:
  172.             default:
  173.                 return UTCSecondToDateTime(startSecond).AddSeconds(v);
  174.             case TimeEnum.Mi:
  175.                 return UTCSecondToDateTime(startSecond).AddMinutes(v);
  176.             case TimeEnum.H:
  177.                 return UTCSecondToDateTime(startSecond).AddHours(v);
  178.             case TimeEnum.D:
  179.                 return UTCSecondToDateTime(startSecond).AddDays(v);
  180.             case TimeEnum.M:
  181.                 return UTCSecondToDateTime(startSecond).AddMonths(v);
  182.             case TimeEnum.Y:
  183.                 return UTCSecondToDateTime(startSecond).AddYears(v);
  184.  
  185.         }
  186.     }
  187.  
  188.     #region 获取UTC
  189.  
  190.     publicstaticint DateTimeToUTCSecond(DateTime dateTime)
  191.     {
  192.         return (int)dateTime.ToUniversalTime().Subtract(UTC).TotalSeconds;
  193.     }
  194.  
  195.     static DateTime UTCSecondToDateTime(int second)
  196.     {
  197.         return UTC.AddSeconds(second).ToLocalTime();
  198.     }
  199.     #endregion
  200.  
  201.     // 这是占位符成员字段
  202.     publicint startSecond;
  203.     publicshort type;
  204.     publiclongvalue;
  205.     staticreadonly DateTime UTC = TimeZoneInfo.ConvertTimeToUtc(new DateTime(1970, 1, 1), TimeZoneInfo.Utc);
  206.     // 私有成员
  207.     privatebool _null;
  208. }
  209. publicenum TimeEnum
  210. {
  211.     N = 0,
  212.     T = 1,//Times
  213.     S = 2,
  214.     Mi = 3,
  215.     H = 4,
  216.     D = 5,
  217.     M = 6,
  218.     Y = 7
  219. }

 

  

  

  

      

    

标签：

相关文章