VB訪問SQL Server數據庫技術全揭密

　　摘 要：

　　本文討論了Visual Basic應用程序訪問SQL Server數據庫的幾種常用的方法，分別說明了每種方法的內部機理并給出了每種方法的一個簡單的實例，最后比較了每種方法性能和優缺點。

　　一、引言　　

　　 SQL Server是微軟推出的中小型網絡數據庫系統，是目前最常用的數據庫系統之一。隨著SQL Server網絡數據庫應用程序日益增多，這種Web數據庫應用系統的正常運行一般依賴于已存在的用戶數據庫。創建維護數據庫的工作可用SQL Server提供的SQL Enterprise Manager工具來進行，如能提供一種定制的數據庫管理工具，通過管理應用程序來管理數據庫及其設備，對用戶來說無疑是最理想的。

　　Visual Basic作為一種面向對象的可視化編程工具，具有簡單易學，靈活方便和易于擴充的特點。而且Microsoft為其提供了與SQL Server通信的API函數集及工具集，因此它越來越多地用作大型公司數據和客戶機—服務器應用程序的前端，與后端的Microsoft SQL Server相結合，VB能夠提供一個高性能的客戶機—服務器方案?！　?

　　二、VB訪問SQL Server數據的常用方法

　　使用Visual Basic作為前端開發語言，與SQL Server接口有幾種常用的方法，即：

　?、贁祿L問對象/Jet

　?、跒镺DBC API編程

　?、凼褂肧QL Server的Visual Basic庫(VBSQL)為DB庫的API編程

　?、躌DO 遠程數據對象(RemoteData Objects)

　?、軦DO 數據對象(Active Data Objects)

　　1、數據訪問對象/Jet

　　VB支持Data Aclearcase/" target="_blank" >ccess Objects(DAOs)的子集。DAO的方法雖然不是性能最好的管理客戶機—服務器之間的對話方式，但它的確有許多優點。DAO/Jet是為了實現從VB訪問Access數據庫而開發的程序接口對象。使用DAOs訪問SQL Server的過程如下：應用程序準備好語句并送至Jet，Jet引擎(MASJT200.DLL)優化查詢，載入驅動程序管理器并與之通訊，驅動程序管理器(ODBC.DLL)通過調用驅動器(SQLSRVR.DLL)的函數，實現連接到數據源，翻譯并向SQL Server提交SQL語句且返回結果。下面是一個用DAOs訪問SQL Server的VB實例?！　?

　　注釋：Form Declarations

Dim mydb As Database Dim mydynaset As Dynaset オ Private Sub Form_Load() 　 Set mydb = OpenDatabase("", False, False, "ODBC; DSN=Myserver; WSID=LCL; DATABASE = sales") 　 Set mydynaset = mydb CreateDynaset("Select*from Customers") オ End Sub

　　上述例子是以非獨占、非只讀方式打開sales數據庫，并檢索Customers表中的所有字段。OpenDatabase函數的最后一個參數是ODBC連接字符串參數，它指明了Microsoft Access連接到SQL Server所需要知道的一些內容。其中“DSN”為數據源名，“WSID”為工作站名，“DATABASE”為所要訪問的數據庫名。

　　2、利用ODBC API編程

　　ODBC(Open Database Connectivity)的思想是訪問異種數據庫的一種可移植的方式。與數據資源對話的公用函數組裝在一個稱為驅動程序管理器(ODBC.DLL)的動態連接中。應用程序調用驅動程序管理器中的函數，而驅動程序管理器反過來通過驅動器(SQLSRVR.DLL)把它們送到服務器中。

　　下面的代碼使用上面一些函數先登錄到一個服務器數據庫，并為隨后的工作設置了語句句柄。

Global giHEnv As Long Global giHDB As Long Global giHStmt As Long Dim myResult As integer Dim myConnection As Srting Dim myBuff As String*256 Dim myBufflen As Integer If SQLAllocEnv(giHEnv)<>SQL_SUCCESS Then 　 MsgBox"Allocation couldn注釋：t happen!" End If If SQLAllocConnect(giHEnv,giHDB)<>SQL_SUCCESS Then 　 MsgBox "SQL Server couldn注釋：t connect!" End If myConnection="DSN=myServer;UID=LCL;PWD=;APP=ODBCTest;WSID=LCL;DATABASE=sales" 　　　myResult=SQLDriverConnect(giHDB,Test,form1.hWnd,myConnection.len(myConnection), 　　　myBuff,256,myBufflen,SQL_DRIVER_COMPLETE_REQU IE D) 　　 myResult=SQLAllocStmt(giHDS,giHStmt) 　　 myResult=SQLFreeStmt(giHStmt,SQL_COLSE) 　　 rsSQL="Select * from Customers Where City = "Hunan"" 　　 myResult = SQLExecDirect(giHStmt,rsSQL,Len(rsSQL))

