Working C# code for MySql Stored Procedures IN, OUT, and INOUT parameters

• Download StoredProcedures_src - 159.55 KB

Credits

Out of all the articles I searched I found these two most helpful. They did not provide the solution but they helped.

Freedom Culture http://freedomculture.wordpress.com/2007/05/23/mysql-50-stored-procedure-programming-tutorial/[^]
Herong’s Tutoril Notes on SQL http://www.herongyang.com/sql/proc_sql_3.html[^]

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:
1. When and where do you use the '@' or the '?' in a parameter?
2. Does the order of the parameters matter?
3. How do you execute the stored procedure in C# code?
4. 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:

• The name of the procedure.
• Set up the parameters collection.
• Set the command type.
• Execute the command.

Here is a stripped down version of what that means.

public string nsert2 =
    "spInsert2;";
    ...
    MySqlConnection conn = new MySqlConnection(ConnectString);
    MySqlCommand cmd = new MySqlCommand(nsert2, conn);
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    ...
    cmd.Parameters.AddWithValue("?usr", (object)"tony wv"); // cast if a constant
    cmd.Parameters["?usr"].Direction = ParameterDirection.Input;
    ...
    conn.Open();
    retval = (Int64)cmd.ExecuteNonQuery();
    ...
    conn.Close();

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:

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

DO NOT USE THIS CODE. EXAMPLE ONLY

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

Revision History

May 12, 2009 First version. Initial document.