ตอนที่ 20 : Visual Basic (VB.Net) เรียกใช้ Call MySQL Stored Procedure

มิถุนายน 12, 2018 6:25 am โดย admin
0
12

ตอนที่ 20 : Visual Basic (VB.Net) เรียกใช้ Call MySQL Stored Procedure ในหัวข้อนี้เราจะมาเรียนรู้วิธีการใช้ Visual Basic (VB.Net) บน .NET Application เรียกใช้งาน Stored Procedure ของ MySQL Database ซึ่งในการเรียกใช้งาน Stored Procedure สามารถใช้ได้กับในหลาย ๆ Connector เช่น MySql,Data.MySqlClient ส่วนคำสั่งในการการเรียกนั้น เราสามารถใช้คำสั่ง CALL procedure_name() ได้ทันที ถ้าหากมีค่า Parameters ก็จะต้องทำการ Pass ค่าไปกับ Query ด้วย หรือจะใช้แบบ Parameter Query แบบ CommandType.StoredProcedure ก็ได้ โดยทั้ง 2 วิธีสามารถใช้ได้เหมือนกัน และหลังจากที่ CALL แล้วก็สามารถที่จะอ่าน Result ที่ Stored Procedure นั้นได้ส่งกลับมาให้

Call MySQL Stored Procedure

1.CALL procedure_name(agr1,agr2,...)

โครงสร้างของตาราง customer

Table : customer

Stored Procedure บน MySQL

Example 1 : การใช้ .NET เรียก Stored Procedure แบบ Query ข้อมูลออกมา

Stored Procedure Name : getCustomer()

1.DROP PROCEDURE IF EXISTS getCustomer;
2. 
3.DELIMITER //
4.CREATE PROCEDURE getCustomer(IN pCountryCode VARCHAR(2))
5.BEGIN
6.SELECT FROM customer WHERE COUNTRY_CODE = pCountryCode;
7.END //
8.DELIMITER ;

Code (VB.Net)

01.Imports MySql.Data.MySqlClient
02.Imports System.Data
03. 
04.Module MyModule
05. 
06.Sub Main()
07.Dim objConn As New MySqlConnection()
08.Dim objCmd As New MySqlCommand()
09.Dim dtAdapter As New MySqlDataAdapter()
10. 
11.Dim ds As New DataSet()
12.Dim dt As DataTable
13.Dim strConnString As String, strSQL As String
14. 
15.strConnString = "Server=localhost;User Id=root; Password=root; Database=mydatabase; Pooling=false"
16. 
17.strSQL = "CALL getCustomer('US')"
18. 
19.objConn.ConnectionString = strConnString
20.objConn.Open()
21.objCmd.Connection = objConn
22.objCmd.CommandText = strSQL
23.objCmd.CommandType = CommandType.Text
24. 
25.dtAdapter.SelectCommand = objCmd
26. 
27.dtAdapter.Fill(ds)
28.dt = ds.Tables(0)
29. 
30.For As Integer = 0 To dt.Rows.Count - 1
31.Console.Write(dt.Rows(i)("CUSTOMER_ID"))
32.Console.Write(" - ")
33.Console.Write(dt.Rows(i)("NAME"))
34.Console.Write(" - ")
35.Console.Write(dt.Rows(i)("EMAIL"))
36.Console.Write(" - ")
37.Console.Write(dt.Rows(i)("COUNTRY_CODE"))
38.Console.Write(" - ")
39.Console.Write(dt.Rows(i)("BUDGET"))
40.Console.Write(" - ")
41.Console.Write(dt.Rows(i)("USED"))
42.Console.Write("")
43.Next
44. 
45.dtAdapter = Nothing
46.objConn.Close()
47.objConn = Nothing
48.End Sub
49. 
50.End Module

Result

C003 – Jame Born – jame.born@thaicreate.com – US – 3000000.00 – 600000.00
C004 – Chalee Angel – chalee.angel@thaicreate.com – US – 4000000.00 – 100000.00

