微軟資料庫通路接口（ODBC，OLEDB，ADO）
目錄
- 資料庫通路接口發展曆史
- ODBC、OLEDB和ADO之間的關系
- VB腳本下的資料庫接口代碼示例OLEDB連接配接ODBC連接配接執行SQL語句遊标操作讀取資料添加資料修改資料常用屬性關閉連接配接
- 在SQL Server中通過連結伺服器查詢OLE DB資料源
- 在SQL Server中直接查詢OLE DB資料源
微軟資料庫通路接口發展曆史
ODBC曆史
ODBC（Open Database Connectivity，開放資料庫互連）。要了解ODBC是什麼，先了解一下資料庫連接配接的相關知識。在最開始連接配接資料庫時，由于資料庫種類繁多，各種資料庫連接配接有不同的需求，這個時期，資料庫連接配接主要依靠各種API函數來進行連接配接。在這種背景下，微軟于1992年發表了ODBC， ODBC就是将這些API函數封裝起來形成統一的接口。當SQL語句進入接口後，驅動器管理程式将它們送入對應的驅動器(driver)，由驅動器将SQL語句送入各種不同的資料庫。
OLE DB曆史
OLE DB（Object Linking and Embedding, Database，對象連結嵌入資料庫）是微軟為以統一方式通路不同類型的資料存儲設計的一種應用程式接口。OLE DB 是建立在 ODBC 功能之上的一個開放規範。ODBC 是為通路關系型資料庫而專門開發的，OLE DB 則用于通路關系型和非關系型資訊源。OLE DB可以通過ODBC連接配接到資料庫，也可以直接連接配接到資料庫，并且直接連接配接的通路速度更快。可以知道，隻要支援ODBC的資料源是一定能夠支援OLEDB的，但是反過來則不一定。
ADO曆史
ADO（ActiveX Data Objects，ActiveX 資料對象）在1996年8月與OLE DB一起被釋出，是在OLE DB上面建立的一個新的統一的資料通路的高層對象模型。ADO推出後順利的取代了微軟早期的資料通路對象層（包括RDO（Remote Data Objects）和DAO（Data Access Objects））。ADO将OLE DB的對象模型進一步簡化，由資料庫廠商開發滿足OLE DB接口的資料提供者（data provider），而ADO本身則是與資料源無關（data source independent）的對象結構，這使得ADO通用性極好。
ODBC、OLEDB和ADO之間的關系
VB腳本下的資料庫接口代碼示例
OLEDB連接配接
标準安全模式(Standard Security)
1
2
3
4
dim conn, sCon
set conn=server.CreateObject("ADODB.Connection")
sCon="Provider=SQLOLEDB;Data Source=(local); Initial Catalog =dbname;User ID=sa;Password=123"
conn.open(sCon)
信任連接配接(Trusted connection)
1
2
3
4
dim conn, sCon
set conn=server.CreateObject("ADODB.Connection")
sCon="Provider=SQLOLEDB; Integrated Security=SSPI; Persist Security Info=False;Data Source=(local); Initial Catalog =dbname"
conn.open(sCon)
ODBC連接配接
通過系統資料源(System DSN)連接配接
先注冊資料源：DSN。打開管理工具 -> 資料源(ODBC) -> 打開系統DSN頁籤 -> 單擊添加按鈕 -> 從清單中選擇SQL Server，單擊完成 -> 在名稱中輸入資料庫名稱，在你想連接配接的SQL Server伺服器中輸入（local）-> 按向導提示完成。
1
2
3
4
dim conn, sCon
set conn=server.CreateObject("ADODB.Connection")
sCon="DSN=注冊名;Uid=sa;Pwd=123"
conn.open(sCon)
通過ODBC驅動程式連接配接
1
2
3
4
dim conn, sCon
set conn=server.CreateObject("ADODB.Connection")
sCon="Driver={SQL Server};Server=(local); Uid=sa;Pwd=123;Database=dbname"
conn.open(sCon)
執行SQL語句
1
2
3
4
Dim Sql, rs
Sql = "Select * From tablename"
Set rs = CreateObject("ADODB.Recordset")
rs.Open sSql,conn,2,3
rs.Open的參數如下：
- Source：有效的指令對象、SQL語句、表名、存儲過程調用、URL 或包含持久存儲的記錄集的檔案或流對象的名稱。
- ActiveConnection：有效的連接配接對象變量，或包含ConnectionString參數的字元串。
- CursorType：可選。一個CursorTypeEnum值，确定提供程式在打開記錄集時應使用的遊标類型。預設值為adOpenForwardOnly。
- LockType：可選。LockTypeEnum值，用于确定提供程式在打開記錄集 時應使用的(并發)類型。預設值為adLockReadOnly。
LockTypeEnum
常量
值
說明
adLockBatchOptimistic
4
執行多行批處理更新時使用這種類型。
adLockOptimistic
3
樂觀鎖定，僅在調用Update方法時鎖定。
adLockPessimistic
2
悲觀鎖定，當記錄集打開的時候将其鎖定。
adLockReadOnly
1
預設，用來打開隻讀記錄。
adLockUnspecified
-1
不指定鎖的類型。
遊标操作
| 1 2 3 4 | rs.MoveFirst '把記錄指針移動到第一條記錄。 rs.MoveLast '把記錄指針移動到最後一條記錄。 rs.MoveNext '把記錄指針移動到下一條記錄。 rs.MovePrevious '把記錄指針移動到上一條記錄。 |
讀取資料
讀取目前遊标訓示的行。
| 1 2 | dim value value = rs("Field1") |
添加資料
| 1 2 3 4 5 | rs.AddNew rs("Field1")=Value1 rs("Field2")=Value2 rs("Field3")=Value3 rs.update |
修改資料
修改目前遊标訓示的行。
| 1 2 3 | rs("Field1")=Value1 rs("Field2")=Value2 rs.update |
常用屬性
| 1 2 3 4 | rs.Fields.Count '字段數量 rs.RecordCount '資料集行數 rs.BOF '如果目前的記錄位置在第一條記錄之前，則傳回 true，否則傳回 fasle。 rs.EOF '如果目前記錄的位置在最後的記錄之後，則傳回 true，否則傳回 fasle。 |
關閉連接配接
執行了select語句後Recordset會處在打開狀态，其他執行其他SQL語句的Recordset未處在打開狀态，不需要關閉。
在VBS中，離開對象作用域後會自動關閉。
| 1 2 3 4 | rs.Close set rs=Nothing conn.Close set conn=Nothing |
在SQL Server中通過連結伺服器查詢OLE DB資料源
建立連結伺服器
配置連結伺服器使SQL Server資料庫引擎能夠通路SQL Server執行個體之外的OLE DB資料源。
在SQL Server Management Studio中，打開對象資料總管，展開“伺服器對象”，右鍵單擊“連結伺服器”，然後單擊“建立連結伺服器”。
在“正常”頁中，“連結伺服器”一欄中為連結伺服器命名，并填入其他資訊。
對連結資料庫查詢
在指定的連結伺服器上執行傳遞查詢。文法如下 ：
| OPENQUERY ( linked_server ,'query' ) |
參數：
- linked_server：表示連結伺服器名稱的辨別符。
- ' query '：在連結伺服器中執行的查詢字元串。該字元串的最大長度為 8 KB。
OPENQUERY 可以在查詢的 FROM 子句中引用，就好象它是一個表名
| SELECT * FROM OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE name = ''NewTitle'''); |
在SQL Server中直接查詢OLE DB資料源
通過OPENDATASOURCE()函數直接查詢OLE DB資料源，文法如下：
| OPENDATASOURCE ( 'provider_name', 'init_string' ) |
示例如下：
| SELECT GroupName, Name, DepartmentID FROM OPENDATASOURCE('MSOLEDBSQL', 'Server=Seattle1;Database=AdventureWorks2016;TrustServerCertificate=Yes;Trusted_Connection=Yes;').HumanResources.Department ORDER BY GroupName, Name; |
版權聲明：本文原創發表于部落格園，作者為星墨 本文版權歸作者和部落格園共有，歡迎轉載，但未經作者同意必須保留此段聲明， 且在文章頁面明顯位置給出原文連接配接，否則視為侵權。