文档主页
MySQL Connector/NET 开发者指南
相关文档
MySQL Connector/NET 发行说明
下载本手册
PDF (US Ltr) - 1.3Mb
PDF (A4) - 1.3Mb
MySQL Connector/NET 开发者指南  /  Connector/NET 编程  /  创建和调用存储过程

5.5 创建和调用存储过程

存储过程是存储在服务器中的一组 SQL 语句。客户端对存储过程进行一次调用，传递可以影响过程逻辑和查询条件的参数，而不是发出单独的硬编码 SQL 语句。

在以下情况下，存储过程特别有用：

  • 存储过程可以充当 API 或抽象层，允许多个客户端应用程序执行相同的数据库操作。应用程序可以用不同的语言编写，并在不同的平台上运行。应用程序不需要对表名、列名、复杂查询等进行硬编码。当您扩展和优化存储过程中的查询时，所有调用该过程的应用程序都会自动获得好处。

  • 当安全性至关重要时，存储过程可以防止应用程序直接操作表，甚至不知道表名和列名等详细信息。例如，银行使用存储过程来执行所有常见操作。这提供了一个一致且安全的环境，并且过程可以确保每个操作都被正确记录。在这种设置中，应用程序和用户无法直接访问数据库表，而只能执行特定的存储过程。

本节不提供有关创建存储过程的详细信息。有关此类信息，请参阅使用存储例程。

创建存储过程

可以使用各种工具在 MySQL 中创建存储过程，例如：

  • mysql 命令行客户端

  • MySQL Workbench

  • MySqlCommand 对象

与命令行和 GUI 客户端不同，在使用 MySqlCommand 类在 Connector/NET 中创建存储过程时，不需要指定特殊的分隔符。例如，要创建一个名为 add_emp 的存储过程，请使用 CommandText 属性和默认命令类型（SQL 文本命令）在您的命令上下文中执行每个单独的 SQL 语句，该命令与服务器建立了打开的连接。

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

访问存储过程

命名存储过程后，您需要为存储过程中的每个参数定义一个 MySqlCommand 参数。IN 参数使用参数名称和包含值的 对象进行定义，OUT 参数使用参数名称和预期返回的数据类型进行定义。所有参数都需要定义参数方向。

要使用 Connector/NET 调用存储过程，您需要创建一个 MySqlCommand 对象，并将存储过程名称作为 CommandText 属性传递。然后将 CommandType 属性设置为 CommandType.StoredProcedure。定义参数后，您可以使用 MySqlCommand.ExecuteNonQuery() 方法调用存储过程。

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 支持通过 MySqlCommand 对象调用存储过程。可以通过使用 MySqlCommand.Parameters 集合将数据传入和传出 MySQL 存储过程。

调用存储过程后，可以使用 MySqlCommand.Parameters 集合的 .Value 属性检索输出参数的值。

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

当使用 MySqlCommand.ExecuteReader 调用存储过程，并且存储过程具有输出参数时，只有在关闭 ExecuteReader 返回的 MySqlDataReader 之后才会设置输出参数。

存储过程代码示例

以下 C# 代码示例演示了如何使用存储过程。此示例假定已提前创建了“employees”数据库：

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.");
        }
    }
}

以下代码显示了 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

上一页   首页   向上   下一页
相关文档
MySQL Connector/NET 发行说明
下载本手册
PDF (US Ltr) - 1.3Mb
PDF (A4) - 1.3Mb