Visual C# ADO.NET 實戰：Access 資料庫新增記錄完整範例

環境需求

• Windows 2000 Server 或更新版本
• MDAC 2.6+ (Microsoft Data Access Components)
• Access 2000 資料庫 sample.mdb，資料表 books：

核心邏輯：OleDbConnection + OleDbCommand

string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=sample.mdb";
using (OleDbConnection conn = new OleDbConnection(connStr))
{
    conn.Open();
    
    string sql = @"INSERT INTO books (bookid, booktitle, bookauthor, bookprice, bookstock) 
                   VALUES (?, ?, ?, ?, ?)";
    
    using (OleDbCommand cmd = new OleDbCommand(sql, conn))
    {
        // 參數化查詢：防止 SQL Injection、型別安全
        cmd.Parameters.AddWithValue("@id", txtBookId.Text);
        cmd.Parameters.AddWithValue("@title", txtBookTitle.Text);
        cmd.Parameters.AddWithValue("@author", txtBookAuthor.Text);
        cmd.Parameters.AddWithValue("@price", txtBookPrice.Text);
        cmd.Parameters.AddWithValue("@stock", txtBookStock.Text);
        
        int rowsAffected = cmd.ExecuteNonQuery();
        Console.WriteLine($"新增 {rowsAffected} 筆記錄");
    }
}

完整 WinForms 程式碼結構

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

public class DataAddForm : Form
{
    // UI 元件宣告
    private TextBox txtBookId, txtBookTitle, txtBookAuthor, txtBookPrice, txtBookStock;
    private Button btnNew, btnSave;
    private Button btnFirst, btnPrev, btnNext, btnLast;
    private DataSet dsBooks;
    private BindingManagerBase bmBooks;
    
    public DataAddForm()
    {
        InitializeComponent();
        ConnectDatabase();
    }
    
    private void ConnectDatabase()
    {
        try
        {
            string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=sample.mdb";
            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                string sql = "SELECT * FROM books";
                OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn);
                dsBooks = new DataSet();
                adapter.Fill(dsBooks, "books");
                
                // 資料繫結
                txtBookId.DataBindings.Add("Text", dsBooks, "books.bookid");
                txtBookTitle.DataBindings.Add("Text", dsBooks, "books.booktitle");
                txtBookAuthor.DataBindings.Add("Text", dsBooks, "books.bookauthor");
                txtBookPrice.DataBindings.Add("Text", dsBooks, "books.bookprice");
                txtBookStock.DataBindings.Add("Text", dsBooks, "books.bookstock");
                
                bmBooks = this.BindingContext[dsBooks, "books"];
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show("資料庫連接失敗：" + ex.Message);
        }
    }
    
    private void btnSave_Click(object sender, EventArgs e)
    {
        // 驗證必填欄位
        if (string.IsNullOrWhiteSpace(txtBookId.Text) ||
            string.IsNullOrWhiteSpace(txtBookTitle.Text) ||
            string.IsNullOrWhiteSpace(txtBookAuthor.Text) ||
            string.IsNullOrWhiteSpace(txtBookPrice.Text) ||
            string.IsNullOrWhiteSpace(txtBookStock.Text))
        {
            MessageBox.Show("所有欄位均為必填！");
            return;
        }
        
        try
        {
            string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=sample.mdb";
            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                conn.Open();
                string sql = @"INSERT INTO books (bookid, booktitle, bookauthor, bookprice, bookstock) 
                               VALUES (?, ?, ?, ?, ?)";
                using (OleDbCommand cmd = new OleDbCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@id", txtBookId.Text);
                    cmd.Parameters.AddWithValue("@title", txtBookTitle.Text);
                    cmd.Parameters.AddWithValue("@author", txtBookAuthor.Text);
                    cmd.Parameters.AddWithValue("@price", txtBookPrice.Text);
                    cmd.Parameters.AddWithValue("@stock", txtBookStock.Text);
                    
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("記錄新增成功！");
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show("儲存失敗：" + ex.Message);
        }
    }
    
    private void btnNew_Click(object sender, EventArgs e)
    {
        // 清空表單準備新增
        txtBookId.Clear();
        txtBookTitle.Clear();
        txtBookAuthor.Clear();
        txtBookPrice.Clear();
        txtBookStock.Clear();
    }
    
    // 導覽按鈕事件
    private void btnFirst_Click(object s, EventArgs e) => bmBooks.Position = 0;
    private void btnPrev_Click(object s, EventArgs e) 
    { 
        if (bmBooks.Position > 0) bmBooks.Position--; 
        else MessageBox.Show("已是第一筆"); 
    }
    private void btnNext_Click(object s, EventArgs e) 
    { 
        if (bmBooks.Position < bmBooks.Count - 1) bmBooks.Position++; 
        else MessageBox.Show("已是最後一筆"); 
    }
    private void btnLast_Click(object s, EventArgs e) => bmBooks.Position = bmBooks.Count - 1;
    
    [STAThread]
    static void Main() => Application.Run(new DataAddForm());
}

關鍵最佳實務

連接字串變體

// Access 2007+ (.accdb)
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=sample.accdb;

// Access 加密資料庫
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=sample.mdb;Jet OLEDB:Database Password=secret;

// SQL Server (SqlClient)
Server=myServer;Database=myDB;User Id=sa;Password=secret;
// 或整合驗證
Server=myServer;Database=myDB;Integrated Security=SSPI;

本章小結

• ADO.NET 核心：Connection、Command、DataAdapter、DataSet
• 參數化查詢是安全、正確、效能的基石
• using 區塊確保非受控資源 (連線) 正確釋放
• WinForms 資料繫結 (DataBindings + BindingContext) 簡化 UI 與資料同步
• 現代專案建議使用 Entity Framework Core 或 Dapper ORM 提升開發效率