ADO.NET 獲取資料庫結構描述 (中繼資料) 完整實作：GetSchema 與 CommandBehavior.SchemaOnly

.NET 專案常需動態讀取資料庫結構 (資料表、檢視、欄位、主鍵、外鍵、索引等) 以支援代碼生成、同步工具、動態查詢建構。ADO.NET 內建 DbConnection.GetSchema() 與 CommandBehavior.SchemaOnly 兩大標準 API，無需撰寫資料庫特定查詢，支援 SQL Server、Oracle、MySQL、PostgreSQL 等所有 ADO.NET Provider。

核心 API 對比

GetSchema 常用集合名稱 (SQL Server 範例)

// 标准集合 (所有 Provider 支援)
"MetaDataCollections"        // 列出所有支援的集合名稱
"DataSourceInformation"      // 提供者資訊
"DataTypes"                  // 資料型別映射
"Restrictions"               // 每個集合支援的限制條件

// SQL Server 特有集合 (SqlClientMetaDataCollectionNames)
"Tables"                     // 使用者資料表
"Views"                      // 檢視表
"Columns"                    // 欄位詳細定義
"Indexes"                    // 索引定義
"IndexColumns"               // 索引包含欄位
"ForeignKeys"                // 外鍵關聯
"ProcedureParameters"        // 預存程序參數
"Procedures"                 // 預存程序清單

完整實作範例：通用 Schema 讀取器

using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;

public class DbSchemaReader
{
    private readonly DbProviderFactory _factory;
    private readonly string _connectionString;

    public DbSchemaReader(string providerName, string connectionString)
    {
        _factory = DbProviderFactories.GetFactory(providerName);
        _connectionString = connectionString;
    }

    // 1. 取得所有使用者資料表/檢視名稱
    public List GetTableNames(bool includeViews = true)
    {
        var tables = new List();
        using (var conn = _factory.CreateConnection())
        {
            conn.ConnectionString = _connectionString;
            conn.Open();

            // Tables 集合限制: [Database, Schema, TableName, TableType]
            string[] restrictions = new string[4];
            restrictions[3] = includeViews ? "VIEW" : "BASE TABLE"; // 或 "BASE TABLE,VIEW"

            var schema = conn.GetSchema("Tables", restrictions);
            foreach (DataRow row in schema.Rows)
                tables.Add(row["TABLE_NAME"].ToString());
        }
        return tables;
    }

    // 2. 取得指定資料表完整欄位定義
    public DataTable GetColumns(string tableName, string schemaName = "dbo")
    {
        using (var conn = _factory.CreateConnection())
        {
            conn.ConnectionString = _connectionString;
            conn.Open();
            // Columns 限制: [Database, Schema, Table, Column]
            var restrictions = new string[] { null, schemaName, tableName, null };
            return conn.GetSchema("Columns", restrictions);
        }
    }

    // 3. 取得主鍵/外鍵/索引資訊
    public DataTable GetPrimaryKeys(string tableName, string schemaName = "dbo")
    {
        using (var conn = _factory.CreateConnection())
        {
            conn.ConnectionString = _connectionString;
            conn.Open();
            var restrictions = new string[] { null, schemaName, tableName };
            return conn.GetSchema("IndexColumns", restrictions); // 或 "PrimaryKeys"
        }
    }

    // 4. 透過 SchemaOnly 取得任意查詢的結果欄位中繼資料
    public DataTable GetQuerySchema(string sql, params DbParameter[] parameters)
    {
        using (var conn = _factory.CreateConnection())
        {
            conn.ConnectionString = _connectionString;
            conn.Open();
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = sql;
                if (parameters != null) foreach (var p in parameters) cmd.Parameters.Add(p);
                using (var reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo))
                {
                    return reader.GetSchemaTable(); // 回傳 DataTable: ColumnName, DataType, AllowDBNull, IsKey, ColumnSize, NumericPrecision...
                }
            }
        }
    }
}

實戰呼叫示範

var reader = new DbSchemaReader("System.Data.SqlClient", connStr);

// 列出所有表
foreach (var t in reader.GetTableNames()) Console.WriteLine(t);

// 讀取 Users 表欄位
var cols = reader.GetColumns("Users");
foreach (DataRow r in cols.Rows)
    Console.WriteLine($"{r["COLUMN_NAME"]}\t{r["DATA_TYPE"]}\t{r["IS_NULLABLE"]}\t{r["COLUMN_DEFAULT"]}");

// 動態查詢 Schema
var qSchema = reader.GetQuerySchema("SELECT u.Id, u.Name, COUNT(o.Id) AS OrderCount FROM Users u LEFT JOIN Orders o ON u.Id = o.UserId GROUP BY u.Id, u.Name");
foreach (DataRow r in qSchema.Rows)
    Console.WriteLine($"{r["ColumnName"]} = {r["DataType"]} (Key={r["IsKey"]})");

跨資料庫 Provider 關鍵差異提醒

最佳實務建議

1. 快取 Schema：啟動時讀取並快取，避免重複往返
2. 使用限制條件：`GetSchema(collection, restrictions)` 大幅減少傳輸量
3. SchemaOnly + KeyInfo：動態 SQL 欄位中繼資料首選，不回傳任何資料列
4. 異步化：.NET Core 3.1+ 使用 `GetSchemaAsync`、`ExecuteReaderAsync`

善用 ADO.NET 內建 Schema API，讓你的工具跨資料庫、零硬編碼、優雅高效。