C# 操作 SQL Server 與 Access 新增資料記錄：OleDb 參數化指令、資料繫結與完整實戰範例

前言：ADO.NET 與 OleDb 存取異構資料源

.NET Framework 透過 System.Data.OleDb 統一存取支援 OLE DB Provider 的資料庫（SQL Server、Access、Oracle、MySQL 等）。本文示範使用 OleDbConnection、OleDbDataAdapter、DataSet、DataBinding 完成視窗表單新增記錄功能，並對比 SQL Server 與 Access 連線字串差異。

核心架構

• 連線：OleDbConnection + 連線字串
• 資料配接器：OleDbDataAdapter 執行 SELECT、填充 DataSet
• 離線資料集：DataSet 記憶體中資料表集合
• 資料繫結：Control.DataBindings.Add("Text", DataSet, "Table.Column") 雙向同步
• 導航：BindingManagerBase (CurrencyManager) 控制記錄指標
• 插入：OleDbCommand.ExecuteNonQuery 執行 INSERT

連線字串對比

完整視窗表單程式碼

using System;
using System.Drawing;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;

public class DataAddForm : Form
{
    // UI 控制項
    private Button btnFirst, btnPrev, btnNext, btnLast;
    private Button btnNew, btnSave;
    private TextBox txtBookId, txtTitle, txtAuthor, txtPrice, txtStock;
    private Label lblTitle, lblId, lblTitle2, lblAuthor, lblPrice, lblStock;

    // 資料物件
    private DataSet ds = new DataSet();
    private BindingManagerBase bm;
    private string connStr = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=data1;Data Source=server1";

    public DataAddForm()
    {
        LoadData();
        InitializeComponent();
    }

