Tables Fundamentals

Introduction to Tables

To better organize a list, you may create each category, then enter the value of each category that corresponds to a particular video. Here is an example:

This type of list is called a table: A table is a two-dimensional list that contains one or different categories of items and each category is represented with a particular value. A category of values is called a column. Under each category, you may have a group of values that belong to the same entry. Such a group of values is called a row or a record. In the above table, the values "A Few Good Men", "Rob Reiner", "1992", "138 Minute", "VH", and "R" constitute one row or record.

In our introduction, we defined a data set as one or more lists considered in a single group. Reversely, one or more lists grouped in a single entity is called a data set. In such a scenario, each list is created as a table, made of categories of values. Put in reverse, a data set is one or more tables used in one database. 

To support the creation and management of a table, the .NET Framework provides the DataTable class that is defined in the System.Data namespace. There are various ways you can create a table. You can declare a variable of type DataTable. To initialize the variable, the DataTable class is equipped with four constructors. The default constructor allows you to create a table without giving more details, especially without formally naming it. Here is an example:

Public Class Exercise
    Private dsVideoCollection As DataSet

    Private Sub btnDocument_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnDocument.Click
        dsVideoCollection = New DataSet("Videos")

        Dim tblDirectors As DataTable = New DataTable
    End Sub
End Class

If you are planning to refer to the table from more than one method, you should declare it globally. Here is an example:

Public Class Exercise
    Private dsVideoCollection As DataSet
    Private tblDirectors As DataTable

    Private Sub btnDocument_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnDocument.Click
        dsVideoCollection = New DataSet("Videos")

        tblDirectors = New DataTable
    End Sub
End Class

When creating a table, you must name it. That is, you must give it an object name. To support the name of a table, the DataTable class is equipped with a property named TableName. To specify the name of a table, you can assign a string to it. Here is an example:

Public Class Exercise
    Private dsVideoCollection As DataSet
    Private tblDirectors As DataTable

    Private Sub btnDocument_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnDocument.Click
        dsVideoCollection = New DataSet("Videos")

        tblDirectors = New DataTable
        tblDirectors.TableName = "Directors"
    End Sub
End Class

To specify the name of a table when creating it, you can use the following constructor of the DataTable class:

Public Sub New(tableName As String)

This constructor expects as argument a string that would constitute the object name of the table. Here is an example:

Public Class Exercise
    Private dsVideoCollection As DataSet
    Private tblDirectors As DataTable
    Private tblVideoCategories As DataTable

    Private Sub btnDocument_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnDocument.Click
        dsVideoCollection = New DataSet("Videos")

        tblDirectors = New DataTable
        tblDirectors.TableName = "Directors"

        tblVideoCategories = New DataTable("Categories")
    End Sub
End Class

After creating a table, you can add it to a DataSet object.

To visually create a table, under the form, you can click the data set object. In the Properties window, you can click Tables and click the ellipsis button of its field. This would open the Table Collection Editor. To create a table, you can click the Add button. A table would be added:

The new table has a default variable name under Members and an object name. To change the variable name, under the Properties list, you can click (Name) and type the desired name. To change the object name, you can click TableName and type the new name.

The tables that belong to a DataSet object are stored in a property called Tables. The DataSet.Tables property is an object of type DataTableCollection. The DataTableCollection class is derived from a class named InternalDataCollectionBase. DataTableCollection is a class that provides everything you need to add, locate, or manage any table that belongs to a DataSet object.

While performing an operation such as adding a new table to the collection, the DataTableCollection class fires a CollectionChanging event. The syntax of this event is:

Public Event CollectionChanging As CollectionChangeEventHandler

You cannot visually generate this event in Microsoft Visual Studio. If you want to catch it, you must manually define it. This event is based on the CollectionChangeEventArgs class. The CollectionChangeEventArgs class has two properties. The Action property specifies what happened to the collection. This property is of type CollectionChangeAction, which is an enumeration. If a new table is being added, then the action is Add.

