MySQL Connector/NET Developer Guide
When a connection has been established with the MySQL database,
the next step is do carry out the desired database operations.
This can be achieved through the use of the
MySqlCommand
object.
You will see how to create a 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.
Once a MySqlCommand
object has been created,
you will 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.
The following code demonstrates how this could be done.
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 command object. Once 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 running its Close
method on it.
In the next example, you will see 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 command object created and the
ExecuteNonQuery
method called on the command
object. You can access your MySQL database with the
mysql command interpreter and verify that the
update was carried out correctly.
Finally, you will see how the ExecuteScalar
method can be used to return a single value. Again, this is
straightforward, as a MySqlDataReader
object is
not required to store results, a simple variable will do. The
following code illustrates how to use
ExecuteScalar
:
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 command object.