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
An article on how to get parameters into and out of MySql stored procedures.
StoredProcedures_Article.zip
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
// Copyright (C) 2009 Ken Jones
//
// This program is free software; you can redistribute it and/or modify
// it under the terms of the GNU General Public License version 2 as published by
// the Free Software Foundation
//
// There are special exceptions to the terms and conditions of the GPL 
// as it is applied to this software. View the full text of the 
// exception in file EXCEPTIONS in the directory of this software 
// distribution.
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with this program; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA 

using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using MySql.Data;
using MySql.Data.Types;
using MySql.Data.MySqlClient;
using BinaryLib;

namespace StoredProcs
{
    public class clsKenTestOldVersion 
    {
        #region data
        /// <summary>
        /// Create the first test table
        /// </summary>
        public string table =
            "DROP TABLE IF EXISTS `test`.`kentest`; " +
            "CREATE TABLE `test`.`kentest` (" +
            "`ID` int unsigned NOT NULL auto_increment,"+
            " `login` varchar(60) NOT NULL default '',"+
            " `name` varchar(250) default NULL,"+
            " `latest_acc` timestamp default '0000-00-00 00:00:00',"+
            " PRIMARY KEY  (`ID`)"+
            " ) Type=InnoDB;";
        /// <summary>
        /// Drop procedure if it exists
        /// </summary>
        public string drop = "DROP PROCEDURE IF EXISTS `test`.`spInsert`;";
        /// <summary>
        /// Create insertion procedure to demonstrate IN parameters
        /// </summary>
        public string proc ="CREATE PROCEDURE `test`.`spInsert`" +
            "(IN usr varchar(60), IN dsply varchar(250)) " +
        "BEGIN " +
            "insert into `test`.`kentest` (`ID`,`login`,`name`,`latest_acc`) " +
            "values (NULL, usr, dsply, NULL); " +
        "END;";

        // These are necessary for other users to use the stored procedure
        // just change the name of the user you want to allow access.
        // the with grant option is optional.
        // Since the user 'demo' created the procedures, he already has
        // the permissions to use them.

        //grant EXECUTE on test.spInsert to 'demo'@'localhost' with grant option;

        //grant EXECUTE on test.spInsert to 'demo'@'%' with grant option;
        /// <summary>
        /// Call the insert procedure passing the data to insert
        /// </summary>
        public string nsert =
            "spInsert;";      /// <summary>
        /// Start of test 2 SQL setup commands
        /// </summary>
        /// ///Drop test table 2 if it exists
        public string dropT2 = "DROP TABLE IF EXISTS `test`.`kentest2`;";
        /// <summary>
        /// Create test table 2
        /// </summary>
        public string table2 = "CREATE TABLE `test`.`kentest2` (" +
          "`ID` int unsigned NOT NULL auto_increment," +
          " `login` varchar(60) NOT NULL default ''," +
          " `name` varchar(250) default NULL," +
          " `latest_acc` timestamp default '0000-00-00 00:00:00'," +
          " PRIMARY KEY  (`ID`)" +
        " ) Type=InnoDB;";
        /// <summary>
        /// Drop table 2 insert procedure
        /// </summary>
        public string drop2 = "DROP PROCEDURE IF EXISTS `test`.`spInsert2`;";

        //public string procI = "CREATE DEFINER=`demo`@`localhost` PROCEDURE `test`.`spInsert2` " +
        /// <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;";
        /// <summary>
        /// Execute the table 2 stored procedure
        /// </summary>
        public string nsert2 =
            "spInsert2";

        // sample update Procedure
        /// <summary>
        /// Begin test 3 - Updates from a stored procedure.
        /// Demonstrates both IN and OUT parameters.
        /// </summary>
        public string dropU = "DROP PROCEDURE IF EXISTS `test`.`spUpdate`;";

        //public string procI = "CREATE DEFINER=`demo`@`localhost` PROCEDURE `test`.`spInsert2` " +
        /// <summary>
        /// Create the update procedure
        /// </summary>
        public string procU = "CREATE PROCEDURE `test`.`spUpdate` " +
            "(IN pkey int, IN usr varchar(60), IN dsply varchar(250), OUT rows int) " +
        "BEGIN " +
            "update `test`.`kentest2` set `login`=usr,`name`=dsply,`latest_acc`=NULL " +
            "where ID=pkey; " +
            "select ROW_COUNT() into rows; " +
         "END;";
        /// <summary>
        /// Execute the update procedure
        /// </summary>
        public string update =
            "spUpdate";

        // sample delete Procedure
        /// <summary>
        /// Begin test 4 - row delete procedure
        /// </summary>
        public string dropD = "DROP PROCEDURE IF EXISTS `test`.`spDelete`;";
        /// <summary>
        /// Create the Delete stored procedure
        /// Demonstrates IN and OUT parameters
        /// </summary>
        public string procD = "CREATE PROCEDURE `test`.`spDelete` " +
            "(IN pkey int, OUT rows int) " +
        "BEGIN " +
            "delete from `test`.`kentest2` " +
            "where ID=pkey; " +
            "select ROW_COUNT() into rows; " +
        "END;";
        public string del =
            "spDelete";

