C# ADO.NET 資料庫存取實戰：DataSet、DataAdapter、連線池與參數化查詢完整指南

ADO.NET 架構核心

ADO.NET 採用離線架構，核心物件：

• Connection：資料庫連線 (連線池管理)
• Command：SQL 命令/預存程序 (參數化防注入)
• DataReader：唯讀、前向、串流式讀取 (高效能)
• DataAdapter：橋接資料庫與 DataSet (Fill/Update)
• DataSet：記憶體內離線資料快取 (多 Table、Relation、Constraint)
• DataTable：單一表格資料 (Rows、Columns、Constraints)

提供者模式：跨資料庫通用代碼

// 使用 DbProviderFactory 撰寫提供者無關代碼
string providerName = "System.Data.SqlClient"; // 或 System.Data.OleDb, Npgsql, MySql.Data
var factory = DbProviderFactories.GetFactory(providerName);

using (var conn = factory.CreateConnection())
{
    conn.ConnectionString = GetConnectionString(providerName);
    conn.Open();
    
    using (var cmd = factory.CreateCommand())
    {
        cmd.Connection = conn;
        cmd.CommandText = "SELECT * FROM Categories";
        
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine($"{reader["CategoryID"]}: {reader["CategoryName"]}");
            }
        }
    }
}

實戰範例：Access 資料庫 (OleDb) 完整操作

using System;
using System.Data;
using System.Data.OleDb;

public class AccessDemo
{
    private const string ConnString = 
        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=BugTypes.mdb;";

    public static void QueryCategories()
    {
        using (var conn = new OleDbConnection(ConnString))
        using (var adapter = new OleDbDataAdapter("SELECT * FROM Categories", conn))
        {
            // DataSet 為離線容器，可包含多 Table
            var dataSet = new DataSet();
            
            // Fill 開啟連線、執行查詢、填入 DataSet、關閉連線
            adapter.Fill(dataSet, "Categories");
            
            // 離線操作 DataSet
            DataTable table = dataSet.Tables["Categories"];
            
            Console.WriteLine($"資料表數量: {dataSet.Tables.Count}");
            Console.WriteLine($"Categories 列數: {table.Rows.Count}");
            Console.WriteLine($"Categories 欄位數: {table.Columns.Count}");
            
            // 列舉欄位中繼資料
            foreach (DataColumn col in table.Columns)
            {
                Console.WriteLine($"欄位: {col.ColumnName} ({col.DataType})");
            }
            
            // 列舉資料列
            foreach (DataRow row in table.Rows)
            {
                Console.WriteLine($"ID={row["CategoryID"]}, Name={row["CategoryName"]}");
            }
        } // 自動關閉連線、釋放資源
    }

    public static void ParameterizedQuery(int categoryId)
    {
        using (var conn = new OleDbConnection(ConnString))
        using (var cmd = new OleDbCommand("SELECT CategoryName FROM Categories WHERE CategoryID = ?", conn))
        {
            // 參數化查詢：防止 SQL Injection、處理資料型別、提升效能
            cmd.Parameters.AddWithValue("@CategoryID", categoryId);
            
            conn.Open();
            object result = cmd.ExecuteScalar();
            Console.WriteLine($"CategoryID={categoryId} → {result}");
        }
    }

    public static void InsertCategory(string name)
    {
        using (var conn = new OleDbConnection(ConnString))
        using (var cmd = new OleDbCommand("INSERT INTO Categories (CategoryName) VALUES (?)", conn))
        {
            cmd.Parameters.AddWithValue("@CategoryName", name);
            conn.Open();
            int affected = cmd.ExecuteNonQuery();
            Console.WriteLine($"新增 {affected} 筆資料");
        }
    }

    public static void UpdateWithDataAdapter()
    {
        using (var conn = new OleDbConnection(ConnString))
        {
            var adapter = new OleDbDataAdapter("SELECT * FROM Categories", conn);
            
            // 關鍵：CommandBuilder 自動產生 INSERT/UPDATE/DELETE 指令
            var builder = new OleDbCommandBuilder(adapter);
            
            var dataSet = new DataSet();
            adapter.Fill(dataSet, "Categories");
            
            DataTable table = dataSet.Tables["Categories"];
            
            // 修改資料
            DataRow newRow = table.NewRow();
            newRow["CategoryName"] = "New Category " + DateTime.Now.Ticks;
            table.Rows.Add(newRow);
            
            // 修改現有列
            if (table.Rows.Count > 1)
            {
                table.Rows[1]["CategoryName"] = "Updated " + DateTime.Now.Ticks;
            }
            
            // 刪除
            if (table.Rows.Count > 2)
            {
                table.Rows[2].Delete();
            }
            
            // 批次更新回資料庫 (依 RowState 執行對應指令)
            int affected = adapter.Update(dataSet, "Categories");
            Console.WriteLine($"資料庫同步更新 {affected} 筆異動");
        }
    }
}

