下面範例中的第一個程式碼區塊對於某類ASP 應用程式是很典型的,該類別應用程式使用ADO 讀取和操作從單一SQL 查詢傳回的記錄集。它使用ADO Recordset 物件讀取從用Microsoft Access 提供的Northwind 範例資料庫傳回的資料記錄。這些程式碼將包含在具有.asp 檔案副檔名的檔案中。
[Visual Basic]
< %@LANGUAGE=VBSCRIPT% >
<!
This ASP example uses ADO to read records from a database and print two
fields from all returned records to an ASP page. Connection to the Northwind database is through an ODBC system data source (DSN.
>
<html>
<body>
<%
dim ADOconn, ADOrs, sqlstr
sqlstr="SELECT * FROM Employees;"
set ADOconn = Server.CreateObject("ADODB.Connection")
ADOconn.Open "DSN = Test"
set ADOrs = ADOconn.execute(sqlstr)
if ADOrs.BOF and ADOrs.EOF then ' Query didn't return any records.
Response.Write("No Records.")
else
ADOrs.MoveFirst
Do While Not ADOrs.EOF
Response.Write(ADOrs("FirstName") & " " _
& ADOrs("LastName") & "<br>")
ADOrs.MoveNext
Loop
Response.Write("<p>End of data.")
end if
ADOrs.close
set ADOrs = nothing
%>
</body>
</html>
下面的範例闡釋將前面範例轉換為ASP.NET 應用程式所需的最低程度的變更。為了符合新的Visual Basic 語法,大多數的變更都是必要的。此檔案可以用.aspx 檔案副檔名重新命名,並且將與ASP.NET 一起運作。修改後的程式碼行以粗體顯示。注意,在第一行上新增了具有aspcompat=true 屬性的<%@ Page > 指令。
[Visual Basic]
< %@Page aspcompat=true Language = VB%>
<!
This example uses ADO to read records from a database and print two
fields from all records in the database to an ASP.NET page.
The database is located on the server and connection is through an ODBC system data source (DSN.
>
<html>
<body>
<%
dim objConn, rs, sqlstr
sqlstr="SELECT * FROM Employees;"
objConn = Server.CreateObject("ADODB.Connection") ' Set removed.
objConn.Open("DSN=TEST") ' Parentheses added.
rs = objConn.execute(sqlstr) ' Set statement removed.
Response.Write("<p>ADO Test</p>")
if rs.BOF and rs.EOF then ' Query didn't return any records.
Response.Write("No Records")
else
rs.MoveFirst
Do While Not rs.EOF
' Specify Value property.
Response.Write(rs("FirstName").Value _
& " " & rs("LastName").Value & "<br>")
rs.MoveNext
Loop
Response.Write("<p>End of data")
end if
rs.close
rs = nothing ' Set statement removed.
%>
下一個範例是一個ASP.NET 應用程序,該程式使用ADO.NET 從與前面範例相同的Northwind 資料庫讀取記錄。這些程式碼產生的輸出等效於前面範例的輸出,並且已被修改以符合ASP.NET 程式碼區塊約定。
此範例會建立一個ADO.NET DataSet 對象,在此情況下此物件包含一個資料表,而該資料表的使用方式與ADO 記錄集的使用方式幾乎相同。請注意,資料集可以由一個或多個構成記憶體駐留資料庫的DataTables、DataRelations 和Constraints 的集合組成,因此ADO.NET 資料集比ADO 記錄集靈活得多。
為了使用ADO.NET,需要匯入System.Data 和System.Data.OleDb 命名空間。如果資料來源是SQL Server 資料庫,則匯入System.Data.SqlClient 命名空間而不是System.Data.OleDb。有關使用ADO 和SQL .NET 資料提供者的連接物件的詳細信息,請參閱管理連線。
[Visual Basic]
< %@Import Namespace="System.Data"%>
< %@Import Namespace="System.Data.OleDb"%>
<!
This example uses ADO.NET to read records from a database and print two
fields from all returned records to an ASP.NET page. The database
is located on the local server.
>
<html>
<Script Language=VB Runat=Server>
Sub Page_Load(Sender As Object, e As EventArgs)
Dim MyConnection As OleDbConnection
Dim MyCommand As OleDbDataAdapter
dim MyDataset As DataSet
dim MyTable As DataTable
dim loop1, numrows As Integer
dim sqlstr As String
sqlstr = "SELECT * FROM Employees;"
' Create a connection to the data source.
MyConnection = New OleDbConnection("Provider=SQLOLEDB;" _
& "server=localhost;"Integrated Security=SSPI;" _
& "Initial Catalog=Northwind")
' Create a Command object with the SQL statement.
MyCommand = New OleDbDataAdapter(sqlstr, MyConnection)
' Fill a DataSet with data returned from the database.
MyDataset = New DataSet
MyCommand.Fill(MyDataset)
' Create a new DataTable object and assign to it
' the new table in the Tables collection.
MyTable = New DataTable
MyTable = MyDataset.Tables(0)
' Find how many rows are in the Rows collection
' of the new DataTable object.
numrows = MyTable.Rows.Count
If numrows = 0 then
Response.Write("<p>No records.</p>")
Else
Response.Write("<p>" & Cstr(numrows) & " records found.</p>")
For loop1 = 0 To numrows - 1
' Print the values of the two columns in the Columns
' collection for each row.
Response.Write(MyTable.Rows(loop1).Item("FirstName") _
& " " & MyTable.Rows(loop1).Item("LastName") & "<br>")
Next loop1
End If
Response.Write("<p>End of data.</p>")
End Sub
</Script>
</html>
在資料庫查詢(甚至是多表聯接查詢)傳回單一記錄集的情況下,可以透過與使用ADO 記錄集的方式幾乎相同的方式使用單一DataTable(在此範例中為MyTable)。
參考《NET FRAMEWORK SDK文擋》