견습공

The Details

The code presented here represents one of the stored procedures and the code necessary to make it work. It is presented so that all of the steps needed are clear.

This is one stored procedure and the code is specifically for that procedure only.

The stored procedure is shown below. Note the absence of '@' signs and '?' symbols. There are no DELIMETER $$ or DELIMETER // present because these statements are being sent from C# code. Those statements are only needed if you are entering the commands through the MySql command line or in an SQL file input.

The procedure spInsert2 demonstrates the use of IN and OUT parameters:

/// <summary>
/// Create Insert procedure for table 2
/// </summary>
public string procI = "CREATE PROCEDURE `test`.`spInsert2` " +
    "(IN usr varchar(60), IN dsply varchar(250), OUT lastinsertid int, " +
    "OUT cat varchar(260), OUT rows int) " +
"BEGIN " +
    "insert into `test`.`kentest2` (`ID`,`login`,`name`,`latest_acc`) " +
    "values (NULL, usr, dsply,NULL); " +
    "select LAST_INSERT_ID() into lastinsertid; " +
    "select CONCAT(usr,dsply) into cat; " +
    "select ROW_COUNT() into rows; " +
    "END;";

There are several syntax issues presented here.

• Database and table names are surrounded by the "`" (backTick) character.
• There are two IN parameters and three OUTparameters.
• The ";" (semi-colon) ends each SQL statement.
• NULL values are used for the auto_increment and timestamp fields.
• The function calls LAST_INSERT_ID, CONCAT, and ROW_COUNT have NO SPACE between the name and the open parenthesis.
• The last three select statements place the results into the OUT parameters.

The user that created the stored procedure already has permissions set up to execute the procedure. If other users are going to use the procedure then you must GRANT them permission by issuing the grant EXECUTE on test.spInsert to 'demo'@'localhost' with grant option;. The "demo" is a user name in my database.

The code below is what is necessary to make the whole thing work. It is primarily concerned with dealing with the parameters. Each parameter must be added to the MySqlCommand Parameter collection. Each parameter corresponds to the parameters defined in the stored procedure. This is where the "?" mark is used. There are five parameters in the procedure and there must be five parameters added to the command parameter collection.

The procedure has usr, dsply, lastinsertid, cat, and rows for parameter names. Therefore you must create a separate MySqlParameter for each one and name them ?usr, ?dsply, ?lastinsertid, ?cat, and ?rows.

Each of those MySqlParameter classes must be told the data type, parameter direction (IN, OUT, INOUT), and IN and INOUT must be assigned a value. After you execute the stored procedure you can retrieve the values of the OUT and INOUT parameters from the MySqlParameter collection. All of the values for IN and OUT parameters are stored in the command parameter collection. When setting the direction, the direction is from the perspective of the stored procedure. An IN parameter is set as Input, an OUT is set as Output, and INOUT is InputOutput.

There is more on how to determine the parameter type later.

The download source files contains a file called clsKenTestVersion.cs. The code below comes from that file and is not the final solution I chose. It is presented here as an example of all the steps necessary to make this work. I was surprised that all this is needed for one stored procedure.

