ADO.NET 核心數據操作技術全解析

引言

.NET 面向網絡的新一代平台誕生，帶來全新開發環境。本文探討 ADO.NET 數據庫編程關鍵技術、步驟與常見錯誤，以 VB.NET 操作 SQL Server 為例。

一、數據庫連接：兩大數據提供者

1. SQL Server .NET Data Provider（專用、高效）

僅支援 SQL Server 7.0+，高度優化。

Imports System.Data.SqlClient

' Windows 整合驗證
Dim strCn As String = "server=SqlServerName;database=DataBaseName;integrated security=SSPI"
Dim mySqlConnection As New SqlConnection(strCn)

' 混合模式
Dim strCn As String = "server=SqlServerName;database=DataBaseName;uid=sa;pwd=sa"
Dim mySqlConnection As New SqlConnection(strCn)

本機可用 localhost，遠端建議用 IP。用畢必呼叫 Close() 或 Dispose()。

2. OLE DB .NET Data Provider（通用）

支援所有 OLE DB 相容資料源（Access、Oracle、DB2、Excel 等）。

Imports System.Data
Imports System.Data.OleDb

' Access
Dim strCn As String = "provider=Microsoft.Jet.OLEDB.4.0;data source=c:\DataSource\Northwind.mdb"
Dim myOleDbConnection As New OleDbConnection(strCn)

' SQL Server
Dim strCn As String = "Provider=SQLOLEDB;Persist Security Info=False;Data Source=SqlServerName;Initial Catalog=DataBaseName;User ID=sa;Password=sa;"
Dim myOleDbConnection As New OleDbConnection(strCn)

二、測試環境建立

CREATE TABLE [dbo].[Test] (
    [TestID] [int] IDENTITY(1,1) NOT NULL,
    [TestName] [varchar](50) NOT NULL,
    [TestValue] [float] NOT NULL,
    [TestDate] [datetime] NOT NULL,
    [TestPerson] [char](10) NOT NULL,
    PRIMARY KEY ([TestID])
)

三、連接模式數據操作

3.1 增刪改：SqlCommand + 參數化查詢

Dim MyConnection As New SqlConnection("server=...;uid=zcq;pwd=731020")
Dim MyCommand As New SqlCommand
MyCommand.Connection = MyConnection
MyCommand.CommandType = CommandType.Text
MyCommand.CommandText = "INSERT INTO Test (TestName, TestValue, TestDate, TestPerson) VALUES (@TestName, @TestValue, @TestDate, @TestPerson)"
MyCommand.Parameters.Add("@TestName", SqlDbType.VarChar, 50).Value = TxtTestName.Text
MyCommand.Parameters.Add("@TestValue", SqlDbType.Float).Value = TxtTestValue.Text
MyCommand.Parameters.Add("@TestDate", SqlDbType.DateTime).Value = DateTimePicker1.Value
MyCommand.Parameters.Add("@TestPerson", SqlDbType.Char, 10).Value = TxtTestPerson.Text
Try
    MyConnection.Open()
    MyCommand.ExecuteNonQuery()
    ...
    MyConnection.Close()
Catch ex As Exception
    ' 錯誤處理
End Try

參數化查詢防注入、提升效能、碼具通用性。更新/刪除同理。

3.2 事務處理

MyConnection.Open()
Dim MyTrans As SqlTransaction = MyConnection.BeginTransaction()
MyCommand.Connection = MyConnection
MyCommand.Transaction = MyTrans
Try
    MyCommand.CommandText = "INSERT ..."
    MyCommand.ExecuteNonQuery()
    MyCommand.CommandText = "DELETE FROM Test WHERE TestID=@TestID"
    MyCommand.ExecuteNonQuery()
    MyTrans.Commit()
Catch ex As Exception
    MyTrans.Rollback()
Finally
    MyConnection.Close()
End Try

四、斷開模式數據操作

SqlDataAdapter.Fill(DataSet) 取數據 → 斷開連接 → 客戶端操作 DataSet → SqlDataAdapter.Update(DataSet) 寫回。適合複雜、長時間交互。

Dim MySqlDataAdapter As New SqlDataAdapter("SELECT * FROM Test", MyConnection)
Dim MyDataSet As New DataSet()
Dim objBc As New SqlCommandBuilder(MySqlDataAdapter) ' 自動生成 Insert/Update/Delete Command
' ... 對 DataSet 表操作 ...
MySqlDataAdapter.Update(MyDataSet, "TableName")
MyDataSet.AcceptChanges()

SqlCommandBuilder 限制（重要）

• 僅適用單表更新
• SELECT 必須返回唯一識別列（主鍵）
• SelectCommand 不可變更
• 多表關聯查詢不可用，須手寫 Command 指派給 UpdateCommand/InsertCommand/DeleteCommand

書籍鮮少強調此點，開發者極易踩坑。

結語

連接模式適合快速處理；斷開模式適合長時間複雜操作。斷開模式更新關鍵在正確構造 DataAdapter 的三大 Command 屬性。實戰中體會，方得書本無法給予的發現與樂趣。