您现在的位置：程式碼之家 >> 編程語言 > >> .NET 網頁程式設計 >> ASP.NET 基礎 >> ASP.NET 基礎

ADO.NET DataTable 更新資料庫完整實作：新增、修改、刪除 (CUD)

編輯：ASP.NET 基礎

前言

使用 DataTable / DataSet 配合 SqlDataAdapter 與 SqlCommandBuilder，可輕鬆實現離線資料集的異動同步回資料庫。本文提供新增 (Insert)、修改 (Update)、刪除 (Delete) 三大操作的標準模式、參數化寫法、交易處理與效能優化。

核心架構


SqlDataAdapter adapter = new SqlDataAdapter(selectSql, connection);
// 自動產生 INSERT/UPDATE/DELETE Command
SqlCommandBuilder builder = new SqlCommandBuilder(adapter); 

DataTable table = new DataTable();
adapter.Fill(table);

// 對 table 操作 (新增/修改/刪除 DataRow)
// ...

// 一次批次同步回資料庫
adapter.Update(table); // 內部依 RowState 自動呼叫對應 Command

操作一：修改現有資料列


public void UpdateUser(int userId, string newName, int newAge)
{
    using (var conn = new SqlConnection(ConnStr))
    using (var da = new SqlDataAdapter("SELECT Id, Name, Age FROM Users WHERE Id = @Id", conn))
    {
        da.SelectCommand.Parameters.AddWithValue("@Id", userId);
        
        var builder = new SqlCommandBuilder(da); // 自動產生 UpdateCommand
        
        var dt = new DataTable();
        da.Fill(dt); // 只取 1 列
        
        if (dt.Rows.Count == 0) throw new Exception("找不到使用者");
        
        DataRow row = dt.Rows[0];
        row.BeginEdit();        // 開始編輯 (可選，但建議用於批次修改)
        row["Name"] = newName;
        row["Age"] = newAge;
        row.EndEdit();          // 結束編輯，RowState 變為 Modified
        
        da.Update(dt);          // 同步回資料庫
    }
}

關鍵點

BeginEdit/EndEdit：暫停/恢復驗證事件，批次修改多欄位時效能較好。
SqlCommandBuilder 要求 SelectCommand 為單一資料表查詢、含主鍵欄位，否則無法自動產生 Command。
複雜查詢 (JOIN、多表) 需手寫 InsertCommand/UpdateCommand/DeleteCommand。

操作二：新增資料列


public int CreateUser(string name, int age)
{
    using (var conn = new SqlConnection(ConnStr))
    using (var da = new SqlDataAdapter("SELECT Id, Name, Age FROM Users WHERE 1=0", conn)) // 只取結構
    {
        var builder = new SqlCommandBuilder(da);
        
        var dt = new DataTable();
        da.Fill(dt); // 空表，但含結構與主鍵資訊
        
        DataRow newRow = dt.NewRow(); // 建立符合結構的新列
        newRow["Name"] = name;
        newRow["Age"] = age;
        // Id 為 Identity，不指定值
        
        dt.Rows.Add(newRow); // RowState = Added
        
        da.Update(dt); // 觸發 InsertCommand
        
        // 取得資料庫回寫的 Identity 值 (需 SelectCommand 包含 SCOPE_IDENTITY)
        // SqlCommandBuilder 4.0+ 自動處理，若失敗需手動設定 UpdatedRowSource
        return (int)newRow["Id"]; 
    }
}

Identity 回寫設定 (關鍵)


// 手動精確控制 InsertCommand 回寫 Identity
builder = new SqlCommandBuilder(da);
da.InsertCommand = builder.GetInsertCommand();
da.InsertCommand.UpdatedRowSource = UpdateRowSource.Both; // 輸出參數 + 首行首列
// 或自訂 CommandText
da.InsertCommand.CommandText = @"
    INSERT INTO Users (Name, Age) VALUES (@Name, @Age);
    SELECT Id, Name, Age FROM Users WHERE Id = SCOPE_IDENTITY();";
da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;

操作三：刪除資料列


public void DeleteUser(int userId)
{
    using (var conn = new SqlConnection(ConnStr))
    using (var da = new SqlDataAdapter("SELECT Id, Name, Age FROM Users WHERE Id = @Id", conn))
    {
        da.SelectCommand.Parameters.AddWithValue("@Id", userId);
        var builder = new SqlCommandBuilder(da);
        
        var dt = new DataTable();
        da.Fill(dt);
        
        if (dt.Rows.Count == 0) return; // 已不存在
        
        DataRow row = dt.Rows[0];
        row.Delete(); // 標記為 Deleted，資料保留在 Row 中供 DeleteCommand 使用
        
        da.Update(dt); // 觸發 DeleteCommand
    }
}

刪除模式比較

方法	RowState	適用場景
`row.Delete()`	Deleted	標準刪除，保留資料供 DeleteCommand 使用主鍵
`dt.Rows.Remove(row)`	Detached	純移除記憶體中列，不會同步刪除資料庫
`dt.Rows.RemoveAt(index)`	Detached	同上

進階：批次異動與交易

