Click here to Skip to main content
11,567,143 members (50,135 online)
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 93.6K 2.6K 35
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
// 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
{
    /// <summary>
    /// Class to demonstrate the use of Mysql parameters.
    /// Also demonstrates how to use the Procedure class.
    /// </summary>
    public class clsKenTest 
    {
        #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;";
        /// <summary>
        /// Class to handle the spInsert stored procedure
        /// </summary>
        public Procedure spInsert;

        // 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.
        /// This SQL is created in the Procedure class and is not needed anymore.
        /// It is show here only for example of what it needs to be.
        /// The database name is not needed since the connect string
        /// attaches to the test database.
        /// </summary>
        public string drop2 = "DROP PROCEDURE IF EXISTS `test`.`spInsert2`;";

        // You can add a definer clause to the create if necessary. This defaults to
        // the user id in the connection string.
        //public string procI = "CREATE DEFINER=`demo`@`localhost` PROCEDURE `test`.`spInsert2` " +
        /// <summary>
        /// Create Insert procedure for table 2. Unfortunately this string must exist
        /// somewhere in order to do this all in code.
        /// </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 bigint) " +
        "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>
        /// Procedure spInsert2
        /// This handles everything for the procedure.
        /// </summary>
        public Procedure spInsert2;

        // sample update Procedure
        /// <summary>
        /// Begin test 3 - Updates from a stored procedure.
        /// Demonstrates both IN and OUT parameters.
        /// 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>
        /// Procedure spUpdate class
        /// </summary>
        public Procedure spUpdate;

        // sample delete Procedure
        /// <summary>
        /// Begin test 4 - row delete procedure
        /// 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;";
        /// <summary>
        /// Procedure spDelete class
        /// </summary>
        public Procedure spDelete;

        /// <summary>
        /// Begin test 5 - test INOUT parameters
        /// Create INOUT stored procedure
        /// This will add the logged in user id to the string sent in
        /// and return it to the code.
        /// </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>
        /// Procedure spInOut class
        /// </summary>
        public Procedure spInOut;
        /// <summary>
        /// Variables from the stored procedures
        /// </summary>
        private string _connect;   // save the MySql connect string
        public Int64 lastinsertid;
        public string newval = "";
        #endregion
        #region Properties
        /// <summary>ConnectString getter and setter property</summary>
        public string ConnectString
        {
            get { return _connect; }
            set { _connect = value; }
        }
        #endregion
        #region Constructor
        /// <summary>
        /// Constructor with connection string
        /// Builds 5 classes for the stored procedures.
        /// </summary>
        /// <param name="connectString">MySql connection string</param>
        public clsKenTest(string connectString)
	    {
            ConnectString = connectString; // save it
            // 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);
        }
        #endregion
        #region Methods
        /// <summary>
        /// Execute an SQL string - not a procedure
        /// Calls to this function use SQL that requires no parameters
        /// This is used for dropping and creating tables
        /// </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)
        {
            // Add the parameters
            spInsert.Add("?usr", "path\\to\\ken\'s\\data");
            spInsert.Add("?dsply", "path\\to\\ken\'s\\data");
            return spInsert.Execute();  // execute the stored procedure
        }
        /// <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 rows = 0;  // number of rows affected
            string ans = "";    // for retrieving strings
            string stringVal = "";  

            // -- Set up the second test table
            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;

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

            // -- 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
            // This always returns a -1 which is supposed to be an error.
            // The insert works and the ID is returned.
            // If anyone knows why this happens, please let me know.
            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

            // -- Do an update using spUpdate procedure
            ans = spUpdate.Drop();  // drop the procedure
            if (!ans.Equals("OK"))
                return "Drop Update Procedure failed with: " + ans;

            ans = spUpdate.Create(procU);  // Create the procedure
            if (!ans.Equals("OK"))
                return "Create Update Procedure failed with: " + ans;

            // Do the Update test
            // This example updates the last inserted record (record 2)
            spUpdate.Add("?pkey", (Int32)lastinsertid);  // used in where clause
            spUpdate.Add("?usr", "tony updated");
            spUpdate.Add("?dsply", "path\\to\\tony\'s\\data\\updated\\");
            spUpdate.AddOut("?rows", MySqlDbType.Int64);

            ans = spUpdate.Execute();  // run the procedure
            if (!ans.Equals("OK"))
                return "Update failed with: " + ans;
            rows = (Int64) spUpdate.Get("?rows");  // This gives the correct result

            // -- now try delete statement spDelete procedure
            ans = spDelete.Drop();
            if (!ans.Equals("OK"))
                return "Drop Delete Procedure failed with: " + ans;

            ans = spDelete.Create(procD); // create delete proc
            if (!ans.Equals("OK"))
                return "Create Delete Procedure failed with: " + ans;

            // Now do the delete
            spDelete.Add("?pkey", (UInt32)1);  // delete record 1
            spDelete.AddOut("?rows", MySqlDbType.Int64);

            ans = spDelete.Execute();  // run the procedure
            if (!ans.Equals("OK"))
                return "Delete failed with: " + ans;
            rows = (Int64) spDelete.Get("?rows");  // works correctly

            // -- now try INOUT statement spInOut procedure
            ans = spInOut.Drop();  // drop the procedure
            if (!ans.Equals("OK"))
                return "Drop INOUT Procedure failed with: " + ans;
            ans = spInOut.Create(procIO);  // Create the procedure
            if (!ans.Equals("OK"))
                return "Create INOUT Procedure failed with: " + ans;

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

You may also be interested in...

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