หรือจะใช้แบบ Parameter Query หรือ CommandType.StoredProcedure ก็ได้เช่นเดียวกัน

Code (VB.Net)

01.Imports MySql.Data.MySqlClient
02.Imports System.Data
03. 
04.Module MyModule
05. 
06.Sub Main()
07.Dim objConn As New MySqlConnection()
08.Dim objCmd As New MySqlCommand()
09.Dim dtAdapter As New MySqlDataAdapter()
10. 
11.Dim ds As New DataSet()
12.Dim dt As DataTable
13.Dim strConnString As String, strStored As String
14. 
15.strConnString = "Server=localhost;User Id=root; Password=root; Database=mydatabase; Pooling=false"
16. 
17.strStored = "getCustomer"
18.objCmd.Parameters.Add(New MySqlParameter("?pCountryCode""US"))
19. 
20.objConn.ConnectionString = strConnString
21.objConn.Open()
22.objCmd.Connection = objConn
23.objCmd.CommandText = strStored
24.objCmd.CommandType = CommandType.StoredProcedure
25. 
26.dtAdapter.SelectCommand = objCmd
27. 
28.dtAdapter.Fill(ds)
29.dt = ds.Tables(0)
30. 
31.For As Integer = 0 To dt.Rows.Count - 1
32.Console.Write(dt.Rows(i)("CUSTOMER_ID"))
33.Console.Write(" - ")
34.Console.Write(dt.Rows(i)("NAME"))
35.Console.Write(" - ")
36.Console.Write(dt.Rows(i)("EMAIL"))
37.Console.Write(" - ")
38.Console.Write(dt.Rows(i)("COUNTRY_CODE"))
39.Console.Write(" - ")
40.Console.Write(dt.Rows(i)("BUDGET"))
41.Console.Write(" - ")
42.Console.Write(dt.Rows(i)("USED"))
43.Console.Write("")
44.Next
45. 
46.dtAdapter = Nothing
47.objConn.Close()
48.objConn = Nothing
49.End Sub
50. 
51.End Module

Example 2 : การใช้ .NET เรียก Stored Procedure แบบ Query เพื่อ Insert ข้อมูล

Stored Procedure Name : insertCustomer()

01.DROP PROCEDURE IF EXISTS insertCustomer;
02. 
03.DELIMITER //
04.CREATE PROCEDURE insertCustomer(IN pCustomerID VARCHAR(4),
05.IN pName VARCHAR(150),
06.IN pEmail VARCHAR(150),
07.IN pCountryCode VARCHAR(2),
08.IN pBudget DECIMAL(18,2),
09.IN pUsed DECIMAL(18,2))
10.BEGIN
11.INSERT INTO customer (CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED)
12.VALUES (pCustomerID, pName, pEmail, pCountryCode, pBudget, pUsed);
13.END //
14.DELIMITER ;

Code (VB.Net)

01.Imports MySql.Data.MySqlClient
02.Imports System.Data
03. 
04.Module MyModule
05. 
06.Sub Main()
07.Dim objConn As New MySqlConnection()
08.Dim objCmd As New MySqlCommand()
09.Dim strConnString As String, strSQL As String
10. 
11.strConnString = "Server=localhost;User Id=root; Password=root; Database=mydatabase; Pooling=false"
12. 
13.Dim sCustomerID As String "C005"
14.Dim sName As String "Fun Wipa"
15.Dim sEmail As String "fun.wipa@thaicreate.com"
16.Dim sCountryCode As String "TH"
17.Dim dBudget As Decimal = 1000000
18.Dim dUsed As Decimal = 0
19. 
20.strSQL = "CALL insertCustomer('" & sCustomerID & "','" & sName & "' " & _
21.",'" & sEmail & "','" & sCountryCode & "','" & dBudget & "','" & dUsed & "')"
22. 
23.objConn.ConnectionString = strConnString
24.objConn.Open()
25.objCmd.Connection = objConn
26.objCmd.CommandText = strSQL
27.objCmd.CommandType = CommandType.Text
28. 
29.objCmd.ExecuteNonQuery()
30. 
31.objConn.Close()
32.objConn = Nothing
33.End Sub
34. 
35.End Module