　　3、使用VBSQL對DB庫API編程

　　DB庫是SQL Server的本地API，SQL Server的Visual Basic庫(VBSQL)為Visual Basic程序員提供API。從一定意義上說，VBSQL是連接Visual Basic程序到SQL Server的性能最好最直接的方式。VBSQL包含以下三個文件：

　　VBSQL.VBX： 包含庫函數，具有訪問重要的消息和處理錯誤的能力

　　VBSQL.BI：包括所有的常量和變量說明

　　VBSQL.HLP：Windows幫助文件，使用VBSQL的指南

　　使用VBSQL時，必須將VBSQL.BI加入到Visual Basic工程文件中，并確保VB程序運行時有VBSQL.VBX文件?！　?

　　一般的DB庫API編程的過程是這樣的：先通過調用SqlInit對DB庫進行初始化，再調用SqlConnection打開一個連接，然后就可做一些工作。下面的代碼是一個初始化DB庫并登錄到服務器的通用例程。

Private Sub InitializeApplication() 　　DBLIB_VERSION=SqlInit() 　　If DBLIB_VERSION=""Then 　　　MsgBox"Could not initialize DBLIB!Exit application.", MB_ICONEXCLAMATION 　　End If 　　End Sub　　 　　Private Function LoginToServer() As integer 　　loginToServer=SUCCEED 　　Status%=SqlSetloginTime%(loginTimeOut) 　　If giSqlConn<>0 Then 　　SqlClose(giSqlConn) 注釋：關閉已打開的連接 　　giSqlConn=SqlOpenConnection(gsServerName, gsLoginID, gsPassword, ProgramName, ProgramName) 　　If giSqlConn<>0 Then 　　liresuit=SqlUse(giSqlConn,"Sales") 　　Else 　　LogintoServer=FAIL 　　End If 　　End Function

　4、RDO 遠程數據對象(RemoteData Objects)

　　要討論RDO,就必然要談到DAO。RDO是從DAO派生出來的,但兩者很大的不同在于其數據庫模式。DAO是針對[記錄(Records)]和[字段( Fields)],而RDO是作為[行(Rows)]和[列(Columns)]來處理。也就是說DAO 是ISAM模式,RDO是關系模式。此外DAO是訪問Access的Jet引擎(Jet是ISAM)的接口，而RDO則是訪問ODBC的接口。

　　可見,RDO是綜合了DAO/Jet、VBSQL/DBLib以及ODBC的優點的對象(Object)。需要強調的是，RDO是包裹著ODBC API的一層薄薄的外殼, 被設計成在后臺(服務器端)有數據庫存在的前提下運行，同時也是針對SQL Server和Oracle而特別設計的。

　　RDO的優勢在于它完全被集成在VB之中。此外，直接訪問SQL Server存儲過程、完全支持T-SQL、T-SQL調試集成在開發環境中、Visual Database Tools的集成化等，也是RDO的長處。

　　在RDO的對象和集合中，有很多對數據庫的狀態和設定進行操作的屬性(Property)，以及對數據庫進行操作的方法(Method)。利用這些，從RDO2.0起就可以開發事件驅動的數據庫應用程序。

　　RDO對象與VB中其他對象的概念相同。與VB用的ActiveX控件(以往稱為Custom Control或OCX、VBX)相似的是,RDO也帶有屬性和方法；但同Spread、InputMan等普遍應用的ActiveX控件不同的是，RDO沒有自己的用戶界面，因而可以和VB標準的Timer控件歸為同一類。當然也可以將RDO看作調用ODBC API函數，進而對后臺數據庫操作加以控制的對象。在RDO的屬性和方法中，包含了對單個的ODBC API函數以及一連串API函數的調用。

　?、賠doEngine對象