    private void LoadData()
    {
        try
        {
            using (var conn = new OleDbConnection(connStr))
            using (var da = new OleDbDataAdapter("SELECT * FROM books", conn))
            {
                da.Fill(ds, "books");
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show("資料庫連線失敗：" + ex.Message);
        }
    }

    private void InitializeComponent()
    {
        // 標題
        lblTitle = new Label { Text = "使用 Visual C# 新增資料記錄", ForeColor = Color.Green, Font = new Font("標楷體", 14, FontStyle.Bold), Location = new Point(32, 16), Size = new Size(336, 24) };

        // 欄位標籤與文字框
        CreateLabelTextBox("書本序號：", out lblId, out txtBookId, 56);
        CreateLabelTextBox("書名：", out lblTitle2, out txtTitle, 108);
        CreateLabelTextBox("作者：", out lblAuthor, out txtAuthor, 160);
        CreateLabelTextBox("價格：", out lblPrice, out txtPrice, 212);
        CreateLabelTextBox("書架號：", out lblStock, out txtStock, 264);

        // 資料繫結
        txtBookId.DataBindings.Add("Text", ds, "books.bookid");
        txtTitle.DataBindings.Add("Text", ds, "books.booktitle");
        txtAuthor.DataBindings.Add("Text", ds, "books.bookauthor");
        txtPrice.DataBindings.Add("Text", ds, "books.bookprice");
        txtStock.DataBindings.Add("Text", ds, "books.bookstock");

        // 導航按鈕
        btnFirst = CreateNavBtn("首記錄", 65, 312, (s,e) => bm.Position = 0);
        btnPrev  = CreateNavBtn("上一條", 135, 312, (s,e) => { if (bm.Position > 0) bm.Position--; else MessageBox.Show("已是第一筆"); });
        btnNext  = CreateNavBtn("下一條", 205, 312, (s,e) => { if (bm.Position < bm.Count - 1) bm.Position++; else MessageBox.Show("已是最後一筆"); });
        btnLast  = CreateNavBtn("尾記錄", 275, 312, (s,e) => bm.Position = bm.Count - 1);

        // 新增/儲存按鈕
        btnNew = new Button { Text = "新建記錄", Location = new Point(62, 354), Size = new Size(96, 32) };
        btnNew.Click += (s,e) => ClearFields();
        btnSave = new Button { Text = "保存記錄", Location = new Point(222, 354), Size = new Size(96, 32) };
        btnSave.Click += BtnSave_Click;

        // 表單設定
        this.Text = "C# 新增資料記錄範例";
        this.ClientSize = new Size(390, 400);
        this.FormBorderStyle = FormBorderStyle.Fixed3D;
        this.Controls.AddRange(new Control[] {
            lblTitle, lblId, txtBookId, lblTitle2, txtTitle, lblAuthor, txtAuthor,
            lblPrice, txtPrice, lblStock, txtStock,
            btnFirst, btnPrev, btnNext, btnLast, btnNew, btnSave
        });

        bm = this.BindingContext[ds, "books"];
    }

    private void CreateLabelTextBox(string labelText, out Label lbl, out TextBox txt, int top)
    {
        lbl = new Label { Text = labelText, Location = new Point(24, top), Size = new Size(112, 20), Font = new Font("標楷體", 10), TextAlign = ContentAlignment.MiddleCenter };
        txt = new TextBox { Location = new Point(184, top), Size = new Size(176, 20) };
    }

    private Button CreateNavBtn(string text, int x, int y, EventHandler handler)
    {
        var btn = new Button { Text = text, Location = new Point(x, y), Size = new Size(40, 24), Font = new Font("標楷體", 8) };
        btn.Click += handler;
        return btn;
    }

    private void ClearFields()
    {
        txtBookId.Clear(); txtTitle.Clear(); txtAuthor.Clear(); txtPrice.Clear(); txtStock.Clear();
        // 讓使用者輸入新序號，不自動產生
    }

    private void BtnSave_Click(object sender, EventArgs e)
    {
        // 驗證必填
        if (string.IsNullOrWhiteSpace(txtBookId.Text) ||
            string.IsNullOrWhiteSpace(txtTitle.Text) ||
            string.IsNullOrWhiteSpace(txtAuthor.Text) ||
            string.IsNullOrWhiteSpace(txtPrice.Text) ||
            string.IsNullOrWhiteSpace(txtStock.Text))
        {
            MessageBox.Show("所有欄位均為必填！");
            return;
        }

        try
        {
            using (var conn = new OleDbConnection(connStr))
            {
                conn.Open();
                // 參數化查詢防止 SQL Injection
                string sql = "INSERT INTO books (bookid, booktitle, bookauthor, bookprice, bookstock) VALUES (?, ?, ?, ?, ?)";
                using (var cmd = new OleDbCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@bookid", txtBookId.Text);
                    cmd.Parameters.AddWithValue("@booktitle", txtTitle.Text);
                    cmd.Parameters.AddWithValue("@bookauthor", txtAuthor.Text);
                    cmd.Parameters.AddWithValue("@bookprice", decimal.Parse(txtPrice.Text));
                    cmd.Parameters.AddWithValue("@bookstock", txtStock.Text);
                    cmd.ExecuteNonQuery();
                }
            }
            // 重新載入以顯示新記錄
            ds.Tables["books"].Clear();
            LoadData();
            MessageBox.Show("記錄已新增！");
        }
        catch (Exception ex)
        {
            MessageBox.Show("儲存失敗：" + ex.Message);
        }
    }

    [STAThread]
    static void Main()
    {
        Application.EnableVisualStyles();
        Application.Run(new DataAddForm());
    }
}

關鍵最佳實踐解析

1. 參數化指令防 SQL Injection

// 錯誤：字串拼接
string sql = "INSERT ... VALUES (" + txtId.Text + ", '" + txtTitle.Text + "', ...)";

// 正確：參數化
string sql = "INSERT ... VALUES (?, ?, ?, ?, ?)";
cmd.Parameters.AddWithValue("@p1", txtId.Text); // OleDb 使用 ? 位置參數，名稱僅供可讀性

2. Using 區塊確保資源釋放

OleDbConnection、OleDbCommand、OleDbDataAdapter 實作 IDisposable，using 保證連線關閉、連線池歸還。

3. DataBinding 雙向同步

• 導航按鈕移動 BindingManagerBase.Position 即自動更新所有繫結控制項
• 使用者修改文字框 → 失去焦點 → DataSet 同步更新
• 新增記錄後 ds.Tables["books"].Clear(); LoadData(); 重新整理

4. 型別轉換與驗證

decimal price = decimal.Parse(txtPrice.Text); // 價格用 decimal 避免浮點誤差
int stock = int.Parse(txtStock.Text);

進階：改用 SqlClient 提升 SQL Server 效能

using System.Data.SqlClient;

string connStr = "Server=server1;Database=data1;User Id=sa;Password=***;";
using (var conn = new SqlConnection(connStr))
using (var cmd = new SqlCommand("INSERT ... VALUES (@id, @title, @author, @price, @stock)", conn))
{
    cmd.Parameters.Add("@id", SqlDbType.VarChar).Value = txtBookId.Text;
    cmd.Parameters.Add("@title", SqlDbType.NVarChar).Value = txtTitle.Text;
    // ...
}

SqlClient 為 SQL Server 原生提供者，效能優於 OleDb，支援 SqlBulkCopy、Table-Valued Parameters、Always Encrypted 等進階功能。

總結

本文提供從連線字串、資料配接器、資料繫結、導航控制到參數化插入的完整 C# WinForms 範例。核心原則：**參數化查詢、Using 管理資源、DataBinding 減少樣板碼、型別正確**。掌握此模式，可快速建構各類資料驅動桌面應用。