26.2.4.4. Accessing Stored Procedures with MySQL Connector/NET
With the release of MySQL version 5 the MySQL server now
supports stored procedures with the SQL 2003 stored procedure
syntax.
A stored procedure is a set of SQL statements that can be
stored in the server. Once this has been done, clients don't
need to keep reissuing the individual statements but can refer
to the stored procedure instead.
Stored procedures can be particularly useful in situations
such as the following:
When multiple client applications are written in different
languages or work on different platforms, but need to
perform the same database operations.
When security is paramount. Banks, for example, use stored
procedures for all common operations. This provides a
consistent and secure environment, and procedures can
ensure that each operation is properly logged. In such a
setup, applications and users would not get any access to
the database tables directly, but can only execute
specific stored procedures.
MySQL Connector/NET supports the calling of stored procedures
through the MySqlCommand object. Data can
be passed in and our of a MySQL stored procedure through use
of the MySqlCommand.Parameters collection.
This section will not provide in-depth information on creating
Stored Procedures. For such information, please refer to
https://dev.mysql.com/doc/mysql/en/stored-procedures.html.
A sample application demonstrating how to use stored
procedures with MySQL Connector/NET can be found in the
Samples directory of your MySQL Connector/NET
installation.
26.2.4.4.2. Creating Stored Procedures from MySQL 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 the MySQL Query
Browser GUI client. Finally, stored procedures can
be created using the .ExecuteNonQuery
method of the MySqlCommand object:
[VB]
Dim conn As New MySqlConnection
Dim cmd As New MySqlCommand
conn.ConnectionString = "server=127.0.0.1;" _
& "uid=root;" _
& "pwd=12345;" _
& "database=test"
Try
conn.Open()
cmd.Connection = conn
cmd.CommandText = "CREATE PROCEDURE add_emp(" _
& "IN fname VARCHAR(20), IN lname VARCHAR(20), IN bday DATETIME, OUT empno INT) " _
& "BEGIN INSERT INTO emp(first_name, last_name, birthdate) " _
& "VALUES(fname, lname, DATE(bday)); SET empno = LAST_INSERT_ID(); END"
cmd.ExecuteNonQuery()
Catch ex As MySqlException
MessageBox.Show("Error " & ex.Number & " has occurred: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
[C#]
MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;
conn = new MySql.Data.MySqlClient.MySqlConnection();
cmd = new MySql.Data.MySqlClient.MySqlCommand();
conn.ConnectionString = "server=127.0.0.1;uid=root;" +
"pwd=12345;database=test;";
try
{
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "CREATE PROCEDURE add_emp(" +
"IN fname VARCHAR(20), IN lname VARCHAR(20), IN bday DATETIME, OUT empno INT) " +
"BEGIN INSERT INTO emp(first_name, last_name, birthdate) " +
"VALUES(fname, lname, DATE(bday)); SET empno = LAST_INSERT_ID(); END";
cmd.ExecuteNonQuery();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
It should be noted that, unlike the command-line and GUI
clients, you are not required to specify a special delimiter
when creating stored procedures in MySQL Connector/NET.
26.2.4.4.3. Calling a Stored Procedure from MySQL Connector/NET
To call a stored procedure using MySQL Connector/NET, create a
MySqlCommand object and pass the stored
procedure name as the .CommandText
property. Set the .CommandType property to
CommandType.StoredProcedure.
After the stored procedure is named, 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 datatype that is expected to be
returned. All parameters need the parameter direction defined.
After defining parameters, call the stored procedure by using
the MySqlCommand.ExecuteNonQuery() method:
[VB]
Dim conn As New MySqlConnection
Dim cmd As New MySqlCommand
conn.ConnectionString = "server=127.0.0.1;" _
& "uid=root;" _
& "pwd=12345;" _
& "database=test"
Try
conn.Open()
cmd.Connection = conn
cmd.CommandText = "add_emp"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("?lname", 'Jones')
cmd.Parameters("?lname").Direction = ParameterDirection.Input
cmd.Parameters.Add("?fname", 'Tom')
cmd.Parameters("?fname").Direction = ParameterDirection.Input
cmd.Parameters.Add("?bday", #12/13/1977 2:17:36 PM#)
cmd.Parameters("?bday").Direction = ParameterDirection.Input
cmd.Parameters.Add("?empno", MySqlDbType.Int32)
cmd.Parameters("?empno").Direction = ParameterDirection.Output
cmd.ExecuteNonQuery()
MessageBox.Show(cmd.Parameters("?empno").Value)
Catch ex As MySqlException
MessageBox.Show("Error " & ex.Number & " has occurred: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
[C#]
MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;
conn = new MySql.Data.MySqlClient.MySqlConnection();
cmd = new MySql.Data.MySqlClient.MySqlCommand();
conn.ConnectionString = "server=127.0.0.1;uid=root;" +
"pwd=12345;database=test;";
try
{
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "add_emp";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("?lname", "Jones");
cmd.Parameters["?lname"].Direction = ParameterDirection.Input;
cmd.Parameters.Add("?fname", "Tom");
cmd.Parameters["?fname"].Direction = ParameterDirection.Input;
cmd.Parameters.Add("?bday", DateTime.Parse("12/13/1977 2:17:36 PM"));
cmd.Parameters["?bday"].Direction = ParameterDirection.Input;
cmd.Parameters.Add("?empno", MySqlDbType.Int32);
cmd.Parameters["?empno"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
MessageBox.Show(cmd.Parameters["?empno"].Value);
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
Once the stored procedure is called, the values of output
parameters can be retrieved by using the
.Value property of the
MySqlConnector.Parameters collection.
