Databases Fundamentals: Data Records

In our description of tables, we saw that a table was made of one or various columns that represent categories of data. After creating such a table and its columns that represent the categories, you (actually the user) can enter values in the table to make it a valuable list. Filling up a table with values is referred to as data entry.

A record on a table is represented as a row (horizontal) of data. To support the various records that belong to a table, the DataTable class is equipped with a property called Rows. The DataTable.Rows property is in fact an object of the DataRowCollection class. The DataRowCollection class provides the necessary properties and methods you can use to create and manage records of a table. A row itself is an object based on the DataRow class.

1. Start Notepad and type the following:
    

   Imports System Imports System.IO Imports System.Xml Imports System.Data Public Class VideoCollection Public dsVideoCollection As DataSet Public Sub New() dsVideoCollection = new DataSet("Videos") End Sub End Class Class Exercise Public Syb Main() Dim vdoColl As VideoCollection = new VideoCollection() End Sub End Class

2. To save the file, on the main menu, click File -> Save
3. Locate your VBasic folder and display it in the Save In combo box
4. Click the Create New Folder button. Type VideoCollection3 and press Enter twice to display the new folder in the Save In combo box
5. Change the Save As Type to All Files. Change the file name to Exercise.vb and click Save

Introduction to Data Entry

When performing data entry and while doing it on a record, the record has a status that can be identify by the DataRow.RowState property which is a value based on the DataRowState enumerator.

A record on a table is represented as a row of data. To support the various records that belong to a table, the DataTable class is equipped with the Rows property which is an object of type DataRowCollection with each record being an object of type DataRow.

Before adding a new record to a table, you must let the table know. This is done by calling the DataTable.NewRow() method. Its syntax is:

Public Function NewRow() As DataRow

The DataTable.NewRow() method returns a DataRow object. Here is an example:

Imports System
Imports System.Xml
Imports System.Data

Module Exercise

    Private dtDirectors As DataTable

    Sub Main()

        dtDirectors = New DataTable("Directors")
        Dim rowDirector As DataRow = dtDirectors.NewRow()

    End Sub

End Module

Practical Learning: Introducing Data Entry

Data Entry

Adding a Value Based on the Column Index

When you call the DataTable.NewRow() method, the record's status is DataRowState.Detached. After calling the DataTable.NewRow() method, you can specify the value that the column would carry. To do this, you must specify the table's column whose value you want to provide. You can locate a column based on an index as we mentioned already that the columns of a table are stored in the DataTable.Columns property which is based on the DataColumnCollection class. Each column can be identified by its index. 

When the record has been added to the table, the record has a status of DataRowState.Added. The above version of the DataRowCollection.Add() method allows you to add a value for one column. To complete a record, you would have to create a value for each column.

Practical Learning: Adding a Value Based on the Column Index

Adding a Value Based on the Column Object Name

To specify the name of the column, the DataRow class is equipped with an index property that allows you to identify a column by its object name, by its variable name, or by its index. Based on this, the DataRow property is overloaded with three versions. One of the versions uses the following syntax:

Overloads Public Default Property Item(ByVal columnName As String) As Object

This property expects the object name of the column passed in its square brackets. When calling this property, you can assign it the desired value for the column. Here is an example:

Imports System
Imports System.Xml
Imports System.Data

Module Exercise

    Private dtDirectors As DataTable
    Private colDirector As DataColumn
    Private dsVideoCollection As DataSet

    Sub Main()

        dsVideoCollection = New DataSet("VideoCollection")

        colDirector = New DataColumn("Director")

        dtDirectors = New DataTable("Directors")
        dtDirectors.Columns.Add(colDirector)
        dsVideoCollection.Tables.Add(dtDirectors)

        Dim rowDirector As DataRow = dtDirectors.NewRow()
        rowDirector("Director") = "John Landis"

    End Sub

End Module

After assigning the desired value to the row, to add the new value to a table, the DataRowCollection class provides the Add() method that is overloaded with two versions. The first version of this method uses the following syntax:

Overloads Public Sub Add(ByVal row As DataRow)

This method simply expects you to pass the DataRow object you previously defined. Here is an example:

Imports System
Imports System.Xml
Imports System.Data

Module Exercise

    Private dtDirectors As DataTable
    Private colDirector As DataColumn
    Private dsVideoCollection As DataSet

    Sub Main()

        dsVideoCollection = New DataSet("VideoCollection")

        colDirector = New DataColumn("Director")

        dtDirectors = New DataTable("Directors")
        dtDirectors.Columns.Add(colDirector)
        dsVideoCollection.Tables.Add(dtDirectors)

        Dim rowDirector As DataRow = dtDirectors.NewRow()
        rowDirector("Director") = "John Landis"
        dtDirectors.Rows.Add(rowDirector)

    End Sub