單一 Update 即隱含交易

adapter.Update(dt) 內部會開啟交易，所有 INSERT/UPDATE/DELETE 要么全成功要么全失敗 (回滾)。

跨多個 DataTable 的交易 (需手動)


using (var conn = new SqlConnection(ConnStr))
{
    conn.Open();
    using (var tran = conn.BeginTransaction())
    {
        try
        {
            var da1 = new SqlDataAdapter("SELECT * FROM Orders", conn);
            var da2 = new SqlDataAdapter("SELECT * FROM OrderDetails", conn);
            
            da1.SelectCommand.Transaction = tran;
            da2.SelectCommand.Transaction = tran;
            
            new SqlCommandBuilder(da1).SetAllCommandsTransaction(tran);
            new SqlCommandBuilder(da2).SetAllCommandsTransaction(tran);
            
            // 操作 dt1, dt2...
            
            da1.Update(dt1);
            da2.Update(dt2);
            
            tran.Commit();
        }
        catch
        {
            tran.Rollback();
            throw;
        }
    }
}

// 擴充方法
public static void SetAllCommandsTransaction(this SqlCommandBuilder builder, SqlTransaction tran)
{
    builder.DataAdapter.InsertCommand.Transaction = tran;
    builder.DataAdapter.UpdateCommand.Transaction = tran;
    builder.DataAdapter.DeleteCommand.Transaction = tran;
}

完整 Console 範例


using System;
using System.Data;
using System.Data.SqlClient;

class Program
{
    static string ConnStr = "Server=.;Database=TestDB;Integrated Security=True;";

    static void Main()
    {
        // 1. 建立測試資料
        int id = CreateUser("David", 28);
        Console.WriteLine($"新增 ID: {id}");

        // 2. 修改
        UpdateUser(id, "David Updated", 29);
        Console.WriteLine("修改完成");

        // 3. 查詢驗證
        using (var conn = new SqlConnection(ConnStr))
        using (var cmd = new SqlCommand("SELECT Name, Age FROM Users WHERE Id=@Id", conn))
        {
            cmd.Parameters.AddWithValue("@Id", id);
            conn.Open();
            using (var r = cmd.ExecuteReader())
            {
                if (r.Read()) Console.WriteLine($"驗證: {r[0]}, {r[1]}");
            }
        }

        // 4. 刪除
        DeleteUser(id);
        Console.WriteLine("刪除完成");
    }

    static int CreateUser(string name, int age)
    {
        using (var conn = new SqlConnection(ConnStr))
        using (var da = new SqlDataAdapter("SELECT Id, Name, Age FROM Users WHERE 1=0", conn))
        {
            var builder = new SqlCommandBuilder(da);
            var dt = new DataTable();
            da.Fill(dt);
            var row = dt.NewRow();
            row["Name"] = name; row["Age"] = age;
            dt.Rows.Add(row);
            da.Update(dt);
            return (int)row["Id"];
        }
    }

    static void UpdateUser(int id, string name, int age)
    {
        using (var conn = new SqlConnection(ConnStr))
        using (var da = new SqlDataAdapter("SELECT Id, Name, Age FROM Users WHERE Id=@Id", conn))
        {
            da.SelectCommand.Parameters.AddWithValue("@Id", id);
            var builder = new SqlCommandBuilder(da);
            var dt = new DataTable();
            da.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                var row = dt.Rows[0];
                row["Name"] = name; row["Age"] = age;
                da.Update(dt);
            }
        }
    }

    static void DeleteUser(int id)
    {
        using (var conn = new SqlConnection(ConnStr))
        using (var da = new SqlDataAdapter("SELECT Id, Name, Age FROM Users WHERE Id=@Id", conn))
        {
            da.SelectCommand.Parameters.AddWithValue("@Id", id);
            var builder = new SqlCommandBuilder(da);
            var dt = new DataTable();
            da.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                dt.Rows[0].Delete();
                da.Update(dt);
            }
        }
    }
}

效能優化建議

避免逐筆 Fill + Update：批次處理時，一次 Fill 取得大量列，記憶體中修改後單次 Update。
大量新增 (Bulk Insert)：超過 1000 筆請用 SqlBulkCopy，比 DataAdapter 快 10-100 倍。
關閉不需要的 Constraint：dt.Constraints.Clear() 可加速 Fill/Update。
使用強型別 DataSet (Typed DataSet)：Visual Studio 設計師產生，編譯期檢查欄位名稱，IntelliSense 友善。

常見錯誤排除

錯誤	原因	解決
`Dynamic SQL generation is not supported`	SelectCommand 非單表、無主鍵、含 JOIN	手寫 Insert/Update/DeleteCommand
`Concurrency violation`	資料列已被他人修改/刪除 (RowState 不符)	加入 Timestamp/RowVersion 欄位做樂觀鎖
Identity 不回寫	InsertCommand 無 OUTPUT/SCOPE_IDENTITY	設定 UpdatedRowSource 或手寫 CommandText
Update 後資料未變	未呼叫 EndEdit / AcceptChanges 過早	確認 RowState 為 Modified 才 Update