C# 大文本導入數據庫：DataTable 類型轉換與流式處理架構

場景：百萬行文本文件（自定義分隔符）導入數據庫，需根據目標表結構動態構建 `DataTable` 並進行強類型轉換。

核心難點

1. 運行時動態確定列數與類型（來自 `user_tab_columns`）。
2. 數據庫類型（如 `DATE`、`NUMBER`）到 CLR 類型（`DateTime`、`Decimal`）的映射不一一對應。
3. 百萬行數據若全載入內存極易 OOM。

解決方案：流式分批 + 類型轉換器模式

1. 類型映射字典（啟動時構建一次）

private static readonly Dictionary DbTypeToClr = new()
{
    {"VARCHAR2", typeof(string)}, {"NVARCHAR2", typeof(string)},
    {"NUMBER", typeof(decimal)}, {"INT", typeof(int)},
    {"DATE", typeof(DateTime)}, {"TIMESTAMP", typeof(DateTime)},
    {"CLOB", typeof(string)}, {"BLOB", typeof(byte[])}
};

2. 通用轉換方法（單行處理）

private static object ConvertValue(string raw, Type targetType)
{
    if (string.IsNullOrEmpty(raw)) return DBNull.Value;
    try
    {
        if (targetType == typeof(DateTime))
            return DateTime.ParseExact(raw, new[] {"yyyy-MM-dd", "yyyy/MM/dd HH:mm:ss"}, null);
        return Convert.ChangeType(raw, targetType);
    }
    catch { return DBNull.Value; } // 或拋出異常記錄壞行
}

3. 流式讀取 + 分批寫入

public async Task ImportLargeFile(string filePath, string tableName, string splitChar, int batchSize = 5000)
{
    var dt = BuildTargetDataTable(tableName); // 根據元數據構建空 DataTable
    using var reader = new StreamReader(filePath, Encoding.Default);
    string line;
    int count = 0;
    
    while ((line = await reader.ReadLineAsync()) != null)
    {
        var fields = line.Split(new[] { splitChar }, StringSplitOptions.None);
        var row = dt.NewRow();
        for (int i = 0; i < Math.Min(fields.Length, dt.Columns.Count); i++)
            row[i] = ConvertValue(fields[i], dt.Columns[i].DataType);
        dt.Rows.Add(row);
        
        if (++count % batchSize == 0)
        {
            await BulkInsertAsync(dt); // SqlBulkCopy / OracleBulkCopy
            dt.Clear(); // 釋放內存
        }
    }
    if (dt.Rows.Count > 0) await BulkInsertAsync(dt);
}

關鍵優勢

• 內存恆定：僅緩存 `batchSize` 行，避免 LOH 碎片。
• 類型安全：集中管理轉換邏輯，易於擴展自定義類型（如 JSON/XML 列）。
• 高性能：`SqlBulkCopy` 批量寫入比逐條 `INSERT` 快 10-100 倍。