A Set of Data, or a Data Set

Introduction

A database is a list of items. The nature of the items is not particularly important because any list of any type of items constitutes a database. The idea of considering it a database is for better organization and management. This means that, traditionally, the word database suggests that the list must be formally created in human memory, on a piece of paper, or on a computer file, etc.

An item that is part of a list is called datum, with the plural being data, but data can also be used for singular. The group of items, or data, that makes up a list is referred to as a set of data.

A Namespace for Data

To support data sets, the .NET Framework provides a namespace named System.Data. This namespace contains all types of classes and enumerations that can be used to create and manage a complete database. The classes can further be used by other database objects and techniques.

Creating a Data Set

To support the creation and management of a set of data, the System.Data namespace contains a class named DataSet. Therefore, to start a data set, declare a variable of type DataSet. To initialize a DataSet variable, the class is equipped with four constructors, the first of which is the default, meaning it doesn't take any argument. The DataSet default constructor allows you to declare a variable without providing further information, just to let the compiler know that you are going to create or need a list of items. Here is an example of starting a data set in a class:

using System.Data;
using System.Web.Mvc;

namespace Exercises.Controllers
{
    public class VideoCollectionController : Controller
    {
        // GET: VideoCollection
        public ActionResult Index()
        {
            DataSet dsVideoCollection = new DataSet();

            return View();
        }
    }
}

If you are planning to use a DataSet object from more than one method or event, you can declare it globally, that is, in the class of a form. Here is an example:

using System.Data;
using System.Web.Mvc;

namespace Exercises.Controllers
{
    public class VideoCollectionController : Controller
    {
        private DataSet dsVideoCollection;

        // GET: VideoCollection
        public ActionResult Index()
        {
            dsVideoCollection = new DataSet();

            return View();
        }
    }
}

If you are creating the data set in a webpage, qualify the name of the class. Here is an example:

@{ 
    System.Data.DataSet dsVideoCollection = new System.Data.DataSet();
}

The Name of a Data Set

A data set must have a name. If you create a data set using the DataSet's default constructor, the compiler would give it a default name. To support the data set's object name, the DataSet class is equipped with a property named DataSetName.

If you create a data set using the class's default constructor, to give it a name, you can assign a string to the DataSetName property. To specify a data set's name when creating it, you can use the following constructor of the DataSet class:

public DataSet(string dataSetName);

This constructor takes as argument the formal name of the set. The name can be anything but you should follow the scheme used for the names of HTML tags. Here is an example:

using System.Data;
using System.Web.Mvc;

namespace Exercises.Controllers
{
    public class VideoCollectionController : Controller
    {
        DataSet dsVideoCollection;

        // GET: VideoCollection
        public ActionResult Index()
        {
            dsVideoCollection = new DataSet("videos");

            return View();
        }
    }
}

Of course, you can also name the data set if you are creating it in a webpage. Here is an example:

@{ 
    System.Data.DataSet dsVideoCollection = new System.Data.DataSet("videos");
}

Introduction to the Tables of a Data Set

Introduction to Tables

Imagine you have a list of movie directors and you want to put 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. You may want to add categories, ratings, years of released, etc, to the list. Here is an example:

This type of list is called a table. A table is a two-dimensional list that contains one or different categories of items. Each category is represented with a particular value. A category of values is called a column. Under 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 or record.

The Tables of a Data Set

In our introduction, we defined a data set as one or more lists considered in a single group. Reversely, one or more lists grouped in a single entity is called a data set. In such a scenario, each list is created as a table, made of categories of values. This means that a data set is one or more tables used in one database.

Creating a Table

To support the creation and management of a table, the System.Data namespace provides a class named DataTable. 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 four constructors. The default constructor allows you to create a table without giving much detail, especially without formally naming it. Here is an example:

using System.Data;
using System.Web.Mvc;

namespace Exercises.Controllers
{
    public class VideoCollectionController : Controller
    {
        DataSet dsVideoCollection;

        // GET: VideoCollection
        public ActionResult Index()
        {
            dsVideoCollection = new DataSet("videos");

            DataTable tblDirectors = new DataTable();

            return View();
        }
    }
}

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

using System.Data;
using System.Web.Mvc;

namespace Exercises.Controllers
{
    public class VideoCollectionController : Controller
    {
        DataSet dsVideoCollection;
        DataTable tblDirectors;

