How can I call a mysql function using MysqlConnector?

Question

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.

Answer 1

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

Answer 2

It looks like you can use:

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

Answer 3

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

Answer 4

                //
                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)
                    {
                       ...
                    }
                }