5.8.1 Using Stored Routines from Connector/Net

Stored procedures in MySQL can be created using a variety of
tools. First, stored procedures can be created using the
mysql command-line client. Second, stored
procedures can be created using MySQL Workbench. Finally, stored
procedures can be created using the
.ExecuteNonQuery method of the
MySqlCommand object.

Unlike the command-line and GUI clients, you are not required to
specify a special delimiter when creating stored procedures in
Connector/Net.

To call a stored procedure using Connector/Net, you create a
MySqlCommand object and pass the stored
procedure name as the .CommandText property.
You then set the .CommandType property to
CommandType.StoredProcedure.

After the stored procedure is named, you create one
MySqlCommand parameter for every parameter in
the stored procedure. IN parameters are
defined with the parameter name and the object containing the
value, OUT parameters are defined with the
parameter name and the data type that is expected to be
returned. All parameters need the parameter direction defined.

After defining the parameters, you call the stored procedure by
using the MySqlCommand.ExecuteNonQuery()
method.

Once the stored procedure is called, the values of the output
parameters can be retrieved by using the
.Value property of the
MySqlConnector.Parameters collection.

Note

When a stored procedure is called using
MySqlCommand.ExecuteReader, and the stored
procedure has output parameters, the output parameters are
only set after the MySqlDataReader returned
by ExecuteReader is closed.

The following C# example code demonstrates the use of stored
procedures. It assumes the database 'employees' has already been
created: