// 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
}
Submit an article or tip