Decompress SQL Server compressed varbinary Array in C#

Question

I have a table in SQL Server with a varbinary(MAX) column which is filled with SQL Compress method of JSON string with utf8 characters for example {"id": 12, title: "فروش"} in the sql I use CAST(DECOMPRESS(data) AS NVARCHAR(MAX)) and result is ok.

In the c# I use this code for decompressing the data column:

public static string Unzip(byte[] bytes)
{
    using (var msi = new MemoryStream(bytes))
    using (var mso = new MemoryStream())
    {
        using (var gs = new GZipStream(msi, CompressionMode.Decompress))
        {
            CopyTo(gs, mso);
        }
        return Encoding.UTF8.GetString(mso.ToArray());
    }
}

But the result is an invalid string:

It's DeflateStream.

GSerg
– GSerg

2020-08-11 10:55:23 +00:00
Commented Aug 11, 2020 at 10:55 — GSerg
– GSerg, Commented Aug 11, 2020 at 10:55

Morten Bork · Accepted Answer · 2020-08-11 11:47:43Z

6

I believe your string is in the format "Unicode" but you are trying to use the encoding type UTF8?

edited Aug 11, 2020 at 11:47

answered Aug 11, 2020 at 10:56

Morten Bork

1,67714 silver badges25 bronze badges

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

3 Comments

Hamed Mahdizadeh Over a year ago

Thank you very much. I changed the encoding and every things got right.

Morten Bork Over a year ago

You are quite welcome, I just realized how much of a nerd I am, I recognized the unicode format... ugh. I need to re-evaluate my life :D

Rocklan Over a year ago

@mortenBork relax, we all have our own unique nerd super powers. This is yours.

Dave Black · Accepted Answer · 2022-06-21 21:11:38Z

0

Depending on if you have the default Collations enabled in SQL, NVARCHAR will be returned as Unicode while VARCHAR will be returned as UTF-8. You could also use SQL Output Collation to force an encoding:

SELECT CAST('abc' AS varchar(5)) COLLATE Latin1_General_100_CI_AS_SC_UTF8

answered Jun 21, 2022 at 21:11

Dave Black

8,1992 gold badges58 silver badges47 bronze badges

Collectives™ on Stack Overflow

Decompress SQL Server compressed varbinary Array in C#

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related