ASP系列講座（十六）訪問資料庫
作者：Eve Cole
更新時間：2009-05-30 19:58:46
ActiveX Data Objects (ADO) 是一項容易使用且可擴充的將資料庫存取新增至Web 頁的技術。可以使用ADO 去編寫緊湊簡潔的腳本以便連接到Open Database Connectivity (ODBC) 相容的資料庫和OLE DB 相容的資料來源。如果您是對資料庫連線有一定了解的腳本編寫人員，那麼您將發現ADO 命令語句並不複雜且容易掌握。同樣地，如果您是經驗豐富的資料庫程式設計人員，您將會正確認識ADO 的先進的與語言無關性和查詢處理功能。
建立ODBC DSN 檔案在建立資料庫腳本之前，必須提供一條使ADO 定位、標識和與資料庫通訊的途徑。資料庫驅動程式使用Data Source Name (DSN) 定位和識別特定的ODBC 相容資料庫，將資訊從Web 應用程式傳遞給資料庫。典型情況下，DSN 包含資料庫配置、使用者安全性和定位訊息，且可以取得Windows NT 註冊表項中或文字檔案的表格。
透過ODBC，您可以選擇您想要建立的DSN 的類型：使用者、系統或檔案。使用者和系統DSN 儲存在Windows NT 登錄中。系統DSN 允許所有的使用者登入到特定的伺服器上去存取資料庫，而使用者DSN 使用適當的安全身分證明限制資料庫到特定使用者的連線。文件DSN 用於從文字檔案中獲取表格，提供了對多用戶的訪問，並且透過複製DSN 文件，可以輕易地從一個伺服器轉移到另一個伺服器。由於以上原因，本主題中的範例將使用文件DSN。
透過在Windows 的“開始”功能表中開啟“控制台”，您可以建立基於DSN 的檔案。雙擊“ODBC”圖標，然後選擇“檔案DSN”屬性頁，按一下“新增”，選擇資料庫驅動程序，然後按一下“下一步”。依照後面的指示配置適用於您的資料庫軟體的DSN。
設定Microsoft Access 資料庫的檔案DSN
在“建立新資料來源”對話方塊中，從列錶框選擇“Microsoft Access Driver”，然後按一下“下一步”。
鍵入您的DSN 檔案名，然後按一下「下一步」。
按一下「完成」以建立資料來源。
在「ODBC Microsoft Access 97 安裝程式」對話方塊中，按一下「選擇」。選擇Microsoft Access 資料庫檔案(*.mdb)，然後按一下「確定」。
注意 由於效能和可靠性的原因，我們極力推薦您使用「客戶-伺服器資料庫引擎」配置由這樣一種Web 應用程式驅動的數據，這些Web 應用程式必須滿足10 個以上的用戶的同時存取。儘管ASP 可以使用任何ODBC 相容的資料庫，但它是為使用客戶伺服器資料庫而設計的，而且經過了嚴格的測試，這些資料庫包括Microsoft ® SQL Server、Oracle 等。
ASP 支援共用檔案資料庫（如Microsoft ® Access 或Microsoft ® FoxPro）作為有效的資料來源。儘管在ASP 文件中的一些範例使用共用文件資料庫，但我們建議只將此類資料庫引擎用於開發或有限的設定方案。共享文件資料庫可能無法很好地適用於可滿足高需求、高品質的Web 應用程式的客戶-伺服器資料庫。
配置SQL Server 資料庫檔案DSN
注意 如果資料庫駐留在遠端伺服器上，請與伺服器管理員聯繫，以取得附加的設定資訊；下列的程序使用SQL Server 的ODBC 預設的設置，它可能不適用於您的硬體配置。
在“建立新資料來源”對話方塊中，從列錶框中選擇“SQL Server”，然後按一下“下一步”。
鍵入DSN 檔案的名稱，然後按一下「下一步」。
按一下「完成」以建立資料來源。
鍵入執行SQL 服務程式的伺服器的名稱、登入ID 和密碼。
在「建立SQL Server 的新資料來源」對話方塊中，在「伺服器」列錶框中鍵入包含SQL Server 資料庫的伺服器的名稱，然後按一下「下一步」。
選擇驗證登入ID 的方式。
如果要選擇SQL 伺服器驗證，請輸入一個登入ID 和密碼，然後按一下「下一步」。
在「建立SQL Server 的新資料來源」對話方塊中，設定預設資料庫、預存程序設定的驅動程式和ANSI 標識，然後按一下「下一步」。 （要獲取詳細信息，請單擊“幫助”。）
在對話方塊（同樣名為「建立SQL Server 的新資料來源」）中，選擇一種字元轉換方法，然後按一下「下一步」。 （詳細信息，請單擊“幫助”。）
在下一個對話方塊（同樣名為「建立SQL Server 的新資料來源」）中，選擇登入設定。
注意 典型情況下， 您只能使用日誌來偵錯資料庫存取問題。
在「ODBC Microsoft SQL Server 安裝程式」對話方塊中，按一下「測試資料來源」。如果DSN 正確創建，「測試結果」對話方塊將指出測試成功完成。
SQL server 連線和安全資訊如果您正在開發用於連接遠端SQL Server 資料庫的ASP 資料庫應用程序，應考慮以下問題：
連線方案- 您可以選擇TCP/IP 套接字和命名管道的方法存取遠端的SQL Server 資料庫。當使用命名管道時，因為在建立連線之前，資料庫使用者必須被Windows NT 確認，所以對只有適當的SQL Server 存取身分而在該電腦上沒有Windows NT 使用者帳號的使用者可能會被拒絕存取命名管道。作為一種替代方案，使用TCP/IP 套接字的連接可直接連接到資料庫伺服器，而不必透過使用命名管道的中間電腦。因為使用TCP/IP 套接字連線可直接連線到資料庫server，所以透過SQL Server 的確認，使用者就可以取得存取權，而不必透過Windows NT 的確認。
注意 連接到遠端資料庫時使用TCP/IP 套接字可提高效能。
安全性- 如果您使用SQL Server 的整合或混合式安全性特性，且SQL Server 資料庫位於遠端伺服器上，則不能使用Windows NT 請求/回應的確認。也就是說，不能將Windows NT 請求/回應身分證轉送到遠端電腦上，而只能使用基本驗證，它會根據使用者提供使用者名稱和口令資訊進行。
有關此主題的詳細信息，請參閱http://www.microsoft.com/sqlsupport/
上的Microsoft SQL Server 技術支援主頁。
配置Oracle 資料庫檔案DSN
首先要確保Oracle 使用者軟體被正確地安裝要建立DSN 的電腦上。詳細信息，請與伺服器管理員聯絡或參閱資料庫軟體文件。
在「建立新資料來源」對話方塊中，從列錶框中選擇“Microsoft ODBC for Oracle”，然後按一下「下一步」。
鍵入DSN 檔案的名稱，然後按一下「下一步」。
按一下「完成」以建立資料來源。
輸入使用者名稱、密碼和伺服器名，然後按一下「確定」。
注意 DSN 檔案使用.dsn 副檔名，位於ProgramsCommon FilesODBCData Sources 目錄中。
有關建立DSN 檔案的詳細信息，請訪問Microsoft ODBC Web 網站：http://microsoft.com/odbc/。
連接資料庫存取資料庫資訊的第一步是和資料庫來源建立連線。 ADO 提供Connection 對象，可使用該物件建立和管理應用程式和ODBC 資料庫之間的連線。 Connection 物件具有各種屬性和方法，可以使用它們開啟和關閉資料庫連接，並且發出查詢請求來更新資訊。
若要建立資料庫連接，首先應建立Connection 物件的實例。例如，下面的腳本建立Connection 對象，接著開啟資料庫連線：
<%
'Create a connection object
Set cn = Server.CreateObject("ADODB.Connection")
'Open a connection; the string refers to the DSN
cn.Open "FILEDSN=MyDatabase.dsn"
%>
注意 無論在等號(=) 之前或之後，DSN 字串都不能包含空格。
在這種情況下，Connection 物件的Open 方法會引用基於DSN 的文件，其中包含關於資料庫的位置和設定資訊。也可以不引用DSN，直接明確引用供應程序、資料來源、使用者ID 和密碼。
用Connection 物件執行查詢用Connection 物件的Execute 方法，您可以發出結構化查詢語言(SQL) 查詢資料庫來源並擷取結果。 SQL 是用於與資料庫通訊的工業標準語言，它有許多命令可用於檢索和更新資訊。
以下的腳本使用Connection 物件的Execute 方法在SQL INSERT 指令的表格中發出查詢，該指令將資料插入特定的資料庫表格。在下面的範例中，腳本將名稱Jose Lugo 插入名為Customers 的資料庫表中。
<%
'Define file based DSN
strDSN = "FILEDSN=MyDatabase.dsn"
'Instantiate the Connection object and open a database connection
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open strDSN
'Define SQL SELECT statement
strSQL = "INSERT INTO Customers (FirstName, LastName) VALUES ('Jose','Lugo')"
'Use the Execute method to issue a SQL query to database
cn.Execute(strSQL)
%>
注意 基於DSN 路徑字串的檔案在等號(=)前後不應包含空格。
除了SQL INSERT 指令以外，您也可以使用SQL UPDATE 和DELETE 指令來變更和刪除資料庫資訊。
用SQL UPDATE 指令，您可以改變資料庫表中各項目值。下面的腳本使用UPDATE 指令將Customers 表中每個LastName 欄位包含姓Smith 記錄的FirstName 欄位變更為Jeff。
<%
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "FILEDSN=MyDatabase.dsn"
cn.Execute "UPDATE Customers SET FirstName = 'Jeff' WHERE LastName = 'Smith' "
%>
若要從資料庫表中刪除特定的記錄，可使用SQL DELETE 指令。下面的腳本從Customers 表中刪除了所有姓Smith 的行：
<%
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "FILEDSN=MyDatabase.dsn"
cn.Execute "DELETE FROM Customers WHERE LastName = 'Smith'"
%>
注意 使用SQL DELETE 指令時，必須謹慎從事。當使用不含WHERE 子句的DELETE 指令時，它將刪除表中的所有行。一定要包含SQL WHERE 子句來指定要刪除的確切行。
使用Recordset 物件處理結果儘管Connection 物件簡化了連接資料庫和查詢任務，但Connection 物件仍有許多不足。確切地說，檢索和顯示資料庫資訊的Connection 物件不能用於建立腳本；您必須確切知道要對資料庫所做的更改，然後才能使用查詢實作變更。
對於檢索資料、檢查結果、更改資料庫，ADO 提供了Recordset 物件。正如它的名稱所暗示的那樣，Recordset 物件有許多您可以使用的特性，根據您的查詢限制，檢索並且顯示一組資料庫行，即「記錄」。 Recordset 物件保持查詢傳回的記錄的位置，允許您一次一項逐步掃描結果。
根據Recordset 物件的指標類型屬性設置，您可以捲動和更新記錄。資料庫指標可讓您在一組記錄中定位到特定的項目。指標也用於檢索和檢查記錄，然後在這些記錄的基礎上執行操作。 Recordset 物件有一些屬性，可用於精確控制指標的行為，提高您檢查和更新結果的能力。例如，您可以使用CursorType 和CursorLocation 屬性設定指標的類型，將結果傳回給用戶端應用程式（結果通常保留在資料庫伺服器上）並顯示其他使用者對資料庫的最後一次變更。
檢索記錄一個成功的資料庫應用程式都使用Connection 物件建立連結並使用Recordset 物件處理傳回的資料。透過「協調」兩個物件的特定功能，您可以開發出幾乎可以執行任何資料處理任務的資料庫應用程式。例如，下面的伺服器端腳本使用Recordset 物件執行SQL SELECT 指令。 SELECT 指令會擷取一組基於查詢限制的資訊。查詢也包含SQL WHERE 子句，用來縮小查詢的範圍。此範例中，WHERE 子句將查詢限制為所有的Customers 資料庫表中所包含的姓Smith 的記錄。
<%
'Establish a connection with data source
strDSN = "FILEDSN=MyDatabase.dsn"
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open strDSN
'Instantiate a Recordset object
Set rsCustomers = Server.CreateObject("ADODB.Recordset")
'Open a recordset using the Open method
' and use the connection established by the Connection object
strSQL = "SELECT FirstName, LastName FROM Customers WHERE LastName = 'Smith' "
rsCustomers.Open strSQL, cn
'Cycle through record set and display the results
' and increment record position with MoveNext method
Set objFirstName = rsCustomers("FirstName")
Set objLastName = rsCustomers("LastName")
Do Until rsCustomers.EOF
Response.Write objFirstName & " " & objLastName & "<BR>"
rsCustomers.MoveNext
Loop
%>
注意，在前面的範例中，用來建立資料庫連線的Connection 物件和Recordset 物件使用該連線從資料庫中檢索結果。當您需要精確地設定和資料庫建立連結所採用的方式時，這個方法是非常有用的。例如，如果您需要在連線嘗試失敗之前指定等待的時間，則需要使用Connection 物件來設定屬性。但是，如果您只是想使用ADO 預設的連接屬性建立連接，則應該使用Recordset 物件的Open 方法去建立連結：
<%
strDSN = "FILEDSN=MyDatabase.dsn"
strSQL = "SELECT FirstName, LastName FROM Customers WHERE LastName = 'Smith' "
Set rsCustomers = Server.CreateObject("ADODB.Recordset")
'Open a connection using the Open method
'and use the connection established by the Connection object
rsCustomers.Open strSQL, strDSN
'Cycle through the record set, display the results,
' and increment record position with MoveNext method
Set objFirstName = rsCustomers("FirstName")
Set objLastName = rsCustomers("LastName")
Do Until rsCustomers.EOF
Response.Write objFirstName & " " & objLastName & "<BR>"
rsCustomers.MoveNext
Loop
%>
當使用Recordset 物件的Open 方法建立一個連線時，必須使用Connection 物件來確保連結的安全性。
用Command 物件改善查詢透過ADO Command 對象，可以像用Connection 物件和Recordset 物件那樣執行查詢，唯一的不同在於用Command 物件您可以在資料庫來源上準備、編譯您的查詢並且重複使用一組不同的值來發出查詢。這種方式的編譯查詢的優點是您可以最大程度地減少向現有查詢重複發出修改的請求所需的時間。另外，您還可以在執行之前透過您的查詢的可變部分的選項使SQL 查詢保持局部未定義。
Command 物件的parameter 集合減少了您的麻煩，使您不必在每次重新發出查詢時重新建立查詢。例如，如果需要定期更新基於庫存清單的Web 系統中的供應和價格信息，可以用下面的方法預先定義查詢：
<%
'Open a connection using Connection object Command object
'does not have an Open method for establishing a connection
strDSN = "FILEDSN=MyDatabase.dsn"
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open strDSN
'Instantiate Command object; use ActiveConnection property to attach
'connection to Command object
Set cm= Server.CreateObject("ADODB.Command")
Set cm.ActiveConnection = cn
'Define SQL query
cm.CommandText = "INSERT INTO Inventory (Material, Quantity) VALUES (?, ?)"
'Save a prepared (or pre-compiled) version of the query specified in CommandText
'property before a Command object's first execution.
cm.Prepared = True
'Define query parameter configuration information
cm.Parameters.Append cm.CreateParameter("material_type",200, ,255 )
cm.Parameters.Append cm.CreateParameter("quantity",200, ,255 )
'Define and execute first insert
cm("material_type") = "light bulbs"
cm("quantity") = "40"
cm.Execute
'Define and execute second insert
cm("material_type") = "fuses"
cm("quantity") = "600"
cm.Execute
%>
請檢查上面的例子，您將注意到，腳本用不同的數值重複建立和發出一個SQL 查詢，而沒有重新定義和重新發送查詢到資料庫來源。用Command 物件編譯查詢也可避免SQL 查詢所造成的合併字串和表格變數問題。特別是，透過使用Command 物件的Parameter 集合可以避免與定義字串、日期、時間變數的類型有關的問題。例如，包含「'」的SQL 查詢值可能會導致查詢失敗：
strSQL = "INSERT INTO Customers (FirstName, LastName) VALUES ('Robert','O'Hara')"
請注意，姓O'Hara 中包含一個“'”，它與在SQL VALUES 關鍵字中用來表示資料的“'”衝突。將查詢數值作為Command 物件參數綁定，可以避免此類問題。
結合HTML 表格和資料庫存取包含HTML 表格的Web 頁可使用戶遠端查詢資料庫並且檢索特定的資訊。用ADO 您可以建立非常簡單的腳本來收集使用者表格資訊、建立自訂的資料庫查詢以及將資訊傳回給使用者。使用ASP Request 對象，您可以檢索輸入到HTML 表格的資訊並將這些資訊合併到SQL 語句中。例如，下面的腳本模組將HTML 表格提供的資訊插入表格中。此腳本以Request 物件的Form 集合收集使用者資訊。
<%
'Open a connection using Connection object. The Command object
'does not have an Open method for establishing a connection
strDSN = "FILEDSN=MyDatabase.dsn"
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open strDSN
'Instantiate Command object
'and use ActiveConnection property to attach
'connection to Command object
Set cm= Server.CreateObject("ADODB.Command")
Set cm.ActiveConnection = cn
'Define SQL query
cm.CommandText = "INSERT INTO MySeedsTable (Type) VALUES (?)"
'Define query parameter configuration information
cm.Parameters.Append cm.CreateParameter("type",200, ,255 )
'Define and execute insert
cm("type") = Request("SeedType")
cm.Execute
%>
管理資料庫連線設計一個能經得起考驗的Web 資料庫應用程式（例如為數千個客戶服務的線上購物應用程式）的最大挑戰，在於如何合理地管理資料庫連線。開啟並且保持資料庫連接，即使在沒有資訊傳輸時，也會嚴重耗費資料庫伺服器的資源並且可能會導致連接性問題。設計良好的Web 資料庫應用程式將回收資料庫連接並能夠補償因網路阻塞造成的延遲。
使連線逾時活動的突然增長可能使資料庫伺服器變得十分笨拙，大量增加建立資料庫連線的時間。結果是，過長的連線延遲將降低資料庫的效能。
用Connection 物件的ConnectionTimeout，您可以限制放棄連線嘗試並發出錯誤訊息之前應用程式等待的時間。例如，下面的腳本設定ConnectionTimeout 屬性，在取消連線嘗試之前等待20 秒：
Set cn = Server.CreateObject("ADODB.Connection")
cn.ConnectionTimeout = 20
cn.Open "FILEDSN=MyDatabase.dsn"
預設的ConnectionTimeout 屬性是30 秒。
注意 在將ConnectionTimeout 屬性合併到資料庫應用程式之前，請務必確保連線提供者和資料來源支援該屬性。
共享連接經常建立和中斷資料庫連接的Web 資料庫應用程式可能會降低資料庫伺服器的效能。 ASP 支援用ODBC 3.5 的共享特性有效管理連線。連接共享維持打開的資料庫連接並管理不同的用戶共享該連接，以維持其效能和減少空閒的連接數。對每一個連線請求，連線池首先確定池中是否有空閒的連線。如果存在，連線池會傳回連線而不是建立到資料庫的新連線。
如果希望將ODBC 驅動程式加入到連線共用中，則必須設定資料庫驅動程式並在Windows NT 登錄中設定驅動程式的CPTimeout 屬性。當ODBC 斷開連線時，連線被存入池中，而不是被斷開。 CPTimeout 屬性決定連線池中的連線保留的時間長度。如果在池中連線保留的時間比CPTimeout 設定的時間長，則連線將關閉並且從池中刪除。 CPTimeout 的預設值是60 秒。
您可以透過建立以下設定的登錄鍵來選擇性地設定CPTimeout 的屬性，從而啟用特定ODBC 資料庫驅動程式的連線池：
HKEY_LOCAL_MACHINESOFTWAREODBCODBCINST.INIdriver-nameCPTimeout = timeout
(REG_SZ, units are in seconds)
例如，下面的鍵將SQL Server 驅動程式的連線池的逾時設定定為180 秒（3 分鐘）。
HKEY_LOCAL_MACHINESOFTWAREODBCODBCINST.INISQL ServerCPTimeout = 180
注意 預設情況下，透過將CPTimeout 設定為60 秒，Web 伺服器將啟動SQL Server 的連線池。
使用跨頁連接儘管您可以透過儲存ASP 的Application 物件的連接重複使用跨頁連接，但是，始終使連接保持開啟是不必要的，也沒有充分利用連接池的優點。如果有許多使用者需要連接到同一個ASP 資料庫應用程序，一個好方法就是，將跨頁連接字串置於ASP 的Application 物件中，重複使用資料庫連接。例如，可以在Global.asa 檔案的Application_OnStart 事件過程中指定連接字串，如下面的腳本所示：
Application.lock
Application("ConnectionString") = "FILEDSN=MyDatabase.dsn"
Application.unlock
然後，在每一個存取資料庫的ASP 檔案中寫入：
<OBJECT RUNAT=Server ID=cn PROGID="ADODB.Connection"> </OBJECT>
若要建立連線物件的實例，請使用下列腳本：
cn.Open Application("ConnectionString")
對於開啟的連接，可以在頁尾寫入以下腳本，關閉連接：
cn.Close
在單一使用者需要重複使用跨頁連線的情況下，使用Session 物件連線比使用Application 物件更好。
關閉連線要更好地使用連線池，就應盡快關閉資料庫連線。預設情況下，當腳本執行完以後，連線將被終止。當不再需要連線時將其關閉，就可以減少對資料庫伺服器的要求並可以使其他使用者能夠使用該連線。
可以使用Connection 物件的Close 方法終止Connection 物件和資料庫之間的連線。下面的腳本打開連接，然後將其關閉：
<% strDSN = "FILEDSN=MyDatabase.dsn"
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open
cn.Close
%>