連線字串最佳實務

連線字串安全儲存

• appsettings.json (ASP.NET Core) + 使用者密碼存儲 / Azure Key Vault
• web.config / app.config <connectionStrings> 區段 + aspnet_regiis -pef 加密
• 絕不硬編碼在代碼中，絕不提交至版控

連線池：效能關鍵

• ADO.NET 自動啟用連線池 (預設啟用，`Pooling=true`)
• 相同連線字串 (逐字元比對) 共用池
• using 區塊結束時，連線歸還池中而非實際關閉
• 關鍵屬性：Min Pool Size、Max Pool Size (預設 100)、Connection Lifetime
• 避免：長時間持有連線、不同連線字串變體、忘記 Dispose

DataSet 進階功能

多表關聯與約束

var dataSet = new DataSet();

// 填入多表
adapter.Fill(dataSet, "Categories");
adapter.Fill(dataSet, "Products");

// 定義關聯
DataColumn parentCol = dataSet.Tables["Categories"].Columns["CategoryID"];
DataColumn childCol = dataSet.Tables["Products"].Columns["CategoryID"];
dataSet.Relations.Add("Cat_Products", parentCol, childCol);

// 導航關聯
foreach (DataRow catRow in dataSet.Tables["Categories"].Rows)
{
    Console.WriteLine($"分類: {catRow["CategoryName"]}");
    foreach (DataRow prodRow in catRow.GetChildRows("Cat_Products"))
    {
        Console.WriteLine($"  產品: {prodRow["ProductName"]}");
    }
}

// 加入唯一約束
dataSet.Tables["Categories"].Constraints.Add("PK_CategoryID", 
    dataSet.Tables["Categories"].Columns["CategoryID"], true);

資料檢視：排序、篩選、自訂視圖

DataView view = new DataView(dataSet.Tables["Products"])
{
    Sort = "UnitPrice DESC",
    RowFilter = "UnitPrice > 50 AND Discontinued = false"
};

foreach (DataRowView rowView in view)
{
    Console.WriteLine($"{rowView["ProductName"]}: ${rowView["UnitPrice"]}");
}

非同步資料庫操作

public async Task GetCategoriesAsync()
{
    using (var conn = new SqlConnection(_connString))
    using (var cmd = new SqlCommand("SELECT * FROM Categories", conn))
    {
        await conn.OpenAsync();
        using (var reader = await cmd.ExecuteReaderAsync())
        {
            var table = new DataTable();
            table.Load(reader); // DataTable.Load 可直接從 DataReader 載入
            return table;
        }
    }
}

// DataAdapter 非同步
await adapter.FillAsync(dataSet, "Categories");
await adapter.UpdateAsync(dataSet, "Categories");

交易處理

using (var conn = new SqlConnection(connString))
{
    conn.Open();
    using (var transaction = conn.BeginTransaction())
    {
        try
        {
            using (var cmd = conn.CreateCommand())
            {
                cmd.Transaction = transaction;
                cmd.CommandText = "UPDATE Accounts SET Balance -= @amt WHERE Id = @from";
                cmd.Parameters.AddWithValue("@amt", 100);
                cmd.Parameters.AddWithValue("@from", 1);
                cmd.ExecuteNonQuery();
                
                cmd.CommandText = "UPDATE Accounts SET Balance += @amt WHERE Id = @to";
                cmd.Parameters["@to"].Value = 2;
                cmd.ExecuteNonQuery();
            }
            transaction.Commit();
        }
        catch
        {
            transaction.Rollback();
            throw;
        }
    }
}

// System.Transactions 交易範圍 (跨多連線、分散式交易)
using (var scope = new TransactionScope())
{
    using (var conn1 = new SqlConnection(connString1)) { /* ... */ }
    using (var conn2 = new SqlConnection(connString2)) { /* ... */ }
    scope.Complete();
}

常見效能優化技巧

總結

ADO.NET 提供離線資料架構，核心在於 Connection、Command、DataReader、DataAdapter、DataSet 協作。掌握參數化查詢防注入、連線池運作、DataSet 離線操作與批次更新、非同步模式、交易一致性，並善用提供者模式撰寫跨資料庫代碼，即能建構高效能、安全、可維護的資料存取層。