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

?

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

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

?

基本函数

1. [Microsoft.SqlServer.Server.SqlFunction(Name = "Time2UTCBin")]
2. public static SqlBinary Time2UTCBin(DateTime time)
3. {
4. ????return new SqlBinary(BitConverter.GetBytes((uint)(MyTime.ConverDateTimeToJavaMilliSecond(time) / 1000)));
5. }
6. [Microsoft.SqlServer.Server.SqlFunction(Name = "UTCBin2Time")]
7. public static SqlDateTime UTCBin2Time(byte[] data,int offset)
8. {
9. ????return new SqlDateTime(MyTime.ConverDateTimeFromJavaMilliSecond(BitConverter.ToUInt32(data, offset) * 1000L));
10. }
11. ?
12. [Microsoft.SqlServer.Server.SqlFunction(Name = "getSum")]
13. public static 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. public static 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. ????return new SqlBinary(buffer);
33. }
34. ?
35. ?
36. [Microsoft.SqlServer.Server.SqlFunction(Name = "Int2Bin")]
37. public static SqlBinary Int2Bin(int number)
38. {
39. ????return new SqlBinary(BitConverter.GetBytes(number));
40. }
41. ?
42. [Microsoft.SqlServer.Server.SqlFunction(Name = "Long2Bin")]
43. public static SqlBinary Long2Bin(long number)
44. {
45. ????return new SqlBinary(BitConverter.GetBytes(number));
46. }
47. [Microsoft.SqlServer.Server.SqlFunction(Name = "Bin2Int")]
48. public static SqlInt32 Bin2Int(byte[] data, int offset)
49. {
50. ????return new SqlInt32(BitConverter.ToInt32(data,offset));
51. }
52. [Microsoft.SqlServer.Server.SqlFunction(Name = "Bin2Long")]
53. public static SqlInt64 Bin2Long(byte[] data, int offset)
54. {
55. ????return new SqlInt64(BitConverter.ToInt64(data, offset));
56. }
57. [Microsoft.SqlServer.Server.SqlFunction(Name = "getByte")]
58. public static SqlByte getByte(byte[] data, int offset)
59. {
60. ????return new SqlByte(data[offset]);
61. }
62. [Microsoft.SqlServer.Server.SqlFunction(Name = "getBytes")]
63. public static SqlBytes getBytes(byte[] data, int offset,int count)
64. {
65. ????byte[] temp = new byte[count];
66. ????Array.Copy(data, offset, temp, 0, count);
67. ????return new SqlBytes(temp);
68. }

?

?

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

1. --交易扩展记录
2. TYPE [dbo].[DeviceTranscationEMV]
3. (
4. [RawData] (200) ,
5. [DeviceID] ,
6. [EMVType] ,
7. [EMVNO] ,
8. [HardwareNo] ,
9. Meter ,
10. run ,
11. ,
12. dead ,
13. StartTime ,
14. EndTime
15. )

?

?

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

?

1. [dbo].[2EMVTaxi]
2. ????(
3. ??????@data () ,
4. ??????@offset = 1 ,
5. ??????@withDeviceID = 0
6. ????)
7. @emv
8. ????(
9. ??????[RawData] (72) ,
10. ??????[DeviceID] ,
11. ??????[EMVType] ,
12. ??????[EMVNO] ,
13. ??????[HardwareNo] ,
14. ??????Meter ,
15. ??????run ,
16. ?????? ,
17. ??????dead ,
18. ??????StartTime ,
19. ??????EndTime
20. ????)
22. ????
23. ???????? @c
24. ???????? @
25. ???????? @sized
26. ?
27. ???????? @withdeviceid = 1
28. ????????????
29. ???????????????? @sized = 16
30. ???????????????? @ = 72 + @sized
31. ????????????
32. ????????
33. ????????????
34. ???????????????? @sized = 0
35. ???????????????? @ = 72
36. ????????????
37. ?
38. ???????? @c = DATALENGTH(@data) / @;
39. ?
40. ???????? sub
41. ?????????????????? ( (@data, 1 + id * @ + @offset,
42. ??????????????????????????????????????????@) binDATA
43. ??????????????????????? sys_id
44. ??????????????????????? id < @c
45. ?????????????????????)
46. ???????????? @emv
47. ????????????????????( rawdata ,
48. ??????????????????????DeviceID ,
49. ??????????????????????hardwareno ,
50. ??????????????????????meter ,
51. ??????????????????????run ,
52. ?????????????????????? ,
53. ??????????????????????dead ,
54. ??????????????????????starttime ,
55. ??????????????????????endtime ,
56. ??????????????????????emvtype ,
57. ??????????????????????emvno
58. ????????????????????)
59. ???????????????????? @withDeviceID
60. ?????????????????????????????? 0 bindata
61. ?????????????????????????????? (bindata, 1 + @sized, @)
62. ???????????????????????????? ,
63. ???????????????????????????? @withDeviceID
64. ?????????????????????????????? 0
65. ?????????????????????????????? ((bindata, 1, @sized) )
66. ???????????????????????????? ,
67. ????????????????????????????dbo.Bin2(bindata, 0 + @sized) RecordNo ,
68. ????????????????????????????dbo.Bin2(bindata, 4 + @sized) Meter ,
69. ????????????????????????????dbo.Bin2(bindata, 8 + @sized) run ,
70. ????????????????????????????dbo.Bin2(bindata, 12 + @sized) ,
71. ????????????????????????????dbo.Bin2(bindata, 16 + @sized) dead ,
72. ????????????????????????????dbo.utcbin2(bindata, 20 + @sized) StartTime ,
73. ????????????????????????????dbo.utcbin2(bindata, 24 + @sized) EndTime ,
74. ????????????????????????????dbo.getByte(bindata, 61 + @sized) RecordType ,
75. ????????????????????????????dbo.getByte(bindata, 62 + @sized) EMVNo
76. ???????????????????? sub
77. ????????
78. ????

