C# ADO.NET 連線式/斷開式操作經典練習（八）：SqlDataReader、DataAdapter、存儲過程與參數化查詢

前言

本文整理自《C# 網絡應用編程基礎練習題與答案（八）》，涵蓋 **連線式架構** (`SqlConnection`/`SqlCommand`/`SqlDataReader`)、**斷開式架構** (`SqlDataAdapter`/`DataSet`/`DataTable`)、**存儲過程參數化** 等 ADO.NET 核心實戰技能。

習題一：連線式查詢 — 各年級平均成績

需求：使用 `SqlDataReader` 計算 `MyTable2` 中按學號前兩位 (年級) 分組的平均成績，顯示於 `ListBox`。

private void btnQueryAvg_Click(object sender, EventArgs e)
{
    listBox.Items.Clear();
    listBox.Items.Add("年級\t平均成績");

    string connStr = Properties.Settings.Default.MyDatabaseConnectionString;
    using (var conn = new SqlConnection(connStr))
    using (var cmd = new SqlCommand(
        "SELECT SUBSTRING(學號, 1, 2) AS 年級, AVG(成績) AS 平均成績 " +
        "FROM MyTable2 GROUP BY SUBSTRING(學號, 1, 2)", conn))
    {
        try
        {
            conn.Open();
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    listBox.Items.Add($"{reader["年級"]}級\t{reader["平均成績"]:F2}");
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show($"查詢失敗: {ex.Message}");
        }
    }
}

關鍵點

• `using` 確保連線/命令/讀取器**確定性釋放** (即使拋異常)。
• `SUBSTRING(學號,1,2)` 取年級 (SQL Server 語法)，`AVG()` 聚合函數。
• `reader["欄位名"]` 索引器支援字串/整數索引，回傳 `object` 需格式化。

習題二：連線式查詢 — 不及格學生明細

需求：查詢成績 < 60 的學生學號、姓名、性別、成績。

private void btnFailed_Click(object sender, EventArgs e)
{
    listBox.Items.Clear();
    listBox.Items.Add("學號\t姓名\t性別\t成績");

    string connStr = Properties.Settings.Default.MyDatabaseConnectionString;
    using (var conn = new SqlConnection(connStr))
    using (var cmd = new SqlCommand(
        "SELECT 學號, 姓名, 性別, 成績 FROM MyTable2 WHERE 成績 < 60", conn))
    {
        try
        {
            conn.Open();
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    listBox.Items.Add($"{reader[0]}\t{reader[1]}\t{reader[2]}\t{reader[3]}");
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show($"查詢失敗: {ex.Message}");
        }
    }
}

習題三：斷開式架構 — ComboBox 載入資料

需求：`Form_Load` 時用 `SqlDataAdapter` 填充 `DataSet`，以 `"[編碼] 名稱"` 格式填入 `ComboBox`。

private void Form1_Load(object sender, EventArgs e)
{
    string connStr = Properties.Settings.Default.MyDatabaseConnectionString;
    using (var conn = new SqlConnection(connStr))
    using (var adapter = new SqlDataAdapter("SELECT * FROM MyTable1", conn))
    {
        var ds = new DataSet();
        try
        {
            adapter.Fill(ds, "MyTable1");
            foreach (DataRow row in ds.Tables["MyTable1"].Rows)
            {
                comboBox.Items.Add($"[{row[0]}] {row[1]}");
            }
            if (comboBox.Items.Count > 0) comboBox.SelectedIndex = 0;
        }
        catch (Exception ex)
        {
            MessageBox.Show($"載入失敗: {ex.Message}");
        }
    }
}

斷開式 vs 連線式

習題四：DataAdapter 填寫 DataSet 完整語法

填空題：

string connString = "server=localhost;Integrated Security=SSPI;database=pubs";
SqlConnection conn = new SqlConnection(connString); // ← 正確：用連線字串建構
string strsql = "select * from MyTable2";
SqlDataAdapter adapter = new SqlDataAdapter(strsql, conn); // ← 正確：SQL + 連線
DataSet dataset = new DataSet();
adapter.Fill(dataset, "MyTable2"); // ← 正確：填充至 DataSet 並指定表名
this.dataGridView1.DataSource = dataset.Tables["MyTable2"];

常見錯誤

