• 在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. CREATE TYPE [dbo].[DeviceTranscationEMV] AS TABLE
    3. (
    4. [RawData] VARBINARY(200) NOT NULL,
    5. [EMVType] TINYINT NOT NULL,
    6. [EMVNO] SMALLINT NOT NULL,
    7. [HardwareNo] INT NOT NULL,
    8. Meter INT,
    9. run INT,
    10. dead INT,
    11. StartTime DATETIME ,
    12. EndTime DATETIME
    13. )

     

     

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

     

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

     

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

     

     

    测试代码

     

    1. PRINT N'构造EMV数据,转换为BIN,然后再转换回EMV数据'
    2. go
    3. DECLARE @emv DeviceTranscationEMV
    4. WITH data
    5.           AS ( SELECT id RecordNo ,
    6.                         15 Meter ,
    7.                         100 run ,
    8.                         80 WORK ,
    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.                FROM dbo.Sys_ID
    15.                WHERE id < 100
    16.              )
    17.     INSERT INTO @emv
    18.             ( rawdata ,
    19.               deviceid ,
    20.               emvtype ,
    21.               emvno ,
    22.               hardwareno ,
    23.               meter ,
    24.               run ,
    25.               work ,
    26.               dead ,
    27.               starttime ,
    28.               endtime
    29.             )
    30.             SELECT 0x00 ,
    31.                     NEWID() ,
    32.                     recordtype ,
    33.                     emvno ,
    34.                     recordno ,
    35.                     meter ,
    36.                     run ,
    37.                     work ,
    38.                     dead ,
    39.                     starttime ,
    40.                     EndTime
    41.             FROM data
    42. FROM @emv
    43.  
    44. SELECT @data = dbo.emvtaxi2binary(@emv, 1)
    45. PRINT @data
    46.  
    47.  
    48. FROM dbo.binary2emvtaxi(@data, 1, 1)

     

     

    执行结果:

     

     

     

     

    资源:

     

     

     

     

  • 相关阅读:
    Codeforces Round #470 (rated, Div. 2, based on VK Cup 2018 Round 1)C. Producing Snow
    day69 Django--Form组件
    day68 Django--中间件
    day67 Cookie&Session
    day66 AJAX
    day62 Django框架--视图系统&路由系统
    day65 django--ORM3
    day63 django--ORM
    Day64 django--ORM2
    Day61 Django框架--模板语言2
  • 原文地址:https://www.cnblogs.com/QinQouShui/p/3970028.html
Copyright © 2020-2023  润新知