This article was written in 2002 and is based upon ASP.NET 1.0.
The most common data centric task an ASP developer can do is create an HTML table from an SQL query. Below is an example of how to do this with ASP.NET. I’ll be connecting to a SQL Server database, but with some slight modifications this code will work for an OLE-DB provider or OLE-ODBC driver. The 3 objects used in this example are SQLConnection, SQLCommand and the SQLDataReader.
Namespace
In order to access data objects a few namespaces are required. The first is System.Data. It contains the basic ADO.NET objects. The second namespace will depend upon your data source. If it is SQL Server then you will use the System.Data.SQLClient namespace. If it isn’t then use System.Data.OleDb.
<%@Page Language="VB" %> <%@Import Namespace="System.Data" %> <%@Import Namespace="System.Data.SqlClient" %>
Connection
For SQL Server the object used to connect to the data source is SQLConnection. Ole-DB requires the OleDbConnection. Like the ADOB.Connection object in classic ADO, this also requires a connection string.
'-- SQL Server Example Dim strConnect As String strConnect = "server=localhost;database=elvis;uid=king;pwd=tcb;" Dim objConnection As New SQLConnection(strConnect) objConnection.Open
Command
Now that I’ve connected to the data source, we will want to execute an SQL query against it. In this example I want a list of all Elvis movies. The SQLCommand object allows us to execute our SQL statement against the data source. Ole-DB users will use the OleDBCommand object.
Dim strSQL As String strSQL = "SELECT filmName, yearReleased FROM TCBFilms" Dim objCommand as New SQLCommand(strSQL,objConnection)
DataReader
The DataReader object is way to access the returned data quickly. It is similar to ADO’s Recordset object. In the this example, I’ll connect the DataReader to an asp:datagrid servers-side control. This will create an HTML table with column headers. No more iterating through the Recordset row-by-row writing HTML to the screen for each row. Ole-DB should use the OleDBDataReader object.
Dim objDataReader as SQLDataReader objDataReader = objCommand.ExecuteReader() myDataGrid.DataSource = objDataReader myDataGrid.DataBind() objConnection.Close()
<asp:datagrid id="myDataGrid" runat="server" />
Last Words
This is the just a basic example of using ADO.NET objects to create a database driven HTML table with ASP.NET. ADO.NET has many more powerful features including handling disconnected data and working with XML.
