MySQL and Visual C++ .NET - Lesson 06: The Columns of a Table

	The Columns of a Table

Columns Fundamentals

Introduction

In the previous lesson, when introducing tables, we saw that the column was the most required piece of information to create a table. The column is used to organize that information. In our introduction to tables, we saw the formula to create a table was with its columns was:

CREATE TABLE Country(Column1, Column2, Column3)

We also saw that each column of a table was created using the following formula:

ColumnName DataType Options

Based on this, the primary piece of information you must provide for a column is a name.

The Name of a Column

The name of a column should follow the same rules and suggestions we reviewed for the database objects.

Columns Data Types

Introduction

After specifying the name of a column, the SQL interpreter would need to know the kind of information the column will hold. You must specify the data type that is necessary for a particular column. To support the data types used in a database, the MySQL Connector/Net library provides the MySql.Data.Types namespace that provides the necess ary data types.

Data Types: Bit

The bit is the smallest data type. It is used for a field that would validate a piece of information as being true or false, 1 or 0. This is also the data type you should select if a check box would be used to validate the value of this column. This means that it can be used where the C++' bool or the .NET Framework's System.Boolean data types would be applied. The .NET Framework database equivalent to this data type is the SqlBoolean class.

Data Types: Integers

int: An integer is considered a natural or round number. If you want to create a column whose fields would hold numbers in the range of -2,147,483,648 to 2,147,483,647 you can specify its data type as int.

tinyint: This data type can be used for a column that would hold (very) small numbers that range from 0 to 255. It is equivalent to the .NET Framework's System.Byte database. Because C++ doesn't have a byte data type, the tinyint type can be used where a short or rather an unsigned short would be used. You should use this data type only when you know for sure that the values used on this column will be small. When in doubt, you should use int. The .NET Framework database equivalent to this data type is the SqlByte class.

smallint: The smallint data type follows the same rules and principles as the int data type except that it is used to store smaller numbers that would range between -32,768 and 32,767. This means it is equivalent to the C++' short integer or the .NET Framework's System.Int16 type. The .NET Framework database equivalent to this data type is the SqlInt16 class.

bigint: The bigint data type follows the same rules and principles as the int data type except that its field can hold numbers from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. It is somehow equivalent to the C++' long integer but more accurately equivalent to the .NET Framework's System.Int64 data type. As such, you can apply this data type for a column that would hold (very) large numbers. The .NET Framework database equivalent to this data type is the SqlInt64 class.

Data Types: Binary

binary: This data type is used for a column that would hold hexadecimal numbers. Examples of hexadecimal numbers are 0x7238, 0xFA36, or 0xAA48D. Use the binary data type if all entries under the column would have the exact same length (or quantity). If you anticipate that some entries would be different than others, then use the alternative varbinary data type. The varbinary also is used for hexadecimal numbers but allows dissimilar entries, as long as all entries are hexadecimals. The .NET Framework database equivalent to this data type is the SqlBinary class.

Data Types: Floating-Point Values

NUMERIC and DECIMAL: This data type is used on a column that will hold (either whole or) real numbers, numbers that include a decimal separator (the character used as the decimal separator as set in the Control Panel) between the digits. An example would be 12.125 or 44.80. If you anticipate such a number for a field, specify its data type as NUMERIC or DECIMAL. This data type is mostly equivalent to the C++' double or the .NET Framework's System.Double data type. The .NET Framework database equivalent to this data type is the SqlDecimal class.

FLOAT: A floating-point number is a fractional number, like the DECIMAL and NUMERIC types. Floating-point numbers can be used if you would allow the database engine to apply an approximation to the actual number that a field is supposed to carry. This is mostly equivalent to the C#' float or the .NET Framework's System.Single data type. As you may be aware when using float, this data type doesn't offer good precision. The .NET Framework database equivalent to this data type is the SqlDouble class.

Data Types: Characters

CHAR: A field of characters can consist of any kinds of alphabetical symbols in any combination, readable or not. If you want a column to hold a fixed number of characters, such as the book shelf numbers of a library, apply the CHAR data type for such a column. This is equivalent to the C++' char and __wchar_t types of the .NET Framework's System.Char data type except that the SQL's char type is suitable if all fields of a column would have the same length. The .NET Framework database equivalent to this data type is the SqlString class.

Data Types: Strings

VARCHAR: Like the string is in C++ and most other languages, the VARCHAR data type is the most common data type of SQL. It represents a string. This means that it can be used on any column whose values you cannot predict. The .NET Framework database equivalent to this data type is the SqlString class.

TEXT: The text data type can be applied to a field whose data would consist of ASCII characters. The .NET Framework database equivalent to this data type is the SqlString class.

Data Types: Date and Time

As its name suggests, a DATETIME data type is used for a column whose data would consist of date and/or time values. The entries must be valid date or time values but SQL Server allows a lot of flexibility, even to display a date in a non-traditional format. The date value of a DATETIME field can be comprised between January 1st, 1753 and December 31, 9999. This data type is equivalent to the .NET Framework's DATETIME data type. The .NET Framework database equivalent to this data type is the SqlDateTime class.