End Module

In the same way, you can identify each column of a table by its object name and assign it the appropriate value. Once the record is complete, you can add it to the table. Here is an example:

Adding an Array of Records

The above version of the DataRowCollection.Add() method means that you must identify each column before assigning a value to it. If you already know the sequence of columns and don't need to explicitly identify them, you can store all values in an array and simply add the array as a complete record. To do this, you can use the second version of the DataRowCollection.Add() method whose syntax is:

Overloads Public Overridable Function Add(ByVal values() As Object) As DataRow

Here is an example:

Private Sub AddNewVideo()
        Dim vdoRecord() As String = {"Fatal Attraction", "Adrian Lyne", "1987", _
                                     "120 Minute", "R", "DVD", "Drama"}

        dtVideos.Rows.Add(vdoRecord)
End Sub

There is an alternative to this second version of the DataRowCollection.Add() method. As opposed to passing an array of values to the Add() method, you can first define an array, assign that array to a DataRow variable, then pass that DataRow object to the Add() method. To support this technique, the DataRow class is equipped with an ItemArray property that expects an array. Here is an example:

Private Sub AddNewVideo()
        Dim vdoNewVideo() As String = {"Her Alibi", "Bruce Beresford", "1989", _
                       "94 Minute", "PG-13", "DVD", "Comedy"}
        Dim rowNewVideo As DataRow = dtVideos.NewRow()
        rowNewVideo.ItemArray = vdoNewVideo
        dtVideos.Rows.Add(rowNewVideo)
End Sub

After creating the records of a table, if a record contains invalid values, the DataRow.HasErrors property can help you identify them.

Locating Records and Their Values

Locating a Record

Before performing any operation on a record, you must be able to locate it. That is, you must be able to identify a record among the various records of a table. To locate a record in the DataTable.Rows collection, the DataRowCollection class provides the Item property that is defined as follows

Public Default ReadOnly Property Item(ByVal index As Integer) As DataRow

The records of a table are stored in a list (called the DataRowCollection). The first record has an index of 0. The second record has an index of 1, and so on. Here is an example of using it to retrieve the information stored in a record.

When you pass an index to this property, the compiler would check whether the record exists. If a record with that index exists, its DataRow value is produced. If you specify an index that is either less than 0 or beyond the number of records in the table, the compiler would throw an IndexOutOfRangeException exception.

To get the number of records that a table contains, access the Count property of its DataRowCollection. The Count property is inherited from the InternalDataCollectionBase class, which is the parent of many collection classes.

When the records of a DataTable object have been created, you can get their list as an array using its List property that is inherited from the InternalDataCollectionBase class. This property returns an ArrayList type of list.

Locating a Value

As mentioned already, a record is in fact one or a group of values from each of the columns of the table. Consider the following table:

The "A Few Good Men" string is a value of the Title column. In the same way, "VHS" is a value of the Format column. In some circumstances, you will need to locate a particular value in order to perform an operation on it. As seen above, you can start by locating the record you need and return its DataRow object. To know the table that the record belongs to, access its DataRow.Table property. This property is declared as follows:

Public ReadOnly Property Table As DataTable

To locate the value that a record holds under a particular column, the DataRow class provides the Item property that is overloaded with three versions (actually six, but at this time we are interested in the first three only). One of the versions of this property uses the following syntax:

Overloads Public Default Property Item(ByVal columnName As String) As Object

To use this property, pass the object name of the column in the square brackets. Instead of using the index of a column, you can also locate a value using the variable name of its column. To do this, you can use the following syntax of the DataRow indexed property:

Overloads Public Default Property Item(ByVal column As DataColumn) As Object

This property expects the object name of the column passed in its square brackets. The third option you have is to identify the column by its index. To do this, use the following syntax of the DataRow indexed property:

Overloads Public Default Property Item(ByVal columnIndex As Integer) As Object

This property expects the index of the column.

Record Maintenance

Once a table has been filled with records, you can perform maintenance operations on it such as changing some records or removing others. To remove a record from a table, you can call the DataRowCollection.Remove() method. Its syntax is:

Public Sub Remove(ByVal row As DataRow)

This method takes as argument a DataRow object and checks whether the table contains. If that record exists, it gets deleted, including all of its entries for each column. When calling this method, you must pass an exact identification of the record. If you don't have that identification, you can delete a record based on its index. To do this, you would call the DataRowCollection.RemoveAt() method. Its syntax is:

Public Sub InsertAt(ByVal row As DataRow, ByVal pos As Integer)

This method takes as argument the index of the record you want to delete. If a record with that index exists, it would be deleted.

To delete all records of a table, call the DataRowCollection.Clear() method. Its syntax is:

Public Sub Clear()

This method is used to clear the table of all records.