        // GET: VideoCollection
        public ActionResult Index()
        {
            dsVideoCollection = new DataSet("videos");

            tblDirectors = new DataTable();

            return View();
        }
    }
}

When creating a table, you must name it; that is, you must give it an object name. To support the name of a table, the DataTable class is equipped with a property named TableName. To specify the name of a table, you can assign a string to it. In the real world, most people (including me) name a table in plural. Here is an example:

@{
    System.Data.DataTable tblDirectors = new System.Data.DataTable();
    tblDirectors.TableName = "Directors";
}

Particularly in a data set, that is, in a database based on the DataSet class, a table should be named in lowercase. Here is an example:

@{
    System.Data.DataTable tblDirectors = new System.Data.DataTable();
    tblDirectors.TableName = "director";
}

To specify the name of a table when creating it, you can use the following constructor of the DataTable class:

public DataTable(string tableName);

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

using System.Data;
using System.Web.Mvc;

namespace Exercises.Controllers
{
    public class VideoCollectionController : Controller
    {
        DataSet dsVideoCollection;
        DataTable tblDirectors;
        DataTable tblVideoCategories;

        // GET: VideoCollection
        public ActionResult Index()
        {
            dsVideoCollection = new DataSet("videos");

            tblDirectors = new DataTable();

            tblVideoCategories = new DataTable("category");

            return View();
        }
    }
}

Creating Tables in a Collection

Introduction

The tables that belong to a DataSet object are stored in a property called Tables. The DataSet.Tables property is an object of a collection class named DataTableCollection. The DataTableCollection class is derived from a class named InternalDataCollectionBase. DataTableCollection is a class that provides everything you need to add, locate, or manage any table that belongs to a DataSet object.

Adding a New Table to a Collection

After creating a table, you can add it to a DataSet object. Using the DataSet.Tables property, to let you add a created table to a DataSet object, the DataTableCollection class is equipped with the classic Add() method that is overloaded. The first version of this method has the following syntax:

public virtual DataTable Add();

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

using System.Data;
using System.Web.Mvc;

namespace Exercises.Controllers
{
    public class VideoCollectionController : Controller
    {
        DataSet dsVideoCollection;
        DataTable tblDirectors;
        DataTable tblVideoCategories;

        DataTable tblRatings;

        // GET: VideoCollection
        public ActionResult Index()
        {
            dsVideoCollection = new DataSet("videos");

            tblDirectors = new DataTable();
            tblVideoCategories = new DataTable("category");

            tblRatings = dsVideoCollection.Tables.Add();

            return View();
        }
    }
}

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:

public virtual void Add(DataTable table);

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

@{
    System.Data.DataSet dsVideoCollection = new System.Data.DataSet();

    System.Data.DataTable tblVideoCategories = new System.Data.DataTable("category");

    dsVideoCollection.Tables.Add(tblVideoCategories);
}

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

public virtual DataTable Add(string name);

This version works like the first except that, instead of the default name (such as Table1, Table2, etc), it lets you specify the desired name of the new table. Here are examples:

using System.Data;
using System.Web.Mvc;

namespace Exercises.Controllers
{
    public class VideoCollectionController : Controller
    {
        DataTable tblActors;
        DataTable tblFormats;
        DataTable tblRatings;
        DataTable tblDirectors;
        DataTable tblVideoCategories;
        DataSet dsVideoCollection;

        // GET: VideoCollection
        public ActionResult Index()
        {
            dsVideoCollection = new DataSet("Videos");

            tblDirectors = new DataTable();
            tblDirectors.TableName = "director";
            dsVideoCollection.Tables.Add(tblDirectors);

            tblVideoCategories = new DataTable("category");
            dsVideoCollection.Tables.Add(tblVideoCategories);

            tblRatings = dsVideoCollection.Tables.Add();

            tblActors = dsVideoCollection.Tables.Add("actor");
            tblFormats = dsVideoCollection.Tables.Add("format");

            return View();
        }
    }
}

Creating a Range of Tables

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

public void AddRange(DataTable[] tables);

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

@{
    System.Data.DataSet dsBooks = new System.Data.DataSet("books");

    System.Data.DataTable dtCategories = new System.Data.DataTable("category");
    System.Data.DataTable dtAuthors = new System.Data.DataTable("author");
    System.Data.DataTable dtPublishers = new System.Data.DataTable("publisher");
    System.Data.DataTable dtBooks = new System.Data.DataTable("book");

    System.Data.DataTable[] colTables = { dtCategories, dtAuthors, dtPublishers, dtBooks };
    dsBooks.Tables.AddRange(colTables);
}

