Visual Basic .NET Databases: Introduction to Data Columns

	Databases Fundamentals: Data Columns

Introduction to Columns

We have defined a column as a technique categorizing some values that belong to a table. Based on this, one category from a list of a video application can be made of video titles. Another category can contain the years the videos were released. Yet another category can contain a number used to identify the format of the video. Here is an example:

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

The string on top of each column allows the user to identify what that column is used for. That string is called the column header. The columns of a table are stored in a property called Columns of the DataTable class. The Columns property is an object of type DataColumnCollection. The DataColumnCollection class provides everything that is necessary to create and manage any column. The DataColumnCollection class is in fact a collection of objects.

Practical Learning: Introducing Columns

Start Microsoft Visual Studio .NET and create a new Windows Forms Application named PeopleInMyLife3
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
As done for the lesson on 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 and change its properties as follows:
(Name): tblPersons
TableName: Persons
Click the Add button again and set the properties as follows:
(Name): tblGenders
TableName: Genders
Click the Add button again and set the properties as follows:
(Name): tblTypesOfRelationship
TableName: TypesOfRelationship
Click Close

Columns Names

A column of a table is an object of type DataColumn. Therefore, to create a column, you can first declare a DataColumn variable. The DataColumn class is equipped with five constructors. The default constructor allows you to create a column without providing more details. Here is an example:

Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        Dim colCategoryID As DataColumn = New DataColumn
End Sub

If you are planning to reference a column from more than one method or event, you should declare it globally, in the class of the form. Here is an example:

Dim colCategoryID As DataColumn

Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        colCategoryID = New DataColumn
End Sub

To distinguish them, each column must have a specific and unique name. The name of a column allows you and the compiler to identify a particular column. To specify the object name of a column, when creating it, you can use the second constructor whose syntax is:

Public Sub New(ByVal columnName As String)

This constructor expects as argument the name of the column. Here is an example:

Dim colCategoryID As DataColumn

Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        colCategoryID = New DataColumn("CategoryID")
End Sub

If you have already declared a DataColumn object, to specify or change its name, assign the desired string to the DataColumn.ColumnName property. Based on these descriptions, the minimum information needed to create a column is a name. If you don't specify a name, a default name is assigned to the new column.

Adding a Column to a Table

To make a column part of a table, you must add it to the table's collection of columns. As mentioned earlier, the columns of a table are grouped into the DataTable.Columns property, which is based on the DataColumnCollection class. This class is equipped with an Add() method that allows you to add a column to the table.

The DataColumnCollection.Add() method is overloaded with 3 versions. The first version uses the following syntax:

Overloads Public Overridable Function Add() As DataColumn

When called, this would create a new column and return it. The compiler would assign a default name to the column. If this is the first column, it would be named Column1. If it is the second column, it would be named Column2, and so on. You can still specify or change the name of a column created with the above version. To do this, assign the desired string to the DataColumn.ColumnName. Here is an example:

Dim dsVideoCollection As DataSet
Dim dtDirectors As DataTable
Dim dtVideoCategories As DataTable
Dim dtRatings As DataTable
Dim dtActors As DataTable
Dim dtFormats As DataTable
Dim colCategoryID As DataColumn
Dim colDirector As DataColumn

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)

        colDirector = dtDirectors.Columns.Add()
        colDirector.ColumnName = "Director"

        dtRatings = dsVideoCollection.Tables.Add()

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

        colCategoryID = New DataColumn("CategoryID")
End Sub

If you want to specify the name of the new column when calling the DataColumnCollection.Add() method, you can use the following version:

Overloads Public Overridable Function Add(ByVal columnName As String) As DataColumn

This method takes as argument the name of the new column and returns that new column. Here is an example:

Dim dsVideoCollection As DataSet
Dim dtDirectors As DataTable
Dim dtVideoCategories As DataTable
Dim dtRatings As DataTable
Dim dtActors As DataTable
Dim dtFormats As DataTable
Dim colCategoryID As DataColumn
Dim colDirector As DataColumn
Dim colDirectorID As DataColumn

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)

        colDirectorID = dtDirectors.Columns.Add("DirectorID")
        colDirector = dtDirectors.Columns.Add()
        colDirector.ColumnName = "Director"

        dtRatings = dsVideoCollection.Tables.Add()

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

        colCategoryID = New DataColumn("CategoryID")
