How to call a mySQL stored function in C#?

I'd like to call a stored function in C#. I need articles and some examples for th开发者_如何学JAVAis.

It's almost identical to how you would call a SQL Server Stored Procedure:

using(MySqlConnection conn = new MySqlConnection(connString))
{
    MySqlCommand command = new MySqlCommand("spSomeProcedure;", conn);
    command.CommandType = System.Data.CommandType.StoredProcedure;

    // Add your parameters here if you need them
    command.Parameters.Add(new MySqlParameter("someParam", someParamValue));

    conn.Open();

    int result = (int)command.ExecuteScalar();
}

http://forums.asp.net/p/988462/1278686.aspx

    MySqlCommand cmd = new MySqlCommand("DeleteMessage", new MySqlConnection(GetConnectionString()));
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new MySqlParameter("param1", MessageItem.Entry_ID));
    cmd.Connection.Open();
    int i = cmd.ExecuteNonQuery();
    cmd.Connection.Close();

Stored routines

Stored functions and stored procedures are called in different ways.

Stored function is used as regular function in SQL statement. For example

SELECT id, title,  my_function(price) FROM table

Stored procedures are called using CALL statement.

CALL my_procedure(1,2,'title');

I don't know C#, so probably you can use MySqlCommand class to call stored procedures, but you can't use it to call stored functions.

I actually couldn't get the other methods suggested to return a value. I ended up creating a string to call the function and then executed that string with .ExecuteScalar:

MySqlTransaction mySqlTransaction = testDataMySqlConnection.BeginTransaction();

mySqlCommand = new MySqlCommand
    {
    Connection = testDataMySqlConnection,
    CommandText = "SELECT sf_UnitsAttempted('" + ... + ");",
    CommandType = CommandType.Text
    };

var f = (float)mySqlCommand.ExecuteScalar();
mySqlCommand.Dispose();
return f;

I know the question is about returning from a stored function, and Justin's answer here covers that. I wanted to add that if you wanted to return a DataTable from a stored procedure instead, you can do it using a DataAdapter:

// using MySql.Data.MySqlClient; // remember to include this

/* Helper method that takes in a Dictionary list of parameters, 
   and returns a DataTable. 
   The connection string is fetched from a resources file. */
public static DataTable ExecuteProc(string procedureName, Dictionary<string,object> parameterList)
{
    DataTable outputDataTable;

    using (MySqlConnection MySqlConnection = new MySqlConnection(Resources.SQL_CONNECTION_STRING))
    {
        using (MySqlCommand sqlCommand = new MySqlCommand(procedureName, MySqlConnection))
        {
            sqlCommand.CommandType = CommandType.StoredProcedure;

            if (parameterList != null)
            {
                foreach(string key in parameterList.Keys)
                {
                    string parameterName = key;
                    object parameterValue = parameterList[key];

                    sqlCommand.Parameters.Add(new MySqlParameter(parameterName, parameterValue));
                }
            }

            MySqlDataAdapter sqlDataAdapter = new MySqlDataAdapter(sqlCommand);
            DataSet outputDataSet = new DataSet();
            sqlDataAdapter.Fill(outputDataSet, "resultset");

            outputDataTable = outputDataSet.Tables["resultset"];
        }
    }

    return outputDataTable;
}