Accessing a Table in the Collection

Locating a Table by Name

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 let you locate a table in the DataSet.Tables collection, the DataTableCollection class is equipped with the Item indexed property available in three versions. To locate a table using its name, use the following version of this property:

public DataTable this[string name] {get;}

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

@{
    System.Data.DataSet dsVideoCollection = new System.Data.DataSet("Videos");

    System.Data.DataTable tblDirectors = new System.Data.DataTable();
    tblDirectors.TableName = "director";
    dsVideoCollection.Tables.Add(tblDirectors);

    System.Data.DataTable tbl = dsVideoCollection.Tables["director"];
}

<p>Table Name: @tbl.TableName</p>

Locating a Table by its Index

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[] property. Its syntax is:

public DataTable this[int index] {get;}

This property expects as argument the index of the table in the DataSet.Tables collection. Here is an example:

@{
    System.Data.DataSet dsVideoCollection = new System.Data.DataSet("videos");

    System.Data.DataTable tblDirectors = new System.Data.DataTable();
    tblDirectors.TableName = "director";
    dsVideoCollection.Tables.Add(tblDirectors);

    System.Data.DataTable tblVideoCategories = new System.Data.DataTable("category");
    dsVideoCollection.Tables.Add(tblVideoCategories);

    System.Data.DataTable tblRatings = dsVideoCollection.Tables.Add();

    System.Data.DataTable tblActors = dsVideoCollection.Tables.Add("actor");
    System.Data.DataTable tblFormats = dsVideoCollection.Tables.Add("format");

    System.Data.DataTable tbl = dsVideoCollection.Tables[3];
}

<p>Table Name: @tbl.TableName</p>

This would produce Actors

For Each Table in a Collection

The InternalDataCollectionBase class, the parent of DataTableCollection, implements the GetEnumerator() method of the IEnumerable interface. This allows you to use a foreach loop to visit each member table of the collection. Once you have reached a table in the collection, you can access any of its public properties or methods. Here is an example of applying foreach on a collection of tables of a data set to list their names:

<!DOCTYPE html>
<html>
<head>
<title>Video Collection</title>
</head>
<body>
<h1>Video Collection</h1>

@{
    System.Data.DataSet dsVideoCollection = new System.Data.DataSet("videos");

    System.Data.DataTable tblDirectors = new System.Data.DataTable();
    tblDirectors.TableName = "Directors";
    dsVideoCollection.Tables.Add(tblDirectors);

    System.Data.DataTable tblVideoCategories = new System.Data.DataTable("category");
    dsVideoCollection.Tables.Add(tblVideoCategories);

    System.Data.DataTable tblRatings = dsVideoCollection.Tables.Add();

    System.Data.DataTable tblActors = dsVideoCollection.Tables.Add("actor");
    System.Data.DataTable tblFormats = dsVideoCollection.Tables.Add("format");
}

<h3>List of Tables</h3>
<ul>
    @foreach (System.Data.DataTable tbl in dsVideoCollection.Tables)
    {
        <li>@tbl.TableName</li>
    }
</ul>
</body>
</html>

This would produce:

You can use this approach to identity a table and then perform a desired operation on it.

Locating a Table in a Collection

The Index of a Table

When using the DataTable this[int index] indexed property, 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 in three versions. One of the versions uses the following syntax:

public virtual int IndexOf(DataTable table);

This version takes as argument the variable name of the table. Here is an example of calling this method:

@{
    System.Data.DataSet dsVideoCollection = new System.Data.DataSet("Videos");

    System.Data.DataTable tblDirectors = new System.Data.DataTable();
    tblDirectors.TableName = "Directors";
    dsVideoCollection.Tables.Add(tblDirectors);

    System.Data.DataTable tblVideoCategories = new System.Data.DataTable("category");
    dsVideoCollection.Tables.Add(tblVideoCategories);

    System.Data.DataTable tblRatings = dsVideoCollection.Tables.Add();

    System.Data.DataTable tblActors = dsVideoCollection.Tables.Add("actor");
    System.Data.DataTable tblFormats = dsVideoCollection.Tables.Add("format");

    int index = dsVideoCollection.Tables.IndexOf(tblActors);
}

<p>Table Index: @index</p>

This would produce 3

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

public virtual int IndexOf(string tableName);

Checking Whether a Data Set Contains a Certain Table

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 bool Contains(string name);

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

