MySql 存储过程 IN、OUT 和 INOUT 参数的 C# 代码示例
4.29/5 (9投票s)
一篇关于如何向 MySql 存储过程传入和传出参数的文章。
致谢
在我搜索的所有文章中，这两篇最有帮助。它们没有提供解决方案，但有所帮助。
Freedom Culture http://freedomculture.wordpress.com/2007/05/23/mysql-50-stored-procedure-programming-tutorial/[^]Herong 的 SQL 教程笔记 http://www.herongyang.com/sql/proc_sql_3.html[^]
测试环境
MySql 版本 5.0.34
Connector/NET 版本 5.2.6
Microsoft Visual Studio 2005
引言
我有一个应用程序，它有许多相关联的表，其中向主表的插入需要向相关表进行插入。我知道有很多方法可以做到这一点，但我需要将这些代码封装在存储过程中。
这是一个 C# 程序和类，旨在理解和测试如何向 MySql 存储过程传入和传出参数。随附的源代码演示了如何实现这一点。
我写这篇文章是希望其他人不必花三天时间搜索这个答案。
早期解决方案
和其他所有事情一样，你需要先了解问题，然后才能找到解决方案。我有一些问题，但在文档中找不到答案。
- 问题
- 何时何地在参数中使用“@”或“?”？
- 参数的顺序重要吗？
- 如何在 C# 代码中执行存储过程？
- 调用 ExecuteNonQuery 还是 ExecuteScalar？
“@”符号可以在存储过程的 SET 或 DECLARE 语句中使用。它用于用户定义变量，不应与参数混淆。该变量在其声明的 BEGIN...END 块内有效。使用“@”来自 MySql 的旧版本，现在在声明局部用户变量时不再需要使用它。
“?”符号用于指定要添加到 MySqlCommand 参数集合的参数名称。
例如
cmd.Parameters.AddWithValue("?dsply", asAString);
将参数添加到参数集合的顺序无关紧要。参数集合是一个 HASH 表，可以通过参数名称进行索引。
示例
cmd.Parameters["?dsply"].Direction = ParameterDirection.Input;这设置了 "?dsply" 参数的 Direction 值。
为了在代码中执行存储过程，你需要几样东西
- 过程的名称。
- 设置参数集合。
- 设置命令类型。
- 执行命令。
这是一个精简版的意思。
public string nsert2 = "spInsert2;"; ... MySqlConnection conn = new MySqlConnection(ConnectString); MySqlCommand cmd = new MySqlCommand(nsert2, conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; ... cmd.Parameters.AddWithValue("?usr", (object)"tony wv"); // cast if a constant cmd.Parameters["?usr"].Direction = ParameterDirection.Input; ... conn.Open(); retval = (Int64)cmd.ExecuteNonQuery(); ... conn.Close();你通常会使用 ExecuteNonQuery 来执行过程。如果过程从表中选择单个值，你可以使用类似以下内容
string name = (string) cmd.ExecuteScalar();单个值将位于 name 中。
根据我找到的文章数量，我不是唯一有疑问的人。
寻求答案
我经常发现，获得答案的最佳方法是编写一些代码并在调试器下运行它。所以这就是这段代码的目的。在此过程中，我阅读了大量 MySql 源代码。
类 Form1 是在 Visual Studio 中创建新项目时生成的 Windows 窗体类向导。它被更改以处理窗体上的简单控件。
类 clsKenTest 是实现解决方案的类。
在此测试的初始设计期间，我决定需要几个存储过程来回答所有问题。我还希望有执行插入、删除和更新的过程。
细节
此处提供的代码代表了一个存储过程以及使其工作所需的代码。它被呈现出来，以便所有需要的步骤都清晰明了。
这是一个存储过程，代码仅针对该过程。
存储过程如下所示。请注意没有“'@'”符号和“'?'”符号。没有 DELIMITER $$ 或 DELIMITER // 语句，因为这些语句是从 C# 代码发送的。这些语句仅在你通过 MySql 命令行或在 SQL 文件输入中输入命令时才需要。
过程 spInsert2 演示了 IN 和 OUT 参数的使用
////// Create Insert procedure for table 2 /// public string procI = "CREATE PROCEDURE `test`.`spInsert2` " + "(IN usr varchar(60), IN dsply varchar(250), OUT lastinsertid int, " + "OUT cat varchar(260), OUT rows int) " + "BEGIN " + "insert into `test`.`kentest2` (`ID`,`login`,`name`,`latest_acc`) " + "values (NULL, usr, dsply,NULL); " + "select LAST_INSERT_ID() into lastinsertid; " + "select CONCAT(usr,dsply) into cat; " + "select ROW_COUNT() into rows; " + "END;";
这里有几个语法问题。
- 数据库和表名用“`”（反引号）字符括起来。
- 有两个 IN 参数和三个 OUT 参数。
- “;”（分号）结束每个 SQL 语句。
- NULL 值用于 auto_increment 和 timestamp 字段。
- 函数调用 LAST_INSERT_ID、CONCAT 和 ROW_COUNT 在名称和左括号之间没有空格。
- 最后三个 select 语句将结果放入 OUT 参数中。
创建存储过程的用户已经设置了执行过程的权限。如果其他用户要使用该过程，则必须通过发出 grant EXECUTE on test.spInsert to 'demo'@'localhost' with grant option; 来 GRANT 他们权限。“demo”是我数据库中的一个用户名。
下面的代码是使整个事情工作所必需的。它主要关注处理参数。每个参数都必须添加到 MySqlCommand Parameter 集合中。每个参数都对应于存储过程中定义的参数。这就是使用“?”标记的地方。过程中有五个参数，并且必须向命令参数集合添加五个参数。
过程的参数名称为 usr, dsply, lastinsertid, cat, 和 rows。因此，你必须为每个参数创建一个单独的 MySqlParameter 并将其命名为 ?usr, ?dsply, ?lastinsertid, ?cat, 和 ?rows。
必须告知每个 MySqlParameter 类的数据类型、参数方向（IN、OUT、INOUT），并且必须为 IN 和 INOUT 分配一个值。执行存储过程后，你可以从 MySqlParameter 集合中检索 OUT 和 INOUT 参数的值。IN 和 OUT 参数的所有值都存储在命令参数集合中。设置方向时，方向是从存储过程的角度来看的。IN 参数设置为 Input，OUT 参数设置为 Output，INOUT 参数设置为 InputOutput。
稍后将介绍如何确定参数类型。
下载的源文件包含一个名为 clsKenTestVersion.cs 的文件。下面的代码来自该文件，并不是我选择的最终解决方案。此处将其作为使此工作所需的所有步骤的示例。我很惊讶一个存储过程需要这么多步骤。
请勿使用此代码。仅供示例// Ready to try executing the procedures MySqlConnection conn = new MySqlConnection(ConnectString); MySqlCommand cmd = new MySqlCommand(nsert2, conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; // For IN or INOUT you must provide an initial value. // Two ways to supply the input parameters. The three lines below // are the first way //cmd.Parameters.Add(new MySqlParameter("?usr", MySqlDbType.VarChar)); //cmd.Parameters["?usr"].Value = "tony nsert"; //cmd.Parameters["?usr"].Direction = ParameterDirection.Input; // -- // OR do it like this cmd.Parameters.AddWithValue("?usr", (object)"tony wv"); // cast if a constant cmd.Parameters["?usr"].Direction = ParameterDirection.Input; // -- // The value can also be a variable string asAString = "path\\to\\tony\'s\\data\\"; //cmd.Parameters.Add(new MySqlParameter("?dsply", MySqlDbType.VarChar)); //cmd.Parameters["?dsply"].Value = asAString; //cmd.Parameters["?dsply"].Direction = ParameterDirection.Input; // OR this way cmd.Parameters.AddWithValue("?dsply", asAString); cmd.Parameters["?dsply"].Direction = ParameterDirection.Input; cmd.Parameters.Add(new MySqlParameter("?lastinsertid", MySqlDbType.Int64)); cmd.Parameters["?lastinsertid"].Direction = ParameterDirection.Output; cmd.Parameters.Add(new MySqlParameter("?cat", MySqlDbType.VarChar)); cmd.Parameters["?cat"].Direction = ParameterDirection.Output; cmd.Parameters.Add(new MySqlParameter("?rows", MySqlDbType.Int32)); cmd.Parameters["?rows"].Direction = ParameterDirection.Output; try { conn.Open(); // this ALWAYS returns a 0 for this insert retval = (Int64)cmd.ExecuteNonQuery(); retval = (int)cmd.ExecuteNonQuery(); // insert second row for update // Now get the OUT parameters rows = (int)cmd.Parameters["?rows"].Value; lastinsertid = (Int64)cmd.Parameters["?lastinsertid"].Value; ans = (string)cmd.Parameters["?cat"].Value; // ans is ignored after this } catch (MySqlException ex) { return "Insert failed with: " + ex.Message; } finally { conn.Close(); } return "OK";
重要内容
目前，我将忽略所有 MySql 的底层细节，只关注与参数相关的内容。在执行过程之前，必须设置好参数。
对于 IN 参数，你可以为每个参数设置两行代码。
cmd.Parameters.AddWithValue("?dsply", asAString); cmd.Parameters["?dsply"].Direction = ParameterDirection.Input;
对于 OUT 参数，你可以为每个参数设置两行代码。
cmd.Parameters.Add(new MySqlParameter("?lastinsertid", MySqlDbType.Int64)); cmd.Parameters["?lastinsertid"].Direction = ParameterDirection.Output;
如你所见，设置一个过程很简单，但相当繁琐，因为每个参数都必须这样做。
对于 INOUT 参数，你可以为每个参数设置三行代码。
cmd.Parameters.Add(new MySqlParameter("?dsply", MySqlDbType.VarChar)); cmd.Parameters["?dsply"].Value = asAString; cmd.Parameters["?dsply"].Direction = ParameterDirection.InputOutput;
这是一个例子。“?dsply”没有设置为 INOUT 参数。
完成此操作后，你可以使用以下命令运行该过程
conn.Open();
retval = (Int64)cmd.ExecuteNonQuery();
conn.Close();
暂时忽略 retval。
检索结果
现在过程已经执行，可以访问 OUT 和 INOUT 参数。这是通过以下方式完成的
rows = (int)cmd.Parameters["?rows"].Value; lastinsertid = (Int64)cmd.Parameters["?lastinsertid"].Value; ans = (string)cmd.Parameters["?cat"].Value; // ans is ignored after this
OUT 和 INOUT 参数通过从命令参数集合中获取它们来放置在你的程序变量中。这些值是从过程中获得的。rows = ROW_COUNT(), lastinsertid = LAST_INSERT_ID(), 和 ans = CONCAT(usr,dsply) 都在过程中完成。
我应该注意到，在我的系统上，ROW_COUNT() 适用于 Update 和 Delete，但对于插入总是返回 -1。插入有效，所以我不知道为什么会这样。
请注意，参数值存储为对象，因此在检索它们时必须将它们强制转换为正确的数据类型。如果你不知道返回的类型，可以使用一个技巧来查找。
Object obj = cmd.Parameters["?lastinsertid"].Value; Type typ = obj.GetType(); lastinsertid = (Int64) obj;
在调试器中运行此代码，在最后一行设置一个断点。当它停止时，查看 typ，它会告诉你使用的类型转换。
结论一
上面的代码展示了如何实现这一点。如果你满足于这种方式，那么你可以从下载中的旧版本文件中复制代码，并且不需要阅读本文的其余部分。
这种机制对我来说似乎很繁琐，所以我创建了一个辅助类来处理存储过程。本文的其余部分将介绍这个类。
过程类
这个类处理存储过程。虽然你通常不会在同一个使用存储过程的代码中删除和创建存储过程，但该类支持这些操作。这个类让我可以将大部分 MySql 结构和细节从应用程序实际尝试做的事情中移开。对我来说，这使得代码更具可读性。
我确信还有其他（可能更好）的方法可以做到这一点。我随时欢迎提出建议。
Procedure 类旨在每个类实例处理一个存储过程。演示程序运行 5 个存储过程，因此 clsKenTest 构造函数执行以下操作
// Make one object per stored procedure spInsert = new Procedure("spInsert", ConnectString); spInsert2 = new Procedure("spInsert2", ConnectString); spUpdate = new Procedure("spUpdate", ConnectString); spDelete = new Procedure("spDelete", ConnectString); spInOut = new Procedure("spInOut", ConnectString);
构造函数使用字符串过程名称来构建删除过程命令并执行该过程。ConnectString 显然是与 MySql 通信所必需的。尽管构建了删除过程命令，但你必须调用 Drop() 方法才能运行它。
spInsert2 过程与前面示例中使用的过程相同，因此我将在这里使用它进行比较。
删除和创建过程通过以下方式完成
// -- Start insert test // -- Drop spInsert2 and then add it ans = spInsert2.Drop(); // drop the procedure if (!ans.Equals("OK")) return "Drop Procedure 2 failed with: " + ans; ans = spInsert2.Create(procI); // create the stored procedure if (!ans.Equals("OK")) return "Create Procedure 2 failed with: " + ans;
此代码执行与上述版本相同的事情，但对我来说似乎更容易使用。
// -- Set up parameters before running spInsert2. There are 5 parameters // For IN or INOUT you must provide an initial value. // -- // for an IN parameter do it like this spInsert2.Add("?usr", "tony wv"); // The value can also be a variable string asAString = "path\\to\\tony\'s\\data\\"; spInsert2.Add("?dsply", asAString); // adds an IN parameter // OUT parameters must know the data type. The program variable // for the output is selected after the procedure runs. spInsert2.AddOut("?lastinsertid", MySqlDbType.Int64); spInsert2.AddOut("?cat", MySqlDbType.VarChar); spInsert2.AddOut("?rows", MySqlDbType.Int64); // insert two rows ans = spInsert2.Execute(); // run the procedure ans = spInsert2.Execute(); // insert second row if (!ans.Equals("OK")) return "Insert failed with: " + ans; // Get the OUT data rows = (Int64) spInsert2.Get("?rows"); // Get ID of inserted row. (This is the auto_increment value assigned) lastinsertid = (Int64) spInsert2.Get("?lastinsertid"); // Test concatenating the usr and dsply into an OUT variable stringVal = (string) spInsert2.Get("?cat"); // -- End of insert test
这与 Intellisense 配合得很好。当我添加一个参数时，所有 3 个添加方法都会显示出来，并显示我需要提供的参数。
要查看的代码在 Procedure.cs 和 clsKenTest.cs 中。代码中有很多注释。
INOUT 参数的探究
这些代码几乎相同。以下是该过程
public string procIO = "CREATE PROCEDURE `test`.`spInOut` " + "(IN pkey int, INOUT chnge varchar(260)) " + "BEGIN " + "select CONCAT(chnge,CURRENT_USER()) into chnge; " + "END;";
这是一个简单的过程，有两个参数。它将当前用户名附加到传入的字符串中。
// Now run the INOUT procedure // Parameters can be in any order. // IN and INOUT must have values set spInOut.Add("?pkey", (UInt32) 2); // record 2 is all that is left in the table spInOut.AddInOut("?chnge", "The current user is: "); ans = spInOut.Execute(); // execute the procedure if (!ans.Equals("OK")) return "Execute INOUT Procedure failed with: " + ans; newval = (string) spInOut.Get("?chnge"); // works
这就是全部。希望这篇文章有所帮助。请随时提出任何改进意见或建议。