c# - MySql 存储过程不从 asp.net 执行

Question

我能够执行 MySQL sp。在服务器上它工作正常，但是当从 asp.net 调用时它不能正常工作。下面是存储过程:

CREATE PROCEDURE `GetCategoryForBackLinkID`(IN BLID int, OUT CatID int)
BEGIN
        SELECT CategoryID INTO CatID FROM backlink where BackLinkID = BLID;
END

下面是asp.net代码

MySqlCommand cmd1 = new MySqlCommand("GetCategoryForBackLinkID");
MySqlConnection con1 = new MySqlConnection();

//ConnectionStringSettings mySetting = ConfigurationManager.ConnectionStrings["linkbuilding1Entities3"];
con1.ConnectionString = "server=67.227.183.117;User Id=rammu1;Pwd=eframmu1;database=linkbuilding1;Persist Security Info=True";
cmd1.Connection = con1;
using (cmd1.Connection)
{
     cmd1.Connection.Open();

     MySqlParameter returnParameter1 = cmd1.Parameters.Add("BLID", MySqlDbType.Int16);
     returnParameter1.Direction = ParameterDirection.Input;
     returnParameter1.Value = maximumbacklinid;

     MySqlParameter returnParameter2 = cmd1.Parameters.Add("CatID", MySqlDbType.Int16);
     returnParameter2.Direction = ParameterDirection.Output;

     cmd1.ExecuteNonQuery();                   
     CategID = (Int16)returnParameter2.Value;

我得到的错误是

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GetCategoryForBackLinkID' at line 1.

这里可能有什么问题？

Answer 1

嗯，我不是 100% 确定，但看起来你需要评估你的 CommandType属性喜欢；

cmd1.CommandType = CommandType.StoredProcedure;

由于您使用存储过程，此属性默认为 Text。这就是为什么您的程序认为您的 "GetCategoryForBackLinkID" 字符串是有效的 SQL 查询，而不是存储过程。

When you set the CommandType property to StoredProcedure, you should set the CommandText property to the name of the stored procedure. The command executes this stored procedure when you call one of the Execute methods.

using(MySqlConnection con1 = new MySqlConnection(connString))
using(MySqlCommand cmd1 = con.CreateCommand())
{
   cmd1.CommandType = CommandType.StoredProcedure;
   // Add your parameter values.
   cmd1.ExecuteNonQuery();
   CategID = (int)returnParameter2.Value;
}