資料庫存儲大型文本/圖片：UTF-8 二進位字串轉換與 Image 欄位讀寫完整方案

文本存儲：防注入、支援 Unicode 的 UTF-8 二進位字串方案

將字串 → UTF-8 位元組陣列 → 逗點分隔字串存入 NVARCHAR(MAX) / TEXT，讀取時反向還原。

字串 → 二進位字串

private string TextToBinString(string text) {
    var bytes = Encoding.UTF8.GetBytes(text);
    return string.Join(",", bytes); // "72,101,108,108,111"
}

二進位字串 → 字串

private string BinStringToText(string binStr) {
    var parts = binStr.Split(',');
    var bytes = new byte[parts.Length];
    for (int i = 0; i < parts.Length; i++)
        bytes[i] = byte.Parse(parts[i]);
    return Encoding.UTF8.GetString(bytes);
}

優化版 (避免大字串分割效能問題)

private string BinStringToTextFast(string binStr) {
    var bytes = binStr.Split(',').Select(byte.Parse).ToArray();
    return Encoding.UTF8.GetString(bytes);
}
// 或直接存 VARBINARY/IMAGE 欄位更高效 (見下)

圖片存儲：二進位串流直寫 IMAGE/VARBINARY(MAX) 欄位

寫入 (上傳)

private void SaveImage(HttpPostedFile postedFile, string description) {
    int len = postedFile.ContentLength;
    if (len == 0) return;

    byte[] buffer = new byte[len];
    postedFile.InputStream.Read(buffer, 0, len);

    using (var conn = new SqlConnection(connStr)) {
        const string sql = @"
            INSERT INTO ImageStore (ImageData, ContentType, Description, Size)
            VALUES (@Data, @Type, @Desc, @Size)";
        var cmd = new SqlCommand(sql, conn);
        cmd.Parameters.Add("@Data", SqlDbType.Image, len).Value = buffer;
        cmd.Parameters.Add("@Type", SqlDbType.NVarChar, 50).Value = postedFile.ContentType;
        cmd.Parameters.Add("@Desc", SqlDbType.NVarChar, 200).Value = description;
        cmd.Parameters.Add("@Size", SqlDbType.BigInt).Value = len;
        conn.Open();
        cmd.ExecuteNonQuery();
    }
}

讀取 (輸出)

private void GetImage(int id) {
    using (var conn = new SqlConnection(connStr)) {
        const string sql = "SELECT ImageData, ContentType, Size FROM ImageStore WHERE Id = @Id";
        var cmd = new SqlCommand(sql, conn);
        cmd.Parameters.Add("@Id", SqlDbType.Int).Value = id;
        conn.Open();
        using (var rd = cmd.ExecuteReader()) {
            if (rd.Read()) {
                Response.ContentType = rd.GetString(1); // ContentType
                Response.OutputStream.Write((byte[])rd[0], 0, rd.GetInt64(2));
                Response.Flush();
            }
        }
    }
}

// 呼叫方式: 

替代方案：MemoryStream + GDI+ 處理

byte[] bytes = (byte[])rd["ImageData"];
using (var ms = new MemoryStream(bytes)) {
    var img = Image.FromStream(ms);
    pictureBox1.Image = img; // WinForms 顯示
}

關鍵最佳實務