หรือจะใช้แบบ Parameter Query หรือ CommandType.StoredProcedure ก็ได้เช่นเดียวกัน

Code (VB.Net)

01.Imports MySql.Data.MySqlClient
02.Imports System.Data
03. 
04.Module MyModule
05. 
06.Sub Main()
07.Dim objConn As New MySqlConnection()
08.Dim objCmd As New MySqlCommand()
09.Dim strConnString As String, strStored As String
10. 
11.strConnString = "Server=localhost;User Id=root; Password=root; Database=mydatabase; Pooling=false"
12. 
13.Dim sCustomerID As String "C005"
14.Dim sName As String "Fun Wipa"
15.Dim sEmail As String "fun.wipa@thaicreate.com"
16.Dim sCountryCode As String "TH"
17.Dim dBudget As Decimal = 1000000
18.Dim dUsed As Decimal = 0
19. 
20.strStored = "insertCustomer"
21.objCmd.Parameters.Add(New MySqlParameter("?pCustomerID", sCustomerID))
22.objCmd.Parameters.Add(New MySqlParameter("?pName", sName))
23.objCmd.Parameters.Add(New MySqlParameter("?pEmail", sEmail))
24.objCmd.Parameters.Add(New MySqlParameter("?pCountryCode", sCountryCode))
25.objCmd.Parameters.Add(New MySqlParameter("?pBudget", dBudget))
26.objCmd.Parameters.Add(New MySqlParameter("?pUsed", dUsed))
27. 
28.objConn.ConnectionString = strConnString
29.objConn.Open()
30.objCmd.Connection = objConn
31.objCmd.CommandText = strStored
32.objCmd.CommandType = CommandType.StoredProcedure
33. 
34.objCmd.ExecuteNonQuery()
35. 
36.objConn.Close()
37.objConn = Nothing
38.End Sub
39. 
40.End Module

ส่วนวิธีการ UPDATE และ DELETE ก็ใช้หลักการเดียวกับการ INSERT ข้อมูล

เพิ่มเติม : ในกรณีที่ต้องการกำหนดชนิด DataType ของ Parameters

1.objCmd.Parameters.Add("?pCustomerID", MySqlDbType.VarChar).Value = sCustomerID
2.objCmd.Parameters.Add("?pName", MySqlDbType.VarChar).Value = sName
3.objCmd.Parameters.Add("?pEmail", MySqlDbType.VarChar).Value = sEmail
4.objCmd.Parameters.Add("?pCountryCode", MySqlDbType.VarChar).Value = sCountryCode
5.objCmd.Parameters.Add("?pBudget", MySqlDbType.Decimal).Value = dBudget
6.objCmd.Parameters.Add("?pUsed", MySqlDbType.Decimal).Value = dUsed

สำหรับตัวอย่างที่ 3 และ 4 จะเป็นการอ่านจาก OUTPUT หรือ OUT

ตอนที่ 9 : การใช้พารามิเตอร์ชนิด OUT เพื่อส่งค่ากลับ (MySQL : Stored Procedure)

Example 3 : การใช้ .NET เรียก Stored Procedure และอ่านค่า OUTPUT พร้อมกับ SELECT ข้อมูล

Stored Procedure Name : getCustomer()