@{
    System.Data.DataSet dsVideoCollection = new System.Data.DataSet("Videos");

    System.Data.DataTable tblDirectors = new System.Data.DataTable();
    tblDirectors.TableName = "Directors";
    dsVideoCollection.Tables.Add(tblDirectors);

    System.Data.DataTable tblVideoCategories = new System.Data.DataTable("category");
    dsVideoCollection.Tables.Add(tblVideoCategories);

    System.Data.DataTable tblRatings = dsVideoCollection.Tables.Add();

    System.Data.DataTable tblActors = dsVideoCollection.Tables.Add("actor");
    System.Data.DataTable tblFormats = dsVideoCollection.Tables.Add("format");
}

@if (dsVideoCollection.Tables.Contains("Actors"))
{
    <p>The Actors table exists.</p>
}
else
{
    <p>The Actors table does not exist.</p>
}
@if (dsVideoCollection.Tables.Contains("VideoTypes"))
{
    <p>The VideoTypes table exists</p>
}
else
{
    <p>The VideoTypes table does not exist</p>
}

This would produce:

Tables Maintenance

Deleting a Table

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

public void Remove(DataTable table);

This version expects the variable 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:

@{
    System.Data.DataSet dsVideoCollection = new System.Data.DataSet("Videos");

    System.Data.DataTable tblDirectors = new System.Data.DataTable();
    tblDirectors.TableName = "Directors";
    dsVideoCollection.Tables.Add(tblDirectors);

    System.Data.DataTable tblVideoCategories = new System.Data.DataTable("category");
    dsVideoCollection.Tables.Add(tblVideoCategories);

    System.Data.DataTable tblRatings = dsVideoCollection.Tables.Add();

    System.Data.DataTable tblActors = dsVideoCollection.Tables.Add("actor");
    System.Data.DataTable tblFormats = dsVideoCollection.Tables.Add("format");
}

<h3>Original List of Tables</h3>

<ul>
@foreach (System.Data.DataTable tbl in dsVideoCollection.Tables)
{
    <li>@tbl.TableName</li>
}
</ul>

@{
    dsVideoCollection.Tables.Remove(tblVideoCategories);
}

<h3>After Deleting a Table</h3>

<ul>
    @foreach (System.Data.DataTable tbl in dsVideoCollection.Tables)
    {
        <li>@tbl.TableName</li>
    }
</ul>

This would produce:

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

public void Remove(string name);

This method expects the formal name of the table as argument. If a table exists under that name, it would be deleted. Here is an example:

@{
    System.Data.DataSet dsVideoCollection = new System.Data.DataSet("Videos");

    System.Data.DataTable tblDirectors = new System.Data.DataTable();
    tblDirectors.TableName = "Directors";
    dsVideoCollection.Tables.Add(tblDirectors);

    System.Data.DataTable tblVideoCategories = new System.Data.DataTable("category");
    dsVideoCollection.Tables.Add(tblVideoCategories);

    System.Data.DataTable tblRatings = dsVideoCollection.Tables.Add();

    System.Data.DataTable tblActors = dsVideoCollection.Tables.Add("actor");
    System.Data.DataTable tblFormats = dsVideoCollection.Tables.Add("format");
}

<h3>Original List of Tables</h3>

<ul>
@foreach (System.Data.DataTable tbl in dsVideoCollection.Tables)
{
    <li>@tbl.TableName</li>
}
</ul>

@{
    dsVideoCollection.Tables.Remove("Actors");
}

<h3>After Deleting a Table</h3>

<ul>
    @foreach (System.Data.DataTable tbl in dsVideoCollection.Tables)
    {
        <li>@tbl.TableName</li>
    }
</ul>

If no table with the name is found, the compiler would throw an ArgumentException exception. Therefore, you should first check that a table with the undesired name exists before deleting it.

If the table exists in the collection, it may not allow the user to delete it. To find out whether a table can be deleted, call the DataTableCollection.CanRemove() method. Its syntax is:

public bool CanRemove(DataTable table);

When calling the DataTableCollection.Remove() method, if the DataTable object passed as argument is not found, the compiler would throw either an ArgumentNullException exception or an ArgumentException exception. Here is an example:

@{
    System.Data.DataSet dsVideoCollection = new System.Data.DataSet("Videos");
}

@{
    dsVideoCollection.Tables.Remove("GenresOrTypes");
}

This would produce:

Therefore, before deleting a table, you should first check its existence. To do this, you can call the DataTableCollection.Contains() method we saw earlier.