End Sub

If you have already formally created a DataColumn object, to add it to the collection of columns of a table, you can call the following version of the DataColumnCollection.Add() method:

Overloads Public Sub Add(ByVal column As DataColumn)

This method expects a DataColumn object as argument. You can either primarily create a DataColumn value or you can define one in the parentheses of the method. Here are two 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
Dim colCategoryID As DataColumn
Dim colDirector As DataColumn
Dim colDirectorID As DataColumn
Dim colVideoTitle As DataColumn
Dim dtVideos 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)

        colDirectorID = dtDirectors.Columns.Add("DirectorID")
        colDirector = dtDirectors.Columns.Add()
        colDirector.ColumnName = "Director"

        dtVideos = New DataTable("ListOfVideos")
        colVideoTitle = New DataColumn("Title")
        dtVideos.Columns.Add(colVideoTitle)
        dtVideos.Columns.Add(New DataColumn("Director"))

        dtRatings = dsVideoCollection.Tables.Add()

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

        colCategoryID = New DataColumn("CategoryID")
End Sub

An Array of Columns

Instead of adding one column (at a time) to a table, you can first create an array of columns and add that array to the collection of columns. To do this, you can call the DataColumnCollection.AddRange() method. Its syntax is:

Public Sub AddRange(ByVal columns() As DataColumn)

This method takes as argument an array of predefined columns. 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")

        dtVideos = New DataTable("ListOfVideos")

        Dim colVideos(7) As DataColumn
        colVideos(0) = New DataColumn("Title")
        colVideos(1) = New DataColumn("Director")
        colVideos(2) = New DataColumn("YearReleased")
        colVideos(3) = New DataColumn("Length")
        colVideos(4) = New DataColumn("Rating")
        colVideos(5) = New DataColumn("Format")
        colVideos(6) = New DataColumn("Category")

        dtVideos.Columns.AddRange(colVideos)
        dsVideoCollection.Tables.Add(dtVideos)
End Sub

Practical Learning: Creating Columns

Under the form, click dsPeople if necessary. In the Properties window, click the Tables field to reveal its ellipsis button and click the ellipsis button
In the Members list, click Genders if necessary. In the Genders Properties list, click the Columns field to reveal its ellipsis button and click its ellipsis button
In the Columns Collection Editor, click the Add button
While Column1 is still selected in the Members list, in the Column1 Properties list, click (Name) and type colGender
Click ColumnName and type Gender
Click Close

In the same way, create the following columns for the corresponding tables

Table	Column (Name)	ColumnName
TypesOfRelationship	colTypeOfRelationship	TypeOfRelationship
Persons	colFullName	FullName
Persons	colFriendlyName	FriendlyName
Persons	colRelationshipType	TypeOfRelationship
Persons	colKnownSince	KnownSince
Persons	colCurrentResidence	CurrentResidence
Persons	colPhoneNumber	PhoneNumber
Persons	colLastUpdate	LastUpdate
Persons	colNotifyIfEmergency	NotifyIfEmergency

Click Close twice

Columns Data Types

If you create an object, such as a form, that allows the user to enter records for the above list of videos, you can reduce the likelihood of mistakes. For example, you can prevent the table from allowing the user to enter a director name in a box reserved for the year released. To exercise this type of control, the DataColumn class allows you to specify an appropriate or desired data type for each column. Just as done in C# regular applications, the data types of a table allow its columns to accept or reject inappropriate values. Although we saw that the name was the most important aspect of a column, in reality, a data type is also required. Fortunately, if you don't specify the data type of a column, it is assumed to be a string and the string data type is automatically assigned to it.

To specify the data type of a column, you have two main alternatives. When declaring a column, to specify its data type, you can initialize the DataColumn variable using the third constructor. Its syntax is:

Public Sub New(ByVal columnName As String, ByVal dataType As Type)

