Asking for legitimate example of calling stored procedure C#: MYSQL

Question

I've spent about 7 hours trying to figure this out by trial and error. All the online examples I have seen either don't work, or dont apply, or only show half of what Im looking for.

Here is what I'm asking for: 1. An example of a simple stored procedure in MYSQL using one IN parameter and one OUT parameter. 2. An example of a FUNCTIONING (really important, cause online examples havent worked sometimes...) call from Visual Studio, using C#. Either a text call or stored procedure command type work. 3. AddWithValue has been deprecated. 4. I would love to see the out parameter actually work.

If this is impossible with MYSQL and visual studio, that would be nice to know as well.

MYSQL documentation is not thorough enough for this particular example. And pls, no Visual Studio or C# hatred.

Thanks in advance! :)

EDIT:

This is what I have managed to do so far, and it DOES NOT WORK!!!

MYSQL side, using HeidiSQL:

CREATE DEFINER=`root`@`localhost` PROCEDURE `login`(IN `stuff` VARCHAR(50), IN `pass` VARCHAR(50), OUT `param3` INT)
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN

    set param3 = 0;
    set param3 = (select count(*) from users where username=stuff and userpassword=pass);
    select @param3;

END

And in C# side, I attempt to get this OUT parameter. Now, this is after multiple iterations, where I have gutted what the function used to be, and boiled it down to two issues: 1. The OUT parameters won't work, and 2. Even though Visual studio passes IN parameters, SQL refuses to recognize them.

protected void Login_Authenticate(object sender, AuthenticateEventArgs e)
    {
        using (MySqlConnection con = new MySqlConnection(strcon))
        {
            con.Open();

            MySqlCommand com = new MySqlCommand("CALL login(@stuff, @pass, @param3);", con);
            com.CommandType = CommandType.Text;
            com.Parameters.Add("@stuff", MySqlDbType.VarChar);
            com.Parameters["@stuff"].Value = Login.UserName;
            com.Parameters.Add("@pass", MySqlDbType.VarChar);
            com.Parameters["@pass"].Value = Login.Password;

            try
            {
                obj = com.ExecuteScalar();
                objparam = com.Parameters["param3"].Value;
                if (Convert.ToInt32(obj) != 0)
                {
                    Response.Redirect("Welcome.aspx");
                }
                else
                {
                    Login.PasswordRequiredErrorMessage = "invalid user name and password";
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            con.Close();
        }
    }

Answer 1

I believe the code and the pictures say more than I ever will.

C# DB Layer (DB Layer has conn as a connection string):

// Note: this is an instance (myDB in terms of the GUI Object)

using System.Data;
using MySql.Data.MySqlClient;
...
...
public long MultBySeven(long theNum)
{   // Call a Mysql Stored Proc named "multBy7"
    // which takes an IN parameter, Out parameter (the names are important. Match them)
    // Multiply the IN by 7 and return the product thru the OUT parameter

    long lParam = 0;
    using (MySqlConnection lconn = new MySqlConnection(connString))
    {
        lconn.Open();
        using (MySqlCommand cmd = new MySqlCommand())
        {
            cmd.Connection = lconn;
            cmd.CommandText = "multBy7"; // The name of the Stored Proc
            cmd.CommandType = CommandType.StoredProcedure; // It is a Stored Proc

            // Two parameters below. An IN and an OUT (myNum and theProduct, respectively)
            cmd.Parameters.AddWithValue("@myNum", theNum); // lazy, not specifying ParameterDirection.Input;
            cmd.Parameters.AddWithValue("@theProduct", MySqlDbType.Int32);
            cmd.Parameters["@theProduct"].Direction = ParameterDirection.Output; // from System.Data
            cmd.ExecuteNonQuery(); // let it rip
            Object obj = cmd.Parameters["@theProduct"].Value;
            lParam = (Int32)obj;    // more useful datatype
        }
    }
    return (lParam);
}

C# GUI Test Layer:

private void btnTestInOut_Click(object sender, EventArgs e)
{   // This GUI Layer call thru the use of a business object or data layer object (`myDB`)
    long localHere = myDB.MultBySeven(11);
}

Stored Procedure (take a number, multiply by 7):

DROP PROCEDURE IF EXISTS multBy7;
DELIMITER $
CREATE PROCEDURE multBy7
(   IN myNum INT,
    OUT theProduct INT
)
BEGIN
    SET theProduct=myNum*7;
END$
DELIMITER ;

Debug View (read: it works. 11x7=77):

MySQL Connector 6.9.9.0 / Visual Studio 2015:

See also 5.10.1 Using Stored Routines from Connector/Net, age unknown.