Visual Basic .NET Databases: Introduction to Data Tables

	Databases Fundamentals: Data Tables

Introduction to Tables

Imagine you have a list of movie directors and you want to group their names into a list. Here is an example:

Rob Reiner, Jonathan Lynn, Bruce Beresford, Jonathan Demme, Adrian Lyne

This is a one-dimensional list like a simple array. While working on this list, you may decide to create a video collection and make the above items into a formal list. A typical movie provides such information as its length, its rating, the year it was released, etc. To create such a list, you would group items by categories. One category may contain the titles of the videos. Another category may contain the names of the directors, and so on.

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:

Video Title	Director	© Year	Length	Format	Rating
A Few Good Men	Rob Reiner	1992	138 Minutes	VHS	R
The Distinguished Gentleman	Jonathan Lynn		112 Minutes	DVD	R
The Lady Killers	Joel Coen & Ethan Coen		104 Minutes	DVD	R
Fatal Attraction	Adrian Lyne	1987	120 Minutes	VHS	R
Her Alibi	Bruce Beresford	1989	94 Minutes	DVD	PG-13
The Manchurian Candidate	Jonathan Demme	2004	129 Minutes	DVD	R

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. In 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 Minutes", "VH", and "R" constitute one row one record.

Practical Learning: Introducing Tables

Start Microsoft Visual Studio .NET and create a new Windows Forms Application named PeopleInMyLife2
In the Toolbox, click the Data button.
To create a DataSet, click DataSet and click the form
In the Add Dataset dialog box, click the Untyped Dataset radio button and click OK
While the DataSet control is still selected, in the Properties window, click (Name) and type dsPeople
Still in the Properties window, set the DataSetName to People

Creating a Table

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 three constructors. The default constructor allows you to create a table without giving more details, especially without formally naming it. Here is an example:

Dim dsVideoCollection As DataSet

Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        dsVideoCollection = New DataSet("Videos")
        Dim dtDirectors As DataTable = New DataTable
End Sub

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

Dim dsVideoCollection As DataSet
Dim dtDirectors As DataTable

Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        dsVideoCollection = New DataSet("Videos")
        dtDirectors = New DataTable
End Sub

The name of the DataTable variable is required when creating a table. This name will be used by you and the compiler to identify the table. In some cases, you may want the table to hold an object name that you would want to use later on as we will see with some methods. To provide a formal name to a table when creating it, you can use the second constructor of the DataTable class. Its syntax is:

Public Sub New(ByVal tableName As String)

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

Dim dsVideoCollection As DataSet
Dim dtDirectors As DataTable
Dim dtVideoCategories As DataTable

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

        dtVideoCategories = New DataTable("Categories")

        dtDirectors = New DataTable
End Sub

If you have already declared a DataTable variable using either of both constructors and decide to specify or change its name, you can assign a string to the DataTable.TableName property. Here is an example:

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

        dtVideoCategories = New DataTable("Categories")

        dtDirectors = New DataTable
        dtDirectors.TableName = "Directors"
End Sub

The TableName name is primarily optional but it is used by various methods as we will see. If you don't provide it, for example if you declare a DataTable variable using the default constructor and don't specify its name through the DataTable.TableName property, the compiler would generate a default name for the table, such as Table1, Table2, and so on.

In the same way, you can create as many tables as you judge necessary for your application and as we will see when moving on. After creating a table, you can add it to a DataSet. 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 is a class that provides everything you need to add, locate, or manage any table that belongs to a DataSet.

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

Overloads Public Overridable Function Add() As DataTable

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

Dim dsVideoCollection As DataSet
Dim dtDirectors As DataTable
Dim dtVideoCategories As DataTable
Dim dtRatings As DataTable

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

        dtVideoCategories = New DataTable("Categories")

        dtDirectors = New DataTable
        dtDirectors.TableName = "Directors"

        dtRatings = dsVideoCollection.Tables.Add()
End Sub

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:

Overloads Public Overridable Sub Add(ByVal table As DataTable)

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

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

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

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

        dtRatings = dsVideoCollection.Tables.Add()
End Sub

This second version of this method requires that you create a DataTable object first and the table probably have 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:

