CODEWITHSUNDEEP
c#sqlsql-servert-sql
Aniruddha Gore
Aniruddha Gore

Reputation: 67

How can I store two smallint values in a varbinary(max)?

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.

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

Upvotes: 0

Views: 301

Answers (2)

8kb
8kb

Reputation: 11416

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

Upvotes: 1

sstan
sstan

Reputation: 36553

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.

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:

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
}

Upvotes: 0

Related Questions