　　最初調用RDO對象以及RDC(遠程數據控件)時，自動生成rdoEngine對象的附帶事件(incident)。rdoEngine用于對RDO全局屬性的參數、選項進行設置，是在RDO的階層結構內處于最上層的對象，包含了所有的其他對象。

　　rdoEngine對象與DAO/Jet不同，雖然被多個應用程序共享，但體現rdoEngine對象的設定值的屬性卻并不共用，而是在各自的應用程序的程序界面中對其分別加以設定。這些設定值對其他使用RDO以及RDC的應用程序沒有任何影響。rdoEngine不是集合的要素，而是重新定義的對象，rdoEngine對象不能被追加作成對象屬性的初值。

　?、趓doEnvironment對象

　　RDO對象在自動創建rdoEngine對象時，將rdoEnviroment對象的初始值生成并保存為rdoEnviroments(0)。一般情況下，應用程序中不必追加rdoEnvironment對象，大多只需對已有的rdoEnviroments(0)進行操作就可以了。只有在支持一個以上事務(Transaction)，需要將用戶名和口令信息分別處理的情況下，利用rdoCreateEnvironment方法將特定的用戶名和口令值做成新的rdoEnvironment對象。在這個方法中可以指定固有名、用戶名和口令，如果所指定的值與rdoEnvironments集合的已經存在的成員名稱相同，會產生錯誤。新建的rdoEnvironment對象自動追加在rdoEnvironments集合的最后。調rdoCreateEnvironment方法時，其name參數可以是長度為0的文字列，這時新的rdoEnvironment對象將不會被追加在rdoEnvironments集合之中。

　?、踨doConnection對象

　　rdoConnection對象用于同SQL Server的連接管理。

　　下面是與SQL Server連接的例子。

　　用OpenConnection方法的一個實例。

　　設定的DSN為MyDSN: 　　

Dim Cn As rdoConnection Dim En As rdoEnvironment Dim Conn As String Conn = "DSN = MyDSN; UID = Jacob;" & "PWD = 123456; DATA BASE = MyDb;" Set Cn= En.OpenConnection("", rdDriverPrompt, False, Co nn) Set Cn= En.OpenConnection(Prompt:= rdDriverPrompt, Rea dOnly:= False,Connect:= Cnn)

　　用EstablishConnection方法的一個實例。

　　這里以獨立的rdoConnection對象為例說明與SQL Server的連接。
　　

Public WithEvents Eng As rdoEngine Public WithEvents Cn As rdoConnection Private Sub Form_Load() 　Set Eng = New rdoEngine 　Set Cn = New rdoConnection 　With Cn 　　.Connect = "UID = ; PWD = ;" & "DATABASE = pubs; DSN = biblio" 　　.LoginTimeout = 5 　　.EstablishConnection rdoDriverNoPromt, True, rdAsyncEna ble 　End With End Sub

　
　　在這個例子中，Form_Load函數對rdoEngine和rdoConnection對象進行初始化。這里有一點需要注意，rdoConnection對象是處于獨立的狀態之下，即使是處于未連接狀態也可以設置屬性的值。接下來是rdoConnect對象的事件處理程序。從RDO 2.0起可以實現異步方式(rdAsyn cEnable)，EstablishConnection就設定為該值。在異步狀態下，不必等待與數據庫的連接，程序可以迅速從Form_Load 函數中退出。 然后是BeforeConnect事件，該處理在與數據庫的連接開始以前被激發，此時不能進行有關終止連接的操作: 　

Private Sub Cn_BeforeConnect(ConnetString As String, Pro mpt As Variant) 　MsgBox "正在連接" & ConnectString, vbOKOnly, "連接前" End Sub

　　連接完成之后的事件處理:

Private Sub Cn_Connect(ByVal ErrorOccurred As Boolean) 　Dim M As String 　If ErrorOccurred Then 　　For Each er In rdoErrors 　　　M = M & er & vbCrLf & M 　　Next 　　MsgBox "連接失敗" & vbCrLf & M 　Else 　　MsgBox "連接成功" 　　注釋：這是確認連接狀態的測試代碼 　Cn.Excute "use pubs" End Sub

　
　　RDO連接處理結束后，在該事件中確認連接成功與否。連接成功的情況下ErrorOccurred返回False，失敗時為True，由此可以對rdoErrors集合進行檢測：　　

