SQL Databases

The SQL is very flexible when it comes to names. In fact, it is very less restrictive than C++. Still, there are rules you must follow when naming the objects in your databases:

• A name can start with either a letter (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, z, A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, or Z), a digit (0, 1, 2, 3, 4, 5, 6, 7, 8, or 9), an underscore (_) or a non-readable character. Examples are _n, act, %783, Second
• After the first character (letter, digit, underscore, or symbol), the name can have combinations of underscores, letters, digits, or symbols. Examples are _n24, act_52_t
• A name cannot include space, that is, empty characters. If you want to use a name that is made of various words, start the name with an opening square bracket and end it with a closing square bracket. Example are [Full Name] or [Date of Birth]

Because of the flexibility of SQL, it can be difficult to maintain names in a database. Based on this, there are conventions we will use for our objects. In fact, we will adopt the rules used in C/C++, C#, Pascal, Java, and Visual Basic, etc. In our databases:

• A name will start with either a letter (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, z, A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, or Z) or an underscore
• After the first character, we can use any combination of letters, digits, or underscores
• A name will not start with two underscores
• A name will not include one or more empty spaces. That is, a name will be made in one word
• If the name is a combination of words, at least the second word will start in uppercase. Examples are dateHired, _RealSport, FullName, or DriversLicenseNumber

The command to create a database in SQL uses the following formula:

CREATE DATABASE DatabaseName

The CREATE DATABASE (remember that SQL is not case-sensitive, even when you include it in a C++ statement) expression is required.

The DatabaseName factor is the name that the new database will carry. Although SQL is not case-sensitive, as a C++ programmer, you should make it a habit to be aware of the cases you use to name your objects.

As done in C#, every statement in SQL can be terminated with a semi-colon. Based on this, the above formula would be:

CREATE DATABASE DatabaseName;

To create a database with code, simply pass a CREATE DATABASE statement (including the name of the database) to a MySqlCommand object.

Practical Learning: Creating a Database

1. Start Microsoft Visual C++ .NET or Microsoft Visual Studio .NET and create a new Windows Forms Application named MySQLBCR1
2. In the Solution Explorer, right-click References and click Add Reference...
3. In the Add Reference dialog box, click Browse...
4. In the Select Component dialog box, locate the folder where your MySQL Connector Net is installed and display it in the Look In combo box
5. Double-click bin followed by your version of the .NET Framework
    
   
6. Click MySql.Data.dll
    
   
7. Click Open
    
   
8. After making sure that the assembly has been selected, in the Add Reference dialog box, click OK
9. Add a Button to the form and change its properties as follows:
   (Name): btnCreateDB
   Text:     Create Database
    
   
10. Double-click the Create Database button
11. In the top section of the file, under the other using namespace lines, type
    using namespace MySql::Data::MySqlClient
12. Implement the Click event of the button as follows:
     

    #pragma once namespace MySQLBCR1 { using namespace System; using namespace System::ComponentModel; using namespace System::Collections; using namespace System::Windows::Forms; using namespace System::Data; using namespace System::Drawing; using namespace MySql::Data::MySqlClient; . . . No Change private: System::Windows::Forms::Button * btnCreateDB; . . . No Change private: System::Void btnCreateDB_Click(System::Object * sender, System::EventArgs * e) { MySqlConnection *conDatabase = new MySqlConnection(S"Data Source=localhost;Persist Security Info=yes"); MySqlCommand *cmdDatabase = new MySqlCommand(S"CREATE DATABASE CarRental1;", conDatabase); conDatabase->Open(); cmdDatabase->ExecuteNonQuery(); conDatabase->Close(); } }; }

13. Execute the application
14. Click the Create Database button
15. Close the form and return to your programming environment
16. To allow the user to specify the name of the database, change the design of the form as follows:
     

    Control Name Text Label   New Database: TextBox txtNewDatabase   Button btnCreateDB Create

17. Double-click the Create button and change its code as follows:
     

    private: System::Void btnCreateDB_Click(System::Object * sender, System::EventArgs * e) { String *strDatabase = this->txtNewDatabase->Text; if( strDatabase->Equals(S"") ) return; String *strConnection = String::Concat(S"CREATE DATABASE ", strDatabase, S";"); MySqlConnection *conDatabase = new MySqlConnection(S"Data Source=localhost;Persist Security Info=yes"); MySqlCommand *cmdDatabase = new MySqlCommand(strConnection, conDatabase); conDatabase->Open(); cmdDatabase->ExecuteNonQuery(); conDatabase->Close(); txtNewDatabase->Text = ""; txtNewDatabase->Focus(); }

18. Execute the application
19. Type a string in the New Database text box and click Create
20. Close the form and return to your programming environment

Database Maintenance

Deleting a Database

If you have created a database but don't need it anymore, you can delete it. To delete a database, you use the DROP DATABASE instruction followed by the name of the database. The formula used is:

DROP DATABASE DatabaseName

Before deleting a database in SQL, you must make sure the database is not being used or accessed by some one else or by another object.

Practical Learning: Deleting a Database