Home

The Columns of a Table

 

Fundamentals of Columns of a Table

 

Introduction

A column is a technique of 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 of a table:

 
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

A category of information of a table is called a column. 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.

To support the columns of a table, the .NET Framework provides the DataColumn class.

Creating a Column

To create a column, you can first declare a variable of type DataColumn. The DataColumn class is equipped with five constructors. The default constructor allows you to create a column without giving details. Here is an example:

Public Class Exercise
    Private colCategoryID As DataColumn

    Private Sub btnDocument_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnDocument.Click
        colCategoryID = new DataColumn
    End Sub
End Class

To visually create a column, after displaying the Tables Collection Editor, under Members, click the name of the table. Then under Properties, click Columns and click its ellipsis button. This would open the Columns Collection Editor. To create a new column, click Add:

Columns Collection Editor

After clicking Add, the studio would declare a variable for the new column and a default variable name would be given to it. To change the name of the variable, under Properties, click (Name) and type the desired name.

The Name of a Column

In a database, as reviewed for a table, a column must have an object name. To distinguish them, each column must have a specific and unique object name. The object name of a column allows you to identify the column as a database object. The object name does not follow the rules of variables. For example, a column's object name can be FullName, Date of Birth, First Name + Last Name, CD w/+ H2, $alary, or P@Hip!. As you can see, you can give any insane object name to a column. To make it easily identifiable, here are the rules and suggestions we will follow to name our columns:

  • The name of a column will start with a letter or an underscore
  • The name of a column will be made of letters, digits, underscores (only one at a time), and spaces
  • The name of a column will not include special characters such as !, @, #, $, %, ^, &, or *
  • If the name is a combination of words, each word will start in uppercase

To specify the object name of a column, when creating it, you can use the second constructor of the DataColumn class. Its syntax is:

Public Sub New(columnName As String)

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

Public Class Exercise
    Private colCategoryID As DataColumn

    Private Sub btnDocument_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnDocument.Click
        colCategoryID = New DataColumn("CategoryID")
    End Sub
End Class

To support the name of a column, the DataColumn class is equipped with a property named ColumnName. This property is of type string. If you have already declared a DataColumn variable, to specify or change its name, assign the desired string to the DataColumn.ColumnName property. Here is an example:

Public Class Exercise
    Private colCategoryID As DataColumn
    Private colCategory As DataColumn

    Private Sub btnDocument_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnDocument.Click
        colCategoryID = New DataColumn("CategoryID")
        colCategory = New DataColumn
        colCategory.ColumnName = "Category"
    End Sub
End Class

Based on these descriptions, the minimum information needed to create a column is a name. If you do not specify a name, a default name is assigned to the new column.

If you are visually creating your columns, after clicking Add, a default name would be given to it. To specify or change the name of the column, under Members, click the column. Under Properties, click ColumnName and type the desired name.

Operations on the Columns of a Table

 

Introduction

To support, or to hold, the columns of a table, the DataTable class is equipped with a property named Columns. The Columns property is an object of type DataColumnCollection. Like DataTableCollection, the DataColumnCollection class is derived from the InternalDataCollectionBase class. The DataColumnCollection class provides everything that is necessary to create and manage the columns of a table.

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. The DataColumnCollection class is equipped with a method named Add that allows you to add a column to the table. The DataColumnCollection.Add() method is overloaded with 5 versions. One of the versions uses the following syntax:

Public Function Add As DataColumn

When called, this method adds a new column and returns 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 of the Add() method. To do this, assign the desired string to the DataColumn.ColumnName. Here is an example:

Public Class Exercise
    Private colDirectorID As DataColumn
    Private dtDirectors As DataTable

    Private Sub btnDocument_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnDocument.Click
        dtDirectors = New DataTable
        colDirectorID = dtDirectors.Columns.Add()
    End Sub
End Class

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