01.DROP PROCEDURE IF EXISTS getCustomer;
02. 
03.DELIMITER //
04.CREATE PROCEDURE getCustomer(IN pCountryCode VARCHAR(2),
05.OUT pRowFound INT)
06.BEGIN
07. 
08.Return SELECT
09.SELECT FROM customer WHERE COUNTRY_CODE = sCountryCode;
10. 
11.Return  pRowFound
12.SET pRowFound  = FOUND_ROWS();
13. 
14.END //
15. 
16.DELIMITER ;

Code (VB.Net)

01.Imports MySql.Data.MySqlClient
02.Imports System.Data
03. 
04.Module MyModule
05. 
06.Sub Main()
07. 
08.Dim objConn As New MySqlConnection()
09.Dim objCmd As New MySqlCommand()
10.Dim dtAdapter As New MySqlDataAdapter()
11. 
12.Dim ds As New DataSet()
13.Dim dt As DataTable
14.Dim strConnString As String, strStored As String
15. 
16.strConnString = "Server=localhost;User Id=root; Password=root; Database=mydatabase; Pooling=false"
17. 
18.strStored = "getCustomer"
19.objCmd.Parameters.Add(New MySqlParameter("?pCountryCode", MySqlDbType.VarChar)).Value = "US"' IN
20.objCmd.Parameters.Add(New MySqlParameter("?pRowFound", MySqlDbType.Int32)).Direction = ParameterDirection.Output ' OUT
21. 
22.objConn.ConnectionString = strConnString
23.objConn.Open()
24.objCmd.Connection = objConn
25.objCmd.CommandText = strStored
26.objCmd.CommandType = CommandType.StoredProcedure
27. 
28.' Get Select
29.dtAdapter.SelectCommand = objCmd
30.dtAdapter.Fill(ds)
31. 
32.' Get Result
33.' objCmd.Parameters["?pRowFound"].Value
34.Console.WriteLine(String.Format("pRowFound = {0}", objCmd.Parameters("?pRowFound").Value))
35.Console.WriteLine("")
36. 
37.' Loop Data Table
38.dt = ds.Tables(0)
39.' *** Cursor at 0 , 1 , 2, ...
40.For As Integer = 0 To dt.Rows.Count - 1
41.Console.Write(dt.Rows(i)("CUSTOMER_ID"))
42.Console.Write(" - ")
43.Console.Write(dt.Rows(i)("NAME"))
44.Console.Write(" - ")
45.Console.Write(dt.Rows(i)("EMAIL"))
46.Console.Write(" - ")
47.Console.Write(dt.Rows(i)("COUNTRY_CODE"))
48.Console.Write(" - ")
49.Console.Write(dt.Rows(i)("BUDGET"))
50.Console.Write(" - ")
51.Console.Write(dt.Rows(i)("USED"))
52.Console.WriteLine("")
53.Next
54. 
55.objConn.Close()
56.objConn = Nothing
57. 
58.End Sub
59. 
60.End Module

VB.Net MySQL Stored OUTPUT

ค่าที่ถูกส่งมาทั้ง SELECT และ Parameters ที่เป็นแบบ OUT

Example 4 : การใช้ .NET เรียก Stored Procedure และอ่านค่า OUTPUT ที่มีตั้งแต่ 2 ค่าขึ้นไป

Stored Procedure Name : insertCustomer()

