ASP.NET Data Access
The .NET Framework includes a new data access technology named ADO.NET, an improvement to ADO. However, the classes that make up ADO.NET are different from the ADO objects that you might be familiar with. Some changes must be made to existing ADO applications to convert them to ADO.NET. The changes do not have to be made immediately for them to run under ASP.NET, however, as ADO will function under ASP.NET. Nonetheless, converting ADO applications to ADO.NET is worthwhile. For disconnected applications, ADO.NET offers performance advantages over ADO disconnected record sets. Whereas ADO requires that transmitting and receiving components be COM objects, ADO.NET transmits data in standard XML format so that COM marshaling or data-type conversions are not required.
Data retrieved from a database is generally used in one of two ways. The records might be examined and manipulated by ASP code as a precursor to some other action without being displayed directly to the client, or the records simply might be displayed to the client in a table or grid. The examples in this topic show how to convert a typical ADO scenario to ADO.NET. There is a great deal more to the ADO.NET model than these short examples illustrate; this is simply a quick overview of how to port common ASP and ADO scenarios to the .NET Framework. For a more detailed introduction to ADO.NET, see the Overview of ADO.NET.
Working with a Single Table
The first block of code in the following example is typical of an ASP application that uses ADO to read and manipulate a set of records returned from a single SQL query. It uses an ADO Recordset object to read the data records returned from the Northwind sample database supplied with Microsoft Access. This code would be contained in a file with an .asp file name extension.
<%@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(Server.HtmlEncode(ADOrs("FirstName")) & " " _ & Server.HtmlEncode(ADOrs("LastName")) & "<br>") ADOrs.MoveNext Loop Response.Write("<p>End of data.") end if ADOrs.close set ADOrs = nothing %> </body> </html>
The following example illustrates the minimum changes necessary to convert the preceding example to an ASP.NET application. Most of the changes are necessary to comply with new Visual Basic syntax. This file can be renamed with an .aspx file name extension and will run with ASP.NET. Revised lines appear in bold type. Note the addition of the <%@ Page > directive with the aspcompat=true attribute on the first line.
<%@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(Server.HtmlEncode(rs("FirstName").Value) _ & " " & Server.HtmlEncode(rs("LastName").Value) & "<br>") rs.MoveNext Loop Response.Write("<p>End of data") end if rs.close rs = nothing ' Set statement removed. %>
The next example is an ASP.NET application that uses ADO.NET to read records from the same Northwind database as the previous example. This code, which generates output equivalent to that of the previous example, has been modified to comply with ASP.NET code block conventions.
The example creates an ADO.NET DataSet object, which in this case contains one data table that is used in much the same way as an ADO Recordset. Note that a DataSet can consist of collections of one or more DataTables, DataRelations, and Constraints that form a memory-resident database, so an ADO.NET DataSet is a great deal more flexible than an ADO Recordset.
In order to use ADO.NET, you need to import the System.Data and System.Data.OleDb namespaces. If your data source is a SQL Server database, import the System.Data.SqlClient namespace instead of System.Data.OleDb. For details on using the connection objects for ADO and SQL .NET Data Providers, see Managed Connections.
<%@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(Server.HtmlEncode(MyTable.Rows(loop1).Item("FirstName")) _ & " " & Server.HtmlEncode(MyTable.Rows(loop1).Item("LastName")) & "<br>") Next loop1 End If Response.Write("<p>End of data.</p>") End Sub </Script> </html>
In cases where a database query (even a multi-table join query), returns a single set of records, you can use a single DataTable (
MyTable in the example) in much the same way that you use an ADO Recordset.
Working with Multiple Database Tables
The ADO.NET DataSet can contain multiple tables and a set of relations that describes a local copy of a relational database. Think of it as a mini-database in server memory. Using the relation objects, your code can navigate through the collection of tables to access data records in complex ways without making further queries to the main data store. For more information about the components of a DataSet, see The ADO.NET DataSet.
Displaying Data in a Control
To display database data in tabular format in a browser, ASP developers have had to write code to intersperse the data with HTML tags to build HTML tables on the fly. ASP.NET includes DataGrid, DataList, and Repeater server controls that greatly simplify the task of displaying tabular data on a Web page. ADO.NET datasets can be easily bound to these controls. The ASP.NET engine, working with a control, sends pure HTML 3.2 to the browser, producing richly formatted layouts to the user.