ตอนที่ 22 : Visual C# (C# .Net) เรียกใช้ Call MySQL Stored Procedure

CALL procedure_name(agr1,agr2,...)

DROP PROCEDURE IF EXISTS getCustomer;

DELIMITER //
CREATE PROCEDURE getCustomer(IN pCountryCode VARCHAR(2))
BEGIN
   SELECT * FROM customer WHERE COUNTRY_CODE = pCountryCode;
END //
DELIMITER ;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Data;

namespace MyApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            MySqlConnection objConn = new MySqlConnection();
            MySqlCommand objCmd = new MySqlCommand();
            MySqlDataAdapter dtAdapter = new MySqlDataAdapter();

            DataSet ds = new DataSet();
            DataTable dt;
            String strConnString, strSQL;

            strConnString = "Server=localhost;User Id=root; Password=root; " + 
                "Database=mydatabase; Pooling=false";

            strSQL = "CALL getCustomer('US')";

            objConn.ConnectionString = strConnString;
            objConn.Open();
            objCmd.Connection = objConn;
            objCmd.CommandText = strSQL;
            objCmd.CommandType = CommandType.Text;

            dtAdapter.SelectCommand = objCmd;

            dtAdapter.Fill(ds);
            dt = ds.Tables[0];

            for (int i = 0; i <= dt.Rows.Count - 1; i++)
            {
                Console.Write(dt.Rows[i]["CUSTOMER_ID"]);
                Console.Write(" - ");
                Console.Write(dt.Rows[i]["NAME"]);
                Console.Write(" - ");
                Console.Write(dt.Rows[i]["EMAIL"]);
                Console.Write(" - ");
                Console.Write(dt.Rows[i]["COUNTRY_CODE"]);
                Console.Write(" - ");
                Console.Write(dt.Rows[i]["BUDGET"]);
                Console.Write(" - ");
                Console.Write(dt.Rows[i]["USED"]);
                Console.Write("");
            }

            dtAdapter = null;
            objConn.Close();
            objConn = null;
        }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Data;

namespace MyApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            MySqlConnection objConn = new MySqlConnection();
            MySqlCommand objCmd = new MySqlCommand();
            MySqlDataAdapter dtAdapter = new MySqlDataAdapter();

            DataSet ds = new DataSet();
            DataTable dt;
            String strConnString, strStored;

            strConnString = "Server=localhost;User Id=root; Password=root; " + 
                "Database=mydatabase; Pooling=false";

            strStored = "getCustomer";
            objCmd.Parameters.Add(new MySqlParameter("?pCountryCode", "US"));

            objConn.ConnectionString = strConnString;
            objConn.Open();
            objCmd.Connection = objConn;
            objCmd.CommandText = strStored;
            objCmd.CommandType = CommandType.StoredProcedure;

            dtAdapter.SelectCommand = objCmd;

            dtAdapter.Fill(ds);
            dt = ds.Tables[0];

            for (int i = 0; i <= dt.Rows.Count - 1; i++)
            {
                Console.Write(dt.Rows[i]["CUSTOMER_ID"]);
                Console.Write(" - ");
                Console.Write(dt.Rows[i]["NAME"]);
                Console.Write(" - ");
                Console.Write(dt.Rows[i]["EMAIL"]);
                Console.Write(" - ");
                Console.Write(dt.Rows[i]["COUNTRY_CODE"]);
                Console.Write(" - ");
                Console.Write(dt.Rows[i]["BUDGET"]);
                Console.Write(" - ");
                Console.Write(dt.Rows[i]["USED"]);
                Console.Write("");
            }

            dtAdapter = null;
            objConn.Close();
            objConn = null;
        }
    }
}

DROP PROCEDURE IF EXISTS insertCustomer;

DELIMITER //
CREATE PROCEDURE insertCustomer(IN pCustomerID VARCHAR(4),
	IN pName VARCHAR(150),
	IN pEmail VARCHAR(150),
	IN pCountryCode VARCHAR(2),
	IN pBudget DECIMAL(18,2),
	IN pUsed DECIMAL(18,2))
BEGIN
   INSERT INTO customer (CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED)
	VALUES (pCustomerID, pName, pEmail, pCountryCode, pBudget, pUsed);
END //
DELIMITER ;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Data;

