MySQL calls the Stored Procedure

1: Stored Procedure Return Value

View Code

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)
};
// The stored procedure parameters correspond to each other by name
Param1 [1]. Value = "beginning ";
Param1 [0]. Value = 32;
Ado. ExecuteNonQuery (conn, CommandType. StoredProcedure, "spTest1", param1 );
Console. ReadLine ();
}

2: Functions

View Code

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

View Code

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 ),
};
// Must the Return function be added? Symbol
Object obj = ado. ExecuteScalar (conn, CommandType. StoredProcedure, "fnTest1", param1 );
Console. ReadLine ();
}

3: return record set

View Code

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)
};
// Must the Return function be added? Symbol
Using (DataSet ds = ado. ExecuteDataset (conn, CommandType. StoredProcedure, "spTest2", param1 ))
{
Console. ReadLine ();
}

}

4: returns binary data.

View Code

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

Summary

1: MySql stored procedure parameter values correspond to names.

2: Do the parameter names of function return values need to be added with additional symbols? Indicates a variable.