CODEWITHSUNDEEP
c#sql-server
VasyPupkin
VasyPupkin

Reputation: 153

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

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());
    } 
}

Upvotes: 2

Views: 2489

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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) ...

Upvotes: 2

Related Questions