SQL Server 圖像/二進位大物件 (BLOB) 儲存與存取機制全解析

前言

醫療影像（X光、CT）、GIS 空間資料、文件管理系統皆需儲存大型二進位物件。SQL Server 提供 image / varbinary(max) / FILESTREAM 等機制，本文深入剖析儲存架構、T-SQL 操作函數、VB6/ADO 串流存取與最佳實務。

一、BLOB 儲存架構原理

資料類型演進

儲存頁面機制

• 行內資料（≤ 8000 bytes）： 直接儲存在資料頁（IN_ROW_DATA）
• 行外資料（> 8000 bytes）： 分配 LOB 頁面（LOB_DATA），資料列僅存 16-byte 指標（Root 指向 B-tree）
• Text/Image 頁面： 8KB 頁面串聯，支援部分讀寫

二、T-SQL 直接操作 BLOB（舊式 API）

適用於 image/text/ntext，不建議新開發使用，僅維護舊系統參考。

核心函數

範例：寫入影像

DECLARE @ptr VARBINARY(16)
SELECT @ptr = TEXTPTR(img_ct) FROM zy_ct WHERE id_ct = 20010101001
WRITETEXT zy_ct.img_ct @ptr 0xFFD8FFE0...  -- 二進位資料

缺點

• 程式碼複雜、易出錯
• READTEXT 無法直接回傳給前端應用
• 需手動管理指標、位移、長度

三、VB6 / ADO 串流存取（GetChunk / AppendChunk）

經典 VB6 應用主流作法，封裝底層 T-SQL 函數。

核心方法

關鍵行為

• 循序存取：每次 GetChunk/AppendChunk 從上次位置繼續
• 切換欄位會重置位置指標
• 欄位為 Null 時呼叫會報錯 → 需先 UPDATE SET col = 0x0 初始化

完整 VB6 範例：檔案寫入資料庫

Public Function AppendBlobFromFile(blobColumn As ADODB.Field, ByVal FileName As String) As Boolean
    On Error GoTo ErrHandler
    Dim FileNum As Long, FileLen As Long, ChunkSize As Long
    Dim Chunks As Long, Fragment As Long, i As Long
    Dim Buffer() As Byte
    
    ChunkSize = 2048  ' 2KB 區塊，避免大記憶體配置
    FileNum = FreeFile
    Open FileName For Binary Access Read As #FileNum
    FileLen = LOF(FileNum)
    
    If IsNull(blobColumn) Then Exit Function
    If FileLen = 0 Then Close #FileNum: AppendBlobFromFile = True: Exit Function
    
    Chunks = FileLen \ ChunkSize
    Fragment = FileLen Mod ChunkSize
    
    ' 先寫入零碎區塊
    If Fragment > 0 Then
        ReDim Buffer(Fragment - 1)
        Get #FileNum, , Buffer
        blobColumn.AppendChunk Buffer
    End If
    
    ' 迴圈寫入完整區塊
    ReDim Buffer(ChunkSize - 1)
    For i = 1 To Chunks
        Get #FileNum, , Buffer
        blobColumn.AppendChunk Buffer
    Next i
    
    Close #FileNum
    AppendBlobFromFile = True
    Exit Function
    
ErrHandler:
    AppendBlobFromFile = False
    MsgBox "寫入圖像錯誤: " & Err.Description, vbCritical
End Function

完整 VB6 範例：資料庫讀出檔案

Public Function ReadBlobToFile(blobColumn As ADODB.Field, ByVal FileName As String) As Boolean
    On Error GoTo ErrHandler
    Dim FileNum As Long, DataLen As Long, ChunkSize As Long
    Dim Chunks As Long, Fragment As Long, i As Long
    Dim Buffer() As Byte
    
    ChunkSize = 2048
    If IsNull(blobColumn) Then Exit Function
    DataLen = blobColumn.ActualSize
    If DataLen < 8 Then Exit Function  ' 非有效圖像
    
    FileNum = FreeFile
    Open FileName For Binary Access Write As #FileNum
    
    Chunks = DataLen \ ChunkSize
    Fragment = DataLen Mod ChunkSize
    
    If Fragment > 0 Then
        ReDim Buffer(Fragment - 1)
        Buffer = blobColumn.GetChunk(Fragment)
        Put #FileNum, , Buffer
    End If
    
    ReDim Buffer(ChunkSize - 1)
    For i = 1 To Chunks
        Buffer = blobColumn.GetChunk(ChunkSize)
        Put #FileNum, , Buffer
    Next i
    
    Close #FileNum
    ReadBlobToFile = True
    Exit Function
    
ErrHandler:
    ReadBlobToFile = False
    MsgBox "讀取圖像錯誤: " & Err.Description, vbCritical
End Function

四、現代 .NET 最佳實務（SqlClient 串流）

強烈建議新專案使用 .NET SqlClient 串流 API，效能與易用性遠勝 VB6/ADO。

寫入範例

using (var conn = new SqlConnection(connStr))
using (var cmd = new SqlCommand("UPDATE Docs SET Content = @Content WHERE Id = @Id", conn))
{
    cmd.Parameters.Add("@Id", SqlDbType.Int).Value = id;
    var param = cmd.Parameters.Add("@Content", SqlDbType.VarBinary, -1); // -1 = max
    param.Value = File.OpenRead(filePath);  // 串流直接繫結，不載入記憶體
    conn.Open();
    cmd.ExecuteNonQuery();
}

讀取範例

using (var conn = new SqlConnection(connStr))
using (var cmd = new SqlCommand("SELECT Content FROM Docs WHERE Id = @Id", conn))
{
    cmd.Parameters.Add("@Id", SqlDbType.Int).Value = id;
    conn.Open();
    using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
    {
        if (reader.Read())
        using (var fs = File.Create(outPath))
        using (var stream = reader.GetStream(0))  // .NET 4.5+ GetStream
            stream.CopyTo(fs);
    }
}

五、效能與架構建議

結語

從 TEXTPTR/WRITETEXT 到 GetChunk/AppendChunk，再到 .NET 串流與 FILESTREAM，SQL Server BLOB 存取經歷了三代演進。現代開發應優先採用 varbinary(max) + .NET 串流 API，大檔案考慮 FILESTREAM，避免將資料庫當成檔案伺服器濫用。記住：資料庫擅長結構化查詢與交易一致性，檔案系統擅長大型二進位串流——各司其職才是王道。