The CollectionChangeEventArgs class is also equipped with a property named Element, which is of type object. This property holds a DataTableCollection object with the current changes.

Using the DataSet.Tables property, to add a created table to a DataSet object, call one of the Add() methods of the DataTableCollection class. The first version of this method has the following syntax:

Public Function Add As DataTable

This method can be used to add a new table that uses the default name. Here is an example:

Public Class Exercise
    Private tblRatings As DataTable
    Private dsVideoCollection As DataSet
    Private tblDirectors As DataTable
    Private tblVideoCategories As DataTable

    Private Sub btnDocument_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnDocument.Click
        dsVideoCollection = New DataSet("Videos")

        tblDirectors = New DataTable
        tblDirectors.TableName = "Directors"

        tblVideoCategories = New DataTable("Categories")

        tblRatings = dsVideoCollection.Tables.Add()
    End Sub
End Class

If this is the first table added to the collection, it would be named Table1. The second version of the DataTableCollection.Add() method uses the following syntax:

Public Sub Add(table As DataTable)

This version allows you to add a predefined or declared DataTable object. Here is an example:

Public Class Exercise
    Private tblRatings As DataTable
    Private dsVideoCollection As DataSet
    Private tblDirectors As DataTable
    Private tblVideoCategories As DataTable

    Private Sub btnDocument_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnDocument.Click
        dsVideoCollection = New DataSet("Videos")

        tblDirectors = New DataTable
        tblDirectors.TableName = "Directors"
        dsVideoCollection.Tables.Add(tblDirectors)

        tblVideoCategories = New DataTable("Categories")
        dsVideoCollection.Tables.Add(tblVideoCategories)

        tblRatings = dsVideoCollection.Tables.Add()
    End Sub
End Class

This second version of the method requires that you create a DataTable object first and the table probably has a name. Alternatively, if you want to add a table using its formal name, you can use the third version of this method. Its syntax is:

Public Function Add(name As String) As DataTable

This version works like the first except that, instead of the default name (such as Table1, Table2, etc), it lets you specify the desired name of the new table. Here are examples:

Public Class Exercise
    Private tblActors As DataTable
    Private tblFormats As DataTable
    Private tblRatings As DataTable
    Private dsVideoCollection As DataSet
    Private tblDirectors As DataTable
    Private tblVideoCategories As DataTable

    Private Sub btnDocument_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnDocument.Click
        dsVideoCollection = New DataSet("Videos")

        tblDirectors = New DataTable
        tblDirectors.TableName = "Directors"
        dsVideoCollection.Tables.Add(tblDirectors)

        tblVideoCategories = New DataTable("Categories")
        dsVideoCollection.Tables.Add(tblVideoCategories)

        tblRatings = dsVideoCollection.Tables.Add()

        tblActors = dsVideoCollection.Tables.Add("Actors")
        tblFormats = dsVideoCollection.Tables.Add("Formats")
    End Sub
End Class

When a new table has been created, the DataTableCollection class fires a CollectionChanged event. The syntax of this event is:

Public Event CollectionChanged As CollectionChangeEventHandler

Once again, you cannot visually generate this event. Instead, if you want to catch it, you must manually define it. This event fires every time a new table is added to the collection. This event also is based on the CollectionChangeEventArgs class. If a new table was added, then the action of the event is Add.

Instead of adding one table at a time, you can create a list of tables and then add it to the DataSet.Tables collection. To support this operation, the DataTableCollection is equipped with the AddRange() method. Its syntax is:

Public Sub AddRange(tables As DataTable())

This method expects an array of DataTable objects as argument. Here is an example:

