Spec-Zone .ru
спецификации, руководства, описания, API

22.2.5.9. Accessing Stored Procedures with Connector/Net

MySQL server version 5 and up supports stored procedures with the SQL 2003 stored procedure syntax.

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:

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.

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 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.

22.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)        {            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.AddWithValue("@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 SystemImports System.Collections.GenericImports System.LinqImports System.TextImports System.DataImports MySql.DataImports MySql.Data.MySqlClientModule 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.AddWithValue("@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 SubEnd Module