Introduction to SQL and ADO.NET

Actions on a Microsoft SQL Server Database System  

Introduction

In the previous lesson, we learned different ways of connecting to a server. After establishing a connection, if you are successful, the database system becomes available to you and you can take actions, such as creating a database and/or manipulating data. An action you perform on the database server or on a database is carried by a object called a command.

To support the various actions that you can perform on a Microsoft SQL Server database, the .NET Framework provides the SqlCommand class. To use it, you can declare a pointer to SqlCommand using one of its constructors. If you want the studio to declare a SqlCommand variable for you, after creating a Windows Forms Application, in the Data section of the Toolbox, you can click the SqlCommand button and click the form. A SqlCommand object would be added to your form and you can configure it "visually" using the Properties window:

The SqlCommand class is equipped with four constructors. The default constructor allows you to initiate a command without specifying what action would be taken.The action to perform is created as a string statement. This action is represented by the SqlCommand::CommandText property which is of type String. If you want to use the default constructor, you can then create a string that would carry the action to perform. Once the string is ready, you can assign it the CommandText property. This would be done as follow:

SqlCommand *cmdSQL = new SqlCommand;
String *strCommandToExecute = S"Blah Blah Blah";
cmdSQL->CommandText = strCommandToExecute;

After creating the action that would be performed, you must specify what connection would carry it. To do this, you can first create a SqlConnection object. To provide it to the command, the SqlCommand class is equipped with a Connection property that is of type SqlConnection. After creating a SqlConnection object, to provide it to the command, you can assign it to the SqlCommand::Connection property.

Instead of declaring a SqlCommand variable and the command text separately, as an alternative, you can define the command text when declaring the SqlCommand variable. To do this, you can use the second constructor of the SqlCommand class. The syntax of this constructor is:

public: SqlCommand(String *cmdText);

Once again, after using this constructor, you must specify what connection would carry the action. To do this, you can assign a SqlConnection object to the Connection property of your SqlCommand.

Instead of assigning the SqlConnection to the SqlCommand::Connection property, you can specify what connection would carry the action at the same time you are creating the command. To specify the connection when declaring the SqlCommand variable, you can use the third constructor of this class. Its syntax is:

public: SqlCommand(String* cmdText, SqlConnection* connection);

The second argument to this constructor is an established connection you would have defined. If you had initiated the action using the default constructor of the SqlCommand class, you can assign a SqlConnection object to the Connection property of the SqlCommand class.

In the next sections and future lessons, we will study the types of commands that would be carried.

After establishing a connection and specifying what command needs to be carried, you can execute it. To support this, the SqlCommand class is equipped with the ExecuteNonQuery() method. Its syntax is:

public: virtual int ExecuteNonQuery();

This method doesn't take any argument. The SqlCommand object that calls it must have prepared a valid command.

In future lessons, we will see that there are other ways a SqlCommand object can execute commands.

In some cases, some actions take longer than others to execute. For this type of command, the compiler would keep trying to execute a command until successful. If there is a problem, this operation can take long or too long. You can specify how long the compiler should wait to try executing the command, again.

The SqlCommand::CommandTimeOut property allows you to specify the time to wait before trying to execute a command. The default value of this property is 30 (seconds). If you want a different value, assign it to your SqlCommand variable.

In this and the next few lessons, all of the commands we perform will be communicated as strings. When we study (stored) procedures, we will see other types of commands. To allow you to specify the type of command you want to perform, the SqlCommand class is equipped with the CommandType property, which is based on the CommandType enumerator.

The CommandType enumerator has three members: StoredProcedure, TableDirect, and Text. For a SqlCommand object, the default value is Text.

The Structured Query Language  

Introduction

The Structured Query Language, known as SQL, is a universal language used on various computer systems to create and manage databases.

SQL can be pronounced Sequel or S. Q. L. In our lessons, we will consider the Sequel pronunciation. For this reason, the abbreviation will always be considered as a word, which would result in “A SQL statement” instead of "An SQL statement". Also, we will regularly write, “The SQL” instead of “The SQL language, as the L already represents Language.