Public Class Exercise
    Private Sub btnDocument_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnDocument.Click
        Dim dsBooks As DataSet = New DataSet("Book")

        Dim dtCategories As DataTable = New DataTable("Categorie")
        Dim dtAuthors As DataTable = New DataTable("Author")
        Dim dtPublishers As DataTable = New DataTable("Publisher")
        Dim dtBooks As DataTable = New DataTable("Book")

        Dim colTables() As DataTable = _
        { _
            dtCategories, _
            dtAuthors, _
            dtPublishers, _
            dtBooks _
        }
        dsBooks.Tables.AddRange(colTables)
    End Sub
End Class

After creating the tables that are part of an application, before performing any operation on a table, you must first retrieve its reference. This can be done by locating the particular desired table from the collection. To locate a table in the DataSet.Tables collection, the DataTableCollection class is equipped with the Item indexed property in three versions. To locate a table using its name, use the following version of this property:

Public ReadOnly Property Item ( _
	name As String _
) As DataTable

To use this property, enter the object name of the table in the square brackets of the DataTableCollection() property. Here is an example:

Public Class Exercise
    Private tblActors As DataTable
    Private tblFormats As DataTable
    Private tblRatings As DataTable
    Private tblDirectors As DataTable
    Private tblVideoCategories As DataTable
    Private dsVideoCollection As DataSet

    Private Sub btnDocument_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnDocument.Click
        dsVideoCollection = New DataSet("Videos")

        tblDirectors = New DataTable()
        tblDirectors.TableName = "Directors"
        dsVideoCollection.Tables.Add(tblDirectors)

        tblVideoCategories = New DataTable("Categories")
        dsVideoCollection.Tables.Add(tblVideoCategories)

        tblRatings = dsVideoCollection.Tables.Add()

        tblActors = dsVideoCollection.Tables.Add("Actors")
        tblFormats = dsVideoCollection.Tables.Add("Formats")

        Dim tbl As DataTable = dsVideoCollection.Tables("Directors")

        txtVideo.Text = tbl.TableName
    End Sub
End Class

Instead of locating a table by its name, you can use its index from the collection. To do this, you can use the second version of the DataTableCollection() property. Its syntax is:

Public ReadOnly Property Item(index As Integer) As DataTable

This property expects as argument the index of the table in the DataSet.Tables collection. Here is an example:

Private Sub btnDocument_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnDocument.Click
        dsVideoCollection = New DataSet("Videos")

        tblDirectors = New DataTable()
        tblDirectors.TableName = "Directors"
        dsVideoCollection.Tables.Add(tblDirectors)

        tblVideoCategories = New DataTable("Categories")
        dsVideoCollection.Tables.Add(tblVideoCategories)

        tblRatings = dsVideoCollection.Tables.Add()

        tblActors = dsVideoCollection.Tables.Add("Actors")
        tblFormats = dsVideoCollection.Tables.Add("Formats")

        Dim tbl As DataTable = dsVideoCollection.Tables(3)

        txtVideo.Text = tbl.TableName
End Sub

This would produce Actors.

The InternalDataCollectionBase class, the parent of DataTableCollection, implements the GetEnumerator() method of the IEnumerable interface. This allows you to use a For Each loop to visit each member table of the collection. Once you have reached a table in the collection, you can access any of its public properties or methods. Here is an example of applying For Each on a collection of tables of a data set to list their names:

Private Sub btnDocument_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnDocument.Click
        dsVideoCollection = New DataSet("Videos")

        tblDirectors = New DataTable()
        tblDirectors.TableName = "Directors"
        dsVideoCollection.Tables.Add(tblDirectors)

        tblVideoCategories = New DataTable("Categories")
        dsVideoCollection.Tables.Add(tblVideoCategories)

        tblRatings = dsVideoCollection.Tables.Add()

        tblActors = dsVideoCollection.Tables.Add("Actors")
        tblFormats = dsVideoCollection.Tables.Add("Formats")

        For Each tbl As DataTable In dsVideoCollection.Tables
            lbxVideos.Items.Add(tbl.TableName)
        Next
End Sub

This would produce:

You can use this approach to identity a table and then perform a desired operation on it.

