The Tables of a Data Set

Imagine you have a list of movie directors and you want to group 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. While working on this list, you may decide to create a video collection and make the above items into a formal list. A typical movie provides such information as its length, its rating, the year it was released, etc. To create such a list, you would group items by categories. One category may contain the titles of the videos. Another category may contain the names of the directors, and so on.

To better organize a list, you may create each category, then enter the value of each category that corresponds to a particular video. 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 and 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 Minute", "VH", and "R" constitute one row or record.

1. Start Microsoft Visual Studio and create a Console Application named VideoCollection1
2. To create a new class, in the Solution Explorer, right-click VideoCollection1 -> Add -> Class...
3. Set the Name to Video and press Enter
4. Change the file as follows:
   

   using System;
   using System.Collections.Generic;
   using System.Linq;
   using System.Text;
   using System.Data;
   
   namespace VideoCollection1
   {
       public class Video
       {
           public DataSet dsVideoCollection;
   
           public Video()
           {
               dsVideoCollection = new DataSet("Videos");
           }
       }
   }

To support the creation and management of a table, the .NET Framework provides the DataTable class that is defined in the System.Data namespace. 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 three constructors. The default constructor allows you to create a table without giving more details, especially without formally naming it. Here is an example:

using System;
using System.Data;

public class VideoCollection
{
    public DataSet dsVideoCollection;

    public VideoCollection()
    {
        dsVideoCollection = new DataSet("Videos");

        DataTable tblDirectors = new DataTable();
    }
}

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

public class VideoCollection
{
    public DataTable tblDirectors;
    public DataSet dsVideoCollection;

    public VideoCollection()
    {
        dsVideoCollection = new DataSet("Videos");

        tblDirectors = new DataTable();
    }
}

The name of the DataTable variable is required when creating a table. This name will be used by you and the compiler to identify the table. In some cases, you may want the table to hold an object name that you would want to use later as we will see with some methods. To provide a formal name to a table when creating it, you can use the second constructor of the DataTable class. Its syntax is:

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

public class VideoCollection
{
    public DataTable tblDirectors;
    public DataTable tblVideoCategories;
    public DataSet dsVideoCollection;

    public VideoCollection()
    {
        dsVideoCollection = new DataSet("Videos");

        tblDirectors = new DataTable();
        tblVideoCategories = new DataTable("Categories");
    }
}

If you have already declared a DataTable variable using either of both constructors and decide to specify or change its name, you can assign a string to the DataTable.TableName property. Here is an example:

using System;
using System.Data;

public class VideoCollection
{
    public DataTable tblDirectors;
    public DataTable tblVideoCategories;
    public DataSet dsVideoCollection;

    public VideoCollection()
    {
        dsVideoCollection = new DataSet("Videos");

        tblDirectors = new DataTable();
        tblDirectors.TableName = "Directors";
        tblVideoCategories = new DataTable("Categories");
    }
}

The TableName name is primarily optional but it is used by various methods as we will see. If you don't provide it, for example if you declare a DataTable variable using the default constructor and don't specify its name through the DataTable.TableName property, the compiler would generate a default name for the table, such as Table1, Table2, and so on. In the same way, you can create as many tables as you judge necessary for your application and as we will see when moving on.

After creating a table, you can add it to a DataSet object.

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

The DataTableCollection class 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:

using System;
using System.Data;

public class VideoCollection
{
    public DataTable tblActors;
    public DataTable tblFormats;
    public DataTable tblRatings;
    public DataTable tblDirectors;
    public DataTable tblVideoCategories;
    public DataSet   dsVideoCollection;

    public VideoCollection()
    {
        dsVideoCollection = new DataSet("Videos");

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

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

        tblRatings = dsVideoCollection.Tables.Add("Ratings");

        tblActors = dsVideoCollection.Tables.Add("Actors");
        tblFormats = dsVideoCollection.Tables.Add("Formats");
    }

    public void ShowTables()
    {
        DataTableCollection tables = this.dsVideoCollection.Tables;

        foreach (DataTable tbl in tables)
            Console.WriteLine("Table Name: {0}", tbl.TableName);
    }
}

