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 database word
suggests that the list must be formally created in human memory, on a piece of paper,
or on a computer file, etc. Any type of thing can be made into a list. This means that
one list can be made of people. Another list can be made of CDs. Another list
can be made of countries, and so on. Because of this, before creating a list, you should
first plan it by deciding what kinds of items would compose the list.

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.

Data Set Creation

To support the creation and management of a set of data,
the .NET Framework provides the DataSet class, which is defined in the System.Data
namespace. Therefore, to create a list, you can start by declaring 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:

To visually assist you with creating a data set, in its Data
section, the Toolbox provides the DataSet object. Therefore, to visually start
creating a data set, you can click DataSet and click the form. When you do this,
the Add Dataset dialog box would come up. You must click the Untyped Dataset
radio button:

Then click OK. After doing it, the compiler would
programmatically declare a variable and give its name in the (Name) field
of the Properties window.

The Name of a Data Set

When creating a set of data, you must name it. This would allow you to refer to the
set later on by a formal 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. Therefore, 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. If you visually create a
data set, the Microsoft Visual Studio would create a default name for it in the DataSetName
field of the Properties Windows. If you want to change that name, replace that
property's value.

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 it must respect the rules of names of the C#
language. Here is an example:

using System;
using System.Data;
public class VideoCollection
{
public DataSet dsVideoCollection;
public VideoCollection()
{
dsVideoCollection = new DataSet("Videos");
}
}

Tables Fundamentals

Introduction to Tables

Imagine you have a list of movie directors and you want to
group their names into a list. Here is an example:

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:

Video Title

Director

� Year

Length

Format

Rating

A Few Good Men

Rob Reiner

1992

138 Minutes

VHS

R

The Distinguished Gentleman

Jonathan Lynn

112 Minutes

DVD

R

The Lady Killers

Joel Coen & Ethan Coen

104 Minutes

DVD

R

Fatal Attraction

Adrian Lyne

1987

120 Minutes

VHS

R

Her Alibi

Bruce Beresford

1989

94 Minutes

DVD

PG-13

The Manchurian Candidate

Jonathan Demme

2004

129 Minutes

DVD

R

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.

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. Put in reverse, a data set is one or more
tables used in one database.

Creating a Table

Programmatically Creating a Table

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 four constructors. The default constructor allows you to
create a table without giving more details, especially without formally naming
it. Here is an example:

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. Here is an
example:

To visually create a table, under the form, you can click
the data set object. In the Properties window, you can click Tables and click
the ellipsis button of its field. This would open the Table Collection Editor.
To create a table, you can click the Add button. A table would be added:

The new table has a default variable name under Members and
an object name. To change the variable name, under the Properties list, you can
click (Name) and type the desired name. To change the object name, you
can click TableName and type the new name.

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

While performing an operation such as adding a new table to
the collection, the DataTableCollection
class fires a CollectionChanging event. The syntax of this event is:

public event CollectionChangeEventHandler CollectionChanging

You cannot visually generate this event in Microsoft Visual
Studio. If you want to catch it, you must manually define it. Here is an
example:

As you can see, this event is based on the CollectionChangeEventArgs
class. The CollectionChangeEventArgs class has two properties. The Action
property specifies what happened to the collection. This property is of type CollectionChangeAction,
which is an enumeration. If a new table is being added, then the action is Add.

The CollectionChangeEventArgs class is also equipped
with a property named Element, which is of type object. This
property holds a DataTableCollection object with the current changes.

Adding a New Table to a Collection

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:

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:

This event also is based on the CollectionChangeEventArgs
class. If a new table was added, then the action of the event is Add.

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:

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

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:

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:

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 the Existence of a 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:

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:

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
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
or an ArgumentException exception. Here is an example:

While deleting a table from the collection, the DataTableCollection
fires a CollectionChanging event. After a table has been removed from the
collection, the DataTableCollection fires a CollectionChanged
event. As we saw earlier, both events are of type CollectionChangeEventArgs.
While a table is being deleted or after it has been deleted, the action of this
event is Remove.

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.

When clearing the collection of all its tables, the DataTableCollection
fires a CollectionChanging event. After the tables have been deleted, the
DataTableCollection fires a CollectionChanged event. In both
cases, the action of this event is Refresh.