Like other non-platform specific languages such as C/C++, Pascal, or Java, the SQL you learn can be applied to various database systems. To adapt the SQL to Microsoft SQL Server, the company developed Transact-SQL as Microsoft's implementation of SQL. Transact-SQL is the language used internally by Microsoft SQL Server and MSDE. Although SQL Server highly adheres to the SQL standards, it has some internal details that may not be applied to other database systems like MySQL, Oracle, or even Microsoft Access, etc; although they too fairly conform to the standard.

The SQL we will learn and use here is Transact-SQL. In other words, we will assume that you are using Microsoft SQL Server as your platform for learning about databases. This means that, unless specified otherwise, most of the time, on this site, the word SQL refers to Transact-SQL or the way the language is implemented in Microsoft SQL Server.

As a computer language, the SQL is used to give instructions to an internal program called an interpreter. As we will learn in various sections, you must make sure you give precise instructions. Unlike C++ and XML as we have learned in the past, SQL is not case-sensitive. This means that CREATE, create, and Create mean the same thing. It is a tradition to write SQL's own words in uppercase. This helps to distinguish SQL instructions with the words you use for your database.

As we will learn in this and the other remaining lessons, you use the SQL by writing statements. To help you with this, Microsoft SQL Server provides the SQL Query Analyzer application. In the previous lesson, we saw different ways to open it. When it comes up, by default, it displays a blank window you can use to write your code:

An alternative to the SQL Query Analyzer is the SQL Server Enterprise manager. This application is more "visual" and is user-friendlier than SQL Query Analyzer. Still, we will learn that, in some cases, such as troubleshooting procedures or when performing data analysis, the SQL Server Enterprise Manager can offer some windows that allow you to write code.

When working in a Windows Forms Application, you can write the exact same code you would in the SQL Query Analyzer, once your code is ready, you can pass it to a SqlCommand object you would have created as we saw earlier. This would be done as follows:

private: System::Void SomeButton_Click(System::Object *  sender, System::EventArgs *  e)
{
    SqlConnection *conDatabase = new SqlConnection(S"Data Source=(local);Integrated Security=yes");
	 SqlCommand    *cmdDatabase = new SqlCommand(SQL Code, conDatabase);

	 conDatabase->Open();

	 cmdDatabase->ExecuteNonQuery();
	 conDatabase->Close();
}

Probably before using a database, you must first have one. If you are just starting with databases and you want to use one, Microsoft SQL Server ships with two databases ready for you. One of these databases is called Northwind and the other is called pubs.

Besides, or instead of, the Northwind and the pubs databases, you can create your own. A database is primarily a group of computer files that each has a name and a location. When you create a database using Microsoft SQL Server, it is located in the Drive:\Program Files\Microsoft SQL Server\MSSQL\Data folder.

In the previous lesson, we saw that there were different ways to connect to a server. In the same way, there are different ways to create a database.

 

SQL Server Enterprise Manager

To create a new database in SQL Server Enterprise Manager: In the left frame, you can right-click the server or the (local) node, position your mouse on New, and click Database... In the left frame, you can also right-click the Databases node and click New Database When the server name is selected in the left frame, on the toolbar of the window, you can click Action, position the mouse on New, and click Database... When the server name is selected in the left frame, you can right-click an empty area in the right frame, position your mouse on New, and click Database... When the Databases node or any node under it is selected in the left frame, on the toolbar, you can click Action and click New Database... When the Databases node or any node under is selected in the left frame, you can right-click an empty area in the right frame and click New Database... Any of these actions causes the Database Properties to display. You can then enter the name of the database. 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 Using the Database Wizard

