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:

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.

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.

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.

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.

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