public static class Program
{
    static int Main(string[] args)
    {
        VideoCollection coll = new VideoCollection();

        coll.ShowTables();
        Console.WriteLine();
        return 0;
    }
}

This would produce:

Table Name: Directors
Table Name: Categories
Table Name: Ratings
Table Name: Actors
Table Name: Formats

Press any key to continue . . .

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

Using the DataSet.Tables property, to add a created table to a DataSet object, call one of the Add() methods of the DataTableCollection class. 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;
using System.Data;

public class VideoCollection
{
    public DataTable tblRatings;
    public DataTable tblDirectors;
    public DataTable tblVideoCategories;
    public DataSet dsVideoCollection;

    public VideoCollection()
    {
        dsVideoCollection = new DataSet("Videos");

        tblDirectors = new DataTable();
        tblDirectors.TableName = "Directors";
        tblVideoCategories = new DataTable("Categories");

        tblRatings = dsVideoCollection.Tables.Add();
    }
}

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:

using System;
using System.Data;

public class VideoCollection
{
    public DataTable tblRatings;
    public DataTable tblDirectors;
    public DataTable tblVideoCategories;
    public DataSet   dsVideoCollection;

    public VideoCollection()
    {
        dsVideoCollection = new DataSet("Videos");

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

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

        tblRatings = dsVideoCollection.Tables.Add();
    }
}

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

public class VideoCollection
{
    public DataTable tblActors;
    public DataTable tblFormats;
    public DataTable tblRatings;
    public DataTable tblDirectors;
    public DataTable tblVideoCategories;
    public DataSet   dsVideoCollection;

    public VideoCollection()
    {
        dsVideoCollection = new DataSet("Videos");

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

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

        tblRatings = dsVideoCollection.Tables.Add();

        tblActors = dsVideoCollection.Tables.Add("Actors");
        tblFormats = dsVideoCollection.Tables.Add("Formats");
    }
}

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:

using System;
using System.Data;

public class BookCollection
{
    private void Create()
    {
        DataSet dsBooks = new DataSet("Book");

        DataTable dtCategories = new DataTable("Categorie");
        DataTable dtAuthors = new DataTable("Author");
        DataTable dtPublishers = new DataTable("Publisher");
        DataTable dtBooks = new DataTable("Book");

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

• To create tables, change the file as follows:
  

  using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Text;
  using System.Data;
  
  namespace VideoCollection1
  {
      public class Video
      {
          public DataSet dsVideoCollection;
  
          public DataTable tblVideoCategories;
          public DataTable tblDirectors;
          public DataTable tblRatings;
          public DataTable tblActors;
          public DataTable tblFormats;
  
          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 = dsVideoCollection.Tables.Add("Ratings");
  
              tblActors = dsVideoCollection.Tables.Add("Actors");
              tblFormats = dsVideoCollection.Tables.Add("Formats");
          }
      }
  }

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 locate a table in the DataSet.Tables collection, the DataTableCollection class is equipped with the Item property in two 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:

using System;
using System.Data;

public class VideoCollection
{
    public DataTable tblActors;
    public DataTable tblFormats;
    public DataTable tblRatings;
    public DataTable tblDirectors;
    public DataTable tblVideoCategories;
    public DataSet   dsVideoCollection;

    public VideoCollection()
    {
        dsVideoCollection = new DataSet("Videos");

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

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

        tblRatings = dsVideoCollection.Tables.Add();

        tblActors = dsVideoCollection.Tables.Add("Actors");
        tblFormats = dsVideoCollection.Tables.Add("Formats");
    }
}

public static class Program
{
    static int Main(string[] args)
    {
        VideoCollection coll = new VideoCollection();

        DataTable tbl = coll.dsVideoCollection.Tables["Directors"];

        Console.WriteLine("Table Name: {0}", tbl.TableName);
        return 0;
    }
}

This would produce:

Table Name: Directors
Press any key to continue . . .

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:

using System;
using System.Data;

public class VideoCollection
{
    public DataTable tblActors;
    public DataTable tblFormats;
    public DataTable tblRatings;
    public DataTable tblDirectors;
    public DataTable tblVideoCategories;
    public DataSet   dsVideoCollection;