Another technique you can use to create a database is by using the Database Wizard. There are two main ways you can launch the Database Wizard. In the left frame, when the server node or the Databases folder is selected, on the toolbar, you can click the Tools button and click Wizards. This causes the Select Wizard dialog box to display. In the Select Wizard dialog box, you can expand the Database node and click Create Database Wizard: After clicking Create Database Wizard, you can click OK. This would start a wizard where the first page is only used to start it and you can click Next. In the second page of the wizard and in the Database Name text box, you can specify the name you want for your database: After entering the name, you can click Next. In the third, the fourth, the fifth, and the sixth pages of the wizard, you can accept the default by clicking Next on each page: The last page of the wizard shows a summary of the database that will be created. If the information is not accurate, you can click the Back button and make the necessary changes. Once you are satisfied, you can click Finish. If the database is successfully created, you would receive a message box letting you know: You can then click OK.

Creating a Database Using the Query Analyzer

The command used 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. Although this is a requirement in many implementations of SQL, in Microsoft SQL Server, you can omit the semi-colon. Otherwise, the above formula would be CREATE DATABASE DatabaseName; Here is an example: To assist you with writing code, the SQL Query Analyzer includes sections of sample code that can provide placeholders. To access one these codes, on the main menu of SQL Query Analyzer, click File -> New... Then, in the General property page of the New dialog box, you can double-click a category to see the available options. An option is to click the arrow of the New Query button on the toolbar and position the mouse on a group. For example, if you are planning to create a database, you can position the mouse on Create Database and click Create Database Basic Template: An option from the menu fills the query window with code: You can then change the sections of code that need to be changed. Whenever necessary, we will show what you should change to customize the code generated by the wizard. In SQL Query Analyzer, to execute a statement, you can click the Execute Query button or press F5. When the database has been created, two lines of messages in the bottom section of the Query window would let you know:

 

Database Creation With Microsoft Visual Studio .NET

Introduction

As reviewed in previous lessons, there is a tied connection between Microsoft SQL Server and Microsoft Visual Studio .NET. This allows you to perform many routine operations inside of MS Visual Studio .NET. To create a database in Microsoft Visual Studio .NET, first open the Server Explorer window, expand the Servers, the computer name, the SQL Servers, and the server name nodes, then click New Database: This would display the Create Database dialog box. You can first type the name of the new database, then select the type of authentication, following the same rules we reviewed in the previous lesson: Once you are ready, you can click OK. Once the database has been created, its name would appear in the list of databases of the server.

Creating a Database Programmatically

To create a database with code, simply pass a CREATE DATABASE statement (including the name of the database) to a SqlCommand 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 BCR3
2. Add a Button to the form and change its properties as follows:
   (Name): btnCreateDB
   Text:     Create Database
    
   
3. Double-click the Create Database button
4. In the top section of the file, make sure the System::Data namespace was included. Add the System::Data::SqlClient namespace
5. Implement the Click event of the button as follows:
    

   #pragma once namespace BCR3 { 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 System::Data::SqlClient; . . . No Change public __gc class Form1 : public System::Windows::Forms::Form { public: . . . No Change private: System::Windows::Forms::Button * btnCreateDB; . . . No Change private: System::Void btnCreateDB_Click(System::Object * sender, System::EventArgs * e) { SqlConnection *conDatabase = new SqlConnection(S"Data Source=(local);Integrated Security=yes"); SqlCommand *cmdDatabase = new SqlCommand(S"CREATE DATABASE BCR1;", conDatabase); conDatabase->Open(); cmdDatabase->ExecuteNonQuery(); conDatabase->Close(); } }; }

6. Execute the application
7. Click the Create Database button
8. Close the form and return to your programming environment
9. Display the Server Explorer. Expand the Servers node, the name of the server (in lowercase), and the SQL Servers node
10. Right-click the name of the server (in uppercase) and click Refresh. Notice a new database named BCR1
11. 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

12. 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";"); SqlConnection *conDatabase = new SqlConnection(S"Data Source=(local);Integrated Security=yes"); SqlCommand *cmdDatabase = new SqlCommand(strConnection, conDatabase); conDatabase->Open(); cmdDatabase->ExecuteNonQuery(); conDatabase->Close(); }

13. Save all

Database Maintenance

Renaming a Database

