Home

Operations on the Columns of a Table

 

Introduction

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.

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 called Add() that allows you to add a column to the table.

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

public virtual DataColumn Add();

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:

using System;
using System.Data;

public class VideoCollection
{
    private DataColumn colCategoryID;
    private DataColumn colCategory;
    private DataColumn colDirectorID;

    private DataTable dtDirectors;

    public VideoCollection()
    {
        colCategoryID = new DataColumn("CategoryID");
        colCategory = new DataColumn();
        colCategory.ColumnName = "Category";

	dtDirectors = new DataTable();
        colDirectorID = dtDirectors.Columns.Add();
    }
}

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

public virtual DataColumn Add(string name);

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

using System;
using System.Data;

public class VideoCollection
{
    private DataColumn colCategoryID;
    private DataColumn colCategory;

    private DataColumn colDirectorID;
    private DataColumn colDirector;

    private DataTable dtDirectors;

    public VideoCollection()
    {
        colCategoryID = new DataColumn("CategoryID");
        colCategory   = new DataColumn();
        colCategory.ColumnName = "Category";

        dtDirectors   = new DataTable();
        colDirectorID = dtDirectors.Columns.Add();
        colDirector   = dtDirectors.Columns.Add("Director");
    }
}

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 void Add(DataColumn column);

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:

using System;
using System.Data;

public class VideoCollection
{
    private DataColumn colCategoryID;
    private DataColumn colCategory;

    private DataColumn colDirectorID;
    private DataColumn colDirector;

    private DataTable dtDirectors;

    private DataTable dtVideos;
    private DataColumn colVideo;

    public VideoCollection()
    {
        colCategoryID = new DataColumn("CategoryID");
        colCategory = new DataColumn();
        colCategory.ColumnName = "Category";

        dtDirectors = new DataTable();
        colDirectorID = dtDirectors.Columns.Add();
        colDirector = dtDirectors.Columns.Add("Director");

        dtVideos = new DataTable("Videos");
        colVideo = new DataColumn("Video");
        dtVideos.Columns.Add(colVideo);
    }
}

Practical Learning Practical Learning: Creating Columns

  1. To create a few columns, change the file as follows:
     
    using System;
    using System.Data;
    
    namespace VideoCollection2
    {
        public class Video
        {
            private DataSet dsVideoCollection;
    
            private DataColumn colActorName;
            private DataColumn colDateOfBirth;
            private DataTable  tblActors;
    
            private DataTable tblVideos;
    
            public Video()
            {
                dsVideoCollection = new DataSet("VideoCollection");
    
                tblActors = new DataTable("Actors");
    
                colActorName = new DataColumn();
                colActorName.ColumnName = "Actor Name";
                tblActors.Columns.Add(colActorName);
    
                colDateOfBirth = new DataColumn("Date of Birth");
                tblActors.Columns.Add(colDateOfBirth);
    
                dsVideoCollection.Tables.Add(tblActors);
    
                tblVideos = new DataTable("Videos");
                dsVideoCollection.Tables.Add(tblVideos);
            }
    
            public void ShowTables()
            {
                int i = 1;
    
                Console.WriteLine("Video Collection - Tables");
                foreach (DataTable tbl in dsVideoCollection.Tables)
                    Console.WriteLine("{0}. {1}", i++, tbl.TableName);
            }
        }
    }
  2. Save the file

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 void AddRange(DataColumn[] columns);

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

using System;
using System.Data;

public class VideoCollection
{
    private DataTable dtVideos;
    private DataColumn[] colVideos;
    private DataSet dsVideoCollection;

    public VideoCollection()
    {
        dsVideoCollection = new DataSet("Video");
        dtVideos = new DataTable("Video");

        colVideos = new DataColumn[7];

        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);
    }
}

Specifying the Type of Data of a Column

If you create an application that allows the user to enter some values for the above list of videos, you would wish the user enter the right type of data under each column. To assist you with, the DataColumn class allows you to specify an appropriate or desired data type for each column. Just as done in other 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.

To supports data types for a column, the DataColumn class relies on the following .NET Framework structures we have used in previous lessons: Boolean, Byte , Char, DateTime, Decimal, Double, Int16, Int32, Int64, SByte, Single, String, TimeSpan, UInt16, UInt32, and UInt64. The DataColumn class can also support an array of Byte values, as in Byte[], for a column.

When creating a new column, if you don't specify its data type, it is assumed to be a string and the string data type is automatically applied 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 of the class. Its syntax is:

public DataColumn(string columnName, Type dataType);

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:

public static Type GetType(string typeName);

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:

using System;
using System.Data;

public class VideoCollection
{
    private DataColumn colCategoryID;

    public VideoCollection()
    {
        colCategoryID = new DataColumn("CategoryID", Type.GetType("System.Int32"));
    }
}

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:

using System;
using System.Data;

public class VideoCollection
{
    private DataColumn colCategoryID;
    private DataColumn colCategory;

    public VideoCollection()
    {
        colCategoryID = new DataColumn("CategoryID", Type.GetType("System.Int32"));

        colCategory = new DataColumn();
        colCategory.ColumnName = "Category";
        colCategory.DataType = Type.GetType("System.String");
    }
}

Remember that there are various techniques you can use to create a column by specifying its name and its data type.

Practical Learning Practical Learning: Specifying the Data Types of Columns Columns

  1. To create columns with data types and specify the data types of existing columns, change the Video.cs file as follows:
     
    using System;
    using System.Data;
    
    namespace VideoCollection2
    {
        public class Video
        {
            private DataSet dsVideoCollection;
    
            private DataColumn colActorName;
            private DataColumn colDateOfBirth;
            private DataTable tblActors;
    
            private DataColumn colTitle;
            private DataColumn colDirector;
            private DataColumn colLength;
            private DataColumn colFormat;
            private DataColumn colRating;
            private DataTable tblVideos;
    
            public Video()
            {
                dsVideoCollection = new DataSet("VideoCollection");
    
                tblActors = new DataTable("Actors");
    
                colActorName = new DataColumn();
                colActorName.ColumnName = "Actor Name";
                colActorName.DataType   = Type.GetType("System.String");
                tblActors.Columns.Add(colActorName);
    
                colDateOfBirth = new DataColumn("Date of Birth",
                                                Type.GetType("System.DateTime"));
                tblActors.Columns.Add(colDateOfBirth);
    
                dsVideoCollection.Tables.Add(tblActors);
    
                tblVideos = new DataTable("Videos");
    
                colTitle = new DataColumn("Title", Type.GetType("System.String"));
                tblVideos.Columns.Add(colTitle);
    
                colDirector = new DataColumn("Director",
                                             Type.GetType("System.String"));
                tblVideos.Columns.Add(colDirector);
    
                colLength = new DataColumn("Length",
                                           Type.GetType("System.String"));
                tblVideos.Columns.Add(colLength);
    
                colFormat = new DataColumn("Format",
                                           Type.GetType("System.Int16"));
                tblVideos.Columns.Add(colFormat);
    
                colRating = new DataColumn("Rating",
                                           Type.GetType("System.Byte"));
                tblVideos.Columns.Add(colRating);
    
                dsVideoCollection.Tables.Add(tblVideos);
            }
    
            public void ShowTables()
            {
                int i = 1;
    
                Console.WriteLine("Video Collection - Tables");
                foreach (DataTable tbl in dsVideoCollection.Tables)
                    Console.WriteLine("{0}. {1}", i++, tbl.TableName);
            }
        }
    }
  2. Save the file
 

Previous Copyright © 2006 FunctionX, Inc. Next