MySQL :: Connectors and APIs Manual :: 4.5.5 Creating and Calling Stored Procedures

Connectors and APIs Manual / ... / Creating and Calling Stored Procedures

4.5.5 Creating and Calling Stored Procedures

A stored procedure is a set of SQL statements that is stored in the server. Clients make a single call to the stored procedure, passing parameters that can influence the procedure logic and query conditions, rather than issuing individual hardcoded SQL statements.

Stored procedures can be particularly useful in situations such as the following:

Stored procedures can act as an API or abstraction layer, allowing multiple client applications to perform the same database operations. The applications can be written in different languages and run on different platforms. The applications do not need to hardcode table and column names, complicated queries, and so on. When you extend and optimize the queries in a stored procedure, all the applications that call the procedure automatically receive the benefits.
When security is paramount, stored procedures keep applications from directly manipulating tables, or even knowing details such as table and column names. 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.

This section does not provide in-depth information on creating stored procedures. For such information, see Using Stored Routines.

Creating a Stored Procedure

Stored procedures in MySQL can be created using a variety of tools, such as:

The mysql command-line client
MySQL Workbench
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 using the MySqlCommand class. For example, to create a stored procedure named add_emp, use the CommandText property with the default command type (SQL text commands) to execute each individual SQL statement in the context of your command that has an open connection to a server.

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();

Accessing a Stored Procedure

After the stored procedure is named, you define 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.

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 defining the parameters, you call the stored procedure by using the MySqlCommand.ExecuteNonQuery() method.

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();

Connector/NET supports the calling of stored procedures through the MySqlCommand object. Data can be passed in and out of a MySQL stored procedure through use of the MySqlCommand.Parameters collection.

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

Console.WriteLine("Employee number: "+cmd.Parameters["@empno"].Value);
Console.WriteLine("Birthday: " + cmd.Parameters["@bday"].Value);

Note

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

Stored Procedure Code Example

The following C# code example demonstrates the use of stored procedures. This example assumes the 'employees' database was created in advance:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;
namespace UsingStoredProcedures
{
    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

PREV HOME UP NEXT