paladin law paladin law - 4 months ago 30
MySQL Question

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

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

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
            }
        }
Comments