How varbinary type in SQL Server use in function in C#

Question

I have encountered a type problem. I have in SQL Server table with this structure

create table dbo.DataWithCompressedXML
(
    ID int not null,
    Data varbinary(max) not null,
)

The data looks like:

0x0BC9C82C5600A2448592D4E21285E292A2CCBC74454500

Then I want take this value to C# to decompress, for decompress I use function near, after decompress I see the text like (This is a test string!!), but I can't understand in what type I must take my variable value from DB, and then use my function this is a test string!

public static SqlBytes BinaryDecompress(SqlBytes input)
{
    if (input.IsNull)
        return SqlBytes.Null;

    int batchSize = 32768;
    byte[] buf = new byte[batchSize];

    using (MemoryStream result = new MemoryStream())
    {
        using (DeflateStream deflateStream = 
            new DeflateStream(input.Stream, CompressionMode.Decompress, true))
        {
            int bytesRead;
            while ((bytesRead = deflateStream.Read(buf, 0, batchSize)) > 0)
                result.Write(buf, 0, bytesRead);
        }

        return new SqlBytes(result.ToArray());
    } 
}

Well, what does your varbinary column store?? Images? PDF files? Certificates? Depending on what it is, you need to use different data types to handle the decompressed data..... — marc_s
– marc_s, Commented Apr 14, 2016 at 10:47
it consist compress xml, but i dont uderstand how download this varbinary from db in C# and then to decompress by function what i show — VasyPupkin
– VasyPupkin, Commented Apr 14, 2016 at 10:54

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

The question is the word "compressed" ... If you store an XML in a column of varbinary(max) you will see such a HEX-string

SELECT CAST('<root>test</root>' AS VARBINARY(MAX))

--Result: 0x3C726F6F743E746573743C2F726F6F743E

SELECT CAST(0x3C726F6F743E746573743C2F726F6F743E AS VARCHAR(MAX));
SELECT CAST(0x3C726F6F743E746573743C2F726F6F743E AS XML);

Both will return the same, the first a plain text, the second as "real" XML

If you compressed this somehow, you'll first have to de-compress this as you are trying to do this with your DeflateStream. Are you sure, that this is a valid decompression for your given input?

What is the return value of your function (the content of SqlBytes)?

XML in SQL Server is coded UTF-16. If the de-compression is done correctly, it should work to convert your SqlBytes to an UTF-16 string.

Regrettfully your database column is not XML but VARBINARY(MAX), so it might be a different encoding...

UPDATE

According to your comment:

string hex = "0BC9C82C5600A2448592D4E21285E292A2CCBC74454500";
//credits: http://stackoverflow.com/a/321404/5089204
var byteArray = Enumerable.Range(0, hex.Length / 2)
                            .Select(x => Convert.ToByte(hex.Substring(x * 2, 2), 16))
                            .ToArray();
int batchSize = 32768;
byte[] buf = new byte[batchSize];

using (MemoryStream result = new MemoryStream()) {
    using (DeflateStream deflateStream = new DeflateStream(new MemoryStream(byteArray), CompressionMode.Decompress)) {
        int bytesRead;
        while ((bytesRead = deflateStream.Read(buf, 0, batchSize)) > 0)
            result.Write(buf, 0, bytesRead);
    }
}
string s = System.Text.Encoding.Default.GetString(buf);

After this the variable "s" holds "This is a test string!!"

UPDATE2: As to your comment convert in SqlBytes

This should work as easy as new SqlBytes(buf) ...

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Apr 14, 2016 at 11:05

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

VasyPupkin Over a year ago

can your help me convert String String a="0x0BC9C82C5600A2448592D4E21285E292A2CCBC74454500"; in SQLBytes

Gottfried Lesigang Over a year ago

@VasyPupkin Attention, you must put the hex string into the string variable without the "0x"...

Gottfried Lesigang Over a year ago

@VasyPupkin added one tiny update... Does it work now?

Collectives™ on Stack Overflow

How varbinary type in SQL Server use in function in C#

1 Answer 1

UPDATE

UPDATE2: As to your comment convert in SqlBytes

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

UPDATE

UPDATE2: As to your comment convert in SqlBytes

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related