When using the DataTable.Item(index As Integer) indexed property, if you provide an index below or beyond the number of tables in the set, the compiler would throw an IndexOutOfRangeException exception. To avoid this, you can request the index of the table. To do this, call the DataTableCollection.IndexOf() method. It is overloaded in three versions. One of the versions uses the following syntax:

Public Function IndexOf(table As DataTable) As Integer

This version takes as argument the variable name of the table. Here is an example of calling this method:

Private Sub btnDocument_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnDocument.Click
        dsVideoCollection = New DataSet("Videos")

        tblDirectors = New DataTable()
        tblDirectors.TableName = "Directors"
        dsVideoCollection.Tables.Add(tblDirectors)

        tblVideoCategories = New DataTable("Categories")
        dsVideoCollection.Tables.Add(tblVideoCategories)

        tblRatings = dsVideoCollection.Tables.Add()

        tblActors = dsVideoCollection.Tables.Add("Actors")
        tblFormats = dsVideoCollection.Tables.Add("Formats")

        Dim index As Integer = dsVideoCollection.Tables.IndexOf(tblActors)
        txtVideo.Text = index
End Sub

This would produce 3. Instead of using the variable name of the table, you can locate it using its formal name. To do this, call the following version of the IndexOf() method:

Public Function IndexOf(tableName As String) As Integer

Instead of directly locating a table, you may be interested to know whether a particular table exists in the DataSet.Tables collection. To check this, you can call the DataTableCollection.Contains() method. Its syntax is:

Public Function Contains(name As String) As Boolean

This method expects the object name (not the variable name) of a table as argument. If the table exists in the collection, this method returns True. Here is an example:

Private Sub btnDocument_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnDocument.Click
        dsVideoCollection = New DataSet("Videos")

        tblDirectors = New DataTable()
        tblDirectors.TableName = "Directors"
        dsVideoCollection.Tables.Add(tblDirectors)

        tblVideoCategories = New DataTable("Categories")
        dsVideoCollection.Tables.Add(tblVideoCategories)

        tblRatings = dsVideoCollection.Tables.Add()

        tblActors = dsVideoCollection.Tables.Add("Actors")
        tblFormats = dsVideoCollection.Tables.Add("Formats")

        If dsVideoCollection.Tables.Contains("Actors") Then
            MsgBox("The Actors table exists")
        Else
            MsgBox("The Actors table does not exist")
        End If

        If dsVideoCollection.Tables.Contains("VideoTypes") Then
            MsgBox("The VideoTypes table exists")
        Else
            MsgBox("The VideoTypes table does not exist")
        End If
End Sub

This would produce:

If you happen to have a table you don't need anymore or whose role is undefined in your application, you can delete that table. This operation is supported by the DataTableCollection.Remove() method that is overloaded with two versions. To delete a table using its variable declared name, you can use the following version:

Public Sub Remove(table As DataTable)

This version expects the variable name that was used to declare the DataTable object. If the table exists in the DateSet.Tables collection, it would be deleted. Here is an example:

Private Sub btnDocument_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnDocument.Click
        dsVideoCollection = New DataSet("Videos")

        tblDirectors = New DataTable()
        tblDirectors.TableName = "Directors"
        dsVideoCollection.Tables.Add(tblDirectors)

        tblVideoCategories = New DataTable("Categories")
        dsVideoCollection.Tables.Add(tblVideoCategories)

        tblRatings = dsVideoCollection.Tables.Add()

        tblActors = dsVideoCollection.Tables.Add("Actors")
        tblFormats = dsVideoCollection.Tables.Add("Formats")

        For Each tbl As DataTable In dsVideoCollection.Tables
            lbxOriginalVideos.Items.Add(tbl.TableName)
        Next

        dsVideoCollection.Tables.Remove(tblVideoCategories)

        For Each tbl As DataTable In dsVideoCollection.Tables
            lbxNewVideos.Items.Add(tbl.TableName)
        Next
