开发者

How can I call a mysql function using MysqlConnector?

I'm using:

MySqlCommand comHash = new MySqlCommand("MY_FUNCTION", con);
comHash.CommandType = CommandType.StoredProcedure; // ??
comHash.Parameters.AddWithValue("my_parameter", "valu开发者_StackOverflowe1");
comHash.????

I need return the value from function.


I was recently struggling with the same problem. Here is the solution I found.

MySqlCommand comHash = new MySqlCommand("SELECT MY_FUNCTION (?my_parameter)", con);
comHash.CommandType = CommandType.Text;
comHash.Parameters.AddWithValue("my_parameter", "value1");


Is this what you looking for?

static void CallProc()
{
 //Establish connection
MySqlConnection myConn = new MySqlConnection("user id=root;database=demobase;host=localhost");
myConn.Open();
//Set up myCommand to reference stored procedure 'myfunc'
MySqlCommand myCommand = new MySqlCommand("myfunc", myConn);
myCommand.CommandType = System.Data.CommandType.StoredProcedure;

//Create input parameter and assign a value
MySqlParameter myInParam = new MySqlParameter();
myInParam.Value = "Mark";
myCommand.Parameters.Add(myInParam);
myInParam.Direction = System.Data.ParameterDirection.Input;

//Create placeholder for return value
MySqlParameter myRetParam = new MySqlParameter();
myRetParam.Direction = System.Data.ParameterDirection.ReturnValue;
myCommand.Parameters.Add(myRetParam);

//Execute the function. ReturnValue parameter receives result of the stored function
myCommand.ExecuteNonQuery();
Console.WriteLine(myRetParam.Value.ToString());
myConn.Close();
}

The function used was:

CREATE FUNCTION demobase.myfunc(uname CHAR(20))
RETURNS CHAR(60)
RETURN CONCAT(uname,' works with server ',@@version);

Example extracted from here


Try this:

//
MySqlCommand myCommand = new MySqlCommand();
myCommand.CommandType = System.Data.CommandType.StoredProcedure;
myCommand.CommandText = "FNC_IsUserInSite";

MySqlParameter rv = new MySqlParameter();
rv.Direction = System.Data.ParameterDirection.ReturnValue;
rv.MySqlDbType = MySqlDbType.Int32;
rv.ParameterName = "@retval";
myCommand.Parameters.Add(rv);

myCommand.Connection = connection;
//

myCommand.ExecuteScalar();
object ret = myCommand.Parameters["@retval"].Value;

if (ret != null)
{
    if ((int)ret > 0)
    {
       ...
    }
}


It looks like you can use:

dataAdapter = New MySqlDataAdapter(myCommand, myConn);
data = new datatable();
dataAdapter.Fill(data);
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