询问调用存储过程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.

我花了大约7个小时试图通过反复试验找出答案。我在网上看到的所有例子要么行不通，要么不适用，要么只显示了我所寻找的一半。

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.

这就是我要求的:1。MYSQL中使用一个in参数和一个OUT参数的简单存储过程示例。2。使用c#从Visual Studio调用一个功能示例(非常重要，因为在线示例有时不能工作…)。文本调用或存储过程命令类型工作。3所示。AddWithValue已弃用。4所示。我希望out参数能正常工作。

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

如果这在MYSQL和visual studio中是不可能实现的，那么最好也知道。

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

对于这个特定的示例，MYSQL文档不够全面。还有，请不要使用Visual Studio或c#仇恨。

Thanks in advance! :)

提前谢谢!:)

EDIT:

编辑:

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

这就是我到目前为止所做的，而且它不工作!!

MYSQL side, using HeidiSQL:

MYSQL,使用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.

在c#中，我尝试得到这个参数。现在，这是经过多次迭代后的结果，在这里我去掉了函数原来的值，并将其归结为两个问题:1。输出参数不起作用，2。即使Visual studio传入参数，SQL拒绝识别它们。

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();
        }
    }

2 个解决方案

#1

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

在try块中插入

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

执行之后，您的参数应该填充了值，并且还应该能够读取SP结果(select)。

var paramResult = param3.Value;

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

读取SP的结果可以作为读取器或标量。

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

#2

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

我相信代码和图片说的比我想的要多。

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

c# DB层(DB层有conn作为连接字符串):

// 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:

c# GUI测试层:

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):

存储过程(取一个数字，乘以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):

调试视图(读:它工作。11 x7 = 77):

MySQL Connector 6.9.9.0 / Visual Studio 2015:

MySQL Connector 6.9.0 / Visual Studio 2015:

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

参见5.10.1使用来自连接器/Net的存储例程，年龄未知。

#1