This was a really hard task to learn – and unusually difficult in terms of google searching to try and understand the errors I was getting. I hope this helps someone through the initial learning on Specifically Executing Stored Routines on a MySQL Server using IN and OUT Parameters to communicate to the routine. I think this is an important skill to master as you can limit privileges on a shared MySQL account to just what you’ve defined can be executed.
So to get started you will need an account that has “Execute” privileges on a database. Once you have this you’ll want to create a “Stored Routine” which you can then Call from C#. I will show you how
My Routine I’m going to call is:
Code:
DELIMITER $$
CREATE DEFINER=`yourusername`@`%` PROCEDURE `AddThisDevice`(IN ThisMACID VARCHAR(45),IN ThisIP VARCHAR(20), OUT ThisResult VARCHAR(45))
begin
DECLARE myMAC VARCHAR(45) DEFAULT NULL;
select MAC from Devices where MAC = ThisMACID into myMAC;
IF myMAC IS NULL THEN
INSERT INTO Devices (MAC, DeviceIP) VALUES (ThisMACID, ThisIP);
SET ThisResult = “Added”;
ELSE
SET ThisResult = “Exists”;
END IF;
end $$
This simple creates a stored procedure to accept a MAC ID and IP address in the form of VARCHAR, test to see if that MAC already exists in the database and the either Add it and inform back that it was added or otherwise inform that it already exists.
To communicate with this Procedure look at the following C# code.
Code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data; // mysql requires
using MySql.Data.MySqlClient; // mysql requires
using System.Net; // Required for MAC and IP routines
using System.Net.NetworkInformation; // Required for MAC and IP Routines
using System.Configuration; // For using App.config
using System.ComponentModel;
using System.Data; // mysql requires
namespace mysql1
{
class Program
{
static void Main(string[] args)
{
string connStr = ConfigurationManager.AppSettings[“connection”];
MySqlConnection myConnection = new MySqlConnection(connStr);
AddDevice(myConnection);
myConnection.Close();
Console.WriteLine(“closed”);
Console.ReadLine();
}
static void AddDevice(MySqlConnection myConnection)
{
try
{
Console.WriteLine(“Trying Connection….”);
if (myConnection.State != ConnectionState.Open)
{
myConnection.Open();
}
Console.WriteLine(“opened.”);
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = myConnection;
cmd.CommandText = “AddThisDevice”;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new MySqlParameter(“ThisMACID”, MySqlDbType.VarChar));
cmd.Parameters[“ThisMACID”].Value = GetMACAddress();
cmd.Parameters[“ThisMACID”].Direction = ParameterDirection.Input;
cmd.Parameters.Add(new MySqlParameter(“ThisIP”, MySqlDbType.VarChar));
cmd.Parameters[“ThisIP”].Value = GetActiveIP();
cmd.Parameters[“ThisIP”].Direction = ParameterDirection.Input;
cmd.Parameters.Add(new MySqlParameter(“ThisResult”, MySqlDbType.VarChar));
cmd.Parameters[“ThisResult”].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
Console.WriteLine(“Result: {0}”, cmd.Parameters[“ThisResult”].Value);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
You can see here that each Parameter needs (3) lines of configuration. You must first define the parameter and its type using the “add” command. Then you need to assign it a value if it’s an “IN” type. Finally you must specify the directionality of the parameter (ie. IN, OUT, INOUT etc).
In the example above GetMACAddress() and GetActiveIP() both simply return strings.
Final piece of knowledge you need is the connection string – specified as connStr. It isn’t visible as in this case I have it residing in AppSettings. This string needs to take on the following format. Add the following in your App.config
Code:
in the “appSettings” section
add key =”connection” value=”server=www.myserveraddr.com;user=myUserName;database=myDatabaseName;port=3306;password=myPasswordForMySQLUser;”
You will need to download the Mysql / C# connector from the MySQL people. You can find that assembly here: http://dev.mysql.com/downloads/connector/net/1.0.html once you have it downloaded and installed – you will need to include minimum mysql.data assembly.
csharpcodedeveloper 