Introduction to SQL and ADO.NET

In the previous lesson, we learned how to establish a 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 MySQL server database, the MySQL Connector/Net provides the MySqlCommand class. To use it, you can declare a variable of type MySqlCommand using one of its constructors.

The MySqlCommand 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 MySqlCommand.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:

MySqlCommand cmdSQL = new MySqlCommand();
string strCommandToExecute = "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 MySqlConnection object. To provide it to the command, the MySqlCommand class is equipped with a Connection property that is of type MySqlConnection. After creating a MySqlConnection object, to provide it to the command, you can assign it to the MySqlCommand.Connection property.

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

public MySqlCommand(string cmdText);

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

Instead of assigning the MySqlConnection 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 MySqlCommand variable, you can use the third constructor of this class. Its syntax is:

public MySqlCommand(string cmdText, MySqlConnection 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 MySqlCommand class, you can assign a MySqlConnection object to the Connection property of the MySqlCommand 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 MySqlCommand class is equipped with the ExecuteNonQuery() method. Its syntax is:

public int ExecuteNonQuery();

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

In future lessons, we will see that there are other ways a MySqlCommand 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 MySqlCommand.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 MySqlCommand 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 MySqlCommand 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 MySqlCommand object, the default value is Text.

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. Although MySQL highly adheres to the SQL standards, it has some internal details that may not be applied to other database systems like Microsoft SQL Server, Oracle, Paradox, or Microsoft Access, etc; although they too fairly conform to the standard.

The SQL we will learn and use here is the one implemented in MySQL. In other words, we will assume that you are using MySQL as your platform for learning about databases.

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, SQL is not case-sensitive. This means that the words 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 of this site, you use the SQL by writing statements. To do this, you can open the Command Prompt and write your code.

When working in a Windows application, you can write the exact same code you would at the Command Prompt. Once your code is ready, you can pass it to a MySqlCommand object you would have created as we saw earlier. This would be done as follows:

private void cmdLoad_Click(object sender, System.EventArgs e)
{
	MySqlConnection conDatabase = new 
MySqlConnection("Data Source=localhost;Persist Security Info=yes;");
	 MySqlCommand    cmdDatabase = new MySqlCommand(SQL Code, conDatabase);

	 conDatabase.Open();

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

In this example, the SQL Code factor represents a SQL statement you would write and pass it as a string.