Tested under

MySql Version 5.0.34

Connector/NET version 5.2.6

Microsoft Visual Studio 2005

Introduction

I have an application which has many related tables where an insert to the master table requires inserts to the related tables. I know there are many ways to do this but I have a need to encapsulate this code in stored procedures.

This is a C# program and classes built to understand and test getting parameters into and out of MySql stored procedures.
The attached source code demonstrates how to make this work.

I am writing this article in the hopes that no one else will have to spend three days searching for this answer.

An Early Solution

Like everything else, you need to understand the problem before you can arrive at a solution.
I had several questions and I could not find the answers in the documentation.

The questions:

When and where do you use the '@' or the '?' in a parameter?

Does the order of the parameters matter?

How do you execute the stored procedure in C# code?

Do you call ExecuteNonQuery or ExecuteScalar?

The "@" symbol can be used inside a stored procedure in a SET or DECLARE
statement. It is used for user defined variables and should not be confused with parameters. The variable is valib within the
BEGIN...END block in which it is declared. Use of the "@" is from an older version of MySql and is not necessary to use it when declaring local user variables any longer.

The "?" symbol is used when specifying parameter names to be added to the MySqlCommand parameters collection. For example:

cmd.Parameters.AddWithValue("?dsply", asAString);

The order you add the parameters to the parameter collection does not matter. The parameter collection is a HASH table
and can be indexed by the parameter name. Example:

cmd.Parameters["?dsply"].Direction = ParameterDirection.Input;

This sets the Direction value for the "?dsply" parameter.

In order to execute a stored procedure in the code you need several things:

You will normally use ExecuteNonQuery to execute the procedure. If the procedure selects a single value from a table
you can use something like:

string name = (string) cmd.ExecuteScalar();

The single value will be in name.

Based on the number of articles I found, I am not the only one with questions.

The quest for answers

I have often found that the best way to get an answer is to write some code and run it under the debugger. So that is what this code is about. During that process I read a LOT of MySql source code.

The class Form1 is the windows form class wizard when you create a new project in Visual Studio. This was changed to handle the simple controls on the form.

The class clsKenTest is the class which implements the solution.

During the initial design of this test I decided that several stored procedures would be needed to answer all
the questions. I also wanted procedures to do Inserts, Deletes, and Updates.

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:

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.

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.

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:

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.

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.

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.

For anyone who is storing the procedures in/on the MySQL Database -
Please remember that you need to grant execute permission on the Procedure and that everytime the procedure is modified that you may well need to re-permission the procedure.

After hours of trying to send the Delimiter command from C# to MySQL I finally found your article.
As an aside you mention there is no need to send a delimiter command. This was the solution to my problem, just delete the lines to try to send a delimiter

One thing though: I presume I would have found your article earlier save for the typo: you wrote DELIMETER instead of DELIMITER.