namespace MyApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            MySqlConnection objConn = new MySqlConnection();
            MySqlCommand objCmd = new MySqlCommand();
            string strConnString = string.Empty;
            string strSQL = string.Empty;

            strConnString = "Server=localhost;User Id=root; Password=root; Database=mydatabase; Pooling=false";

            string sCustomerID = "C005";
            string sName = "Fun Wipa";
            string sEmail = "[email protected]";
            string sCountryCode = "TH";
            decimal dBudget = 1000000;
            decimal dUsed = 0;

            strSQL = "CALL insertCustomer('" + sCustomerID + "','" + sName + "' " 
                + ",'" + sEmail + "','" + sCountryCode + "','" + dBudget + "','" + dUsed + "')";

            objConn.ConnectionString = strConnString;
            objConn.Open();
            objCmd.Connection = objConn;
            objCmd.CommandText = strSQL;
            objCmd.CommandType = CommandType.Text;

            objCmd.ExecuteNonQuery();

            objConn.Close();
            objConn = null;
        }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Data;

namespace MyApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            MySqlConnection objConn = new MySqlConnection();
            MySqlCommand objCmd = new MySqlCommand();
            string strConnString = string.Empty;
            string strStored = string.Empty;

            strConnString = "Server=localhost;User Id=root; Password=root; Database=mydatabase; Pooling=false";

            string sCustomerID = "C005";
            string sName = "Fun Wipa";
            string sEmail = "[email protected]";
            string sCountryCode = "TH";
            decimal dBudget = 1000000;
            decimal dUsed = 0;

            strStored = "insertCustomer";
            objCmd.Parameters.Add(new MySqlParameter("?pCustomerID", sCustomerID));
            objCmd.Parameters.Add(new MySqlParameter("?pName", sName));
            objCmd.Parameters.Add(new MySqlParameter("?pEmail", sEmail));
            objCmd.Parameters.Add(new MySqlParameter("?pCountryCode", sCountryCode));
            objCmd.Parameters.Add(new MySqlParameter("?pBudget", dBudget));
            objCmd.Parameters.Add(new MySqlParameter("?pUsed", dUsed));

            objConn.ConnectionString = strConnString;
            objConn.Open();
            objCmd.Connection = objConn;
            objCmd.CommandText = strStored;
            objCmd.CommandType = CommandType.StoredProcedure;

            objCmd.ExecuteNonQuery();

            objConn.Close();
            objConn = null;
        }
    }
}

            objCmd.Parameters.Add("?pCustomerID", MySqlDbType.VarChar).Value = sCustomerID;
            objCmd.Parameters.Add("?pName", MySqlDbType.VarChar).Value = sName;
            objCmd.Parameters.Add("?pEmail", MySqlDbType.VarChar).Value = sEmail;
            objCmd.Parameters.Add("?pCountryCode", MySqlDbType.VarChar).Value = sCountryCode;
            objCmd.Parameters.Add("?pBudget", MySqlDbType.Decimal).Value = dBudget;
            objCmd.Parameters.Add("?pUsed", MySqlDbType.Decimal).Value = dUsed;

DROP PROCEDURE IF EXISTS getCustomer;

DELIMITER //
CREATE PROCEDURE getCustomer(IN pCountryCode VARCHAR(2),
	OUT pRowFound INT)
BEGIN

	# Return SELECT
	SELECT * FROM customer WHERE COUNTRY_CODE = sCountryCode;
	
	# Return  pRowFound
	SET pRowFound  = FOUND_ROWS();
        
END //

DELIMITER ;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Data;

namespace MyApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            MySqlConnection objConn = new MySqlConnection();
            MySqlCommand objCmd = new MySqlCommand();
            MySqlDataAdapter dtAdapter = new MySqlDataAdapter();

            DataSet ds = new DataSet();
            DataTable dt;
            String strConnString, strStored;

            strConnString = "Server=localhost;User Id=root; Password=root; Database=mydatabase; Pooling=false";

            strStored = "getCustomer";
            objCmd.Parameters.Add(new MySqlParameter("?pCountryCode", MySqlDbType.VarChar)).Value = "US"; // IN
            objCmd.Parameters.Add(new MySqlParameter("?pRowFound", MySqlDbType.Int32)).Direction = ParameterDirection.Output; // OUT

            objConn.ConnectionString = strConnString;
            objConn.Open();
            objCmd.Connection = objConn;
            objCmd.CommandText = strStored;
            objCmd.CommandType = CommandType.StoredProcedure;

            // Get Select
            dtAdapter.SelectCommand = objCmd;
            dtAdapter.Fill(ds);

            // Get Result 
            // objCmd.Parameters["?pRowFound"].Value
            Console.WriteLine(string.Format("pRowFound = {0}", objCmd.Parameters["?pRowFound"].Value));
            Console.WriteLine("");

            // Loop Data Table
            dt = ds.Tables[0]; // *** Cursor at 0 , 1 , 2, ...
            for (int i = 0; i <= dt.Rows.Count - 1; i++)
            {
                Console.Write(dt.Rows[i]["CUSTOMER_ID"]);
                Console.Write(" - ");
                Console.Write(dt.Rows[i]["NAME"]);
                Console.Write(" - ");
                Console.Write(dt.Rows[i]["EMAIL"]);
                Console.Write(" - ");
                Console.Write(dt.Rows[i]["COUNTRY_CODE"]);
                Console.Write(" - ");
                Console.Write(dt.Rows[i]["BUDGET"]);
                Console.Write(" - ");
                Console.Write(dt.Rows[i]["USED"]);
                Console.WriteLine("");
            }

            objConn.Close();
            objConn = null;
        }
    }
}