Private Sub Eng_InfoMessage() 　For Each er In rdoErrors 　　Debug.Print er 　Next 　RdoErrors.Clear End Sub

　

　　不能與SQL Server連接的原因多種多樣，有可能是由于對數據庫的訪問權限、網絡連接問題、數據庫表的信息錯誤、SQL Server同時連接的許可數、資源不足等等，具體情況需要與網絡管理員商量。 斷開連接的操作非常簡單，但又很重要，因為RDO不提供自動斷開的功能?！　?

Cn.Close Set Cn = Nothing 注釋：釋放對象所占的內存資源 En.Close Set En = Nothing 注釋：釋放對象所占的內存資源

　　VB是對象語言，Form、ActiveX控件也都是對象。使用對象后必須養成將對象設為Nothing把它從內存中釋放的編程習慣。這樣可以預防很多不可預測錯誤，往往程序中發生原因不明的錯誤時，其原因就在于此?！　?

　5、ADO 數據對象(Active Data Objects)

　　ADO是基于全新的OLE DB技術，OLE DB可對電子郵件、文本文件、復合文件、數據表等各種各樣的數據通過統一的接口進行存取。隨著ActiveX控件的升級(Windows 98的ActiveX 5.0)，RDO將被以ActiveX技術為基礎的ADO接口所替代。下面將介紹基于ActiveX技術的ADO訪問SQL Server 6.5數據庫的技術和方法?；跒g覽器的ADO接口常用函數如下：

　　(1)取當前的工作數據庫

　　由于管理任務一般都必須在Master庫中完成，因此在執行管理任務之前，最好保存當前工作庫，以便完成任務之后再切換回原來的任務?！　?

Public Function SQLGetCurrentDatabaseName(Cn As ADODB.Connection) As String 　Dim sSQL As String 　Dim RS As New ADODB.Recordset 　On Error GoTo errSQLGetCurrentDatabaseName 　sSQL="select CurrentDB=DB_NAME ( )" 　RS.Open sSQL, Cn 　SQLGetCurrentDatabaseName=Trim $ (RS! CurrentDB) 　RS.Close 　Exit Function 　errSQLGetCurrentDatabaseName: 　SQLGetCurrentDatabaseName=" " End Function

　　(2)取SQL Server安裝目錄下的DATA子目錄路徑

　　取SQL Server的設備文件缺省目錄，返回如D:MSSQL DATA?！　?

Public Function SQLGetDataPath(Cn As ADODB.Connection) As String 　Dim sSQL As String 　Dim RS As New ADODB.Recordset 　Dim sFullPath As String 　On Error GoTo errSQLGetDataPath 　sSQL="select phyname from master..sysdevices where name=注釋：master注釋： " 　RS.Open sSQL, Cn 　sFullPath = RS! phyname 　RS.Close 　SQLGetDataPath=Left $ (sFullPath, Len(sFullPath) -10) 注釋：MASTER.DAT的大小 　　Exit Function 　errSQLGetDataPath: 　SQLGetDataPath=" " End Function

　　
　　(3)創建一個新數據庫　　

Public Function SQLCreateDatabase65 (Cn As ADODB.Connection,sDBName As String, sDataDeviceName As String, nDataSize As Integer, Optional sLogDeviceName, Optional nLogSize) As Boolean 　Dim sSQL As String 　On Error GoTo errSQLCreateDatabase65 　Dim sDB As String 　sDB =SQLGetCurrentDatabaseName(Cn) 　sSQL = "USE master" 　Cn.Execute sSQL 　sSQL ="CREATE DATABASE" & sDBName 　sSQL = sSQL &" ON " & sDataDeviceName & "=" & nDataSize 　If Not IsMissing(sLogDeviceName) And Not IsMissing(nLogSize) Then 　　sSQL = sSQL & "LOG ON" & sLogDeviceName & "="& nLogSize 　End If 　Cn.Execute sSQL 　sSQL = "USE" & sDB 　Cn.Execute sSQL 　SQLCreateDatabase65 = True 　Exit Function 　errSQLCreateDatabase65: 　On Error Resume Next 　sSQL = "USE " & sDB 　Cn.Execute sSQL 　SQLCreateDatabase65 = False End Function

　　(4)判斷一個數據庫是否存在

