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

มิถุนายน 14, 2018 2:37 am โดย admin
0
13

ตอนที่ 22 : Visual C# (C# .Net) เรียกใช้ Call MySQL Stored Procedure ในหัวข้อนี้เราจะมาเรียนรู้วิธีการใช้Visual C# (C# .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 (C#)

01.using System;
02.using System.Collections.Generic;
03.using System.Linq;
04.using System.Text;
05.using System.Threading.Tasks;
06.using MySql.Data.MySqlClient;
07.using System.Data;
08. 
09.namespace MyApplication
10.{
11.class Program
12.{
13.static void Main(string[] args)
14.{
15.MySqlConnection objConn = new MySqlConnection();
16.MySqlCommand objCmd = new MySqlCommand();
17.MySqlDataAdapter dtAdapter = new MySqlDataAdapter();
18. 
19.DataSet ds = new DataSet();
20.DataTable dt;
21.String strConnString, strSQL;
22. 
23.strConnString = "Server=localhost;User Id=root; Password=root; " +
24."Database=mydatabase; Pooling=false";
25. 
26.strSQL = "CALL getCustomer('US')";
27. 
28.objConn.ConnectionString = strConnString;
29.objConn.Open();
30.objCmd.Connection = objConn;
31.objCmd.CommandText = strSQL;
32.objCmd.CommandType = CommandType.Text;
33. 
34.dtAdapter.SelectCommand = objCmd;
35. 
36.dtAdapter.Fill(ds);
37.dt = ds.Tables[0];
38. 
39.for (int i = 0; i <= dt.Rows.Count - 1; i++)
40.{
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.Write("");
53.}
54. 
55.dtAdapter = null;
56.objConn.Close();
57.objConn = null;
58.}
59.}
60.}

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 (C#)

01.using System;
02.using System.Collections.Generic;
03.using System.Linq;
04.using System.Text;
05.using System.Threading.Tasks;
06.using MySql.Data.MySqlClient;
07.using System.Data;
08. 
09.namespace MyApplication
10.{
11.class Program
12.{
13.static void Main(string[] args)
14.{
15.MySqlConnection objConn = new MySqlConnection();
16.MySqlCommand objCmd = new MySqlCommand();
17.MySqlDataAdapter dtAdapter = new MySqlDataAdapter();
18. 
19.DataSet ds = new DataSet();
20.DataTable dt;
21.String strConnString, strStored;
22. 
23.strConnString = "Server=localhost;User Id=root; Password=root; " +
24."Database=mydatabase; Pooling=false";
25. 
26.strStored = "getCustomer";
27.objCmd.Parameters.Add(new MySqlParameter("?pCountryCode""US"));
28. 
29.objConn.ConnectionString = strConnString;
30.objConn.Open();
31.objCmd.Connection = objConn;
32.objCmd.CommandText = strStored;
33.objCmd.CommandType = CommandType.StoredProcedure;
34. 
35.dtAdapter.SelectCommand = objCmd;
36. 
37.dtAdapter.Fill(ds);
38.dt = ds.Tables[0];
39. 
40.for (int i = 0; i <= dt.Rows.Count - 1; i++)
41.{
42.Console.Write(dt.Rows[i]["CUSTOMER_ID"]);
43.Console.Write(" - ");
44.Console.Write(dt.Rows[i]["NAME"]);
45.Console.Write(" - ");
46.Console.Write(dt.Rows[i]["EMAIL"]);
47.Console.Write(" - ");
48.Console.Write(dt.Rows[i]["COUNTRY_CODE"]);
49.Console.Write(" - ");
50.Console.Write(dt.Rows[i]["BUDGET"]);
51.Console.Write(" - ");
52.Console.Write(dt.Rows[i]["USED"]);
53.Console.Write("");
54.}
55. 
56.dtAdapter = null;
57.objConn.Close();
58.objConn = null;
59.}
60.}
61.}

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 (C#)

01.using System;
02.using System.Collections.Generic;
03.using System.Linq;
04.using System.Text;
05.using System.Threading.Tasks;
06.using MySql.Data.MySqlClient;
07.using System.Data;
08. 
09.namespace MyApplication
10.{
11.class Program
12.{
13.static void Main(string[] args)
14.{
15.MySqlConnection objConn = new MySqlConnection();
16.MySqlCommand objCmd = new MySqlCommand();
17.string strConnString = string.Empty;
18.string strSQL = string.Empty;
19. 
20.strConnString = "Server=localhost;User Id=root; Password=root; Database=mydatabase; Pooling=false";
21. 
22.string sCustomerID = "C005";
23.string sName = "Fun Wipa";
24.string sEmail = "fun.wipa@thaicreate.com";
25.string sCountryCode = "TH";
26.decimal dBudget = 1000000;
27.decimal dUsed = 0;
28. 
29.strSQL = "CALL insertCustomer('" + sCustomerID + "','" + sName + "' "
30.",'" + sEmail + "','" + sCountryCode + "','" + dBudget + "','" + dUsed + "')";
31. 
32.objConn.ConnectionString = strConnString;
33.objConn.Open();
34.objCmd.Connection = objConn;
35.objCmd.CommandText = strSQL;
36.objCmd.CommandType = CommandType.Text;
37. 
38.objCmd.ExecuteNonQuery();
39. 
40.objConn.Close();
41.objConn = null;
42.}
43.}
44.}

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

Code (C#)

01.using System;
02.using System.Collections.Generic;
03.using System.Linq;
04.using System.Text;
05.using System.Threading.Tasks;
06.using MySql.Data.MySqlClient;
07.using System.Data;
08. 
09.namespace MyApplication
10.{
11.class Program
12.{
13.static void Main(string[] args)
14.{
15.MySqlConnection objConn = new MySqlConnection();
16.MySqlCommand objCmd = new MySqlCommand();
17.string strConnString = string.Empty;
18.string strStored = string.Empty;
19. 
20.strConnString = "Server=localhost;User Id=root; Password=root; Database=mydatabase; Pooling=false";
21. 
22.string sCustomerID = "C005";
23.string sName = "Fun Wipa";
24.string sEmail = "fun.wipa@thaicreate.com";
25.string sCountryCode = "TH";
26.decimal dBudget = 1000000;
27.decimal dUsed = 0;
28. 
29.strStored = "insertCustomer";
30.objCmd.Parameters.Add(new MySqlParameter("?pCustomerID", sCustomerID));
31.objCmd.Parameters.Add(new MySqlParameter("?pName", sName));
32.objCmd.Parameters.Add(new MySqlParameter("?pEmail", sEmail));
33.objCmd.Parameters.Add(new MySqlParameter("?pCountryCode", sCountryCode));
34.objCmd.Parameters.Add(new MySqlParameter("?pBudget", dBudget));
35.objCmd.Parameters.Add(new MySqlParameter("?pUsed", dUsed));
36. 
37.objConn.ConnectionString = strConnString;
38.objConn.Open();
39.objCmd.Connection = objConn;
40.objCmd.CommandText = strStored;
41.objCmd.CommandType = CommandType.StoredProcedure;
42. 
43.objCmd.ExecuteNonQuery();
44. 
45.objConn.Close();
46.objConn = null;
47.}
48.}
49.}

ส่วนวิธีการ 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 (C#)

01.using System;
02.using System.Collections.Generic;
03.using System.Linq;
04.using System.Text;
05.using System.Threading.Tasks;
06.using MySql.Data.MySqlClient;
07.using System.Data;
08. 
09.namespace MyApplication
10.{
11.class Program
12.{
13.static void Main(string[] args)
14.{
15.MySqlConnection objConn = new MySqlConnection();
16.MySqlCommand objCmd = new MySqlCommand();
17.MySqlDataAdapter dtAdapter = new MySqlDataAdapter();
18. 
19.DataSet ds = new DataSet();
20.DataTable dt;
21.String strConnString, strStored;
22. 
23.strConnString = "Server=localhost;User Id=root; Password=root; Database=mydatabase; Pooling=false";
24. 
25.strStored = "getCustomer";
26.objCmd.Parameters.Add(new MySqlParameter("?pCountryCode", MySqlDbType.VarChar)).Value = "US"// IN
27.objCmd.Parameters.Add(new MySqlParameter("?pRowFound", MySqlDbType.Int32)).Direction = ParameterDirection.Output; // OUT
28. 
29.objConn.ConnectionString = strConnString;
30.objConn.Open();
31.objCmd.Connection = objConn;
32.objCmd.CommandText = strStored;
33.objCmd.CommandType = CommandType.StoredProcedure;
34. 
35.// Get Select
36.dtAdapter.SelectCommand = objCmd;
37.dtAdapter.Fill(ds);
38. 
39.// Get Result
40.// objCmd.Parameters["?pRowFound"].Value
41.Console.WriteLine(string.Format("pRowFound = {0}", objCmd.Parameters["?pRowFound"].Value));
42.Console.WriteLine("");
43. 
44.// Loop Data Table
45.dt = ds.Tables[0]; // *** Cursor at 0 , 1 , 2, ...
46.for (int i = 0; i <= dt.Rows.Count - 1; i++)
47.{
48.Console.Write(dt.Rows[i]["CUSTOMER_ID"]);
49.Console.Write(" - ");
50.Console.Write(dt.Rows[i]["NAME"]);
51.Console.Write(" - ");
52.Console.Write(dt.Rows[i]["EMAIL"]);
53.Console.Write(" - ");
54.Console.Write(dt.Rows[i]["COUNTRY_CODE"]);
55.Console.Write(" - ");
56.Console.Write(dt.Rows[i]["BUDGET"]);
57.Console.Write(" - ");
58.Console.Write(dt.Rows[i]["USED"]);
59.Console.WriteLine("");
60.}
61. 
62.objConn.Close();
63.objConn = null;
64.}
65.}
66.}

C# 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 (C#)

01.using System;
02.using System.Collections.Generic;
03.using System.Linq;
04.using System.Text;
05.using System.Threading.Tasks;
06.using MySql.Data.MySqlClient;
07.using System.Data;
08. 
09.namespace MyApplication
10.{
11.class Program
12.{
13.static void Main(string[] args)
14.{
15.MySqlConnection objConn = new MySqlConnection();
16.MySqlCommand objCmd = new MySqlCommand();
17. 
18.String strConnString, strStored;
19. 
20.strConnString = "Server=localhost;User Id=root; Password=root; Database=mydatabase; Pooling=false";
21.objConn.ConnectionString = strConnString;
22.objConn.Open();
23. 
24.string strCustomerID = "C005";
25.string strName = "Fun Wipa";
26.string strEmail = "fun.wipa@thaicreate.com";
27.string strCountryCode = "TH";
28.decimal dBudget = 100000;
29.decimal dUsed = 0;
30. 
31.strStored = "insertCustomer";
32.objCmd.Parameters.Add(new MySqlParameter("?pCustomerID", MySqlDbType.VarChar, 4)).Value = strCustomerID; // IN
33.objCmd.Parameters.Add(new MySqlParameter("?pName", MySqlDbType.VarChar, 150)).Value = strName; // IN
34.objCmd.Parameters.Add(new MySqlParameter("?pEmail", MySqlDbType.VarChar, 150)).Value = strEmail; // IN
35.objCmd.Parameters.Add(new MySqlParameter("?pCountryCode", MySqlDbType.VarChar, 2)).Value = strCountryCode; // IN
36.objCmd.Parameters.Add(new MySqlParameter("?pBudget", MySqlDbType.VarChar)).Value = dBudget; // IN
37.objCmd.Parameters.Add(new MySqlParameter("?pUsed", MySqlDbType.VarChar)).Value = dUsed; // IN
38. 
39.objCmd.Parameters.Add(new MySqlParameter("?pResult", MySqlDbType.Int32)).Direction = ParameterDirection.Output; // OUT
40.objCmd.Parameters.Add(new MySqlParameter("?pMessage", MySqlDbType.VarChar, 500)).Direction = ParameterDirection.Output; // OUT
41. 
42.objCmd.Connection = objConn;
43.objCmd.CommandText = strStored;
44.objCmd.CommandType = CommandType.StoredProcedure;
45. 
46.objCmd.ExecuteNonQuery();
47. 
48.// Get Result
49.// objCmd.Parameters["?pResult"].Value
50.Console.WriteLine(string.Format("pResult = {0}", objCmd.Parameters["?pResult"].Value));
51. 
52.// objCmd.Parameters["?pMessage"].Value
53.Console.WriteLine(string.Format("pMessage = {0}", objCmd.Parameters["?pMessage"].Value));
54. 
55.objConn.Close();
56.objConn = null;
57.}
58.}
59.}

C# MySQL Stored OUTPUT

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

C# MySQL Stored OUTPUT

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

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

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