6.1.2 The MySqlCommand Object

When a connection has been established with the MySQL database, the next step enables you to perform database operations. This task can be achieved through the use of the MySqlCommand object.

After it has been created, there are three main methods of interest that you can call:

• ExecuteReader to query the database. Results are usually returned in a MySqlDataReader object, created by ExecuteReader.

• ExecuteNonQuery to insert, update, and delete data.

• ExecuteScalar to return a single value.

After the MySqlCommand object is created, you can call one of the previous methods on it to carry out a database operation, such as perform a query. The results are usually returned into a MySqlDataReader object, and then processed. For example, the results might be displayed as the following code example demonstrates.

using System;
using System.Data;

using MySql.Data;
using MySql.Data.MySqlClient;

public class Tutorial2
{
    public static void Main()
    {
        string connStr = "server=localhost;user=root;database=world;port=3306;password=******";
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            Console.WriteLine("Connecting to MySQL...");
            conn.Open();

            string sql = "SELECT Name, HeadOfState FROM Country WHERE Continent='Oceania'";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            MySqlDataReader rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                Console.WriteLine(rdr[0]+" -- "+rdr[1]);
            }
            rdr.Close();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }

        conn.Close();
        Console.WriteLine("Done.");
    }
}

When a connection has been created and opened, the code then creates a MySqlCommand object. Then the SQL query to be executed is passed to the MySqlCommand constructor. The ExecuteReader method is then used to generate a MySqlReader object. The MySqlReader object contains the results generated by the SQL executed on the MySqlCommand object. When the results have been obtained in a MySqlReader object, the results can be processed. In this case, the information is printed out by a while loop. Finally, the MySqlReader object is disposed of by invoking the Close method.

The next example shows how to use the ExecuteNonQuery method.

The procedure for performing an ExecuteNonQuery method call is simpler, as there is no need to create an object to store results. This is because ExecuteNonQuery is only used for inserting, updating and deleting data. The following example illustrates a simple update to the Country table:

using System;
using System.Data;

using MySql.Data;
using MySql.Data.MySqlClient;

public class Tutorial3
{
    public static void Main()
    {
        string connStr = "server=localhost;user=root;database=world;port=3306;password=******";
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            Console.WriteLine("Connecting to MySQL...");
            conn.Open();

            string sql = "INSERT INTO Country (Name, HeadOfState, Continent) VALUES ('Disneyland','Mickey Mouse', 'North America')";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }

        conn.Close();
        Console.WriteLine("Done.");
    }
}

The query is constructed, the MySqlCommand object created and the ExecuteNonQuery method called on the MySqlCommand object. You can access your MySQL database with mysql and verify that the update was carried out correctly.

Finally, you can use the ExecuteScalar method to return a single value. Again, this is straightforward, as a MySqlDataReader object is not required to store results, a variable is used instead. The following code illustrates how to use the ExecuteScalar method:

using System;
using System.Data;

using MySql.Data;
using MySql.Data.MySqlClient;

public class Tutorial4
{
    public static void Main()
    {
        string connStr = "server=localhost;user=root;database=world;port=3306;password=******";
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            Console.WriteLine("Connecting to MySQL...");
            conn.Open();

            string sql = "SELECT COUNT(*) FROM Country";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            object result = cmd.ExecuteScalar();
            if (result != null)
            {
                int r = Convert.ToInt32(result);
                Console.WriteLine("Number of countries in the world database is: " + r);
            }

        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }

        conn.Close();
        Console.WriteLine("Done.");
    }
}

This example uses a simple query to count the rows in the Country table. The result is obtained by calling ExecuteScalar on the MySqlCommand object.