在SQL中使用CLR提供基本函数对二进制数据进行解析与构造

二进制数据包的解析一般是借助C#等语言，在通讯程序中解析后形成字段，再统一单笔或者批量（表类型参数）提交至数据库，在通讯程序中，存在BINARY到struct再到table的转换。

现借助CLR提供基本的INT2HEX(小端)等函数，在SQL中直接解析数据包。

基本函数

1. [Microsoft.SqlServer.Server.SqlFunction(Name = "Time2UTCBin")]
2. publicstatic SqlBinary Time2UTCBin(DateTime time)
3. {
4.     returnnew SqlBinary(BitConverter.GetBytes((uint)(MyTime.ConverDateTimeTojavaMilliSecond(time) / 1000)));
5. }
6. [Microsoft.SqlServer.Server.SqlFunction(Name = "UTCBin2Time")]
7. publicstatic SqlDateTime UTCBin2Time(byte[] data,int offset)
8. {
9.     returnnew SqlDateTime(MyTime.ConverDateTimeFromJavaMilliSecond(BitConverter.ToUInt32(data, offset) * 1000L));
10. }
11.  
12. [Microsoft.SqlServer.Server.SqlFunction(Name = "getSum")]
13. publicstatic SqlByte Sum(byte[] buffer, int startPos, int endPos)
14. {
15.     byte b = 0;
16.     for (int i = startPos; i <= endPos; i++)
17.     {
18.         b ^= buffer[i];
19.     }
20.     return b;
21. }
22.  
23. [Microsoft.SqlServer.Server.SqlFunction(Name = "updateSum")]
24. publicstatic SqlBinary updateSum(byte[] buffer, int startPos, int endPos, int sumPos)
25. {
26.     byte b = 0;
27.     for (int i = startPos; i <= endPos; i++)
28.     {
29.         b ^= buffer[i];
30.     }
31.     buffer[sumPos] = b;
32.     returnnew SqlBinary(buffer);
33. }
34.  
35.  
36. [Microsoft.SqlServer.Server.SqlFunction(Name = "Int2Bin")]
37. publicstatic SqlBinary Int2Bin(int number)
38. {
39.     returnnew SqlBinary(BitConverter.GetBytes(number));
40. }
41.  
42. [Microsoft.SqlServer.Server.SqlFunction(Name = "Long2Bin")]
43. publicstatic SqlBinary Long2Bin(long number)
44. {
45.     returnnew SqlBinary(BitConverter.GetBytes(number));
46. }
47. [Microsoft.SqlServer.Server.SqlFunction(Name = "Bin2Int")]
48. publicstatic SqlInt32 Bin2Int(byte[] data, int offset)
49. {
50.     returnnew SqlInt32(BitConverter.ToInt32(data,offset));
51. }
52. [Microsoft.SqlServer.Server.SqlFunction(Name = "Bin2Long")]
53. publicstatic SqlInt64 Bin2Long(byte[] data, int offset)
54. {
55.     returnnew SqlInt64(BitConverter.ToInt64(data, offset));
56. }
57. [Microsoft.SqlServer.Server.SqlFunction(Name = "getByte")]
58. publicstatic SqlByte getByte(byte[] data, int offset)
59. {
60.     returnnew SqlByte(data[offset]);
61. }
62. [Microsoft.SqlServer.Server.SqlFunction(Name = "getBytes")]
63. publicstatic SqlBytes getBytes(byte[] data, int offset,int count)
64. {
65.     byte[] temp = newbyte[count];
66.     Array.Copy(data, offset, temp, 0, count);
67.     returnnew SqlBytes(temp);
68. }

数据包的结构体（表类型）

1. --交易扩展记录
2. CREATE TYPE [dbo].[DeviceTranscationEMV] ASTABLE
3. (
4. [RawData] VARBINARY(200) NOTNULL,
5. [DeviceID] UNIQUEIDENTIFIERNOTNULL,
6. [EMVType] TINYINTNOTNULL,
7. [EMVNO] SMALLINTNOTNULL,
8. [HardwareNo] INTNOTNULL,
9. Meter INT,
10. run INT,
11. WORKINT,
12. dead INT,
13. StartTime DATETIME ,
14. EndTime DATETIME
15. )

SQL中借助CLR实现的转换函数

