Home

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 Practical Learning: Introducing Columns

  1. Start Notepad and, from what we learned in the lesson on tables, type the following:
     
    using System;
    using System.Data;
    
    namespace VideoCollection
    {
    	public class Video
    	{
    		public DataSet dsVideoCollection;
    
    		public DataTable tblVideoCategories;
    		public DataTable tblDirectors;
    		public DataTable tblRatings;
    		public DataTable tblActors;
    		public DataTable tblFormats;
    		public DataTable tblVideos;
    
    		public Video()
    		{
    			dsVideoCollection = new DataSet("Videos");
    
    			tblDirectors = new DataTable();
    			tblDirectors.TableName = "Directors";
    			dsVideoCollection.Tables.Add(tblDirectors);
    		
    			tblVideoCategories = new DataTable("Categories");
    			dsVideoCollection.Tables.Add(tblVideoCategories);
    
    			tblRatings = new DataTable("Ratings");
    			dsVideoCollection.Tables.Add(tblRatings);
    
    			tblActors = dsVideoCollection.Tables.Add("Actors");
    			tblFormats = dsVideoCollection.Tables.Add("Formats");
    		}
    	}
    
    	class Exercise
    	{
    		public static int Main()
    		{
    			return 0;
    		}
    	}
    }
  2. To save the file, on the main menu, click File -> Save
  3. Locate your CSharp Lessons folder and display it in the Save In combo box
  4. Click the Create New Folder button. Type VideoCollection2 and press Enter twice to display the new folder in the Save In combo box
  5. Change the Save As Type to All Files. Change the file name to Exercise.cs and click Save

Columns Names

A column of a table is an object of type DataColumn. Therefore, 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 more details. Here is an example:

using System;
using System.Data;

namespace VdoColl
{
	class Exercise
	{
		static void Main()
		{
			DataColumn colCategoryID = new DataColumn();
		}
	}
}

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:

using System;
using System.Data;

namespace VdoColl
{
	class Exercise
	{
		private static DataColumn colCategoryID;

		static void Main()
		{
			colCategoryID = new DataColumn();
		}
	}
}

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. The name must follow the rules of variables in C#. To specify the object name of a column, when creating it, you can use the second constructor whose syntax is:

public DataColumn(string name);

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

using System;
using System.Data;

namespace VdoColl
{
	class Exercise
	{
		private static DataColumn colCategoryID;

		static void Main()
		{
			colCategoryID = new DataColumn("CategoryID");
		}
	}
}

If you have already declared a DataColumn object, to specify or change its name, assign the desired string to the DataColumn.ColumnName property. Here is an example:

using System;
using System.Data;

namespace VdoColl
{
	class Exercise
	{
		private static DataColumn colCategoryID;
		private static DataColumn colCategory;

		static void Main()
		{
			colCategoryID = new DataColumn("CategoryID");

			colCategory = new DataColumn();
			colCategory.ColumnName = "Category";
		}
	}
}

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. 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:

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;

namespace VdoColl
{
	class Exercise
	{
		private static DataColumn colCategoryID;
		private static DataColumn colCategory;
		
		private static DataTable dtDirectors;
		private static DataColumn colDirectorID;

		static void Main()
		{
			dtDirectors = new DataTable();

			colDirectorID = dtDirectors.Columns.Add();

			colCategoryID = new DataColumn("CategoryID");

			colCategory = new DataColumn();
			colCategory.ColumnName = "Category";
		}
	}
}

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;

namespace VdoColl
{
	class Exercise
	{
		private static DataColumn colCategoryID;
		private static DataColumn colCategory;
		
		private static DataTable dtDirectors;
		private static DataColumn colDirectorID;
		private static DataColumn colDirector;

		static void Main()
		{
			dtDirectors = new DataTable();

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

			colCategoryID = new DataColumn("CategoryID");

			colCategory = new DataColumn();
			colCategory.ColumnName = "Category";
		}
	}
}

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;

namespace VdoColl
{
	class Exercise
	{
		private static DataColumn colCategoryID;
		private static DataColumn colCategory;
		
		private static DataTable dtDirectors;
		private static DataColumn colDirectorID;
		private static DataColumn colDirector;

		private static DataTable dtVideos;
		private static DataColumn colVideo;

		static void Main()
		{
			dtDirectors = new DataTable();

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

			colCategoryID = new DataColumn("CategoryID");

			colCategory = new DataColumn();
			colCategory.ColumnName = "Category";

			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 VideoCollection
    {
        public class Video
        {
    	public DataSet dsVideoCollection;
    
    	public DataTable tblVideoCategories;
    	public DataColumn colCategoryID;
    	public DataColumn colCategory;
    
    	public DataTable tblDirectors;
    	public DataTable tblRatings;
    	public DataTable tblActors;
    	public DataTable tblFormats;
    	public DataTable tblVideos;
    
    
    	public Video()
    	{
    	    dsVideoCollection = new DataSet("Videos");
    
    	    tblDirectors = new DataTable();
    	    tblDirectors.TableName = "Directors";
    	    dsVideoCollection.Tables.Add(tblDirectors);
    		
    	    tblVideoCategories = new DataTable("Categories");
    	    dsVideoCollection.Tables.Add(tblVideoCategories);
    
    	    colCategoryID = new DataColumn();
    	    colCategoryID.ColumnName = "CategoryID";
    	    tblVideoCategories.Columns.Add(colCategoryID);
    
    	    colCategory = new DataColumn();
    	    colCategory.ColumnName = "Category";
    	    tblVideoCategories.Columns.Add(colCategory);
    
    	    tblRatings = new DataTable("Ratings");
    	    dsVideoCollection.Tables.Add(tblRatings);
    
    	    tblActors = dsVideoCollection.Tables.Add("Actors");
    	    tblFormats = dsVideoCollection.Tables.Add("Formats");
    	}
        }
    
        class Exercise
        {
    
    	public static int Main()
    	{
    	    Video vdo = new Video();
    
    	    return 0;
    	}
        }
    }
  2. Save the file

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;

namespace VdoColl
{
	class Exercise
	{
		private static DataSet dsVideoCollection;

		private static DataTable dtVideos;
		static DataColumn[] colVideos = new DataColumn[7];

		static void Main()
		{
			dsVideoCollection = new DataSet("Video");

			dtVideos = new DataTable("Video");
		
			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);
		}
	}
}
 

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. 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 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;

namespace VdoColl
{
	class Exercise
	{
		private static DataTable dtCategories;
		private static DataColumn colCategory;

		static void Main()
		{
			dtCategories = new DataTable("Categories");

	    colCategory   = new DataColumn("Category", System.Type.GetType("System.String"));
			dtCategories.Columns.Add(colCategory);
		}
	}
}

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;

namespace VdoColl
{
	class Exercise
	{
		private static DataTable dtCategories;
		private static DataColumn colCategoryID;
		private static DataColumn colCategory;

		static void Main()
		{
			dtCategories = new DataTable("Categories");

			colCategoryID = new DataColumn();
			colCategoryID.ColumnName = "CategoryID";
			colCategoryID.DataType = System.Type.GetType("System.Int32");
			dtCategories.Columns.Add(colCategoryID);

colCategory   = new DataColumn("Category", System.Type.GetType("System.String"));
			dtCategories.Columns.Add(colCategory);
		}
	}
}

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

 

Previous Copyright 2005 FunctionX, Inc. Next