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.

Labels: ADO.NET, ASP.NET, DataGrid, VB.NET