Oracle 5.0, Reference Manual

Page: 1929 / 3232

Connector/Net Programming
1909
Note
When you call a stored procedure, the command object makes an additional
SELECT
call to determine the parameters of the stored procedure. You must
ensure that the user calling the procedure has the
SELECT
[578] privilege on the
mysql.proc
table to enable them to verify the parameters. Failure to do this
will result in an error when calling the procedure.
This section will not provide in-depth information on creating Stored Procedures. For such information,
please refer to
http://dev.mysql.com/doc/mysql/en/stored-routines.html .
A sample application demonstrating how to use stored procedures with Connector/Net can be found in
the
Samples
directory of your Connector/Net installation.
20.2.5.9.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:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;
namespace UsingStoredRoutines
{
class Program
{
static void Main(string[] args)