1. CREATEFUNCTION [dbo].[Binary2EMVTaxi]
2.     (
3.       @data VARBINARY(MAX) ,
4.       @offset INT = 1 ,
5.       @withDeviceID BIT = 0
6.     )
7. RETURNS @emv TABLE
8.     (
9.       [RawData] BINARY(72) NOTNULL ,
10.       [DeviceID] UNIQUEIDENTIFIER ,
11.       [EMVType] TINYINTNOTNULL ,
12.       [EMVNO] SMALLINTNOTNULL ,
13.       [HardwareNo] INTNOTNULL ,
14.       Meter INT ,
15.       run INT ,
16.       WORKINT ,
17.       dead INT ,
18.       StartTime DATETIME ,
19.       EndTime DATETIME
20.     )
21. AS
22.     BEGIN
23.         DECLARE @c INT
24.         DECLARE @sizeINT
25.         DECLARE @sized INT
26.  
27.         IF @withdeviceid = 1
28.             BEGIN
29.                 SET @sized = 16
30.                 SET @size = 72 + @sized
31.             END
32.         ELSE
33.             BEGIN
34.                 SET @sized = 0
35.                 SET @size = 72
36.             END
37.  
38.         SET @c = DATALENGTH(@data) / @size;
39.  
40.         WITH sub
41.                   AS ( SELECTSUBSTRING(@data, 1 + id * @size + @offset,
42.                                           @size) binDATA
43.                        FROM sys_id
44.                        WHERE id < @c
45.                      )
46.             INSERT @emv
47.                     ( rawdata ,
48.                       DeviceID ,
49.                       hardwareno ,
50.                       meter ,
51.                       run ,
52.                       WORK ,
53.                       dead ,
54.                       starttime ,
55.                       endtime ,
56.                       emvtype ,
57.                       emvno
58.                     )
59.                     SELECTCASE @withDeviceID
60.                               WHEN 0 THEN bindata
61.                               ELSESUBSTRING(bindata, 1 + @sized, @size)
62.                             END ,
63.                             CASE @withDeviceID
64.                               WHEN 0 THENNULL
65.                               ELSECAST (SUBSTRING(bindata, 1, @sized) ASUNIQUEIDENTIFIER)
66.                             END ,
67.                             dbo.Bin2Int(bindata, 0 + @sized) RecordNo ,
68.                             dbo.Bin2Int(bindata, 4 + @sized) Meter ,
69.                             dbo.Bin2Int(bindata, 8 + @sized) run ,
70.                             dbo.Bin2Int(bindata, 12 + @sized) WORK ,
71.                             dbo.Bin2Int(bindata, 16 + @sized) dead ,
72.                             dbo.utcbin2time(bindata, 20 + @sized) StartTime ,
73.                             dbo.utcbin2time(bindata, 24 + @sized) EndTime ,
74.                             dbo.getByte(bindata, 61 + @sized) RecordType ,
75.                             dbo.getByte(bindata, 62 + @sized) EMVNo
76.                     FROM sub
77.         RETURN
78.     END

1. CREATEFUNCTION [dbo].[EMVTaxi2Binary]
2.     (
3.       @emv DeviceTranscationEMV READONLY ,
4.       @withDeviceID BIT = 0
5.     )
6. RETURNSVARBINARY(MAX)
7. AS
8.     BEGIN
9.         DECLARE @bin VARBINARY(MAX)
10.         SET @bin = 0x0
11.         IF @withDeviceID = 0
12.             SELECT @bin = @bin + dbo.updatesum(dbo.int2Bin([HardwareNo])
13.                                                 + dbo.int2Bin(meter)
14.                                                 + dbo.int2Bin(run)
15.                                                 + dbo.int2Bin(work)
16.                                                 + dbo.int2Bin(dead)
17.                                                 + dbo.time2utcbin(starttime)
18.                                                 + dbo.time2utcbin(endtime)
19.                                                 + CAST(0 ASBINARY(33))
20.                                                 + CAST(emvtype ASBINARY(1))
21.                                                 + CAST(emvno ASBINARY(1))
22.                                                 + CAST (0 ASBINARY(1))
23.                                                 + CAST(0 ASBINARY(7)) + 0x55,
24.                                                 0, 62, 63)
25.             FROM @emv
26.  
27.         ELSE
28.             SELECT @bin = @bin + CAST (deviceid ASBINARY(16))
29.                     + dbo.updatesum(dbo.int2Bin([HardwareNo])
30.                                     + dbo.int