The Length of Data

One way you can tune your database is to control the amount of text entered in a column's field. As various columns can hold different types of data, so can the same data type control its own mechanism of internal data entry. The length of data means different things to different fields. Columns that carry the same data type can have different lengths.

Most columns use a default length. The only type you should think of changing the length of data is if the column is character (char) or string-based (varchar): You should not touch the others.

To specify the length of a column, if you are using the New Table window from SQL Server Enterprise, or the dbo.Table1 window from Microsoft Visual Studio .NET, you can enter the desired number in the Length box that corresponds to the column.

If you are creating a SQL statement, after typing the name of the type, type the opening parenthesis, followed by the desired number, followed by the closing parenthesis.

The rules of Length columns are:

Bit Fields: We saw already that a bit column type is meant for one of two answers. The user is supposed to simply let the database know that the answer is yes or no, true or false, on or off, 1 or 0. Therefore, the only length of this field is 1.

Integers: The length of an integer is the number of bytes its field can hold. For an int type, that would be 4 bytes.

Decimal and Floating-Point Numbers: The Length specifies how many bytes the field can store.

Strings: The Length of a character or string column specifies the maximum number of characters that the field can hold.

In some circumstances, you will need to change or specify the length as it applies to a particular field. For example, since you should use the varchar data type for a string field whose content will change from one record to another, not all varchar columns need to have the same length. Although a First Name and a Book Title columns should use the varchar type, both columns would not have the same length of entries. As it happens, people hardly have a first name that is beyond 20 characters and many book titles go beyond 32 characters. In this case, both fields would use the same data type but different lengths. On the other hand, for columns of datetime and money data types, you should accept the default length suggested by the database.

Practical Learning: Creating a Table

Change the design of the form as follows:

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

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

private void btnCreateContinents_Click(object sender, System.EventArgs e)
{
	String strCreate = S"CREATE TABLE Continents("
		              S"ContinentName varchar(100),"
		              S"Area bigint,"
	                              S"Population bigint);";

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

Execute the application and click the Create Continents button
After a few seconds, click the Create Continents button and notice that you receive an error
Click Quit and return to the form
On the form, double-click the Create Countries button

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 = S"CREATE TABLE Continents ("
		              S"ContinentName varchar(100), "
		              S"Area bigint, Population bigint);";

 	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 = S"CREATE TABLE Countries ("
		             S"CountryName varchar(120),"
		             S"Continent int,"
		             S"Area bigint,"
		             S"Population bigint,"
		             S"Capital varchar(80),"
		             S"Code char(2));";

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

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

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

Columns Maintenance

Columns Information

So far, we have seen various ways to create a table. This means that you may be aware of the information about about the columns you would have created. If you are working on a table created by someone else or you have forgotten what your old table looks like, you can enquire about the names of columns, their data types and other pieces of information related to the columns.

To get the information about the columns of a table, execute the DESCRIBE TableName statement by replacing TableName with the name of the table that holds the columns.

Adding a Column

After creating a table or when using any existing table, you may find out that a column is missing. You can add a new column to a table if necessary.

To create a new column, the formula to use is:

ALTER TABLE TableName ADD NewColumnName DataType Options

When using this statement, the ALTER TABLE expression and the ADD keyword are required. You must specify the name of the table that the new column will belong to. This is done using the TableName factor in our formula. Specify the name of the new column in the NewColumnName placeholder of our formula. On the right side of the NewColumnName placeholder, specify the options in the same way we described for columns.

If you are working from a Windows Forms Application code, create an ALTER TABLE expression and pass it to a MySqlCommand object before executing it. Here is an example:

private void btnAddColumn_Click(object sender, System.EventArgs e)
{
	MySqlConnection conDatabase = 
		new MySqlConnection("Data Source=localhost;Database='CountriesStats';Persist Security Info=yes");
	MySqlCommand    cmdDatabase = 
		new MySqlCommand("ALTER TABLE Countries ADD TypeOfGovernment varchar(100);",
			conDatabase);

	conDatabase.Open();

	cmdDatabase.ExecuteNonQuery();
	conDatabase.Close();
}

Deleting a Column

If you have a column you don't need, you can remove that column from the table.

The formula to delete a column is:

ALTER TABLE TableName DROP COLUMN ColumnName

In this formula, the ALTER TABLE and the DROP COLUMN expressions are required. The TableName factor is the name of the table that owns the column. The ColumnName factor is the name of the column to be deleted.

To programmatically remove a column, create an ALTER TABLE statement using the above formula, pass the statement as string to a MySqlCommand object before executing it.

There is no warning when deleting a column. If you are programmatically deleting a column, you can provide your own warning through a message box to make the user decide to continue or keep the column.


Previous	Copyright © 2005-2016, FunctionX