ASP.NET Data Access

Article
10/17/2014

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.

For specific examples that use these controls to display database data, seeBinding SQL Data to a DataGrid Control, Binding SQL Data to a DataList Control, and Binding SQL Data to a Repeater Control.

ASP.NET Data Access

Working with a Single Table

Working with Multiple Database Tables

Displaying Data in a Control

See Also

Additional resources