Database maintenance consists of renaming one database or removing another. To change the name of a database, Transact-SQL provides sp_renamedb. (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 like cos or pow 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). The formula used would be: EXEC sp_renamedb 'ExistingName', 'NewName' The EXEC sp_renamedb expression is required. The ExistingName factor is the name of the database that you want to rename. The NewName factor is the name you want the database to have after renaming it. Here is an example of renaming a database in SQL Query Analyzer: EXEC sp_renamedb 'RentalCars', 'BethesdaCarRental GO To rename a table in a C++ code, pass the EXEC sp_renamedb code as string to a SqlCommand object and call the SqlCommand::ExecuteNonQuery() method.

Deleting a Database

If you have created a database but don't need it anymore, you can delete it. It is important to know, regardless of how you create a database, whether using SQL Server Enterprise Manager, SQL Query Analyzer, the Command Prompt, or Visual Studio .NET, every database can be access by any of these tools and you can delete any of the databases using any of these tools. As done with creating a database, every tool provides its own means. To delete a database in SQL Server Enterprise Manager, locate it in either the left or the right frames, right-click it and click Delete. You would receive a message box asking for a confirmation. If you still want to delete the database, you can click Yes. To delete a database in SQL Query Analyzer, 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

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 = this->txtDeleteDB->Text; if( strDatabase->Equals(S"") ) return; if( strDatabase->Equals(S"master") ) { MessageBox::Show(S"You are not allowed to delete the \"master\" database"); return; } if( strDatabase->Equals(S"model") ) { MessageBox::Show(S"You are not allowed to delete the \"model\" database"); return; } if( strDatabase->Equals(S"msdb") ) { MessageBox::Show(S"You are not allowed to delete the \"msdb\" database"); return; } if( strDatabase->Equals(S"tempdb") ) { MessageBox::Show(S"You are not allowed to delete the \"tempdb\" database"); return; } if( strDatabase->Equals(S"Northwind") ) { MessageBox::Show(S"Even though you can, don't delete the Northwind database"); return; } if( strDatabase->Equals(S"pubs") ) { MessageBox::Show(S"Even though you can, don't delete the \"pubs\" database"); return; } String *strConnection = String::Concat(S"DROP DATABASE ", strDatabase, S";"); SqlConnection *conDatabase = new SqlConnection(S"Data Source=(local);Integrated Security=yes"); SqlCommand *cmdDatabase = new SqlCommand(strConnection, conDatabase); conDatabase->Open(); cmdDatabase->ExecuteNonQuery(); conDatabase->Close(); this->txtDeleteDB->Text = S""; }

3. Execute the application
4. In the lower text box, type BCR1 and click Delete
5. Again, in the lower text box, type Keba Na Matraque and click Delete
    
   
6. Notice that you receive an error
7. Click Quit and access the Code Editor. Change both events 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"IF EXISTS (SELECT * " S"FROM master..sysdatabases " S"WHERE name = N'", strDatabase, S"')" S"DROP DATABASE ", strDatabase, S";" S"CREATE DATABASE ", strDatabase, S";"); SqlConnection *conDatabase = new SqlConnection(S"Data Source=(local);Integrated Security=yes"); SqlCommand *cmdDatabase = new SqlCommand(strConnection, conDatabase); conDatabase->Open(); cmdDatabase->ExecuteNonQuery(); conDatabase->Close(); this->txtNewDatabase->Text = S""; } private: System::Void btnDeleteDB_Click(System::Object * sender, System::EventArgs * e) { String *strDatabase = this->txtDeleteDB->Text; if( strDatabase->Equals(S"") ) return; if( strDatabase->Equals(S"master") ) { MessageBox::Show(S"You are not allowed to delete the \"master\" database"); return; } if( strDatabase->Equals(S"model") ) { MessageBox::Show(S"You are not allowed to delete the \"model\" database"); return; } if( strDatabase->Equals(S"msdb") ) { MessageBox::Show(S"You are not allowed to delete the \"msdb\" database"); return; } if( strDatabase->Equals(S"tempdb") ) { MessageBox::Show(S"You are not allowed to delete the \"tempdb\" database"); return; } if( strDatabase->Equals(S"Northwind") ) { MessageBox::Show(S"Even though you can, don't delete the Northwind database"); return; } if( strDatabase->Equals(S"pubs") ) { MessageBox::Show(S"Even though you can, don't delete the \"pubs\" database"); return; } String *strConnection = String::Concat(S"IF EXISTS (SELECT * " S"FROM master..sysdatabases " S"WHERE name = N'", strDatabase, S"')" S"DROP DATABASE ", strDatabase, S";"); SqlConnection *conDatabase = new SqlConnection(S"Data Source=(local);Integrated Security=yes"); SqlCommand *cmdDatabase = new SqlCommand(strConnection, conDatabase); conDatabase->Open(); cmdDatabase->ExecuteNonQuery(); conDatabase->Close(); this->txtDeleteDB->Text = S""; } private: System::Void btnClose_Click(System::Object * sender, System::EventArgs * e) { Close(); }

