The Tables of a Database

Tables are the foundation of organizing lists of items. This concept is valid regardless of the type of list. As a database application is primarily a list of things, the SQL uses the same approach at organizing information. Before creating a table, you must specify what database it would belong to.

1. Start SharpDevelop
2. To create a new application, on the main menu, click File -> New -> Combine
3. In the Categories tree of the New Project dialog box, make sure that C# is selected; otherwise, select C#.
   In the Templates list, click Windows Application
4. Set the Name to Countries1
5. Change the Location to a directory of your choice such as Drive:\Programs\MySQLApps and click Create
6. In the Projects section of the left frame, expand the Combine and the Countries1 nodes if necessary.
   Right-click References and click Add Reference
7. In the Add Reference dialog box, click the .NET Assembly Browser tab and click the Browse button
8. In the Open dialog box, locate the folder where your MySQL Connector Net is installed and display it in the Look In combo box
9. Double-click Bin followed by your version of the .NET Framework
10. Click MySql.Data.dll and click Open
11. After making sure that the assembly has been selected, in the Add Reference dialog box, click OK
12. In the top section of the file, under the other using lines, type:
     
    using MySql.Data.MySqlClient;
13. In the lower section of the MainForm.cs tab, click Design
14. Add a new button to the form and change its properties as follows:
    (Name): btnCreateDB
    Text: Create Statistics Database
15. Double-click the Create Statistics Database button
16. To create a database for this lesson, implement the code as follows:
     

    private void btnCreateDB_Click(object sender, System.EventArgs e) { string strConnection = "CREATE DATABASE CountriesStats;"; MySqlConnection conDatabase = new MySqlConnection("Data Source=localhost;Persist Security Info=yes"); MySqlCommand cmdDatabase = new MySqlCommand(strConnection, conDatabase); conDatabase.Open(); cmdDatabase.ExecuteNonQuery(); conDatabase.Close(); }

17. Execute the application and click the Create button
18. Close the form and return to your programming environment

The statement used to create a new table uses the following formula

CREATE TABLE TableName

The CREATE and TABLE keywords must be used to let SQL know that you want to create a table. The TableName factor specifies the name of the new table. The TableName can use the rules and suggestions we reviewed for the database objects.

After specifying the name of the table, you must list the columns of the table. The list of columns starts with an opening parenthesis "(". The list ends with a closing parenthesis ")". Each column must be separated from the next with a comma, except for the last column. You can include all columns on the same line if possible as follows:

CREATE TABLE Country(Column1, Column2, Column3)

There are two primary pieces of information you must specify for each column: its name and its type. Therefore, the syntax of creating a column is:

ColumnName DataType Options

To programmatically create a table, you can follow the same formula rules of creating a table using SQL code. Once the statement is ready, you can pass it to a MySqlCommand object. To execute the statement, you can call the SqlCommand.ExecuteNonQuery() method.

The Name of a Table

While creating a table, you must name it. If you are creating a table using SQL code, you must name your table using the above formula of CREATE TABLE TableName.

The name of a table:

• Can be made of digits only. For example you can have a table called 148
• Can start with a digit, a letter, or an underscore
• Can be made of letters, digits, and spaces

Besides these rules, you can make up yours. In our lessons, the name of a table

• Will start with a letter, in uppercase or lowercase. Examples are Employees, Accounts
• If made of a combination of words, it will have each part start in uppercase. Examples are BookCategories, CustomersBankAccounts.

As implied above, to create a table, you must include at least one column. In the next lesson, we will have more details on how to create and manage columns.

Practical Learning: Creating Tables

1. Change the design of the form as follows:
    

   Control Name Text Button btnCreateDB Create Statistics Database Button btnCreateContinents Create Continents Button btnCreateCountries Create Countries

2. Double-click the Create Continents button and implement its code as follows:
    

   private void btnCreateContinents_Click(object sender, System.EventArgs e) { string strCreate = "CREATE TABLE Continents(ContinentName char);"; MySqlConnection conDatabase = new MySqlConnection("Data Source=localhost;Database='CountriesStats';Persist Security Info=yes"); MySqlCommand cmdDatabase = new MySqlCommand(strCreate, conDatabase); conDatabase.Open(); cmdDatabase.ExecuteNonQuery(); conDatabase.Close(); }

