本文示範如何在 C# 中操作 DataTable:列舉指定 DataRow 的所有欄位值、使用 NewRow() 建立新列、設定欄位數值、加入 DataTable,並透過 SqlDataAdapter 將變更回寫資料庫。範例使用經典的 Northwind 資料庫 Customers 資料表。
public static void DisplayDataRow(DataRow row, DataTable table)
{
Console.WriteLine("\n--- DisplayDataRow ---");
foreach (DataColumn col in table.Columns)
{
Console.WriteLine($"{col.ColumnName} = {row[col]}");
}
}
public static void AddDataRow(DataTable table, SqlDataAdapter adapter, SqlConnection conn)
{
Console.WriteLine("\n--- AddDataRow ---");
// 1. 建立新列
DataRow newRow = table.NewRow();
Console.WriteLine($"新列狀態: {newRow.RowState}"); // Detached
// 2. 設定欄位值
newRow["CustomerID"] = "JxCOM";
newRow["CompanyName"] = "Jx Company";
newRow["Address"] = "1 Main Street";
// 3. 加入 DataTable
table.Rows.Add(newRow);
Console.WriteLine($"加入後狀態: {newRow.RowState}"); // Added
// 4. 更新資料庫
conn.Open();
int affected = adapter.Update(table);
conn.Close();
Console.WriteLine($"影響列數: {affected}");
Console.WriteLine($"更新後狀態: {newRow.RowState}"); // Unchanged
// 5. 顯示新列內容
DisplayDataRow(newRow, table);
}
public static void Main()
{
string connStr = "server=localhost;database=Northwind;uid=sa;pwd=sa";
using (SqlConnection conn = new SqlConnection(connStr))
{
// SelectCommand
SqlCommand selectCmd = conn.CreateCommand();
selectCmd.CommandText = "SELECT CustomerID, CompanyName, Address FROM Customers ORDER BY CustomerID";
// InsertCommand (參數化防止 SQL Injection)
SqlCommand insertCmd = conn.CreateCommand();
insertCmd.CommandText = @"
INSERT INTO Customers (CustomerID, CompanyName, Address)
VALUES (@CustomerID, @CompanyName, @Address)";
insertCmd.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
insertCmd.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName");
insertCmd.Parameters.Add("@Address", SqlDbType.NVarChar, 60, "Address");
// DataAdapter
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = selectCmd;
adapter.InsertCommand = insertCmd;
// 填充 DataSet
DataSet ds = new DataSet();
conn.Open();
int count = adapter.Fill(ds, "Customers");
conn.Close();
Console.WriteLine($"填入 {count} 列");
// 取得 DataTable 並新增
DataTable customers = ds.Tables["Customers"];
AddDataRow(customers, adapter, conn);
}
}
Detached (新建未加入) → Added (已加入) → Unchanged (更新後)。RowState,自動產生對應的 INSERT/UPDATE/DELETE 命令。SqlParameter 建構子的最後一個引數 sourceColumn 指定對應 DataTable 的欄位名稱,Update 時自動取值。using 區塊確保釋放。新專案建議改用 Entity Framework Core 或 Dapper:
context.Customers.Add(new Customer { ... }); await context.SaveChangesAsync();await conn.ExecuteAsync("INSERT ...", new { CustomerID="JxCOM", ... });兩者皆提供更簡潔的語法、更強的型別安全與更佳效能。