ตอนที่ 20 : Visual Basic (VB.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 ;

Imports MySql.Data.MySqlClient
Imports System.Data

Module MyModule

    Sub Main()
        Dim objConn As New MySqlConnection()
        Dim objCmd As New MySqlCommand()
        Dim dtAdapter As New MySqlDataAdapter()

        Dim ds As New DataSet()
        Dim dt As DataTable
        Dim strConnString As String, strSQL As String

        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 i As Integer = 0 To dt.Rows.Count - 1
            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("")
        Next

        dtAdapter = Nothing
        objConn.Close()
        objConn = Nothing
    End Sub

End Module

Imports MySql.Data.MySqlClient
Imports System.Data

Module MyModule

    Sub Main()
        Dim objConn As New MySqlConnection()
        Dim objCmd As New MySqlCommand()
        Dim dtAdapter As New MySqlDataAdapter()

        Dim ds As New DataSet()
        Dim dt As DataTable
        Dim strConnString As String, strStored As String

        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 i As Integer = 0 To dt.Rows.Count - 1
            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("")
        Next

        dtAdapter = Nothing
        objConn.Close()
        objConn = Nothing
    End Sub

End Module

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 ;

Imports MySql.Data.MySqlClient
Imports System.Data

Module MyModule

    Sub Main()
        Dim objConn As New MySqlConnection()
        Dim objCmd As New MySqlCommand()
        Dim strConnString As String, strSQL As String

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

        Dim sCustomerID As String = "C005"
        Dim sName As String = "Fun Wipa"
        Dim sEmail As String = "[email protected]"
        Dim sCountryCode As String = "TH"
        Dim dBudget As Decimal = 1000000
        Dim dUsed As Decimal = 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 = Nothing
    End Sub

End Module

Imports MySql.Data.MySqlClient
Imports System.Data

Module MyModule

    Sub Main()
        Dim objConn As New MySqlConnection()
        Dim objCmd As New MySqlCommand()
        Dim strConnString As String, strStored As String

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

        Dim sCustomerID As String = "C005"
        Dim sName As String = "Fun Wipa"
        Dim sEmail As String = "[email protected]"
        Dim sCountryCode As String = "TH"
        Dim dBudget As Decimal = 1000000
        Dim dUsed As Decimal = 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 = Nothing
    End Sub

End Module

            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 ;

Imports MySql.Data.MySqlClient
Imports System.Data

Module MyModule

    Sub Main()

        Dim objConn As New MySqlConnection()
        Dim objCmd As New MySqlCommand()
        Dim dtAdapter As New MySqlDataAdapter()

        Dim ds As New DataSet()
        Dim dt As DataTable
        Dim strConnString As String, strStored As String

        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 i As Integer = 0 To dt.Rows.Count - 1
            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("")
        Next

        objConn.Close()
        objConn = Nothing

    End Sub

End Module

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 ;

Imports MySql.Data.MySqlClient
Imports System.Data

Module MyModule

    Sub Main()

        Dim objConn As New MySqlConnection()
        Dim objCmd As New MySqlCommand()

        Dim strConnString As String, strStored As String

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

        Dim strCustomerID As String = "C005"
        Dim strName As String = "Fun Wipa"
        Dim strEmail As String = "[email protected]"
        Dim strCountryCode As String = "TH"
        Dim dBudget As Decimal = 100000
        Dim dUsed As Decimal = 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 = Nothing

    End Sub

End Module