Here is an example of calling this method before deleting a table:

@{
    System.Data.DataSet dsVideoCollection = new System.Data.DataSet("Videos");
}

@if (dsVideoCollection.Tables.Contains("GenresOrTypes"))
{
    dsVideoCollection.Tables.Remove("GenresOrTypes");
}
else
{
    <p>The table named GenresOrTypes was not found in the database</p>
}

This would produce:

Clearing a Collection of Tables

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

public void Clear();

Calling this method would remove all DataTable objects of the DataSet. Here is an example:

@{
    System.Data.DataSet dsVideoCollection = new System.Data.DataSet("Videos");

    System.Data.DataTable tblDirectors = new System.Data.DataTable();
    tblDirectors.TableName = "Directors";
    dsVideoCollection.Tables.Add(tblDirectors);

    System.Data.DataTable tblVideoCategories = new System.Data.DataTable("category");
    dsVideoCollection.Tables.Add(tblVideoCategories);

    System.Data.DataTable tblRatings = dsVideoCollection.Tables.Add();

    System.Data.DataTable tblActors = dsVideoCollection.Tables.Add("actor");
    System.Data.DataTable tblFormats = dsVideoCollection.Tables.Add("format");
}

<p>The data set originally contains @dsVideoCollection.Tables.Count tables.</p>

@{
    dsVideoCollection.Tables.Clear();
}

<p>At the end, the data set contains @dsVideoCollection.Tables.Count tables.</p>

Fundamentals of Columns of a Table

Introduction

A column is a technique of categorizing some values that belong to a table. Here is an example of a table with various columns:

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

To support the columns of a table, the System.Data namespace contains a class named DataColumn.

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:

using System.Data;
using System.Web.Mvc;

namespace Exercises.Controllers
{
    public class VideoCollectionController : Controller
    {
        DataColumn colCategoryID;

        // GET: VideoCollection
        public ActionResult Index()
        {
            colCategoryID = new DataColumn();

            return View();
        }
    }
}

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 in C#. For example, a column's object name can be FullName, Date of Birth, First Name + Last Name, CD w/+ H2, $alary, or P@Hip!. 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 *

public DataColumn(string name);

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

using System.Data;
using System.Web.Mvc;

namespace Exercises.Controllers
{
    public class VideoCollectionController : Controller
    {
        DataColumn colCategoryID;

        // GET: VideoCollection
        public ActionResult Index()
        {
            colCategoryID = new DataColumn("CategoryID");

            return View();
        }
    }
}

In the real world, for most people, including me:

• If the name of a column is in one word, the first letter would be in uppercase
• If the name of a column is a combination of words, each word would start in uppercase

In a DataSet-based database:

1. If the name of a column is in one word, we will use lowercase
2. If the name of a column is a combination of words, each word will be in lowercase and the word will be separated by a dash

To specify the object name of a column, when creating it, you can use the second constructor of the DataColumn 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:

using System.Data;
using System.Web.Mvc;

namespace Exercises.Controllers
{
    public class VideoCollectionController : Controller
    {
        DataColumn colCategoryID;
        DataColumn colCategory;

        // GET: VideoCollection
        public ActionResult Index()
        {
            colCategoryID = new DataColumn("category-id");

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

            return View();
        }
    }
}

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.

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, which is a collection class. 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 a member 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 virtual DataColumn Add();

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:

using System.Data;
using System.Web.Mvc;

namespace Exercises.Controllers
{
    public class VideoCollectionController : Controller
    {
        DataColumn colDirectorID;
        DataTable dtDirectors;

        // GET: VideoCollection
        public ActionResult Index()
        {
            dtDirectors = new DataTable();
            colDirectorID = dtDirectors.Columns.Add();

            return View();
        }
    }
}

If you want to specify the object 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.Data;
using System.Web.Mvc;

namespace Exercises.Controllers
{
    public class VideoCollectionController : Controller
    {
        DataColumn colDirectorID;
        DataColumn colDirector;

        DataTable dtDirectors;

        // GET: VideoCollection
        public ActionResult Index()
        {
            dtDirectors = new DataTable();
            colDirectorID = dtDirectors.Columns.Add();

            colDirector = dtDirectors.Columns.Add("director");

            return View();
        }
    }
}

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.Data;
using System.Web.Mvc;