8. Execute the application
9. After using it, close the form

Accessories for SQL Code Writing

The Current Database

While writing code in a Query Window of the SQL Query Analyzer, you should always know what database you are working on, otherwise you may add code to the wrong database. To programmatically specify the current database, type the USE keyword followed by the name of the database. Here is an example: USE Northwind

The End of a Statement

In SQL, after writing a statement, you can end it with a semi-colon. In fact, many SQL environments require the semi-colon. If you want to execute many statements in the SQL Query Analyzer, you can write them once. Here is an example: CREATE DATABASE CountriesStatistics USE CountriesStatistics In this case, you are asking SQL Server to create a database and then to make it the current database. This statement will not work: The problem here is that, when the interpreter encounters these two lines, it would execute all of them at once and in fact would treat both lines as one expression. When the statements are executed, some of them must come after others. To separate statements, that is, to indicate when a statement ends, you can/must use the GO keyword (in reality and based on SQL standards, it is the semi-colon that would be more relevant, but the Microsoft SQL Server interpreter accepts GO as the end of a statement). Therefore, the above two statements can be performed separately but with one execution as follows: CREATE DATABASE CountriesStatistics GO USE CountriesStatistics GO This time, when you execute the code, it would work fine:

Comments

A comment is text that an interpreter would not consider as code. As such, a comment is written any way you like. What ever it is made of would not be read.

Transact-SQL supports two types of comments. The C++ style of comment that starts with /* and ends with */ can be used in SQL. To apply it, start a line with /*, include any kind of text you like, on as many lines as you want. To close the commented section, type */. Here is an example of a line of comment:

-- =============================================
-- Database: MotorVehicleDivision
-- =============================================
/* First find out if the database we want to create exists already */
IF EXISTS (SELECT * 
	   FROM   master..sysdatabases 
	   WHERE  name = N'MotorVehicleDivision')
	DROP DATABASE MotorVehicleDivision
GO

CREATE DATABASE MotorVehicleDivision
GO

A comment can also spread on more than one line, like a paragraph. Here is an example:

-- =============================================
-- Database: MotorVehicleDivision
-- =============================================

/* First find out if the MotorVehicleDivision database we 
   want to create exists already.
   If that database exists, we don't want it anymore. So,
   delete it from the system. */

IF EXISTS (SELECT * 
	   FROM   master..sysdatabases 
	   WHERE  name = N'MotorVehicleDivision')
	DROP DATABASE MotorVehicleDivision
GO

CREATE DATABASE MotorVehicleDivision
GO

Transact-SQL also supports the double-dash comment. This comment applies to only one line of text. To use it, start the line with --. Anything on the right side of -- is part of a comment and would not be considered as code. Here is an example:

-- =============================================
-- Database: MotorVehicleDivision
-- =============================================

/* First find out if the MotorVehicleDivision database we 
   want to create exists already.
   If that database exists, we don't want it anymore. So,
   delete it from the system. */

IF EXISTS (SELECT * 
	   FROM   master..sysdatabases 
	   WHERE  name = N'MotorVehicleDivision')
	DROP DATABASE MotorVehicleDivision
GO

-- Now that the database is not in the system, create it
CREATE DATABASE MotorVehicleDivision
GO