如何在varbinary（max）中存储两个smallint值？

I am dealing with a SQL table that has a column of type varbinary(max). I intend to store two Int16 values in it via a stored procedure. I am going to consume this column value in C# code so I was hoping if I could do something like 'save one value in the first 8 bits and second value in last 8 bits', etc. I explored SQL bitwise operators but was unable to conclude how I can do it.

我正在处理一个SQL表,其中包含varbinary(max)类型的列。我打算通过存储过程在其中存储两个Int16值。我将在C#代码中使用此列值,所以我希望我能做一些事情,比如'在前8位保存一个值,在最后8位保存第二个值',等等。我探索了SQL按位运算符但是无法总结我如何做到这一点。

Would greatly appreciate if I can get any pointers or links to read-up.

如果我可以得到任何指针或链接读取,将非常感激。

2 个解决方案

#1

You can convert the stored procedure parameters to binary and concatenate them:

您可以将存储过程参数转换为二进制并将它们连接起来:

DECLARE @T TABLE (BinaryValue VARBINARY(MAX)) 

DECLARE @Int1 SMALLINT
DECLARE @Int2 SMALLINT 

SELECT 
    @Int1 = 32767,  
    @Int2 = -32768

INSERT @T (BinaryValue) 
SELECT CAST(ISNULL(@Int1,0) AS VARBINARY(2)) + CAST(ISNULL(@Int2,0) AS VARBINARY(2))

SELECT 
    BinaryValue, 
    Int1 = CAST(SUBSTRING(BinaryValue, 1, 2) AS SMALLINT) ,
    Int2 = CAST(SUBSTRING(BinaryValue, 3, 2) AS SMALLINT) 
FROM 
    @T

#2

To store 2 Int16 values, you obviously need a total of 32 bits, or 4 bytes. Here is some C# code that shows you how you can convert your 2 Int16 values to a byte array and back the other way around using bit shifting.

要存储2个Int16值,显然需要总共32位或4个字节。下面是一些C#代码,它们向您展示如何将2个Int16值转换为字节数组,然后使用位移返回相反的方向。

I realize that you may need to do some of this inside a stored procedure. But if you study the simple bit shifting logic, you shouldn't have a hard time translating the logic into your procedure.

我意识到你可能需要在存储过程中做一些这样的事情。但是如果你研究简单的位移逻辑,你就不应该很难将逻辑转换成你的程序。

Hopefully this will get you started:

希望这会让你开始:

public static void Main(string[] args)
{
    Int16 value1 = 12345;
    Int16 value2 = 31210;
    byte[] bytes = new byte[4];
    bytes[0] = (byte)(value1 >> 8);
    bytes[1] = (byte)value1;
    bytes[2] = (byte)(value2 >> 8);
    bytes[3] = (byte)value2;

    // store the byte array in your db column.

    // Now let's pretend we're reading the byte array and converting back to our numbers.
    Int16 decodedValue1 = (Int16)((bytes[0] << 8) | bytes[1]);
    Int16 decodedValue2 = (Int16)((bytes[2] << 8) | bytes[3]);
    Console.WriteLine(decodedValue1); // prints 12345
    Console.WriteLine(decodedValue2); // prints 31210
}

Here is another way to do it without explicit bit shifting in C#, by using the built-in BitConverter class:

通过使用内置的BitConverter类,这是另一种在C#中没有显式位移的方法:

public static void Main(string[] args)
{
    Int16 value1 = 12345;
    Int16 value2 = 31210;

    byte[] bytes = new byte[4];
    Array.Copy(BitConverter.GetBytes(value1), 0, bytes, 0, 2);
    Array.Copy(BitConverter.GetBytes(value2), 0, bytes, 2, 2);

    // store the byte array in your db column.

    // Now let's pretend we're reading the byte array and converting back to our numbers.
    Int16 decodedValue1 = BitConverter.ToInt16(bytes, 0);
    Int16 decodedValue2 = BitConverter.ToInt16(bytes, 2);
    Console.WriteLine(decodedValue1); // prints 12345
    Console.WriteLine(decodedValue2); // prints 31210
}

#1