• `new SqlDataAdapter(conn)` 忘記 SQL 指令 → 需 `new SqlDataAdapter(sql, conn)` 或 `adapter.SelectCommand = cmd`。
• `Fill(dataset)` 未指定表名 → 產生 `Table`, `Table1`... 不易引用。

習題五：斷開式查詢 — 年齡 > 18 且禁止編輯

private void btnAdults_Click(object sender, EventArgs e)
{
    string connStr = Properties.Settings.Default.MyDatabaseConnectionString;
    using (var conn = new SqlConnection(connStr))
    using (var adapter = new SqlDataAdapter(
        "SELECT id, xm, xb, nl FROM person WHERE nl > 18 ORDER BY id", conn))
    {
        var ds = new DataSet();
        try
        {
            adapter.Fill(ds, "person");
            dataGridView.DataSource = ds.Tables["person"];

            // 禁止使用者編輯
            dataGridView.AllowUserToAddRows = false;
            dataGridView.AllowUserToDeleteRows = false;
            dataGridView.ReadOnly = true; // 整體唯讀
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }
}

習題六：存儲過程參數 `nvarchar` vs `nchar` 陷阱

問題：`@surname nvarchar(2)` 改為 `@surname nchar(2)`，結果是否正確？

答案：**不一定。**

• `nvarchar(2)`：變長，儲存 `"王"` 佔 2 字元。
• `nchar(2)`：定長，**不足右側填補空格**，儲存 `"王"` 實際為 `"王 "` (含尾隨空格)。
• 若呼叫端傳 `"王"`，存儲過程內比較 `@surname = '王'` 會**失敗** (因實際是 `'王 '`)。
• 解決：存儲過程內用 `RTRIM(@surname)` 或呼叫端 `PadRight(2)`，或統一用 `nvarchar`。

習題七：存儲過程 OUTPUT 參數 — 統計包含某漢字的人數

private void btnCountByChar_Click(object sender, EventArgs e)
{
    if (string.IsNullOrWhiteSpace(txtChar.Text))
    {
        MessageBox.Show("請輸入漢字");
        return;
    }

    string connStr = Properties.Settings.Default.MyDatabaseConnectionString;
    using (var conn = new SqlConnection(connStr))
    using (var cmd = new SqlCommand("SelectFilterStudentsNum", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@surname", txtChar.Text.Trim());
        // OUTPUT 參數必須指定 Direction
        var outParam = cmd.Parameters.Add("@record", SqlDbType.Int);
        outParam.Direction = ParameterDirection.Output;

        try
        {
            conn.Open();
            cmd.ExecuteNonQuery(); // 執行存儲過程
            int count = (int)outParam.Value;
            MessageBox.Show($"有 {count} 條記錄包含「{txtChar.Text}」");
        }
        catch (Exception ex)
        {
            MessageBox.Show($"統計失敗: {ex.Message}");
        }
    }
}

存儲過程範例 (SQL Server)

CREATE PROCEDURE SelectFilterStudentsNum
    @surname NVARCHAR(10),
    @record INT OUTPUT
AS
BEGIN
    SELECT @record = COUNT(*) 
    FROM MyTable2 
    WHERE 姓名 LIKE '%' + @surname + '%';
END

總結：ADO.NET 實戰核心清單

1. **連線字串放設定檔** (`Settings.settings` / `appsettings.json`)，不硬編碼。
2. **`using` 區塊包裹** `SqlConnection`、`SqlCommand`、`SqlDataReader`、`SqlDataAdapter`、`DataSet` — 確保釋放非託管資源 (連線池、鎖)。
3. **參數化查詢** (`@param`) — 防 SQL 注入、計畫快取重用、型別安全。
4. **`AddWithValue` 便利但隱含型別推斷風險** — 關鍵查詢建議顯式 `cmd.Parameters.Add("@p", SqlDbType.NVarChar, 50).Value = val`。
5. **OUTPUT/RETURN VALUE** — `Direction = ParameterDirection.Output` / `ReturnValue`，`ExecuteNonQuery` 後讀取 `.Value`。
6. **`DataReader` 唯讀順向** — 高效能串流場景首選；**`DataAdapter` + `DataSet`** — UI 繫結、離線編輯、多表關聯首選。
7. **`nchar` 定長填空格陷阱** — 比較/查詢時務必 `RTRIM` 或用 `nvarchar`。