DROP PROCEDURE IF EXISTS insertCustomer;

DELIMITER //
CREATE PROCEDURE insertCustomer(IN pCustomerID VARCHAR(4),
	IN pName VARCHAR(150),
	IN pEmail VARCHAR(150),
	IN pCountryCode VARCHAR(2),
	IN pBudget DECIMAL(18,2),
	IN pUsed DECIMAL(18,2),
	OUT pResult INT,
	OUT pMessage VARCHAR(500))
BEGIN
	
	# Declare Variable
	DECLARE errCode CHAR(5) DEFAULT '00000';
	DECLARE errMsg TEXT;
	DECLARE effRows INT;

	# Declare Handler Exception
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
	    BEGIN
	      GET DIAGNOSTICS CONDITION 1
		errCode = RETURNED_SQLSTATE, errMsg = MESSAGE_TEXT;
	    END;

	# Statement 
	INSERT INTO customer (CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED)
		VALUES (pCustomerID, pName, pEmail, pCountryCode, pBudget, pUsed);

	# Set Result 
	IF errCode = '00000' THEN
		GET DIAGNOSTICS effRows = ROW_COUNT;
		SET pResult  = effRows;
		SET pMessage = 'Insert Data Successfully';
	ELSE
		SET pResult  = 0;
		SET pMessage = CONCAT('Error, Code = ',errCode,', Message = ',errMsg);
	END IF;

END //
DELIMITER ;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Data;

namespace MyApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            MySqlConnection objConn = new MySqlConnection();
            MySqlCommand objCmd = new MySqlCommand();

            String strConnString, strStored;

            strConnString = "Server=localhost;User Id=root; Password=root; Database=mydatabase; Pooling=false";
            objConn.ConnectionString = strConnString;
            objConn.Open();

            string strCustomerID = "C005";
            string strName = "Fun Wipa";
            string strEmail = "[email protected]";
            string strCountryCode = "TH";
            decimal dBudget = 100000;
            decimal dUsed = 0;

            strStored = "insertCustomer";
            objCmd.Parameters.Add(new MySqlParameter("?pCustomerID", MySqlDbType.VarChar, 4)).Value = strCustomerID; // IN
            objCmd.Parameters.Add(new MySqlParameter("?pName", MySqlDbType.VarChar, 150)).Value = strName; // IN
            objCmd.Parameters.Add(new MySqlParameter("?pEmail", MySqlDbType.VarChar, 150)).Value = strEmail; // IN
            objCmd.Parameters.Add(new MySqlParameter("?pCountryCode", MySqlDbType.VarChar, 2)).Value = strCountryCode; // IN
            objCmd.Parameters.Add(new MySqlParameter("?pBudget", MySqlDbType.VarChar)).Value = dBudget; // IN
            objCmd.Parameters.Add(new MySqlParameter("?pUsed", MySqlDbType.VarChar)).Value = dUsed; // IN

            objCmd.Parameters.Add(new MySqlParameter("?pResult", MySqlDbType.Int32)).Direction = ParameterDirection.Output; // OUT
            objCmd.Parameters.Add(new MySqlParameter("?pMessage", MySqlDbType.VarChar, 500)).Direction = ParameterDirection.Output; // OUT

            objCmd.Connection = objConn;
            objCmd.CommandText = strStored;
            objCmd.CommandType = CommandType.StoredProcedure;

            objCmd.ExecuteNonQuery();

            // Get Result 
            // objCmd.Parameters["?pResult"].Value
            Console.WriteLine(string.Format("pResult = {0}", objCmd.Parameters["?pResult"].Value));

            // objCmd.Parameters["?pMessage"].Value
            Console.WriteLine(string.Format("pMessage = {0}", objCmd.Parameters["?pMessage"].Value));

            objConn.Close();
            objConn = null;
        }
    }
}