To specify a column's data type, select one from the Type class of the System namespace by calling the Type.GetType() method. The GetType() method is overloaded with three versions. The first version has the following syntax:

Overloads Public Shared Function GetType(ByVal typeName As String) As Type

This method expects as argument a valid data type defined in the .NET Framework. The data type must be retrieved from the Type class of the System namespace. The name of the data type must be qualified with a period operator. Here is an example:

Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        Dim colCategory As DataColumn = New DataColumn("Category", System.Type.GetType("System.String"))
End Sub

If you used the default constructor to declare a DataColumn, to specify its data type, assign its qualified type to the DataColumn.DataType property. Here is an example:

Dim dsVideoCollection As DataSet
Dim dtDirectors As DataTable
Dim dtVideoCategories As DataTable
Dim dtRatings As DataTable
Dim dtActors As DataTable
Dim dtFormats As DataTable
Dim colCategoryID As DataColumn
Dim colDirector As DataColumn
Dim colDirectorID As DataColumn
Dim colVideoTitle As DataColumn
Dim dtVideos As DataTable

Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        dtDirectors = New DataTable
        colDirector = dtDirectors.Columns.Add()
        colDirector.ColumnName = "Director"
        colDirector.DataType = System.Type.GetType("System.String")
End Sub

Remember that there are various techniques you can use to create a column by specifying its name and its data type. Here are various examples from what we have used so far:

Dim dsVideoCollection As DataSet
Dim dtDirectors As DataTable
Dim dtVideoCategories As DataTable
Dim dtRatings As DataTable
Dim dtActors As DataTable
Dim dtFormats As DataTable
Dim colCategoryID As DataColumn
Dim colDirector As DataColumn
Dim colDirectorID As DataColumn
Dim colVideoTitle As DataColumn
Dim colActor As DataColumn
Dim dtVideos 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"
        colDirector = dtDirectors.Columns.Add()
        colDirector.ColumnName = "Director"
        colDirector.DataType = System.Type.GetType("System.String")
        dsVideoCollection.Tables.Add(dtDirectors)

        colDirectorID = dtDirectors.Columns.Add("DirectorID")

        dtVideos = New DataTable("ListOfVideos")

        dtRatings = dsVideoCollection.Tables.Add()

        dtActors = dsVideoCollection.Tables.Add("Actors")
        colActor = New DataColumn("Actor", System.Type.GetType("System.String"))
        dtActors.Columns.Add(colActor)

        dtFormats = dsVideoCollection.Tables.Add("Formats")

        colCategoryID = New DataColumn("CategoryID")

        Dim colVideos(7) As DataColumn
        colVideos(0) = New DataColumn("Title")
        colVideos(0).DataType = System.Type.GetType("System.String")
        colVideos(1) = New DataColumn("Director")
        colVideos(1).DataType = System.Type.GetType("System.String")
        colVideos(2) = New DataColumn("YearReleased", System.Type.GetType("System.Int16"))
        colVideos(3) = New DataColumn("Length", System.Type.GetType("System.String"))
        colVideos(4) = New DataColumn("Rating", System.Type.GetType("System.String"))
        colVideos(5) = New DataColumn("Format", System.Type.GetType("System.String"))
        colVideos(6) = New DataColumn("Category", System.Type.GetType("System.String"))

        dtVideos.Columns.AddRange(colVideos)
        dsVideoCollection.Tables.Add(dtVideos)
End Sub

Practical Learning: Creating Columns

Under the Data Center form, click dsPeople if necessary. In the Properties window, click the Tables field to reveal its ellipsis button and click the ellipsis button
In the Members list, click Persons if necessary. In the Persons Properties list, click the Columns field to reveal its ellipsis button and click its ellipsis button
In the Members list of the Columns Collection Editor, click LastUpdate
In the LastUpdate Properties list, click DataType to display the arrow of its combo box and select System.DateTime
In the Members list, click NotifyIfEmergency and, in the NotifyIfEmergency Properties list, click DataType and set it to System.Boolean
Click Close twice


Previous	Copyright © 2005-2016, FunctionX	Next