3. Execute the application and click the Create Continents button
4. After a few seconds, click the Create Continents button and notice that you receive an error
5. Click Quit and return to the form
6. On the form, double-click the Create Countries button
7. To use code that checks the existence of a table, using sample code like the one generated by the Create Table Basic Template option of the SQL Query Analyzer, implement both events as follows:
    

   private void btnCreateContinents_Click(object sender, System.EventArgs e) { string strCreate = "CREATE TABLE Continents (ContinentName char);"; MySqlConnection conDatabase = new MySqlConnection("Data Source=localhost;Database='CountriesStats';Persist Security Info=yes"); MySqlCommand cmdDatabase = new MySqlCommand(strCreate, conDatabase); conDatabase.Open(); cmdDatabase.ExecuteNonQuery(); conDatabase.Close(); } private void btnCreateCountries_Click(object sender, System.EventArgs e) { string strCreate = "CREATE TABLE Countries (CountryName char);"; MySqlConnection conDatabase = new MySqlConnection("Data Source=localhost;Database='CountriesStats';Persist Security Info=yes"); MySqlCommand cmdDatabase = new MySqlCommand(strCreate, conDatabase); conDatabase.Open(); cmdDatabase.ExecuteNonQuery(); conDatabase.Close(); }

8. Execute the application
9. Click the different buttons to create the tables. You can also test renaming a table now
10. Close the form

Table Maintenance

Renaming a Table

If you have a table whose is not appropriate, you can change its name. Before renaming a table, make sure this is what you want to do and make sure you can take care of it in your code. If you rename a table, Microsoft SQL Server would take care of updating it in Microsoft SQL Server. If you had used the name in your Windows Forms Application code, of course, the new name would not be updated in your code. You made need to take care of it yourself.

If you are working in SQL Server Enterprise Manager, to rename a table, first locate its database in the left frame and click the Tables node. In the right-click frame, right-click the name of the table and click Rename. You would proceed the same way you do in Windows Explorer or My Computer: the name would be put into edit mode so you can type the new one and press Enter.

The SQL Server Enterprise Manager is the only utility that allows you to "visually" rename a table. If you are working in one of the other environments we have mentioned, you can only rename the table programmatically. To rename a table with code, Transact-SQL provides sp_rename. (Notice that the name starts with sp_. This is called a stored procedure. We will learn how to create them. For now, we can use them exactly as you have learned to use functions in C#: You don't need to know how they work but you can trust that they work and do what they are supposed to do.

To rename a table, use the following call:

EXEC sp_rename 'ExistingName', 'NewName'

The EXEC sp_rename expression is required. The ExistingName factor is the name of the table you want to rename. The NewName factor is the name you want the table to have after renaming it.

To rename a table in SQL Query Analyzer, make sure you are in the appropriate database first. Then use the above formula. Here is an example:

USE CarRentalDB
GO
EXEC sp_rename 'CarsFrom1996To2000', 'CarsToRetire'
GO

To rename a table in a C# code, pass the sp_rename code as string to a MySqlCommand object and call the SqlCommand.ExecuteNonQuery() method.

Practical Learning: Renaming a Table

Removing a Table

If you have a table you don't need in your database, you can remove it. Before performing this operation, you should make sure you are familiar with the role of the table. The SQL code used to delete a table uses the following formula:

DROP TABLE TableName

The DROP TABLE expression is required and it is followed by the name of the table as TableName. Here is an example:

DROP TABLE FriendsOfMine
GO

If you are working from C# code, create a DROP TABLE TableName; expression and pass it to a MySqlCommand object before calling the SqlCommand.ExecuteNonQuery() method.

It is extremely important to know that, when working with the DROP TABLE TableName statement, you would not receive any warning. If you are working in a Windows Forms Application, you should create your own warning in a message box to make sure that the user really want to delete the table.