使用用户自定义类型 CLR UDT

       

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

     

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.                UNION ALL
12.                SELECT '281474976710693' s ,
13.                         '37次' m
14.                UNION ALL
15.                SELECT 'Y/2015-1-5/1' s ,
16.                         '包年' m
17.                UNION ALL
18.                SELECT 'M/2015-1-5/11' s ,
19.                         '包个月' m
20.                UNION ALL
21.                SELECT 'D/2015-1-1/7' s ,
22.                         '包天' m
23.                UNION ALL
24.                SELECT '1500438729850887' s ,
25.                         '包天' m
26.                UNION ALL
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

 

   

       

       

     

相关资料：

     

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. public struct TimeBalance : INullable
20. {
21.     public override string ToString()
22.     {
23.         switch (type)
24.         {
25.             case 0:
26.             case 1:
27.                 return ToValue().ToString();
28.             default:
29.                 return string.Format("{0}/{1}/{2}", (TimeEnum)type, UTCSecondToDateTime(startSecond).ToString("yyyy-MM-dd HH:mm:ss"), value);
30.         }
31.  
32.  
33.     }
34.     public string ToHex()
35.     {
36.         return type.ToString("X4") +" " + startSecond.ToString("X8") + value.ToString("X4");
37.     }
38.     public string 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.                 return string.Format("{0}/{1}/{2}", (TimeEnum)type, UTCSecondToDateTime(startSecond).ToString(format), EndTime().ToString(format));
74.  
75.         }
76.     }
77.  
78.     public long ToValue()
79.     {
80.         switch (type)
81.         {
82.             case 0:
83.             case 1:
84.                 return value;
85.  
86.             default:
87.                 return ((long)type << 48) + ((long)startSecond << 16) + value;
88.         }
89.     }
90.  
91.     public static 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.             throw new Exception(s.Value + "\r\n" + ex.ToString());
131.         }
132.     }
133.  
134.  
135.  
136.  
137.     public bool IsNull
138.     {
139.         get
140.         {
141.             // 在此处放置代码
142.             return _null;
143.         }
144.     }
145.  
146.     public static TimeBalance Null
147.     {
148.         get
149.         {
150.             TimeBalance h = new TimeBalance();
151.             h._null = true;
152.             return h;
153.         }
154.     }
155.  
156.  
157.     public long Distance()
158.     {
159.         return value;
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.     public static int 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.     public int startSecond;
203.     public short type;
204.     public long value;
205.     static readonly DateTime UTC = TimeZoneInfo.ConvertTimeToUtc(new DateTime(1970, 1, 1), TimeZoneInfo.Utc);
206.     // 私有成员
207.     private bool _null;
208. }
209. public enum 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. }