Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » MySQL » Downloads
 
Add your own
alternative version

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

, 15 May 2009 CPOL
An article on how to get parameters into and out of MySql stored procedures.
StoredProcedures_Article.zip
ScreenShot.png
StoredProcedures_src.zip
StoredProcs
bin
Debug
BinaryLib.dll
BinaryLib.pdb
StoredProcs.exe
StoredProcs.pdb
StoredProcs.vshost.exe
obj
Debug
Refactor
StoredProcs.csproj.GenerateResource.Cache
StoredProcs.exe
StoredProcs.Form1.resources
StoredProcs.pdb
StoredProcs.Properties.Resources.resources
TempPE
Properties
Settings.settings
StoredProcs.suo
StoredProcs.sln.cache
��Title:       Working C# code for MySql Stored Procedures IN, OUT, and INOUT parameters

Author:      Ken Jones

Email:       ken.tachyon@yahoo.com

Member ID:   ken.tachyon

Language:    C# 

Platform:    Windows, .NET Framework

Technology:  MYSql Stored Procedures

Level:       Beginner

Description: An article on how to get parameters into and out of MySql stored procedures.

Section      Database, Platforms, Frameworks, and Libraries

SubSection   MySql, Stored Procedures, Parameters

License:     CPOL



<p></p><p />

<ul class="Download">



<li><a href="StoredProcedures_src.zip">Download StoredProcedures_src - 150 KB</a></li>

</ul>

<img height="300" width="300" alt="ScreenShot.png" src="ScreenShot.png" complete="true" /> 

<h2>Credits</h2>

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

Freedom Culture <a href="http://freedomculture.wordpress.com/2007/05/23/mysql-50-stored-procedure-programming-tutorial/">http://freedomculture.wordpress.com/2007/05/23/mysql-50-stored-procedure-programming-tutorial/</a>[<a target=_blank title='New Window' href="http://freedomculture.wordpress.com/2007/05/23/mysql-50-stored-procedure-programming-tutorial/">^</a>]

<br />

Herong s Tutoril Notes on SQL

<a href="http://www.herongyang.com/sql/proc_sql_3.html">http://www.herongyang.com/sql/proc_sql_3.html</a>[<a target=_blank title='New Window' href="http://www.herongyang.com/sql/proc_sql_3.html">^</a>]

<h2>Tested under</h2>

<p>MySql Version 5.0.34</p>

<p>Connector/NET version 5.2.6</p>

<p>Microsoft Visual Studio 2005</p>

<h2>Introduction</h2>

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

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

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





<h2>An Early Solution</h2>

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

<ol>The questions:

<li>When and where do you use the '@' or the '?' in a parameter?</li>

<li>Does the order of the parameters matter?</li>

<li>How do you execute the stored procedure in C# code?</li>

<li>Do you call ExecuteNonQuery or ExecuteScalar?</li>

</ol>

<p>The <code>"@"</code> symbol can be used inside a stored procedure in a <code>SET</code> or <code>DECLARE</code>

statement. It is used for user defined variables and should not be confused with parameters. The variable is valib within the 

<code>BEGIN...END</code> block in which it is declared. Use of the <code>"@"</code> is from an older version of MySql and is not necessary to use it when declaring local user variables any longer.</p>

<p>The <code>"?"</code> symbol is used when specifying parameter names to be added to the <code>MySqlCommand</code> parameters collection. <br />For example: <pre>cmd.Parameters.AddWithValue("?dsply", asAString);</pre></p>

<p>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. <br />Example: <pre>cmd.Parameters["?dsply"].Direction = ParameterDirection.Input;</pre> This sets the Direction value for the "?dsply" parameter.</p>

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

<ul><li>The name of the procedure.</li><li>Set up the parameters collection.</li>

<li>Set the command type.</li><li>Execute the command.</li></ul></p>

<p>Here is a stripped down version of what that means.<br />

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

</pre>

You will normally use <code>ExecuteNonQuery</code> to execute the procedure. If the procedure selects a single value from a table

 you can use something like:

<pre>    string  name = (string) cmd.ExecuteScalar();</pre>

The single value will be in name.</p>



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

<h2>The quest for answers</h2>

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

<p>The class <code>Form1</code> 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.</p>

<p>The class <code>clsKenTest</code> is the class which implements the solution.</p>

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

<h2>The Details</h2>

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