Hide   Shrink    Copy Code

            // Ready to try executing the procedures
            MySqlConnection conn = new MySqlConnection(ConnectString);
            MySqlCommand cmd = new MySqlCommand(nsert2, conn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            // For IN or INOUT you must provide an initial value.
            // Two ways to supply the input parameters. The three lines below
            // are the first way
            //cmd.Parameters.Add(new MySqlParameter("?usr", MySqlDbType.VarChar));
            //cmd.Parameters["?usr"].Value = "tony nsert";
            //cmd.Parameters["?usr"].Direction = ParameterDirection.Input;
            // --
            // OR do it like this
            cmd.Parameters.AddWithValue("?usr", (object)"tony wv"); // cast if a constant
            cmd.Parameters["?usr"].Direction = ParameterDirection.Input;
            // --
            // The value can also be a variable
            string asAString = "path\\to\\tony\'s\\data\\";
            //cmd.Parameters.Add(new MySqlParameter("?dsply", MySqlDbType.VarChar));
            //cmd.Parameters["?dsply"].Value = asAString;
            //cmd.Parameters["?dsply"].Direction = ParameterDirection.Input;
            // OR this way
            cmd.Parameters.AddWithValue("?dsply", asAString);
            cmd.Parameters["?dsply"].Direction = ParameterDirection.Input;

            cmd.Parameters.Add(new MySqlParameter("?lastinsertid", MySqlDbType.Int64));
            cmd.Parameters["?lastinsertid"].Direction = ParameterDirection.Output;

            cmd.Parameters.Add(new MySqlParameter("?cat", MySqlDbType.VarChar));
            cmd.Parameters["?cat"].Direction = ParameterDirection.Output;

            cmd.Parameters.Add(new MySqlParameter("?rows", MySqlDbType.Int32));
            cmd.Parameters["?rows"].Direction = ParameterDirection.Output;
            try
            {
                conn.Open();
                // this ALWAYS returns a 0 for this insert
                retval = (Int64)cmd.ExecuteNonQuery();
                retval = (int)cmd.ExecuteNonQuery(); // insert second row for update
                // Now get the OUT parameters
                rows = (int)cmd.Parameters["?rows"].Value;
                lastinsertid = (Int64)cmd.Parameters["?lastinsertid"].Value;
                ans = (string)cmd.Parameters["?cat"].Value; // ans is ignored after this
            }
            catch (MySqlException ex)
            {
                return "Insert failed with: " + ex.Message;
            }
            finally
            {
                conn.Close();
            }
            return "OK";

The important stuff

For now, I will ignore all the MySql plumbing and concentrate on what pertains to the parameters. Before you execute the procedure, the parameters must be set up.

For IN parameters you can set them up with two lines of code for each parameter.

cmd.Parameters.AddWithValue("?dsply", asAString);
cmd.Parameters["?dsply"].Direction = ParameterDirection.Input;

For OUT parameters you can set them up with two lines of code for each parameter.

cmd.Parameters.Add(new MySqlParameter("?lastinsertid", MySqlDbType.Int64));
cmd.Parameters["?lastinsertid"].Direction = ParameterDirection.Output;

As you can see, it is simple but rather tedious work to set up one procedure since this must be done for each parameter.

For INOUT parameters you can set them up with three lines of code for each parameter.

cmd.Parameters.Add(new MySqlParameter("?dsply", MySqlDbType.VarChar));
cmd.Parameters["?dsply"].Value = asAString;
cmd.Parameters["?dsply"].Direction = ParameterDirection.InputOutput;

This is an example. "?dsply" is not set up as an INOUT parameter.

Once this is done you can run the procedure with:

conn.Open();
 retval = (Int64)cmd.ExecuteNonQuery();
 conn.Close();

Ignore the retval for now.

Retrieving the results

Now that the procedure has been executed, the OUT and INOUT parameters can be accessed. This is done by:

rows = (int)cmd.Parameters["?rows"].Value;
lastinsertid = (Int64)cmd.Parameters["?lastinsertid"].Value;
ans = (string)cmd.Parameters["?cat"].Value; // ans is ignored after this

The OUT and INOUT parameters are placed in your program variables by getting them from the command parameter collection. These values were obtained from the procedure. rows = ROW_COUNT(), lastinsertid =LAST_INSERT_ID(), and ans = CONCAT(usr,dsply) done within the procedure.

I should note that on my system ROW_COUNT() works for Update and Delete but always returns -1 for inserts. The insert works so I don't know why it does that.

Note that the parameter values are stored as objects so you must cast them to the proper data type when retrieving them. If you don't know what type is being returned there is a trick you can use to find out.

Object obj = cmd.Parameters["?lastinsertid"].Value;
Type typ = obj.GetType();
lastinsertid = (Int64) obj;

Run this code in the debugger, put a break point on the last line. When it stops, look at typ and it will tell you the type cast to use.

Conclusion One

The code above shows how to make this work. If you are content with doing it that way then you can copy the code from the old version file in the download and you don't need to read the rest of this article.

This mechanism seemed tedious to me so I created a helper class to deal with stored procedures. The rest of this article is about that class.

The Procedure Class

This class handles stored procedures. Although you wouldn't normally be dropping and creating procedures in the same code that uses them, the class supports those actions. The class lets me move most of the MySql structures and details away from what the application is actually trying to do. To me, that make the code more readable.

I am sure there are other (and maybe better) ways to do this. I am always open to suggestions.

The Procedure class is built to handle one stored procedure per instance of the class. The demo program runs 5 stored procedures so therefore the clsKenTest constructor does this:

// Make one object per stored procedure
spInsert = new Procedure("spInsert", ConnectString);
spInsert2 = new Procedure("spInsert2", ConnectString);
spUpdate = new Procedure("spUpdate", ConnectString);
spDelete = new Procedure("spDelete", ConnectString);
spInOut = new Procedure("spInOut", ConnectString);

The constructor uses the string procedure name to build a drop procedure command and to execute the procedure. The ConnectString is obviously needed to talk to MySql. Although the drop procedure command is built, you must call the Drop() method to run it.

The spInsert2 procedure is the same one used in the earlier examples so I will use it here for comparison.

Dropping and Creating the procedure is done by:

// -- Start insert test
// -- Drop spInsert2 and then add it
ans = spInsert2.Drop();  // drop the procedure
if (!ans.Equals("OK"))
    return "Drop Procedure 2 failed with: " + ans;

ans = spInsert2.Create(procI);  // create the stored procedure
if (!ans.Equals("OK"))
    return "Create Procedure 2 failed with: " + ans;

This code does the same thing as the version above but seems easier to use to me.

            // -- Set up parameters before running spInsert2. There are 5 parameters
            // For IN or INOUT you must provide an initial value.
            // --
            // for an IN parameter do it like this
            spInsert2.Add("?usr", "tony wv");

            // The value can also be a variable
            string asAString = "path\\to\\tony\'s\\data\\";
            spInsert2.Add("?dsply", asAString);  // adds an IN parameter
            // OUT parameters must know the data type. The program variable
            // for the output is selected after the procedure runs.
            spInsert2.AddOut("?lastinsertid", MySqlDbType.Int64);
            spInsert2.AddOut("?cat", MySqlDbType.VarChar);
            spInsert2.AddOut("?rows", MySqlDbType.Int64);
            // insert two rows
            ans = spInsert2.Execute();  // run the procedure
            ans = spInsert2.Execute(); // insert second row
            if (!ans.Equals("OK"))
                return "Insert failed with: " + ans;
            // Get the OUT data
            rows =  (Int64) spInsert2.Get("?rows");
            // Get ID of inserted row. (This is the auto_increment value assigned)
            lastinsertid = (Int64) spInsert2.Get("?lastinsertid");
            // Test concatenating the usr and dsply into an OUT variable
            stringVal = (string) spInsert2.Get("?cat");
            // -- End of insert test

This works really well with intellisense. When I add a parameter all 3 add methods show up and show the parameters I need to supply.

The code to look at is in Procedure.cs and clsKenTest.cs. There are lots of comments in the code.

A Look at INOUT parameters

The code is almost the same for these. Here is the procedure:

public string procIO = "CREATE PROCEDURE `test`.`spInOut` " +
    "(IN pkey int, INOUT chnge varchar(260)) " +
"BEGIN " +
    "select CONCAT(chnge,CURRENT_USER()) into chnge; " +
"END;";

It is a simple procedure with two parameters. It appends the current user name to the string that was sent in.

// Now run the INOUT procedure
// Parameters can be in any order.
// IN and INOUT must have values set
spInOut.Add("?pkey", (UInt32) 2);  // record 2 is all that is left in the table
spInOut.AddInOut("?chnge", "The current user is: ");

ans = spInOut.Execute(); // execute the procedure
if (!ans.Equals("OK"))
    return "Execute INOUT Procedure failed with: " + ans;
newval = (string) spInOut.Get("?chnge");  // works 

That is all there is to it. I hope this article helps. Please feel free to make any comments or suggestions for improvements.