namespace Exercises.Controllers
{
    public class VideoCollectionController : Controller
    {
        DataColumn colDirectorID;
        DataColumn colDirector;
        DataColumn colDateOfBirth;

        DataTable dtDirectors;

        // GET: VideoCollection
        public ActionResult Index()
        {
            colDateOfBirth = new DataColumn("Date of Birth");

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

            colDirector = dtDirectors.Columns.Add("director");

            dtDirectors.Columns.Add(colDateOfBirth);

            return View();
        }
    }
}

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.Data;
using System.Web.Mvc;

namespace Exercises.Controllers
{
    public class VideoCollectionController : Controller
    {
        DataColumn colDirectorID;
        DataColumn colDirector;
        DataColumn colDateOfBirth;
        DataTable dtDirectors;

        DataColumn[] colVideos;
        DataTable dtVideos;

        // GET: VideoCollection
        public ActionResult Index()
        {
            colDateOfBirth = new DataColumn("Date of Birth");

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

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

            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 = new DataTable("Videos");
            dtVideos.Columns.AddRange(colVideos);

            return View();
        }
    }
}

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

@{
    System.Data.DataColumn[] colVideos = new System.Data.DataColumn[7];

    colVideos[0] = new System.Data.DataColumn("title");
    colVideos[1] = new System.Data.DataColumn("director");
    colVideos[2] = new System.Data.DataColumn("copyright-year");
    colVideos[3] = new System.Data.DataColumn("length");
    colVideos[4] = new System.Data.DataColumn("rating");
    colVideos[5] = new System.Data.DataColumn("format");
    colVideos[6] = new System.Data.DataColumn("category");

    System.Data.DataTable dtVideos = new System.Data.DataTable("Videos");
    dtVideos.Columns.AddRange(colVideos);

    System.Data.DataSet dsVideoCollection = new System.Data.DataSet("VideoCollection");
    dsVideoCollection.Tables.Add(dtVideos);
}

<ul>
@for (int i = 0; i < dtVideos.Columns.Count; i++)
{
    <li>@dtVideos.Columns[i].ColumnName</li>
}
</ul>

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

@{
    System.Data.DataColumn[] colVideos = new System.Data.DataColumn[7];

    colVideos[0] = new System.Data.DataColumn("title");
    colVideos[1] = new System.Data.DataColumn("director");
    colVideos[2] = new System.Data.DataColumn("copyright-year");
    colVideos[3] = new System.Data.DataColumn("length");
    colVideos[4] = new System.Data.DataColumn("rating");
    colVideos[5] = new System.Data.DataColumn("format");
    colVideos[6] = new System.Data.DataColumn("category");

    System.Data.DataTable dtVideos = new System.Data.DataTable("video");
    dtVideos.Columns.AddRange(colVideos);

    System.Data.DataSet dsVideoCollection = new System.Data.DataSet("video");
    dsVideoCollection.Tables.Add(dtVideos);
}

<ul>
@foreach (System.Data.DataColumn col in dtVideos.Columns)
{
    <li>@col.ColumnName</li>
}
</ul>

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 bool Contains(string name);

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 let you 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 void Remove(string name);

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:

@{
    System.Data.DataTable tblVideos = new System.Data.DataTable("video");
    System.Data.DataColumn colTitle = new System.Data.DataColumn("title");
    tblVideos.Columns.Add(colTitle);
    System.Data.DataColumn colDirector = new System.Data.DataColumn("director");
    tblVideos.Columns.Add(colDirector);
    System.Data.DataColumn colLength = new System.Data.DataColumn("length");
    tblVideos.Columns.Add(colLength);
    System.Data.DataColumn colFormat = new System.Data.DataColumn("format");
    tblVideos.Columns.Add(colFormat);
    System.Data.DataColumn colRating = new System.Data.DataColumn("rating");
    tblVideos.Columns.Add(colRating);
}

<h4>Original List of Columns</h4>

<ul>
    @foreach (System.Data.DataColumn col in tblVideos.Columns)
    {
        <li>@col.ColumnName</li>
    }
</ul>
    
<h4>Deleting a Column</h4>

@{ 
    tblVideos.Columns.Remove("format");
}
    
<h4>Current List of Columns</h4>

<ul>
@foreach (System.Data.DataColumn col in tblVideos.Columns)
{
    <li>@col.ColumnName</li>
}
</ul>

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

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 bool CanRemove(DataColumn column);

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 void RemoveAt(int index);

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 void Clear();

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

Home	Copyright © 2005-2019, FunctionX	Next