在SQL中使用CLR提供基本函數對二進制數據進行解析與構造

2024-07-21 02:49:16

字體：大中小

來源：轉載

供稿：網友

二進制數據包的解析一般是借助C#等語言，在通訊程序中解析后形成字段，再統一單筆或者批量（表類型參數）提交至數據庫，在通訊程序中，存在BINARY到struct再到table的轉換。

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

基本函數

[Microsoft.SqlServer.Server.SqlFunction(Name = "Time2UTCBin")]
publicstatic SqlBinary Time2UTCBin(DateTime time)
{
????returnnew SqlBinary(BitConverter.GetBytes((uint)(MyTime.ConverDateTimeTojavaMilliSecond(time) / 1000)));
}
[Microsoft.SqlServer.Server.SqlFunction(Name = "UTCBin2Time")]
publicstatic SqlDateTime UTCBin2Time(byte[] data,int offset)
{
????returnnew SqlDateTime(MyTime.ConverDateTimeFromJavaMilliSecond(BitConverter.ToUInt32(data, offset) * 1000L));
}
?
[Microsoft.SqlServer.Server.SqlFunction(Name = "getSum")]
publicstatic SqlByte Sum(byte[] buffer, int startPos, int endPos)
{
????byte b = 0;
????for (int i = startPos; i <= endPos; i++)
????{
????????b ^= buffer[i];
????}
????return b;
}
?
[Microsoft.SqlServer.Server.SqlFunction(Name = "updateSum")]
publicstatic SqlBinary updateSum(byte[] buffer, int startPos, int endPos, int sumPos)
{
????byte b = 0;
????for (int i = startPos; i <= endPos; i++)
????{
????????b ^= buffer[i];
????}
????buffer[sumPos] = b;
????returnnew SqlBinary(buffer);
}
?
?
[Microsoft.SqlServer.Server.SqlFunction(Name = "Int2Bin")]
publicstatic SqlBinary Int2Bin(int number)
{
????returnnew SqlBinary(BitConverter.GetBytes(number));
}
?
[Microsoft.SqlServer.Server.SqlFunction(Name = "Long2Bin")]
publicstatic SqlBinary Long2Bin(long number)
{
????returnnew SqlBinary(BitConverter.GetBytes(number));
}
[Microsoft.SqlServer.Server.SqlFunction(Name = "Bin2Int")]
publicstatic SqlInt32 Bin2Int(byte[] data, int offset)
{
????returnnew SqlInt32(BitConverter.ToInt32(data,offset));
}
[Microsoft.SqlServer.Server.SqlFunction(Name = "Bin2Long")]
publicstatic SqlInt64 Bin2Long(byte[] data, int offset)
{
????returnnew SqlInt64(BitConverter.ToInt64(data, offset));
}
[Microsoft.SqlServer.Server.SqlFunction(Name = "getByte")]
publicstatic SqlByte getByte(byte[] data, int offset)
{
????returnnew SqlByte(data[offset]);
}
[Microsoft.SqlServer.Server.SqlFunction(Name = "getBytes")]
publicstatic SqlBytes getBytes(byte[] data, int offset,int count)
{
????byte[] temp = newbyte[count];
????Array.Copy(data, offset, temp, 0, count);
????returnnew SqlBytes(temp);
}

數據包的結構體（表類型）

--交易擴展記錄
CREATE TYPE [dbo].[DeviceTranscationEMV] ASTABLE
(
[RawData] VARBINARY(200) NOTNULL,
[DeviceID] UNIQUEIDENTIFIERNOTNULL,
[EMVType] TINYINTNOTNULL,
[EMVNO] SMALLINTNOTNULL,
[HardwareNo] INTNOTNULL,
Meter INT,
run INT,
WORKINT,
dead INT,
StartTime DATETIME ,
EndTime DATETIME
)

SQL中借助CLR實現的轉換函數

CREATEFUNCTION [dbo].[Binary2EMVTaxi]
????(
??????@data VARBINARY(MAX) ,
??????@offset INT = 1 ,
??????@withDeviceID BIT = 0
????)
RETURNS @emv TABLE
????(
??????[RawData] BINARY(72) NOTNULL ,
??????[DeviceID] UNIQUEIDENTIFIER ,
??????[EMVType] TINYINTNOTNULL ,
??????[EMVNO] SMALLINTNOTNULL ,
??????[HardwareNo] INTNOTNULL ,
??????Meter INT ,
??????run INT ,
??????WORKINT ,
??????dead INT ,
??????StartTime DATETIME ,
??????EndTime DATETIME
????)
AS
????BEGIN
????????DECLARE @c INT
????????DECLARE @sizeINT
????????DECLARE @sized INT
?
????????IF @withdeviceid = 1
????????????BEGIN
????????????????SET @sized = 16
????????????????SET @size = 72 + @sized
????????????END
????????ELSE
????????????BEGIN
????????????????SET @sized = 0
????????????????SET @size = 72
????????????END
?
????????SET @c = DATALENGTH(@data) / @size;
?
????????WITH sub
??????????????????AS ( SELECTSUBSTRING(@data, 1 + id * @size + @offset,
??????????????????????????????????????????@size) binDATA
???????????????????????FROM sys_id
???????????????????????WHERE id < @c
?????????????????????)
????????????INSERT @emv
????????????????????( rawdata ,
??????????????????????DeviceID ,
??????????????????????hardwareno ,
??????????????????????meter ,
??????????????????????run ,
??????????????????????WORK ,
??????????????????????dead ,
??????????????????????starttime ,
??????????????????????endtime ,
??????????????????????emvtype ,
??????????????????????emvno
????????????????????)
????????????????????SELECTCASE @withDeviceID
??????????????????????????????WHEN 0 THEN bindata
??????????????????????????????ELSESUBSTRING(bindata, 1 + @sized, @size)
????????????????????????????END ,
????????????????????????????CASE @withDeviceID
??????????????????????????????WHEN 0 THENNULL
??????????????????????????????ELSECAST (SUBSTRING(bindata, 1, @sized) ASUNIQUEIDENTIFIER)
????????????????????????????END ,
????????????????????????????dbo.Bin2Int(bindata, 0 + @sized) RecordNo ,
????????????????????????????dbo.Bin2Int(bindata, 4 + @sized) Meter ,
????????????????????????????dbo.Bin2Int(bindata, 8 + @sized) run ,
????????????????????????????dbo.Bin2Int(bindata, 12 + @sized) WORK ,
????????????????????????????dbo.Bin2Int(bindata, 16 + @sized) dead ,
????????????????????????????dbo.utcbin2time(bindata, 20 + @sized) StartTime ,
????????????????????????????dbo.utcbin2time(bindata, 24 + @sized) EndTime ,
????????????????????????????dbo.getByte(bindata, 61 + @sized) RecordType ,
????????????????????????????dbo.getByte(bindata, 62 + @sized) EMVNo
????????????????????FROM sub
????????RETURN
????END