使用Deflate压缩多文化JSON存SQL Server后读取遇Stream.Read异常

Hey, let's break down what's going wrong here and fix it step by step.

The core issue is that you're treating binary compressed data as a UTF-8 string—that's a classic mistake that corrupts your data before you even get to the Stream.Read step. Here's the breakdown:

What's Causing the Error? #

• Deflate produces raw binary data, not text. When you store this binary data as a string in SQL Server, the database (and your code) tries to interpret it as UTF-8 text. Since compressed bytes aren't valid UTF-8 sequences, some bytes get replaced with placeholder characters (like �) during the string conversion.
• When you later convert that corrupted string back to bytes with Encoding.UTF8.GetBytes(), you're not getting the original compressed data anymore. The Deflate stream can't parse this mangled byte array, hence the InvalidDataException.

Step-by-Step Fixes #

1. Use the Correct SQL Server Data Type #

Stop using string-based types (like VARCHAR, NVARCHAR, TEXT) for compressed data. Instead, use VARBINARY(MAX)—this type is designed to store raw binary data without any encoding or interpretation, so your compressed bytes stay intact.

2. Fix the Compression & Storage Flow #

You need to work directly with byte arrays for compression, not strings. Here's a corrected C# example:

// Your original multi-language JSON string
string originalJson = "{\"@id\":\"2113\",\"description\":\"อาหารเช้าคอนติเนนทัล\"}";

// Convert JSON to UTF-8 bytes first
byte[] jsonUtf8Bytes = Encoding.UTF8.GetBytes(originalJson);

// Compress the UTF-8 bytes with Deflate
byte[] compressedBytes;
using (var compressedStream = new MemoryStream())
{
    using (var deflateStream = new DeflateStream(compressedStream, CompressionMode.Compress))
    {
        deflateStream.Write(jsonUtf8Bytes, 0, jsonUtf8Bytes.Length);
    }
    compressedBytes = compressedStream.ToArray();
}

// Store compressedBytes directly into your SQL Server VARBINARY(MAX) column
// (Use parameterized queries here to avoid issues—never concatenate binary data into SQL strings)

3. Fix the Reading & Decompression Flow #

When reading from the database, grab the raw VARBINARY bytes and decompress them directly—no string conversion involved:

// Read the raw compressed bytes from your VARBINARY(MAX) column
byte[] compressedBytes = ...; // Your database retrieval code here

// Decompress the bytes back to JSON
string decompressedJson;
using (var compressedStream = new MemoryStream(compressedBytes))
{
    using (var deflateStream = new DeflateStream(compressedStream, CompressionMode.Decompress))
    {
        using (var resultStream = new MemoryStream())
        {
            deflateStream.CopyTo(resultStream);
            byte[] jsonUtf8Bytes = resultStream.ToArray();
            decompressedJson = Encoding.UTF8.GetString(jsonUtf8Bytes);
        }
    }
}

// Now you can work with decompressedJson—no InvalidDataException!

4. What About Existing Corrupted Data? #

Unfortunately, any compressed data you already stored as strings is permanently corrupted (the encoding replacement destroyed critical bytes). You'll need to re-compress the original JSON data and store it correctly using the VARBINARY(MAX) method above.

Key Takeaway #

Binary data (like compressed streams) should never be converted to strings unless you explicitly encode it with a binary-to-text format (like Base64)—but even then, storing it as VARBINARY is more efficient and avoids encoding mishaps. For Deflate-compressed JSON, stick to byte arrays and VARBINARY(MAX) all the way.

内容的提问来源于stack exchange，提问作者user3293355