MySQL Connector/NET Developer Guide
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.
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) { MySqlConnection conn = new MySqlConnection(); conn.ConnectionString = "server=localhost;user=root;database=employees;port=3306;password=******"; MySqlCommand cmd = new MySqlCommand(); try { Console.WriteLine("Connecting to MySQL..."); conn.Open(); cmd.Connection = conn; cmd.CommandText = "DROP PROCEDURE IF EXISTS add_emp"; cmd.ExecuteNonQuery(); cmd.CommandText = "DROP TABLE IF EXISTS emp"; cmd.ExecuteNonQuery(); cmd.CommandText = "CREATE TABLE emp (empno INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(20), last_name VARCHAR(20), birthdate DATE)"; cmd.ExecuteNonQuery(); 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 (MySqlException ex) { Console.WriteLine ("Error " + ex.Number + " has occurred: " + ex.Message); } conn.Close(); Console.WriteLine("Connection closed."); try { Console.WriteLine("Connecting to MySQL..."); conn.Open(); cmd.Connection = conn; cmd.CommandText = "add_emp"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@lname", "Jones"); cmd.Parameters["@lname"].Direction = ParameterDirection.Input; cmd.Parameters.AddWithValue("@fname", "Tom"); cmd.Parameters["@fname"].Direction = ParameterDirection.Input; cmd.Parameters.AddWithValue("@bday", "1940-06-07"); cmd.Parameters["@bday"].Direction = ParameterDirection.Input; cmd.Parameters.Add("@empno", MySqlDbType.Int32); cmd.Parameters["@empno"].Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); Console.WriteLine("Employee number: "+cmd.Parameters["@empno"].Value); Console.WriteLine("Birthday: " + cmd.Parameters["@bday"].Value); } catch (MySql.Data.MySqlClient.MySqlException ex) { Console.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message); } conn.Close(); Console.WriteLine("Done."); } } }
The following code shows the same application in Visual Basic:
Imports System Imports System.Collections.Generic Imports System.Linq Imports System.Text Imports System.Data Imports MySql.Data Imports MySql.Data.MySqlClient Module Module1 Sub Main() Dim conn As New MySqlConnection() conn.ConnectionString = "server=localhost;user=root;database=world;port=3306;password=******" Dim cmd As New MySqlCommand() Try Console.WriteLine("Connecting to MySQL...") conn.Open() cmd.Connection = conn cmd.CommandText = "DROP PROCEDURE IF EXISTS add_emp" cmd.ExecuteNonQuery() cmd.CommandText = "DROP TABLE IF EXISTS emp" cmd.ExecuteNonQuery() cmd.CommandText = "CREATE TABLE emp (empno INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(20), last_name VARCHAR(20), birthdate DATE)" cmd.ExecuteNonQuery() 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 Console.WriteLine(("Error " & ex.Number & " has occurred: ") + ex.Message) End Try conn.Close() Console.WriteLine("Connection closed.") Try Console.WriteLine("Connecting to MySQL...") conn.Open() cmd.Connection = conn cmd.CommandText = "add_emp" cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.AddWithValue("@lname", "Jones") cmd.Parameters("@lname").Direction = ParameterDirection.Input cmd.Parameters.AddWithValue("@fname", "Tom") cmd.Parameters("@fname").Direction = ParameterDirection.Input cmd.Parameters.AddWithValue("@bday", "1940-06-07") cmd.Parameters("@bday").Direction = ParameterDirection.Input cmd.Parameters.Add("@empno", MySqlDbType.Int32) cmd.Parameters("@empno").Direction = ParameterDirection.Output cmd.ExecuteNonQuery() Console.WriteLine("Employee number: " & cmd.Parameters("@empno").Value) Console.WriteLine("Birthday: " & cmd.Parameters("@bday").Value) Catch ex As MySql.Data.MySqlClient.MySqlException Console.WriteLine(("Error " & ex.Number & " has occurred: ") + ex.Message) End Try conn.Close() Console.WriteLine("Done.") End Sub End Module