MySQL 调用存储过程

1：存储过程返回值

        public void InputOutputParameters()
        {
            AdoHelper ado = AdoHelper.CreateHelper(DbProvideType.MySql);

            ado.ExecuteNonQuery(conn, CommandType.Text, "CREATE PROCEDURE spTest1( INOUT strVal VARCHAR(50), INOUT numVal INT, OUT outVal INT UNSIGNED ) " +
          "BEGIN  SET strVal = CONCAT(strVal,'ending'); SET numVal=numVal * 2;  SET outVal=99; END");

            IDataParameter[] param1 = new IDataParameter[]{
                ado.GetParameter("numVal", DbType.Int32, ParameterDirection.InputOutput),
                ado.GetParameter("strVal", DbType.String, ParameterDirection.InputOutput),
                ado.GetParameter("outVal", DbType.UInt64, ParameterDirection.Output)
            };
            //存储过程参数按照名称对应
            param1[1].Value = "beginning";
            param1[0].Value = 32;
            ado.ExecuteNonQuery(conn, CommandType.StoredProcedure, "spTest1", param1);
            Console.ReadLine();
        }

2：函数

        public void FunctionNoParams()
        {
            AdoHelper ado = AdoHelper.CreateHelper(DbProvideType.MySql);

            ado.ExecuteNonQuery(conn, CommandType.Text, "CREATE FUNCTION fnTest() RETURNS CHAR(50)" +
          " LANGUAGE SQL DETERMINISTIC BEGIN  RETURN \"Test\"; END");

            object obj = ado.ExecuteScalar(conn, CommandType.Text, "SELECT fnTest()");
            Console.ReadLine();
        }

        public void CallingStoredFunctionasProcedure()
        {
            AdoHelper ado = AdoHelper.CreateHelper(DbProvideType.MySql);

            ado.ExecuteNonQuery(conn, CommandType.Text, "CREATE FUNCTION fnTest1(valin int) RETURNS INT " +
          " LANGUAGE SQL DETERMINISTIC BEGIN return valin * 2; END");

            IDataParameter[] param1 = new IDataParameter[]{
                ado.GetParameter("?rt", DbType.Int32, ParameterDirection.ReturnValue),
                ado.GetParameter("valin", DbType.Int32, 16),
            };
            //Return 函数必须加？符号
            object obj = ado.ExecuteScalar(conn, CommandType.StoredProcedure, "fnTest1", param1);
            Console.ReadLine();
        }

3：返回记录集

        public void ReturningEmptyResultSet()
        {
            AdoHelper ado = AdoHelper.CreateHelper(DbProvideType.MySql);

            ado.ExecuteNonQuery(conn, CommandType.Text, "CREATE TABLE test21 (id int AUTO_INCREMENT NOT NULL, " +
           "Name VARCHAR(100) NOT NULL, PRIMARY KEY(id))");

            ado.ExecuteNonQuery(conn, CommandType.Text, "CREATE TABLE test22 (id int AUTO_INCREMENT NOT NULL, " +
           "id1 INT NOT NULL, id2 INT NOT NULL, PRIMARY KEY(id))");

            ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO test21 (Id, Name) VALUES (1, 'Item1')");
            ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO test21 (Id, Name) VALUES (2, 'Item2')");
            ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO test22 (Id, Id1, Id2) VALUES (1, 1, 1)");
            ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO test22 (Id, Id1, Id2) VALUES (2, 2, 1)");
            ado.ExecuteNonQuery(conn, CommandType.Text, "CREATE PROCEDURE spTest2(Name VARCHAR(100), OUT Table1Id INT) " +
           "BEGIN SELECT t1.Id INTO Table1Id FROM test21 t1 WHERE t1.Name LIKE Name; " +
           "SELECT t3.Id2 FROM test22 t3 WHERE t3.Id1 = Table1Id; END");

            IDataParameter[] param1 = new IDataParameter[]{
                ado.GetParameter("Name", DbType.String, "Item2"),
                ado.GetParameter("Table1Id", DbType.Int32, ParameterDirection.Output)
            };
            //Return 函数必须加？符号
            using (DataSet ds = ado.ExecuteDataset(conn, CommandType.StoredProcedure, "spTest2", param1))
            {
                Console.ReadLine();
            }
            
        }

4：返回二进制

        public void BinaryAndVarBinaryParameters()
        {
            AdoHelper ado = AdoHelper.CreateHelper(DbProvideType.MySql);

            ado.ExecuteNonQuery(conn, CommandType.Text, "CREATE PROCEDURE spTest3(OUT out1 BINARY(20), OUT out2 VARBINARY(20)) " +
          "BEGIN SET out1 = 'out1'; SET out2='out2'; END");


            IDataParameter[] param1 = new IDataParameter[]{
                ado.GetParameter("out1", DbType.Object, ParameterDirection.Output),
                ado.GetParameter("out2", DbType.Object, ParameterDirection.Output)
            };
            
            ado.ExecuteNonQuery(conn, CommandType.StoredProcedure, "spTest3", param1);
            Console.ReadLine();
        }

总结

1：MySql的存储过程参数赋值是按照名称对应的。

2：函数返回值的参数名称需要额外增加符号?表示是变量。