Public Function SQLExistDatabase(Cn As ADODB.Connection, sDBName As String) As Boolean 　Dim sSQL As String 　Dim RS As New ADODB.Recordset 　Dim bTmp As Boolean 　on Error GoTo errSQLExistDatabase 　sSQL = "select CntDB = count ( * ) " 　sSQL = sSQL & "From master.dbo.sysdatabases" 　sSQL = sSQL & "Where name = 注釋： "& sDBName & " 注釋： " 　RS.Open sSQL, Cn 　If RS! CntDB = 0 Then bTmp = False Else bTmp = True 　　RS.Close 　　SQLExistDatabase = bTmp 　　Exit Function 　　errSQLExistDatabase: 　　SQLExistDatabase = False 　　Exit Function 　End Function

　　(5)刪除一個數據庫　　

Public Function SQLDropDatabase (Cn As ADODB.Connection, sDBName As String) As Boolean 　Dim sSQL As String 　On Error GoTo errSQLDropDatabase 　If Not SQLExistDatabase(Cn, sDBName) Then 　　SQLDropDatabase = True 　　 Exit Function 　End If 　Dim sDB As String 　sDB = SQLGetCurrentDatabaseName(Cn) 　sSQL = "Use master" 　Cn.Execute sSQL 　sSQL = "DROP DATABASE " & sDBName 　Cn.Execute sSQL 　sSQL = "USE " & sDB 　Cn.Execute sSQL 　SQLDropDatabase = True 　Exit Function errSQLDropDatabase: 　On Error Resume Next 　sSQL = "USE " & sDB 　Cn.Execute sSQL 　SQLDropDatabase = False End Function

　　三、性能比較及應用說明

　　用VB開發基于SQL Server的數據庫系統，以上幾種訪問SQL Server的方法各有各的特點。DAOs方法是基于對象的，因而便于使用，但是它是從Visual Basic到SQL Server最慢的連接方式。ODBC API和VBSQL方法從本質上講是基于程序的。ODBC API方法通用性好，允許最強的互操作性，編程簡單，但速度慢于VBSQL方法。VBSQL方法通過VBSQL控件，提供了重要的SQL Server前端應用程序所需的靈活性、強大功能和良好性能。它具有真正的事件驅動及錯誤處理能力，完全支持異步處理、游標和計算列等。這些都是VBSQL方法超出其它方法的優勢，但其編程稍復雜。RDO是位于ODBC API之上的一個對象模型層，它依賴ODBC API、ODBC驅動程序以及后端數據庫引擎來實現，用RDO所需的程序短小(約250 KB)、快速。RDO具備基本的ODBC處理方法，可直接執行大多數ODBC API函數，RDO包含在VB 4.0/VB 5.0企業版中，由MSRDO32.DLL動態連接庫來實現。RDO是綜合了DAO/Jet、VBSQL/DBLib和ODBC的優點的對象模型，包含ODBC API應用層，設計為在后臺(服務器端)有數據庫存在的前提下運行，是針對SQL Server和Oracle而特別設計的。RDO的優勢在于它完全被集成在VB之中，可直接訪問SQL Server存儲過程、完全支持T-SQL、T-SQL調試集成在開發環境中、Visual Database Tools的集成化等。但微軟已宣布今后不再對VBSQL/DBLib進行升級，而ODBC API函數一般的編程方式也不為人們所喜愛，RDO的應用將逐漸減少。至于實際使用哪一種接口方式，在很大程度上依賴于用戶的應用程序的具體情況而定?！　?

　　四、VisualBasic訪問數據庫的前景

　　近來隨著Web應用軟件的迅速發展和現有數據存儲形式的多種多樣，Visual Basic訪問數據庫的解決方案面臨諸如快速提取分布于企業內部和外部有用商業信息等的多種挑戰。為此Microsoft提出一種新的數據庫訪問策略，即“統一數據訪問”（UniversalDataAccess）的策略。“統一數據訪問”提供了高性能的存取包括關系型和非關系型在內的多種數據源，提供獨立于開發工具和開發語言的簡單的編程接口，這些技術使得企業集成多種數據源、選擇更好的開發工具、應用軟件、操作平臺、建立容易維護的解決方案成為可能。

　　“統一數據訪問”的基礎是Microsoft的數據訪問組件。這些組件包括ActiveXDataObjects(ADO)、RemoteDataService(RDS，也稱“高級數據連接器或”ADC)、OLEDB和ODBC。

原文轉自：http://www.anti-gravitydesign.com