        // INOUT parameters
        /// <summary>
        /// Begin test 5 - test INOUT parameters
        /// </summary>
        public string dropIO = "DROP PROCEDURE IF EXISTS `test`.`spInOut`;";
        /// <summary>
        /// Create INOUT stored procedure
        /// </summary>
        public string procIO = "CREATE PROCEDURE `test`.`spInOut` " +
            "(IN pkey int, INOUT chnge varchar(260)) " +
        "BEGIN " +
            "select CONCAT(chnge,CURRENT_USER()) into chnge; " +
        "END;";
        /// <summary>
        /// Execute the INOUT procedure
        /// </summary>
        public string io =
            "spInOut";

        private string _connect;   // save the MySql connect string
        public Int64 lastinsertid;
        public string newval = "";

        #endregion
        #region Properties
        /// <summary>ConnectString getter and setter properties</summary>
        public string ConnectString
        {
            get { return _connect; }
            set { _connect = value; }
        }
        #endregion
        #region Constructor
        /// <summary>
        /// Constructor with connection string
        /// </summary>
        /// <param name="connectString">MySql connection string</param>
        public clsKenTestOldVersion (string connectString)
	    {
            ConnectString = connectString; // save it
	    }
        #endregion
        #region Methods
        /// <summary>
        /// Execute an SQL string - not a procedure
        /// Calls to this function use SQL that requires no parameters
        /// </summary>
        /// <param name="sql">The SQL string to execute</param>
        /// <returns>"OK" or an error message</returns>
        public string ExecSql(string sql)
        {
            MySqlConnection conn = new MySqlConnection(ConnectString);
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.CommandType = System.Data.CommandType.Text;
            try
            {
                conn.Open();
                // Return value is meaningless with this procudure so ignore it
                cmd.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                // return the mysql error message
                // the caller can put it in a messagebox
                return ex.Message;
            }
            finally
            {
                conn.Close();  // always close the connection
            }
            return "OK";
        }
        /// <summary>
        /// Insert into table 1 showing the use of input parameters
        /// Note: this is coded for a single specific SQL statement
        /// as a demonstration.
        /// </summary>
        /// <param name="sql">The SQL command to execute</param>
        /// <returns>"OK" or an error message</returns>
        public string DoInsert(string sql)
        {
            MySqlConnection conn = new MySqlConnection(ConnectString);
            MySqlCommand cmd = new MySqlCommand(nsert, conn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            // NOTE: This is the short form for specifying IN parameters to a 
            // stored procedure.
            // NOTE: The escaped qoute within the string. It was intentionally coded
            // this way. If it is not, use the class 'clsPaths' to handle it.
            cmd.Parameters.AddWithValue("?usr", "path\\to\\ken\'s\\data"); // cast if a constant
            cmd.Parameters["?usr"].Direction = ParameterDirection.Input;

            cmd.Parameters.AddWithValue("?dsply", "path\\to\\ken\'s\\data"); // cast if a constant
            cmd.Parameters["?dsply"].Direction = ParameterDirection.Input;
            try
            {
                conn.Open();
                // Return value is meaningless with this procudure so ignore it
                cmd.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                // return the mysql error message
                // the caller can put it in a messagebox
                return ex.Message;
            }
            finally
            {
                conn.Close();  // always close the connection
            }
            return "OK";
        }
        /// <summary>
        /// Run the rest of the tests from a single button.
        /// It is intended for the user to set break points in the runProc method
        /// and run the demo program with the Visual Studio debugger.
        /// </summary>
        /// <returns>"OK" or an error message</returns>
        public string runProc()
        {
            Int64 retval = 0;  // for ExecuteScalar calls
            int rows = 0;  // number of rows affected
            string ans = "";    // for retrieving strings

            ans = ExecSql(dropT2);  // drop table 2
            if (!ans.Equals("OK"))
                return "Drop table 2 failed with: " + ans;
            ans = ExecSql(table2);  // create table 2
            if (!ans.Equals("OK"))
                return "Create table 2 failed with: " + ans;
            ans = ExecSql(drop2);
            if (!ans.Equals("OK"))
                return "Drop Procedure 2 failed with: " + ans;
            ans = ExecSql(procI);
            if (!ans.Equals("OK"))
                return "Create Procedure 2 failed with: " + ans;
            // 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("?retVal", MySqlDbType.Int64));
            cmd.Parameters["?dsply"].Direction = ParameterDirection.ReturnValue;

            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
                retval = (Int64) cmd.Parameters["?retVal"].Value;
                // Now get the OUT parameters
                rows = (int)cmd.Parameters["?rows"].Value;
                lastinsertid = (Int64)cmd.Parameters["?lastinsertid"].Value;
                ans = (string)cmd.Parameters["?cat"].Value; // This is ignored after this
            }
            catch (MySqlException ex)
            {
                return "Insert failed with: " + ex.Message;
            }
            finally
            {
                conn.Close();
            }
            // do an update
            ans = ExecSql(dropU);
            if (!ans.Equals("OK"))
                return "Drop Update Procedure failed with: " + ans;
            ans = ExecSql(procU);
            if (!ans.Equals("OK"))
                return "Create Update Procedure failed with: " + ans;
            // Do the Update test
            conn = new MySqlConnection(ConnectString);
            cmd = new MySqlCommand(update, conn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            // This example uses the long way to demonstrate that it works.
            cmd.Parameters.Add(new MySqlParameter("?pkey", MySqlDbType.Int32));
            cmd.Parameters["?pkey"].Value = (Int32)lastinsertid;
            cmd.Parameters["?pkey"].Direction = ParameterDirection.Input;

            cmd.Parameters.Add(new MySqlParameter("?usr", MySqlDbType.VarChar));
            cmd.Parameters["?usr"].Value = "tony updated";
            cmd.Parameters["?usr"].Direction = ParameterDirection.Input;

            cmd.Parameters.Add(new MySqlParameter("?dsply", MySqlDbType.VarChar));
            cmd.Parameters["?dsply"].Value = "path\\to\\tony\'s\\data\\updated\\";
            cmd.Parameters["?dsply"].Direction = ParameterDirection.Input;

            cmd.Parameters.Add(new MySqlParameter("?rows", MySqlDbType.Int32));
            cmd.Parameters["?rows"].Direction = ParameterDirection.Output;
            rows = 0;
            try
            {
                conn.Open();
                // this ALWAYS returns a 0 for this update
                cmd.ExecuteNonQuery();
                rows = (int)cmd.Parameters["?rows"].Value;
            }
            catch (MySqlException ex)
            {
                return "Update failed with: " + ex.Message;
            }
            finally
            {
                conn.Close();
            }
            // now try delete statement
            ans = ExecSql(dropD);
            if (!ans.Equals("OK"))
                return "Drop Delete Procedure failed with: " + ans;
            ans = ExecSql(procD);
            if (!ans.Equals("OK"))
                return "Create Delete Procedure failed with: " + ans;
            // Now do the delete
            conn = new MySqlConnection(ConnectString);
            cmd = new MySqlCommand(del, conn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add(new MySqlParameter("?pkey", MySqlDbType.Int32));
            cmd.Parameters["?pkey"].Value = (Int32) 1;
            cmd.Parameters["?pkey"].Direction = ParameterDirection.Input;

            cmd.Parameters.Add(new MySqlParameter("?rows", MySqlDbType.Int32));
            cmd.Parameters["?rows"].Direction = ParameterDirection.Output;
            rows = 0;
            try
            {
                conn.Open();
                // this ALWAYS returns a 0 for this update
                cmd.ExecuteNonQuery();
                rows = (int)cmd.Parameters["?rows"].Value;
            }
            catch (MySqlException ex)
            {
                return "Delete failed with: " + ex.Message;
            }
            finally
            {
                conn.Close();
            }
            // now try INOUT statement
            ans = ExecSql(dropIO);
            if (!ans.Equals("OK"))
                return "Drop INOUT Procedure failed with: " + ans;
            ans = ExecSql(procIO);
            if (!ans.Equals("OK"))
                return "Create INOUT Procedure failed with: " + ans;
            // Now run the INOUT procedure
            conn = new MySqlConnection(ConnectString);
            cmd = new MySqlCommand(io, conn); // execute the stored proc
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            // Can be in any order
            // IN and INOUT must have values set
            cmd.Parameters.Add(new MySqlParameter("?pkey", MySqlDbType.Int32));
            cmd.Parameters["?pkey"].Value = (Int32)2; // record to find
            cmd.Parameters["?pkey"].Direction = ParameterDirection.Input;

            cmd.Parameters.AddWithValue("?chnge", "The current user is: ");
            cmd.Parameters["?chnge"].Direction = ParameterDirection.InputOutput;
            rows = 0;
            try
            {
                conn.Open();
                // this ALWAYS returns a 0 for this query
                //retval = (Int64) cmd.ExecuteNonQuery();
                //object retval2 =  cmd.ExecuteScalar();
                object retval2 = cmd.ExecuteNonQuery();
                //rows = (int)cmd.Parameters["?rows"].Value;
                newval = (string)cmd.Parameters["?chnge"].Value;
            }
            catch (MySqlException ex)
            {
                return "INOUT failed with: " + ex.Message;
            }
            finally
            {
                conn.Close();
            }
            return "OK";
        }
        #endregion
    }// end class
}

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 | Mobile
Web03 | 2.8.140827.1 | Last Updated 15 May 2009
Article Copyright 2009 by ken.tachyon
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid