.NET 2.0 CLR集成SQL Server 2008导出zip文件损坏问题排查

The root cause of your corrupted zip file is how you're copying the stream. StreamReader and StreamWriter are designed for text data, not binary content like zip files. Using them will mangle the binary data because they handle encoding and can introduce unintended character transformations that break the zip structure.

Step-by-Step Fix #

1. Replace the Text-Based Stream Copy with Binary Copy #

Instead of using text readers/writers, use a byte buffer to copy the raw binary stream directly. Here's the corrected CopyStream method:

public static void CopyStream(Stream input, Stream output)
{
    if (input != null && output != null)
    {
        byte[] buffer = new byte[4096]; // 4KB buffer is efficient for most binary transfer scenarios
        int bytesRead;
        while ((bytesRead = input.Read(buffer, 0, buffer.Length)) > 0)
        {
            output.Write(buffer, 0, bytesRead);
        }
    }
}

2. Clean Up Stream Handling in GetStreamFileResult #

Ensure all disposable resources are properly cleaned up using using statements, which automatically close streams even if an error occurs. Here's the revised method:

private static byte[] GetStreamFileResult(Cookie loginCookie, Guid fileGuid, String baseUri)
{
    string url = "some url"; // Fixed missing semicolon in your original code
    CookieContainer cookies = new CookieContainer();
    cookies.Add(new Uri(url), loginCookie);
    
    using (HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url))
    {
        request.CookieContainer = cookies;
        
        using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())
        using (Stream dataStream = response.GetResponseStream())
        using (MemoryStream ms = new MemoryStream())
        {
            CopyStream(dataStream, ms);
            return ms.ToArray();
        }
    }
}

Key improvements here:

• Directly cast WebRequest.Create to HttpWebRequest to avoid repeated casting
• Wrap all disposable objects (request, response, data stream) in using blocks to ensure proper cleanup
• Removed redundant manual Close() calls since using handles this automatically

3. Verify Export Code Uses the Correct Byte Array #

Ensure your export code uses the byte array returned from GetStreamFileResult (I assume stream in your original code was a typo for this array):

byte[] zipData = GetStreamFileResult(loginCookie, fileGuid, baseUri);
File.WriteAllBytes("C:\\folder\\t.zip", zipData);

Why This Works #

Zip files are binary formats that rely on exact byte sequences. StreamReader attempts to decode bytes into text using a default encoding (like UTF-8), which will corrupt non-text bytes (e.g., compressed data). The byte buffer approach copies raw bytes exactly as they are received, preserving the zip file's integrity.

Additional Tips for SQL Server CLR Integration #

• Ensure your CLR assembly has EXTERNAL_ACCESS or UNSAFE permission (required for web requests and file system access)
• Avoid hardcoding file paths; consider storing configurable paths in SQL Server for flexibility
• Add error handling to check for empty or invalid byte arrays before writing to disk

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