End Sub

This would produce:

To delete a table using its object name, you can use the following version of the DataTableCollection.Remove() method:

Public Sub Remove(name As String)

This method expects the formal name of the table as argument. If a table exists under that name, it would be deleted. Here is an example:

Private Sub btnDocument_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnDocument.Click
        dsVideoCollection = New DataSet("Videos")

        tblDirectors = New DataTable()
        tblDirectors.TableName = "Directors"
        dsVideoCollection.Tables.Add(tblDirectors)

        tblVideoCategories = New DataTable("Categories")
        dsVideoCollection.Tables.Add(tblVideoCategories)

        tblRatings = dsVideoCollection.Tables.Add()

        tblActors = dsVideoCollection.Tables.Add("Actors")
        tblFormats = dsVideoCollection.Tables.Add("Formats")

        For Each tbl As DataTable In dsVideoCollection.Tables
            lbxOriginalVideos.Items.Add(tbl.TableName)
        Next

        dsVideoCollection.Tables.Remove("Categories")

        For Each tbl As DataTable In dsVideoCollection.Tables
            lbxNewVideos.Items.Add(tbl.TableName)
        Next
End Sub

If no table with the name is found, the compiler would throw an ArgumentException exception. Once again, you should first check that a table with the undesired name exists before deleting it. If the table exists in the collection, it may not allow the user to delete it. To find out whether a table can be deleted, call the DataTableCollection.CanRemove() method. Its syntax is:

Public Function CanRemove(table As DataTable) As Boolean

When calling the DataTableCollection.Remove() method, if the DataTable object passed as argument is not found, the compiler would throw either an ArgumentNullException or an ArgumentException exception. Here is an example:

Private Sub btnDocument_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnDocument.Click
        dsVideoCollection = New DataSet("Videos")

        tblDirectors = New DataTable()
        tblDirectors.TableName = "Directors"
        dsVideoCollection.Tables.Add(tblDirectors)

        dsVideoCollection.Tables.Remove("Types")
End Sub

This would produce:

Therefore, before deleting a table, you should first check its existence. To do this, you can call the DataTableCollection.Contains() method. Here is an example of calling this method before deleting a table:

Private Sub btnDocument_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnDocument.Click
        dsVideoCollection = New DataSet("Videos")

        tblDirectors = New DataTable()
        tblDirectors.TableName = "Directors"
        dsVideoCollection.Tables.Add(tblDirectors)

        tblVideoCategories = New DataTable("Categories")
        dsVideoCollection.Tables.Add(tblVideoCategories)

        tblRatings = dsVideoCollection.Tables.Add()

        tblActors = dsVideoCollection.Tables.Add("Actors")
        tblFormats = dsVideoCollection.Tables.Add("Formats")

        For Each tbl As DataTable In dsVideoCollection.Tables
            lbxOriginalVideos.Items.Add(tbl.TableName)
        Next

        If dsVideoCollection.Tables.Contains("Types") Then
            dsVideoCollection.Tables.Remove("Types")
        Else
            MsgBox("The table named Types was not found in the database")
        End If

        For Each tbl As DataTable In dsVideoCollection.Tables
            lbxNewVideos.Items.Add(tbl.TableName)
        Next
End Sub

This would produce:

While deleting a table from the collection, the DataTableCollection fires a CollectionChanging event. After a table has been removed from the collection, the DataTableCollection fires a CollectionChanged event. As we saw earlier, both events are of type CollectionChangeEventArgs. While a table is being deleted or after it has been deleted, the action of this event is Remove.

To delete all tables of a DataSet object, you can call the DataTableCollection.Clear() method. Its syntax is:

Public Sub Clear

Calling this method would remove all DataTable objects of the DataSet. When clearing the collection of all its tables, the DataTableCollection fires a CollectionChanging event. After the tables have been deleted, the DataTableCollection fires a CollectionChanged event. In both cases, the action of this event is Refresh.