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.Reflection;
using System.Text;
using MySql.Data;
using MySql.Data.Types;
using MySql.Data.MySqlClient;

namespace StoredProcs
{
    /// <summary>
    /// Class to assist in executing and accessing
    /// MySql Stored procedures.
    /// This class is a helper for a single stored procedure.
    /// There should be one of these built for each procedure to be accessed.
    /// </summary>
    public class Procedure
    {
        #region Data
        private string _connstr;
        private string _procName;
        private string  _drop;

	
        private MySqlConnection _conn;
        private MySqlCommand _cmd;

	
        #endregion
        #region Properties
        /// <summary>Sql to drop the stored procedure</summary>
	    public string  DropString
	    {
		    get { return _drop;}
		    set { _drop = value;}
	    }
        /// <summary>The MySql command structure</summary>
        public MySqlCommand cmd
        {
            get { return _cmd; }
            set { _cmd = value; }
        }
        /// <summary>Name of the stored procedure to execute</summary>
        public string ProcName
        {
            get { return _procName; }
            set { _procName = value; }
        }
        /// <summary>The one and only connection for this procedure</summary>
        public MySqlConnection conn
        {
            get { return _conn; }
            set { _conn = value; }
        }
        /// <summary>MySql connection string</summary>
        public string ConnectString
        {
            get { return _connstr; }
            set { _connstr = value; }
        }
        #endregion
        #region Constructors
        /// <summary>
        /// Constructor - One for each stored procedure.
        /// </summary>
        /// <param name="procedurename">The name of the stored procedure</param>
        /// <param name="connectstring">The connection string</param>
        public Procedure(string procedurename,string connectstring)
        {
            ProcName = procedurename;
            ConnectString = connectstring;
            conn = new MySqlConnection(ConnectString);
            cmd = new MySqlCommand(procedurename, conn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            DropString = "DROP PROCEDURE IF EXISTS `" + procedurename + "`;";
        }
        /// <summary>
        /// Constructor - Made private to disallow. Must have parameters
        /// </summary>
        private Procedure()
        {
        }
        #endregion
        #region Methods
        /// <summary>
        /// Add a parameter to the command parameter array.
        /// This adds an IN parameter. The value must not be null.
        /// </summary>
        /// <param name="name">The parameter name such as "?ID"</param>
        /// <param name="val">The value to set can be any data type</param>
        public void Add(string name, Object val)
        {
            cmd.Parameters.AddWithValue(name, val);
            cmd.Parameters[name].Direction = ParameterDirection.Input;
        }
        /// <summary>
        /// Add an OUT parameter to the command parameter array.
        /// The value passed in can be null but it MUST be of the same data type
        /// as the expected output. Use the name of the field that will be
        /// assigned after the procedure executes.
        /// </summary>
        /// <param name="name">The parameter name</param>
        /// <param name="typ">The database data type</param>
        public void AddOut(string name, MySqlDbType typ)
        {
            //Type ty = val.GetType();
            cmd.Parameters.Add(new MySqlParameter(name, typ));
            cmd.Parameters[name].Direction = ParameterDirection.Output;
        }
        /// <summary>
        /// Add an INOUT parameter. The value must not be null.
        /// </summary>
        /// <param name="name">The parameter name</param>
        /// <param name="val">The value to be sent and the field to be returned</param>
        public void AddInOut(string name, Object val)
        {
            cmd.Parameters.AddWithValue(name, val);
            cmd.Parameters[name].Direction = ParameterDirection.InputOutput;
        }
        /// <summary>
        /// Create the stored procedure
        /// </summary>
        /// <param name="sql">SQL to create the procedure</param>
        /// <returns>"OK" or an error message</returns>
        public string Create(string sql)
        {
            return ExecSql(sql);
        }
        /// <summary>
        /// Drop the stored procedure
        /// </summary>
        /// <returns>"OK" or an error message</returns>
        public string Drop()
        {
            return ExecSql(DropString);
        }
        /// <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>
        /// Execute the stored procedure
        /// </summary>
        /// <returns>"OK" or an error message</returns>
        public string Execute()
        {
            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                return ex.Message;
            }
            finally
            {
                conn.Close();
            }
            return "OK";
        }
        /// <summary>
        /// Get an OUT or INOUT parameter from the parameter array
        /// after execution.
        /// </summary>
        /// <param name="name">The parameter name</param>
        /// <returns>The requested object or null for failure</returns>
        public Object Get(string name)
        {
            if (cmd.Parameters[name].Value != null)
                return cmd.Parameters[name].Value;
            return null;
        }
        #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)

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