Bug #100533 Docs do not make clear that /net mysqlcommand does not allow create procedure
Submitted: 14 Aug 2020 14:27 Modified: 15 Aug 2020 18:20
Reporter: Peter Brawley (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Documentation Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[14 Aug 2020 14:27] Peter Brawley
Description:
As the synopsis says, and as explained years ago on a forum, https://forums.mysql.com/read.php?38,572995,572995#msg-572995, but I cannot find this info in the 5.7 or 8.0 manual

How to repeat:
as above
[14 Aug 2020 14:51] MySQL Verification Team
Hi Mr. Brawley,

Thank you for your bug report.

However, we fail to understand it fully.

We do not have a /net command in our SQL server.

Can you be more precise and even supply some examples ???

Thanks in advance.
[14 Aug 2020 17:23] Peter Brawley
/net is a reference to using connector/net.

See https://forums.mysql.com/read.php?38,689458,689458#msg-689458
[14 Aug 2020 21:45] MySQL Verification Team
Changing category to Connector / Net Documentation
[14 Aug 2020 22:15] MySQL Verification Team
Please check: https://dev.mysql.com/doc/connector-net/en/connector-net-programming-stored-using.html

The following C# example code demonstrates the use of stored procedures. It assumes the database 'employees' has already been created: 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace UsingStoredRoutines
{
    class Program
    {
        static void Main(string[] args)
        {
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "server=localhost;user=root;database=employees;port=3306;password=******";
            MySqlCommand cmd = new MySqlCommand();

            try
            {
                Console.WriteLine("Connecting to MySQL...");
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandText = "DROP PROCEDURE IF EXISTS add_emp";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "DROP TABLE IF EXISTS emp";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "CREATE TABLE emp (empno INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(20), last_name VARCHAR(20), birthdate DATE)";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "CREATE PROCEDURE add_emp(" +
                                  "IN fname VARCHAR(20), IN lname VARCHAR(20), IN bday DATETIME, OUT empno INT)" +
                                  "BEGIN INSERT INTO emp(first_name, last_name, birthdate) " +
                                  "VALUES(fname, lname, DATE(bday)); SET empno = LAST_INSERT_ID(); END";

                cmd.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                Console.WriteLine ("Error " + ex.Number + " has occurred: " + ex.Message);
            }
            conn.Close();
            Console.WriteLine("Connection closed.");
            try
            {
                Console.WriteLine("Connecting to MySQL...");
                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "add_emp";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@lname", "Jones");
                cmd.Parameters["@lname"].Direction = ParameterDirection.Input;

                cmd.Parameters.AddWithValue("@fname", "Tom");
                cmd.Parameters["@fname"].Direction = ParameterDirection.Input;

                cmd.Parameters.AddWithValue("@bday", "1940-06-07");
                cmd.Parameters["@bday"].Direction = ParameterDirection.Input;

                cmd.Parameters.AddWithValue("@empno", MySqlDbType.Int32);
                cmd.Parameters["@empno"].Direction = ParameterDirection.Output;

                cmd.ExecuteNonQuery();

                Console.WriteLine("Employee number: "+cmd.Parameters["@empno"].Value);
                Console.WriteLine("Birthday: " + cmd.Parameters["@bday"].Value);
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                Console.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
            }
            conn.Close();
            Console.WriteLine("Done.");
        }
    }
}
[15 Aug 2020 16:08] Peter Brawley
Thanks!