    public VideoCollection()
    {
        dsVideoCollection = new DataSet("Videos");

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

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

        tblRatings = dsVideoCollection.Tables.Add();

        tblActors = dsVideoCollection.Tables.Add("Actors");
        tblFormats = dsVideoCollection.Tables.Add("Formats");
    }
}

public static class Program
{
    static int Main(string[] args)
    {
        VideoCollection coll = new VideoCollection();

        DataTable tbl = coll.dsVideoCollection.Tables[3];

        Console.WriteLine("Table Name: {0}", tbl.TableName);
        return 0;
    }
}

This would produce:

Table Name: Actors
Press any key to continue . . .

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 two versions. One of the versions takes as argument the variable name of the table. The syntax of this method is:

public virtual int IndexOf(DataTable table);

Here is an example of calling this method:

using System;
using System.Data;

public class VideoCollection
{
    public DataTable tblActors;
    public DataTable tblFormats;
    public DataTable tblRatings;
    public DataTable tblDirectors;
    public DataTable tblVideoCategories;
    public DataSet   dsVideoCollection;

    public VideoCollection()
    {
        dsVideoCollection = new DataSet("Videos");

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

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

        tblRatings = dsVideoCollection.Tables.Add();

        tblActors = dsVideoCollection.Tables.Add("Actors");
        tblFormats = dsVideoCollection.Tables.Add("Formats");
    }

    public void LocateTable()
    {
        int index = dsVideoCollection.Tables.IndexOf(tblActors);
        Console.WriteLine("Table Index: {0}", index.ToString());
    }
}

public static class Program
{
    static int Main(string[] args)
    {
        VideoCollection coll = new VideoCollection();

        coll.LocateTable();
        return 0;
    }
}

This would produce:

Table Index: 3
Press any key to continue . . .

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

When the tables of a DataSet have been created, you can get their list as an array using the DataTableCollection.List property. This property returns an ArrayList type of list.

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 of a table as argument. If the table exists in the collection, this method returns true. Here is an example:

using System;
using System.Data;

public class VideoCollection
{
    public DataTable tblActors;
    public DataTable tblFormats;
    public DataTable tblRatings;
    public DataTable tblDirectors;
    public DataTable tblVideoCategories;
    public DataSet   dsVideoCollection;

    public VideoCollection()
    {
        dsVideoCollection = new DataSet("Videos");

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

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

        tblRatings = dsVideoCollection.Tables.Add();

        tblActors = dsVideoCollection.Tables.Add("Actors");
        tblFormats = dsVideoCollection.Tables.Add("Formats");
    }
}

public static class Program
{
    static int Main(string[] args)
    {
        VideoCollection coll = new VideoCollection();

        if( coll.dsVideoCollection.Tables.Contains("Actors") )
            Console.WriteLine("The Actors table exists");
        else
            Console.WriteLine("The Actors table does not exist");

        Console.WriteLine();

        if (coll.dsVideoCollection.Tables.Contains("VideoTypes"))
            Console.WriteLine("The VideoTypes table exists");
        else
            Console.WriteLine("The VideoTypes table does not exist");

        Console.WriteLine();
        return 0;
    }
}

This would produce:

The Actors table exists

The VideoTypes table does not exist

Press any key to continue . . .

1. To show a list of the tables in the DataSet object, change the Video.cs file as follows:
   

   using System;
   using System.Collections.Generic;
   using System.Linq;
   using System.Text;
   using System.Data;
   
   namespace VideoCollection1
   {
       public class Video
       {
           public DataSet dsVideoCollection;
   
           public DataTable tblVideoCategories;
           public DataTable tblDirectors;
           public DataTable tblRatings;
           public DataTable tblActors;
           public DataTable tblFormats;
   
           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 = dsVideoCollection.Tables.Add("Ratings");
   
               tblActors = dsVideoCollection.Tables.Add("Actors");
               tblFormats = dsVideoCollection.Tables.Add("Formats");
           }
   
           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. Access the Program.cs file and change it as follows:
   

