CODEWITHSUNDEEP
c#mysqlfunction
Zanoni
Zanoni

Reputation: 30978

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", "value1");
comHash.????

I need return the value from function.

Upvotes: 1

Views: 6762

Answers (4)

Mickael
Mickael

Reputation: 1

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

Upvotes: 0

ndoak
ndoak

Reputation: 101

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

Upvotes: 2

Canastro
Canastro

Reputation: 3009

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

Upvotes: 1

JasonOfEarth
JasonOfEarth

Reputation: 739

It looks like you can use:

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

Upvotes: -2

Related Questions