ADO: The Recordset Object

The way data resides inside an ADO Recordset object is somewhat similar to database tables. The data is represented in single rows. A value inside a column is accessed by browsing to the record and reading the value attribute of the Recordset’s Fields(columnName) object property. A reading of a Recordset value is illustrated here:

Set objRS = Server.CreateObject("ADODB.Recordset")
'retrieve a set of rows
Variable1 = objRs.Fields("FieldName")

ADO .NET: The DataSet and DataTable Objects

The DataSet object holds data values in memory in ADO .NET applications. The DataSet itself is not a direct container of data. It holds a collection of Recordset-like objects called DataTables. A DataTable is the client representation of a database table; it is simply a set of rows. The DataSet and DataTable objects are covered in more detail in Chapter 3, “Data Manipulation.”

The following code illustrates the way in which a DataSet is initialized on a Web Form:

Dim objDSArtists As New _
        System.Data.DataSet("Artists")

Dim objDAArtists As _
        System.Data.SqlClient.SqlDataAdapter

Dim strQuery, _
    strCon, _
    strArtistName _
    As String

Dim intArtistID As Long

Dim intLoopCounter As Integer

strCon = "Initial Catalog=Multimedia;"
strCon &= "Data Source=LOCALHOST;UID=sa;" 
strCon &= "PWD=sa;"

strQuery = "SELECT * FROM Artist "
strQuery &= "Order By ArtistName"

objDAArtists = New _
SqlClient.SqlDataAdapter(strQuery, strCon)

objDAArtists.Fill(objDSArtists)

lstSingers.Items.Clear()

With objDSArtists.Tables(0)
     For intLoopCounter = 0 To .Rows.Count - 1
         strArtistName = _
        .Rows(intLoopCounter).Item("ArtistName")
         lstSingers.Items.Add(strArtistName)
     Next
End With

1. The DataSet is initialized followed by a DataAdapter object from the SQL Server .NET data provider, SqlClient.

2. Other variables that would be used for DataSet manipulation are also declared and initialized.

3. The DataAdapter is then initialized with a string representing the query and the connection string for the database on the SQL Server.

4. lstSingers is a ListBox web control. The purpose of the code above is to retrieve a list of Singers and populate the ListBox.

5. After data retrieval, the DataSet is populated by whatever is retrieved by the DataAdapter object, using the latter’s Fill method. When the Fill method is called, the DataSet, which is passed as a parameter, does the following:

   1. Internally creates a DataTable object

   2. Initializes that particular DataTable with the row set retrieved by the DataAdapter

6. The Tables collection property of the DataSet holds references to all the DataTable objects that are part of the particular DataSet. Each object inside the collection has an index. Since the code above created only one DataTable, we are safe in referencing that DataTable with the index 0; otherwise, it would have been better to reference it using a particular name specified when the Fill method is called on the DataAdapter.