01.DROP PROCEDURE IF EXISTS insertCustomer;
02. 
03.DELIMITER //
04.CREATE PROCEDURE insertCustomer(IN pCustomerID VARCHAR(4),
05.IN pName VARCHAR(150),
06.IN pEmail VARCHAR(150),
07.IN pCountryCode VARCHAR(2),
08.IN pBudget DECIMAL(18,2),
09.IN pUsed DECIMAL(18,2),
10.OUT pResult INT,
11.OUT pMessage VARCHAR(500))
12.BEGIN
13. 
14.Declare Variable
15.DECLARE errCode CHAR(5) DEFAULT '00000';
16.DECLARE errMsg TEXT;
17.DECLARE effRows INT;
18. 
19.Declare Handler Exception
20.DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
21.BEGIN
22.GET DIAGNOSTICS CONDITION 1
23.errCode = RETURNED_SQLSTATE, errMsg = MESSAGE_TEXT;
24.END;
25. 
26.# Statement
27.INSERT INTO customer (CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED)
28.VALUES (pCustomerID, pName, pEmail, pCountryCode, pBudget, pUsed);
29. 
30.Set Result
31.IF errCode = '00000' THEN
32.GET DIAGNOSTICS effRows = ROW_COUNT;
33.SET pResult  = effRows;
34.SET pMessage = 'Insert Data Successfully';
35.ELSE
36.SET pResult  = 0;
37.SET pMessage = CONCAT('Error, Code = ',errCode,', Message = ',errMsg);
38.END IF;
39. 
40.END //
41.DELIMITER ;

Code (VB.Net)

01.Imports MySql.Data.MySqlClient
02.Imports System.Data
03. 
04.Module MyModule
05. 
06.Sub Main()
07. 
08.Dim objConn As New MySqlConnection()
09.Dim objCmd As New MySqlCommand()
10. 
11.Dim strConnString As String, strStored As String
12. 
13.strConnString = "Server=localhost;User Id=root; Password=root; Database=mydatabase; Pooling=false"
14.objConn.ConnectionString = strConnString
15.objConn.Open()
16. 
17.Dim strCustomerID As String "C005"
18.Dim strName As String "Fun Wipa"
19.Dim strEmail As String "fun.wipa@thaicreate.com"
20.Dim strCountryCode As String "TH"
21.Dim dBudget As Decimal = 100000
22.Dim dUsed As Decimal = 0
23. 
24.strStored = "insertCustomer"
25.objCmd.Parameters.Add(New MySqlParameter("?pCustomerID", MySqlDbType.VarChar, 4)).Value = strCustomerID ' IN
26.objCmd.Parameters.Add(New MySqlParameter("?pName", MySqlDbType.VarChar, 150)).Value = strName ' IN
27.objCmd.Parameters.Add(New MySqlParameter("?pEmail", MySqlDbType.VarChar, 150)).Value = strEmail ' IN
28.objCmd.Parameters.Add(New MySqlParameter("?pCountryCode", MySqlDbType.VarChar, 2)).Value = strCountryCode ' IN
29.objCmd.Parameters.Add(New MySqlParameter("?pBudget", MySqlDbType.VarChar)).Value = dBudget ' IN
30.objCmd.Parameters.Add(New MySqlParameter("?pUsed", MySqlDbType.VarChar)).Value = dUsed ' IN
31. 
32.objCmd.Parameters.Add(New MySqlParameter("?pResult", MySqlDbType.Int32)).Direction = ParameterDirection.Output ' OUT
33.objCmd.Parameters.Add(New MySqlParameter("?pMessage", MySqlDbType.VarChar, 500)).Direction = ParameterDirection.Output ' OUT
34. 
35.objCmd.Connection = objConn
36.objCmd.CommandText = strStored
37.objCmd.CommandType = CommandType.StoredProcedure
38. 
39.objCmd.ExecuteNonQuery()
40. 
41.' Get Result
42.' objCmd.Parameters["?pResult"].Value
43.Console.WriteLine(String.Format("pResult = {0}", objCmd.Parameters("?pResult").Value))
44. 
45.' objCmd.Parameters["?pMessage"].Value
46.Console.WriteLine(String.Format("pMessage = {0}", objCmd.Parameters("?pMessage").Value))
47. 
48.objConn.Close()
49.objConn = Nothing
50. 
51.End Sub
52. 
53.End Module

VB.Net MySQL Stored OUTPUT

กรณีที่ Insert ข้อมูลสำเร็จ

VB.Net MySQL Stored OUTPUT

กรณีที่ Insert ข้อมูลไม่สำเร็จ

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

แหล่งที่มา : thaicreate.com/tutorial