Home

SQL Databases

 

Database Creation

 

Introduction

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
 

Creating a Database

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 LearningPractical 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 LearningPractical Learning: Deleting a Database

  1. To allow the user to delete a database, change the design of the form as follows:
     
    Control Name Text
    Label   New Database:
    TextBox txtNewDatabase  
    Button btnCreateDB Create
    Label   Database
    TextBox txtDeleteDB  
    Button btnDeleteDB Delete
    Button btnClose Close
  2. Double-click the Delete button and implement its code as follows:
     
    private: System::Void btnDeleteDB_Click(System::Object *  sender, System::EventArgs *  e)
    {
    	 String *strDatabase = txtDeleteDB->Text;
    	if( strDatabase->Equals(S"") )
    		return;
    			
    	String *strDROP = String::Concat(S"DROP DATABASE ", strDatabase, S";");
    	MySqlConnection *conDatabase = new MySqlConnection(S"Data Source=localhost;"
    		                                                  S"Persist Security Info=yes;");
    	MySqlCommand *cmdDatabase = new MySqlCommand(strDROP, conDatabase);
    			
    	conDatabase->Open();
    	cmdDatabase->ExecuteNonQuery();
    	conDatabase->Close();
    			
    	txtDeleteDB->Text = "";
    	txtDeleteDB->Focus();
    }
  3. Return to the form and double-click the Close button
  4. Implement its event as follows:
     
    private: System::Void btnClose_Click(System::Object *  sender, System::EventArgs *  e)
    {
    	 Close();
    }
  5. Execute the application
  6. In the Database text box, type CarRental1 and click Delete
  7. After using it, to close the form, click the Close button
 

Previous Copyright © 2005-2010 FunctionX, Inc. Next