Home

The Tables of a Database

 

Table Creation

 

Introduction

We have defined a database as one or more lists. A list in a database is called a table. The idea is that a table is an arrangement of the categories of information stored in a list and a table makes it easy to locate and manage the records of a list. To better explore lists, you should know how a table organizes its value.

A table is made of one or more categories divided as columns. Consider the following example of a list of teachers of a high school:

Last Name First Name Main Subject Alternate Subject
Pastore Albert Math Physics
Andong Gertrude Chemistry Static
Missiano Helena Physical Ed  
Jones Celestine Comp Sciences Math

Notice that the first names are grouped in a common category, so are the last names and so on. This makes it easy to locate a category and possibly a value.

 

Table Creation

To create a table, you start an expression with CREATE TABLE followed by the name of the table:

CREATE TABLE Name;

The CREATE and TABLE keywords must be used to create a table. The Name factor specifies the name of the new table.

The Name of a Table

After the CREATE TABLE expression, you must enter a name for the table. The name of a table can be very flexible. This flexibility can be overwhelming and confusing. To avoid these, there are suggestions and conventions we will apply when naming our tables:

  • The name of a table will start with a letter. In most cases, the name will start in uppercase
  • Because we believe that a table represents a list of items, its name will be in plural. Examples are Students, Employees, Products
  • When a name is a combination of words, each part will start in uppercase. Examples are Student Names or Sport Activities
  • In most cases, we will avoid including space in a name; but if we do, the name of a table will be included between [ and ]

Here is an example:

using System;
using ADODB;

public class Program
{
    static int Main()
    {
        string strStatement = "CREATE TABLE Persons...";
        
        return 0;
    }
}

After formulating the expression that creates the table, you can pass it to the Execute() method of a Connection variable. This would be done as follows:

using System;
using ADODB;

public class Program
{
    static int Main()
    {
        ConnectionClass conDatabase = new ConnectionClass();

        try
        {
            object objAffected;
            string strStatement = "CREATE TABLE Persons...";
            string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                                   "Data Source='C:\\Programs\\Exercise1.mdb';";

            conDatabase.Open(strConnection, "", "", 0);
            conDatabase.Execute(strStatement, out objAffected, 0);
        }
        finally
        {
            conDatabase.Close();
        }
        return 0;
    }
}

Besides the CREATE TABLE expression followed by a name, there are other issues related to creating a table. We will review more details in future lessons.

Table Maintenance

 

The Tables Collection

The tables of an ADO database are stored in a collection represented in the Connection class by the Tables property. To locate this collection, you can access the Tables property of the Catalog class of the ADOX namespace.

Deleting a Table

To remove a table from a database, create a DROP TABLE expression followed by the name of the table. The formula to use is:

DROP TABLE TableName;

Replace the TableName factor of our formula with the name of the table you want to delete. Here is an example:

using System;
using ADODB;

public class Program
{
    static int Main()
    {
        ConnectionClass conDatabase = new ConnectionClass();

        try
        {
            object objAffected;
            string strStatement = "DROP TABLE Persons";
            string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                                   "Data Source='C:\\Programs\\Exercise1.mdb';";

            conDatabase.Open(strConnection, "", "", 0);
            conDatabase.Execute(strStatement, out objAffected, 0);
        }
        finally
        {
            conDatabase.Close();
        }
        return 0;
    }
}
 

Previous Copyright © 2007-2012 FunctionX Next