Overloads Public Overridable Function Add(ByVal 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 object name of the new table. Here are examples:

Dim dsVideoCollection As DataSet
Dim dtDirectors As DataTable
Dim dtVideoCategories As DataTable
Dim dtRatings As DataTable
Dim dtActors As DataTable
Dim dtFormats As DataTable

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

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

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

        dtRatings = dsVideoCollection.Tables.Add()

        dtActors = dsVideoCollection.Tables.Add("Actors")
        dtFormats = dsVideoCollection.Tables.Add("Formats")
End Sub

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 class is equipped with the AddRange() method. Its syntax is:

Public Sub AddRange(ByVal tables() As DataTable)

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

Private Sub btnTables_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTables.Click
        Dim dsBooks As DataSet = New DataSet("Books")

        Dim dtCategories As DataTable = New DataTable("Categories")
        Dim dtAuthors As DataTable = New DataTable("Authors")
        Dim dtPublishers As DataTable = New DataTable("Publishers")
        Dim dtBooks As DataTable = New DataTable("Books")

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

While it is perfectly possible to programmatically create a table, the Visual Studio .NET programming environment provides graphical means of creating a table, without guessing. This allows you to visually set the characteristics of the table and let Visual Studio .NET write the code for you. As mentioned already, the tables of a DataSet object are stored in its Tables properties. In the same way, the Properties window provides the Tables field that allows you to visually create the tables.

Practical Learning: Creating Tables

Under the form, click dsPeople if necessary. In the Properties window, click the Tables field to reveal its ellipsis button
Click the ellipsis button
In the Tables Collection Editor, click the Add button
While Table1 is selected in the Members list, in the Table1 Properties list, click (Name) and type tblPersons
Click TableName and type Persons
Click the Add button again
While Table1 is selected in the Members list, in the Table1 Properties list, click (Name) and type tblGenders
Click TableName and type Genders
Click the Add button again
While Table1 is selected in the Members list, in the Table1 Properties list, click (Name) and type tblTypesOfRelationship
Click TableName and type TypesOfRelationship
Click Close

Locating a Table

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 property that ships in two versions. To locate a table using its name, use the following version of this property:

Overloads Public Default ReadOnly Property Item(ByVal name As String) As DataTable

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

Private Sub btnTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTable.Click
        Dim tbl As DataTable = Me.dsVideoCollection.Tables("Directors")
        Text = tbl.TableName
End Sub

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.Item() property. Its syntax is:

Overloads Public Default ReadOnly Property Item(ByVal 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 btnTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTable.Click
        Dim tbl As DataTable = Me.dsVideoCollection.Tables(3)
        Text = tbl.TableName
End Sub

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 with two versions. One of the versions takes as argument the variable name of the table. The syntax of this method is:

Overloads Public Overridable Function IndexOf(ByVal table As DataTable) As Integer

Here is an example of calling this method:

Private Sub btnTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTable.Click
        Dim index As Integer = Me.dsVideoCollection.Tables.IndexOf(Me.dtFormats)
        Text = index.ToString()
End Sub

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

Overloads Public Overridable Function IndexOf(ByVal tableName As String) As Integer

When the tables of a DataSet object have been created, you can get their list as an array using the DataTableCollection.List property. This property returns an ArrayList type of list.

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(ByVal name As String) As Boolean

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

Private Sub btnTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTable.Click
        If Me.dsVideoCollection.Tables.Contains("Actors") Then
            Text = "The Actors table exists"
        Else
            Text = "Unknown Table"
        End If
End Sub

Removing Tables

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 locate a table using its variable declared name and delete it, you can use the following version:

Overloads Public Sub Remove(ByVal table As DataTable)

This version expects the 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 btnRemove_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRemove.Click
        Me.dsVideoCollection.Tables.Remove(Me.dtFormats)
End Sub

When calling this method, if the DataTable object passed as argument is not found, the compiler would throw either an ArgumentNullException or an ArgumentException exceptions. For this reason, before deleting a table, you should first check its existence by calling the DataTableCollection.Contains() method.

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

Overloads Public Sub Remove(ByVal 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. If no table with that name is found, the compiler would throw an ArgumentException exception. Once again, you should first check that a table with that name exists before deleting it.

To delete a table you created visually, in the Tables Collection Editor, click it in the Members list and click the Remove button.

Besides checking the existence of a table, even if the table is found, it may not allow the user to delete it. To find out whether a table can be deleted, you can call the DataTableCollection.CanRemove() method. Its syntax is:

Overloads Public Sub Remove(ByVal table As DataTable)

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

Public Sub Clear()

Calling this method would remove all DataTable objects of the DataSet object.


Previous	Copyright © 2005-2016, FunctionX	Next