   using System;
   using System.Collections.Generic;
   using System.Linq;
   using System.Text;
   
   namespace VideoCollection1
   {
       public class Program
       {
           static int Main(string[] args)
           {
               Video vdo = new Video();
   
               vdo.ShowTables();
               Console.WriteLine();
               return 0;
           }
       }
   }

3. Execute the application:
   

   Video Collection - Tables
   1. Directors
   2. Categories
   3. Ratings
   4. Actors
   5. Formats
   
   Press any key to continue . . .

4. Close the DOS window

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

using System;
using System.Data;

public class VideoCollection
{
    . . . No Change

    public void ShowTables()
    {
        DataTableCollection tables = this.dsVideoCollection.Tables;

        foreach (DataTable tbl in tables)
            Console.WriteLine("Table Name: {0}", tbl.TableName);

        dsVideoCollection.Tables.Remove(tblVideoCategories);

        Console.WriteLine();

        foreach (DataTable tbl in tables)
            Console.WriteLine("Table Name: {0}", tbl.TableName);
    }
}

public static class Program
{
    static int Main(string[] args)
    {
        VideoCollection coll = new VideoCollection();

        coll.ShowTables();
        Console.WriteLine();
        return 0;
    }
}

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:

using System;
using System.Data;

public class VideoCollection
{
    . . . No Change

    public void ShowTables()
    {
        DataTableCollection tables = this.dsVideoCollection.Tables;

        foreach (DataTable tbl in tables)
            Console.WriteLine("Table Name: {0}", tbl.TableName);

        dsVideoCollection.Tables.Remove("Categories");

        Console.WriteLine();

        foreach (DataTable tbl in tables)
            Console.WriteLine("Table Name: {0}", tbl.TableName);
    }
}

public static class Program
{
    static int Main(string[] args)
    {
        VideoCollection coll = new VideoCollection();

        coll.ShowTables();
        Console.WriteLine();
        return 0;
    }
}

This would produce:

Table Name: Directors
Table Name: Categories
Table Name: Ratings
Table Name: Actors
Table Name: Formats

Table Name: Directors
Table Name: Ratings
Table Name: Actors
Table Name: Formats

Press any key to continue . . .

If no table with the name is found, the compiler would throw an ArgumentException exception. Once again, you should first check that a table with the undesired name exist 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 or an ArgumentException exceptions. For this reason, before deleting a table, you should first check its existence. To do this, you can call the DataTableCollection.Contains() method.

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

using System;
using System.Data;

namespace VideoCollection1
{
    public class Video
    {
        public DataSet dsVideoCollection;

        public DataTable tblVideoCategories;
        public DataTable tblDirectors;
        public DataTable tblRatings;
        public DataTable tblActors;
        public DataTable tblFormats;

        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 = dsVideoCollection.Tables.Add("Ratings");

            tblActors = dsVideoCollection.Tables.Add("Actors");
            tblFormats = dsVideoCollection.Tables.Add("Formats");
        }

        public void ShowTables()
        {
            int i = 1;

            Console.WriteLine("Video Collection - Tables");
            foreach (DataTable tbl in dsVideoCollection.Tables)
                Console.WriteLine("{0}. {1}", i++, tbl.TableName);
        }
        public void DeleteTable(string name)
        {
            if (dsVideoCollection.Tables.Contains(name))
                dsVideoCollection.Tables.Remove(name);
            else
                Console.WriteLine("Table {0} not found in the database", name);
        }

        public void PerformMaintenance()
        {
            ShowTables();
            Console.WriteLine();

            DeleteTable("Ratings");
            Console.WriteLine();

            ShowTables();
            Console.WriteLine();

            DeleteTable("Types");
            Console.WriteLine();

            ShowTables();
            Console.WriteLine();
        }
    }
}

This would produce:

Video Collection - Tables
1. Directors
2. Categories
3. Ratings
4. Actors
5. Formats

Video Collection - Tables
1. Directors
2. Categories
3. Actors
4. Formats

Table Types not found in the database

Video Collection - Tables
1. Directors
2. Categories
3. Actors
4. Formats

Press any key to continue . . .

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.