VB.NET 使用 ADO.NET 進行資料存取,核心物件:Connection、Command、DataReader、DataAdapter、DataSet。
另可安裝 ODBC.NET Provider (Microsoft.Data.Odbc.dll) 擴充支援。
| Provider | 命名空間 | 支援資料庫 | 適用場景 |
|---|---|---|---|
| SQL Server .NET Data Provider | System.Data.SqlClient | SQL Server 7.0+ | 純 SQL Server 環境,效能最佳 |
| OLE DB .NET Data Provider | System.Data.OleDb | Access、Oracle、SQL Server 等 | 異構資料庫、舊系統遷移 |
| ODBC .NET Data Provider | System.Data.Odbc | Access、Oracle、SQL Server、MySQL、VFP 等 | 需 ODBC 驅動的資料源、跨平台 |
Imports System.Data.SqlClient
Dim connStr As String = "Data Source=ServerName;Initial Catalog=DBName;User ID=sa;Password=;"
Dim conn As New SqlConnection(connStr)
Try
conn.Open()
' 執行命令...
Finally
conn.Close()
End Try
Imports System.Data.OleDb
' SQL Server
Dim connStr As String = "Provider=SQLOLEDB;Data Source=ServerName;Initial Catalog=DBName;User ID=sa;Password=;"
' Access (Jet 4.0)
Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Data\Example.mdb;"
' Oracle (MSDAORA)
Dim connStr As String = "Provider=MSDAORA;Data Source=ServerName;User ID=uid;Password=pwd;"
Dim conn As New OleDbConnection(connStr)
conn.Open()
安裝 odbc_net.msi,工具箱加入 OdbcConnection 等元件。
Imports System.Data.Odbc
' 系統 DSN
Dim connStr As String = "DSN=MyOracleDSN;Uid=uid;Pwd=pwd;"
' 或無 DSN
Dim connStr As String = "Driver={Oracle in OraHome92};Server=ServerName;Uid=uid;Pwd=pwd;"
Dim conn As New OdbcConnection(connStr)
conn.Open()
C:\Program Files\Microsoft.NET\Odbc.Net\Microsoft.Data.Odbc.dll。OdbcCommand, OdbcConnection, OdbcDataAdapter 等,確定。app.config <connectionStrings>,避免硬編碼。Using 區塊自動釋放連線:Using conn As New SqlConnection(str) ... End Using。cmd.Parameters.AddWithValue("@p", val)。