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.

Answer 2

You should set up a reference to the parameter

var param3 = new MySqlParameter();
param3.Direction = ParameterDirection.Output;
param3.DbType = // whatever the dbtype for int is or whatever you need.
param3.ParameterName = "param3";

com.Parameters.Add(param3);

in your try block, insert

var result = com.ExecuteReader(); // or com.ExecuteScalar();

after you execute that, your parameter should have the value populated and you should be able to also read the SP results (select).

var paramResult = param3.Value;

Reading the results of the SP can be done as reader or scalar.

// execute reader
while (result.Read()) {
    int value = result.GetInt32(0)); 
} /* read returned values in result */ 

// execute scalar
int value;
if (int.TryParse($"{result}", out value)) {
    /* do something with value */ 
}

/************************************************/

This block should get you where you need to go

        const string strcon = "whatevs";

        using (MySqlConnection con = new MySqlConnection(strcon))
        {
            const string sql = "login";

            MySqlCommand com = new MySqlCommand(sql, con);
            com.CommandType = CommandType.StoredProcedure;

            var stuffParam = new MySqlParameter("stuff", stuffValue);
            var passParam = new MySqlParameter("pass", passValue);
            var param3Param = new MySqlParameter();
            param3Param.ParameterName = "param3";
            param3Param.DbType = DbType.Int32;
            param3Param.Direction = ParameterDirection.Output;

            com.Parameters.Add(stuffParam);
            com.Parameters.Add(passParam);
            com.Parameters.Add(param3Param);

            try
            {
                var scalarResult = com.ExecuteScalar();

                // because you used select @param3 in your sp.
                int value;
                if (int.TryParse($"{scalarResult}", out value))
                {
                    //do something with value
                }

                //// because you used select @param3 in your sp.
                //var readerResult = com.ExecuteReader();

                //if (readerResult.Read())
                //{
                //    // 
                //    value = readerResult.GetInt32(0);
                //}

                int param3Returned;
                if(int.TryParse($"{param3Param.Value}", out param3Returned))
                {
                    // do something with param3Returned
                }
            }
            catch (Exception ex)
            {
                // do something with ex
            }
        }