I'm using:
MySqlCommand comHash = new MySqlCommand("MY_FUNCTION", con);
comHash.CommandType = CommandType.StoredProcedure; // ??
comHash.Parameters.AddWithValue("my_parameter", "value1");
comHash.????
I need return the value from function.
Add a comment
|
4 Answers
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");
Comments
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)
{
...
}
}
Comments
It looks like you can use:
dataAdapter = New MySqlDataAdapter(myCommand, myConn);
data = new datatable();
dataAdapter.Fill(data);