Public Function Add ( _
	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:

Public Class Exercise
    Private colDirectorID As DataColumn
    Private colDirector As DataColumn
    Private dtDirectors As DataTable

    Private Sub btnDocument_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnDocument.Click
        dtDirectors = New DataTable
        colDirectorID = dtDirectors.Columns.Add()

        colDirector = dtDirectors.Columns.Add("Director")
    End Sub
End Class

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

Public Sub Add(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:

Public Class Exercise
    Private colDirectorID As DataColumn
    Private colDirector As DataColumn
    Private colDateOfBirth As DataColumn

    Private dtDirectors As DataTable

    Private Sub btnDocument_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnDocument.Click
        colDateOfBirth = New DataColumn("Date of Birth")

        dtDirectors = New DataTable
        colDirectorID = dtDirectors.Columns.Add()

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

        dtDirectors.Columns.Add(colDateOfBirth)
    End Sub
End Class

Adding 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(columns As DataColumn())

This method takes as argument an array of predefined columns. Here is an example:

Public Class Exercise
    Private colDirectorID As DataColumn
    Private colDirector As DataColumn
    Private colDateOfBirth As DataColumn
    Private dtDirectors As DataTable

    Private colVideos(7) As DataColumn
    Private dtVideos As DataTable

    Private Sub btnDocument_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnDocument.Click
        colDateOfBirth = New DataColumn("Date of Birth")

        dtDirectors = New DataTable
        colDirectorID = dtDirectors.Columns.Add()

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

        dtDirectors.Columns.Add(colDateOfBirth)

        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 = New DataTable("Videos")
        dtVideos.Columns.AddRange(colVideos)
    End Sub
End Class

Columns Maintenance

 

Introduction

Column maintenance consists of adding one or more columns to a table, identifying an existing column in a table, looking for a column in a table, deleting one column or deleting all columns of a table. All these operations are easily supported by various classes of the System.Data namespace.

The Parent Table of a Column

You are probably now familiar with the relationships among the data set, the table and the columns. Just in case:

  1. A table belongs to a data set and not the contrary
  2. You can create and use a data set without creating a table
  3. A column must belong to a table. A table without at least one column is no table at all. It is useless

When using the information stored in a table as we will learn when studying records, sometimes you will need to identify the table that owns a particular column you are accessing. This information can be provided by the Table property of the DataColumn class.

Identifying a Column

Once again, remember that the group of columns of a table is an object of type DataColumnCollection. To access a column, the DataColumnCollection class is equipped with an indexed property (named Item). Here is an example of using it:

Public Class Exercise
    Private dtVideos As DataTable
    Private colVideos(7) As DataColumn
    Private dsVideoCollection As DataSet

    Private Sub btnDocument_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnDocument.Click
        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 = New DataTable("Videos")
        dtVideos.Columns.AddRange(colVideos)

        dsVideoCollection = New DataSet("VideoCollection")
        dsVideoCollection.Tables.Add(dtVideos)

        For i As Integer = 0 To dtVideos.Columns.Count - 1
            lbxVideos.Items.Add(dtVideos.Columns(i).ColumnName)
        Next
    End Sub
End Class

Videos

The DataColumnCollection class implements the GetEnumerator() method of the IEnumerable interface. This allows you to enumerate the columns of a table using For Each. Here is an example of using it:

Public Class Exercise
    Private dtVideos As DataTable
    Private colVideos(7) As DataColumn
    Private dsVideoCollection As DataSet

    Private Sub btnDocument_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnDocument.Click
        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 = New DataTable("Videos")
        dtVideos.Columns.AddRange(colVideos)

        dsVideoCollection = New DataSet("VideoCollection")
        dsVideoCollection.Tables.Add(dtVideos)

        For Each colVideo As DataColumn In dtVideos.Columns
            lbxVideos.Items.Add(colVideo.ColumnName)
        Next
    End Sub
End Class

Checking the Existence of a Column

To check whether a table contains a certain column, you can call the Contains() method of the DataColumnCollection class. Its syntax is:

Public Function Contains(name As String) As Boolean

This method takes as argument the object name of a column. When the method is called, the compiler would look for that column in the table. If the table contains that column, the method returns True. Otherwise it returns False.

Deleting Columns

 

Deleting a Column by Name

If you happen to have an undesired column in a table, you can delete it. To perform this operation, the DataColumnCollection class provides the Remove() method. This method is overloaded in two versions. One of them uses the following syntax:

Public Sub Remove(name As String)

This method expects the object name of a column as argument. If the table has that column, the column would be deleted. Here is an example:

Public Class Exercise
    Private colTitle As DataColumn
    Private colDirector As DataColumn
    Private colLength As DataColumn
    Private colFormat As DataColumn
    Private colRating As DataColumn
    Private tblVideos As DataTable

    Private Sub btnDocument_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnDocument.Click
        tblVideos = New DataTable("Video")

        colTitle = New DataColumn("Title")
        tblVideos.Columns.Add(colTitle)

        colDirector = New DataColumn("Director")
        tblVideos.Columns.Add(colDirector)

        colLength = New DataColumn("Length")
        tblVideos.Columns.Add(colLength)

        colFormat = New DataColumn("Format")
        tblVideos.Columns.Add(colFormat)

        colRating = New DataColumn("Rating")
        tblVideos.Columns.Add(colRating)

        lbxVideos.Items.Clear()
        lbxNewVideos.Items.Clear()

        For Each col As DataColumn In tblVideos.Columns
            lbxVideos.Items.Add(col.ColumnName)
        Next

        tblVideos.Columns.Remove("Format")

        For Each col As DataColumn In tblVideos.Columns
            lbxNewVideos.Items.Add(col.ColumnName)
        Next
    End Sub
End Class

Videos

If there is no column with the object name passed as argument to the DataColumnCollection.Remove() method, the compiler would throw an ArgumentException exception. Here is an example of calling it:

Private Sub btnDocument_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnDocument.Click
        tblVideos = New DataTable("Video")

        lbxVideos.Items.Clear()
        lbxNewVideos.Items.Clear()

        For Each col As DataColumn In tblVideos.Columns
            lbxVideos.Items.Add(col.ColumnName)
        Next

        tblVideos.Columns.Remove("Category")

        For Each col As DataColumn In tblVideos.Columns
            lbxNewVideos.Items.Add(col.ColumnName)
        Next
End Sub

This would produce:

To remove a column using its object name, you can call the following version of the Remove method:

Public Sub Remove(column As DataColumn)

As this can only be undesirable, the DataColumnCollection class allows you to check the existence of the column on the table. This is done by calling the Contains() method.

Even if the table contains that column, it may not allow the column to be be deleted. For this reason, you should first check that the table allows that the column be deleted. To assist you with checking this, the DataColumnCollection class is equipped with the CanRemove() method. Its syntax is:

Public Function CanRemove(column As DataColumn) As Boolean

Deleting a Column by Index

The columns of a table are arranged in an indexed list with the first (the most left) column at index 0, the second (from left) at index 1, and so on. To delete a column based on its index, you can call the DataColumnCollection.RemoveAt() method. Its syntax is:

Public Sub RemoveAt(index As Integer)

The index of the column is passed to this method. When calling this method, make sure you pass a valid index that is an integer greater than or equal to 0 but less than the DataColumnCollection.Count - 1. If you pass a negative index or a number >= DataColumnCollection.Count, the compiler would throw an IndexOutOfRangeException exception.

If you know the object name of the column, you can first get its index by calling the DataColumnCollection.IndexOf() method and then pass its returned value to the RemoveAt() method.

Clearing the Table of Columns

To delete all columns from a table, you can call the DataColumnCollection.Clear() method. Its syntax is:

Public Sub Clear

After this method has been called, all columns from the table are deleted.

 

Previous Copyright © 2008-2012 FunctionX Next