<p><b>This is one stored procedure and the code is specifically for that procedure only.</b></p>

<p>The stored procedure is shown below. Note the absence of <code>'@'</code> signs and <code>'?'</code> symbols. There are no <code>DELIMETER $$</code> or <code>DELIMETER //</code> 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.</p>

<p>The procedure <code>spInsert2</code> demonstrates the use of IN and OUT parameters:</p>

<pre>

        /// <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;";

</pre>

<p>There are several syntax issues presented here.</p>

<ul><li>Database and table names are surrounded by the <code>"`"</code> (backTick) character.</li>

<li>There are two <code>IN</code> parameters and three <code>OUT</code>parameters.</li>

<li>The <code>";"</code> (semi-colon) ends each SQL statement.</li>

<li><code>NULL</code> values are used for the auto_increment and timestamp fields.</li>

<li>The function calls <code>LAST_INSERT_ID</code>, <code>CONCAT</code>, and <code>ROW_COUNT</code> have NO SPACE between the 

name and the open parenthesis.</li>

<li>The last three select statements place the results into the OUT parameters.</li></ul>

<p>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 <code>GRANT</code> them permission by issuing the

<code>grant EXECUTE on test.spInsert to 'demo'@'localhost' with grant option;</code>. The "demo" is a user name 

in my database.</p>

<p>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 <code>"?"</code> mark is used. There are five parameters in the procedure and 

there must be five parameters added to the command parameter collection.</p>

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

</p>

<p>Each of those <code>MySqlParameter</code> 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.</p>

<p>There is more on how to determine the parameter type later.</p>

<p>The download source files contains a file called <code>clsKenTestVersion.cs</code>. 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. </p>



<b>DO NOT USE THIS CODE. EXAMPLE ONLY</b>

<pre>            // 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";

</pre>

<h2>The important stuff</h2>

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

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

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

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

</pre>

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

<pre>            cmd.Parameters.Add(new MySqlParameter("?lastinsertid", MySqlDbType.Int64));

            cmd.Parameters["?lastinsertid"].Direction = ParameterDirection.Output;

</pre>

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

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

<pre>            cmd.Parameters.Add(new MySqlParameter("?dsply", MySqlDbType.VarChar));

            cmd.Parameters["?dsply"].Value = asAString;

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

</pre>

<p>This is an example. "?dsply" is not set up as an INOUT parameter.</p>

<p>Once this is done you can run the procedure with:</p>

<pre>               conn.Open();

                retval = (Int64)cmd.ExecuteNonQuery();

                conn.Close();

 </pre>

<p>Ignore the <code>retval</code> for now.</p>

<h2>Retrieving the results</h2>

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

<pre>                rows = (int)cmd.Parameters["?rows"].Value;

                lastinsertid = (Int64)cmd.Parameters["?lastinsertid"].Value;

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

</pre>

<p>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 = <code>ROW_COUNT()</code>, lastinsertid = <code>LAST_INSERT_ID()</code>, 

and ans = <code>CONCAT(usr,dsply)</code> done within the procedure.</p>

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

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

<pre>                Object obj = cmd.Parameters["?lastinsertid"].Value;

                Type typ = obj.GetType();

                lastinsertid = (Int64) obj;

</pre>

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

<h2>Conclusion One</h2>

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

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

<h2>The Procedure Class</h2>

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

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

<p>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:</p>

<pre>            // 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);

</pre>

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

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

<p>Dropping and Creating the procedure is done by:</p>

<pre>            // -- 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;

</pre>

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

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

</pre>

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

<p>The code to look at is in <code>Procedure.cs</code> and <code>clsKenTest.cs</code>. There are lots of comments

in the code.</p>

<h2>A Look at INOUT parameters</h2>

<p>The code is almost the same for these. Here is the procedure:</p>

<pre>        public string procIO = "CREATE PROCEDURE `test`.`spInOut` " +

            "(IN pkey int, INOUT chnge varchar(260)) " +

        "BEGIN " +

            "select CONCAT(chnge,CURRENT_USER()) into chnge; " +

        "END;";

</pre>

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

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

</pre>

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

<h2>Revision History</h2>

May 12, 2009 First version.  Initial document.

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

ken.tachyon
Web Developer
United States United States
Senior software developer / consultant with 30 years experience.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150327.1 | Last Updated 15 May 2009
Article Copyright 2009 by ken.tachyon
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid