首页 > 代码库 > 在SQL中使用CLR提供基本函数对二进制数据进行解析与构造

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

2024-07-22 10:15:52   214人阅读

?

二进制数据包的解析一般是借助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. [DeviceID] UNIQUEIDENTIFIER NOT NULL,
  6. [EMVType] TINYINT NOT NULL,
  7. [EMVNO] SMALLINT NOT NULL,
  8. [HardwareNo] INT NOT NULL,
  9. Meter INT,
  10. run INT,
  11. WORK INT,
  12. dead INT,
  13. StartTime DATETIME ,
  14. EndTime DATETIME
  15. )

?

?

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

?

  1. CREATE FUNCTION [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) NOT NULL ,
  10. ??????[DeviceID] UNIQUEIDENTIFIER ,
  11. ??????[EMVType] TINYINT NOT NULL ,
  12. ??????[EMVNO] SMALLINT NOT NULL ,
  13. ??????[HardwareNo] INT NOT NULL ,
  14. ??????Meter INT ,
  15. ??????run INT ,
  16. ??????WORK INT ,
  17. ??????dead INT ,
  18. ??????StartTime DATETIME ,
  19. ??????EndTime DATETIME
  20. ????)
  21. AS
  22. ????BEGIN
  23. ????????DECLARE @c INT
  24. ????????DECLARE @size INT
  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 ( SELECT SUBSTRING(@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. ????????????????????SELECT CASE @withDeviceID
  60. ??????????????????????????????WHEN 0 THEN bindata
  61. ??????????????????????????????ELSE SUBSTRING(bindata, 1 + @sized, @size)
  62. ????????????????????????????END ,
  63. ????????????????????????????CASE @withDeviceID
  64. ??????????????????????????????WHEN 0 THEN NULL
  65. ??????????????????????????????ELSE CAST (SUBSTRING(bindata, 1, @sized) AS UNIQUEIDENTIFIER)
  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. CREATE FUNCTION [dbo].[EMVTaxi2Binary]
  2. ????(
  3. ??????@emv DeviceTranscationEMV READONLY ,
  4. ??????@withDeviceID BIT = 0
  5. ????)
  6. RETURNS VARBINARY(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 AS BINARY(33))
  20. ????????????????????????????????????????????????+ CAST(emvtype AS BINARY(1))
  21. ????????????????????????????????????????????????+ CAST(emvno AS BINARY(1))
  22. ????????????????????????????????????????????????+ CAST (0 AS BINARY(1))
  23. ????????????????????????????????????????????????+ CAST(0 AS BINARY(7)) + 0x55,
  24. ????????????????????????????????????????????????0, 62, 63)
  25. ????????????FROM @emv
  26. ?
  27. ????????ELSE
  28. ????????????SELECT @bin = @bin + CAST (deviceid AS BINARY(16))
  29. ????????????????????+ dbo.updatesum(dbo.int2Bin([HardwareNo])
  30. ????????????????????????????????????+ dbo.int2Bin(meter) + dbo.int2Bin(run)
  31. ????????????????????????????????????+ dbo.int2Bin(work) + dbo.int2Bin(dead)
  32. ????????????????????????????????????+ dbo.time2utcbin(starttime)
  33. ????????????????????????????????????+ dbo.time2utcbin(endtime)
  34. ????????????????????????????????????+ CAST(0 AS BINARY(33))
  35. ????????????????????????????????????+ CAST(emvtype AS BINARY(1))
  36. ????????????????????????????????????+ CAST(emvno AS BINARY(1))
  37. ????????????????????????????????????+ CAST (0 AS BINARY(1))
  38. ????????????????????????????????????+ CAST(0 AS BINARY(7)) + 0x55, 0, 62, 63)
  39. ????????????FROM @emv
  40. ?
  41. ????????RETURN @bin
  42. ????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. SELECT *
  43. FROM @emv
  44. DECLARE @data VARBINARY(MAX)
  45. ?
  46. SELECT @data = http://www.mamicode.com/dbo.emvtaxi2binary(@emv, 1)>
  47. PRINT @data
  48. ?
  49. ?
  50. SELECT *
  51. FROM dbo.binary2emvtaxi(@data, 1, 1)

?

?

执行结果：

?

?

?

?

资源：

?

?

?

?

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


联系
我们