?

1. [dbo].[EMVTaxi2]
2. ????(
3. ??????@emv DeviceTranscationEMV READONLY ,
4. ??????@withDeviceID = 0
5. ????)
6. ()
8. ????
9. ???????? @bin ()
10. ???????? @bin = 0x0
11. ???????? @withDeviceID = 0
12. ???????????? @bin = @bin + dbo.updatesum(dbo.2Bin([HardwareNo])
13. ????????????????????????????????????????????????+ dbo.2Bin(meter)
14. ????????????????????????????????????????????????+ dbo.2Bin(run)
15. ????????????????????????????????????????????????+ dbo.2Bin()
16. ????????????????????????????????????????????????+ dbo.2Bin(dead)
17. ????????????????????????????????????????????????+ dbo.2utcbin(starttime)
18. ????????????????????????????????????????????????+ dbo.2utcbin(endtime)
19. ????????????????????????????????????????????????+ (0 (33))
20. ????????????????????????????????????????????????+ (emvtype (1))
21. ????????????????????????????????????????????????+ (emvno (1))
22. ????????????????????????????????????????????????+ (0 (1))
23. ????????????????????????????????????????????????+ (0 (7)) + 0x55,
24. ????????????????????????????????????????????????0, 62, 63)
25. ???????????? @emv
26. ?
27. ????????
28. ???????????? @bin = @bin + (deviceid (16))
29. ????????????????????+ dbo.updatesum(dbo.2Bin([HardwareNo])
30. ????????????????????????????????????+ dbo.2Bin(meter) + dbo.2Bin(run)
31. ????????????????????????????????????+ dbo.2Bin() + dbo.2Bin(dead)
32. ????????????????????????????????????+ dbo.2utcbin(starttime)
33. ????????????????????????????????????+ dbo.2utcbin(endtime)
34. ????????????????????????????????????+ (0 (33))
35. ????????????????????????????????????+ (emvtype (1))
36. ????????????????????????????????????+ (emvno (1))
37. ????????????????????????????????????+ (0 (1))
38. ????????????????????????????????????+ (0 (7)) + 0x55, 0, 62, 63)
39. ???????????? @emv
40. ?
41. ???????? @bin
42. ????

?

?

测试代码

?

1. N‘构造EMV数据，转换为BIN，然后再转换回EMV数据‘
2. go
3. @emv DeviceTranscationEMV
4. data
5. ?????????? ( id RecordNo ,
6. ????????????????????????15 Meter ,
7. ????????????????????????100 run ,
8. ????????????????????????80 ,
9. ????????????????????????20 dead ,
10. ????????????????????????DATEADD(s, id, ‘2014-9-1 12:50:01‘) StartTime ,
11. ????????????????????????DATEADD(mi, id, ‘2014-9-1 13:23:11‘) EndTime ,
12. ????????????????????????4 RecordType ,
13. ????????????????????????0 EMVNo
14. ??????????????? dbo.Sys_ID
15. ??????????????? id < 100
16. ?????????????)
17. ???? @emv
18. ????????????( rawdata ,
19. ??????????????deviceid ,
20. ??????????????emvtype ,
21. ??????????????emvno ,
22. ??????????????hardwareno ,
23. ??????????????meter ,
24. ??????????????run ,
25. ?????????????? ,
26. ??????????????dead ,
27. ??????????????starttime ,
28. ??????????????endtime
29. ????????????)
30. ???????????? 0x00 ,
31. ????????????????????NEWID() ,
32. ????????????????????recordtype ,
33. ????????????????????emvno ,
34. ????????????????????recordno ,
35. ????????????????????meter ,
36. ????????????????????run ,
37. ???????????????????? ,
38. ????????????????????dead ,
39. ????????????????????starttime ,
40. ????????????????????EndTime
41. ???????????? data
42. *
43. @emv
44. @data ()
45. ?
46. @data = dbo.emvtaxi2(@emv, 1)
47. @data
48. ?
49. ?
50. *
51. dbo.2emvtaxi(@data, 1, 1)

?

?

执行结果：

?

?

?

?

资源：

?

?

?

?