ตอนที่ 20 : Visual C# (C# .Net) เรียกใช้ EXEC/CALL - SQL Server Stored Procedure |
ตอนที่ 20 : Visual C# (C# .Net) เรียกใช้ EXEC/CALL - SQL Server Stored Procedure ในหัวข้อนี้เราจะมาเรียนรู้วิธีการใช้ Visual C# (C# .Net) บน .NET Application เรียกใช้งาน Stored Procedure ของ SQL Server Database ซึ่งในการเรียกใช้งาน Stored Procedure สามารถใช้ได้กับในหลาย ๆ Connector เช่น System.Data.SqlClient ส่วนคำสั่งในการการเรียกนั้น เราสามารถใช้คำสั่ง EXEC procedure_name() ได้ทันที ถ้าหากมีค่า Parameters ก็จะต้องทำการ Pass ค่าไปกับ Query ด้วย หรือจะใช้แบบ Parameter Query แบบ CommandType.StoredProcedure ก็ได้ โดยทั้ง 2 วิธีสามารถใช้ได้เหมือนกัน และหลังจากที่ EXEC แล้วก็สามารถที่จะอ่าน Result ที่ Stored Procedure นั้นได้ส่งกลับมาให้
Call SQL Server Stored Procedure
EXEC procedure_name agr1,agr2,...
โครงสร้างของตาราง CUSTOMER
Table : CUSTOMER
Example 1 : การใช้ .NET เรียก Stored Procedure แบบ Query ข้อมูลออกมา
Stored Procedure Name : getCustomer()
CREATE PROCEDURE [dbo].[getCustomer]
@pCountryCode VARCHAR(2)
AS
BEGIN
-- set customer
SELECT * FROM CUSTOMER WHERE COUNTRY_CODE = @pCountryCode
END
Code (C#)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
namespace MyApplication
{
class Program
{
static void Main(string[] args)
{
SqlConnection objConn = new SqlConnection();
SqlCommand objCmd = new SqlCommand();
SqlDataAdapter dtAdapter = new SqlDataAdapter();
DataSet ds = new DataSet();
DataTable dt;
String strConnString, strSQL;
strConnString = "Server=localhost;UID=sa;PASSWORD=;database=mydatabase; " +
" Max Pool Size=400;Connect Timeout=600;";
strSQL = "EXEC 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;
}
}
}
Result
หรือจะใช้แบบ Parameter Query หรือ CommandType.StoredProcedure ก็ได้เช่นเดียวกัน
Code (C#)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
namespace MyApplication
{
class Program
{
static void Main(string[] args)
{
SqlConnection objConn = new SqlConnection();
SqlCommand objCmd = new SqlCommand();
SqlDataAdapter dtAdapter = new SqlDataAdapter();
DataSet ds = new DataSet();
DataTable dt;
String strConnString, strStored;
strConnString = "Server=localhost;UID=sa;PASSWORD=;database=mydatabase; " +
" Max Pool Size=400;Connect Timeout=600;";
strStored = "getCustomer";
objCmd.Parameters.Add(new SqlParameter("@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;
}
}
}
Example 2 : การใช้ .NET เรียก Stored Procedure แบบ Query เพื่อ Insert ข้อมูล
Stored Procedure Name : insertCustomer()
CREATE PROCEDURE [dbo].[insertCustomer]
@pCustomerID VARCHAR(4),
@pName VARCHAR(50),
@pEmail VARCHAR(50),
@pCountryCode VARCHAR(2),
@pBudget DECIMAL(18,2),
@pUsed DECIMAL(18,2)
AS
BEGIN
-- insert customer
INSERT INTO CUSTOMER VALUES (@pCustomerID,@pName,@pEmail,@pCountryCode,@pBudget,@pUsed);
END
Code (C#)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
SqlConnection objConn = new SqlConnection();
SqlCommand objCmd = new SqlCommand();
string strConnString = string.Empty;
string strSQL = string.Empty;
strConnString = "Server=localhost;UID=sa;PASSWORD=;database=mydatabase; " +
" Max Pool Size=400;Connect Timeout=600;";
string sCustomerID = "C005";
string sName = "Fun Wipa";
string sEmail = "[email protected]";
string sCountryCode = "TH";
decimal dBudget = 1000000;
decimal dUsed = 0;
strSQL = "EXEC 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;
}
}
}
หรือจะใช้แบบ Parameter Query หรือ CommandType.StoredProcedure ก็ได้เช่นเดียวกัน
Code (C#)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
namespace MyApplication
{
class Program
{
static void Main(string[] args)
{
SqlConnection objConn = new SqlConnection();
SqlCommand objCmd = new SqlCommand();
string strConnString = string.Empty;
string strStored = string.Empty;
strConnString = "Server=localhost;UID=sa;PASSWORD=;database=mydatabase; " +
" Max Pool Size=400;Connect Timeout=600;";
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 SqlParameter("@pCustomerID", sCustomerID));
objCmd.Parameters.Add(new SqlParameter("@pName", sName));
objCmd.Parameters.Add(new SqlParameter("@pEmail", sEmail));
objCmd.Parameters.Add(new SqlParameter("@pCountryCode", sCountryCode));
objCmd.Parameters.Add(new SqlParameter("@pBudget", dBudget));
objCmd.Parameters.Add(new SqlParameter("@pUsed", dUsed));
objConn.ConnectionString = strConnString;
objConn.Open();
objCmd.Connection = objConn;
objCmd.CommandText = strStored;
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.ExecuteNonQuery();
objConn.Close();
objConn = null;
}
}
}
ส่วนวิธีการ UPDATE และ DELETE ก็ใช้หลักการเดียวกับการ INSERT ข้อมูล
เพิ่มเติม : ในกรณีที่ต้องการกำหนดชนิด DataType ของ Parameters
objCmd.Parameters.Add("@pCustomerID", SqlDbType.VarChar).Value = sCustomerID;
objCmd.Parameters.Add("@pName", SqlDbType.VarChar).Value = sName;
objCmd.Parameters.Add("@pEmail", SqlDbType.VarChar).Value = sEmail;
objCmd.Parameters.Add("@pCountryCode", SqlDbType.VarChar).Value = sCountryCode;
objCmd.Parameters.Add("@pBudget", SqlDbType.Decimal).Value = dBudget;
objCmd.Parameters.Add("@pUsed", SqlDbType.Decimal).Value = dUsed;
สำหรับตัวอย่างที่ 3 และ 4 จะเป็นการอ่านจาก OUTPUT หรือ OUT
ตอนที่ 10 : การใช้ OUTPUT และ OUT เพื่อส่งค่ากลับ (SQL Server : Stored Procedure)
Example 3 : การใช้ .NET เรียก Stored Procedure และอ่านค่า OUTPUT พร้อมกับ SELECT ข้อมูล
Stored Procedure Name : getCustomer()
CREATE PROCEDURE [dbo].[getCustomer]
@pCountryCode VARCHAR(2),
@pRowFound INT OUTPUT
AS
BEGIN
-- set rowcount
SELECT @pRowFound = COUNT(*) FROM CUSTOMER WHERE COUNTRY_CODE = @pCountryCode
-- set customer
SELECT * FROM CUSTOMER WHERE COUNTRY_CODE = @pCountryCode
END
Code (C#)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
namespace MyApplication
{
class Program
{
static void Main(string[] args)
{
SqlConnection objConn = new SqlConnection();
SqlCommand objCmd = new SqlCommand();
SqlDataAdapter dtAdapter = new SqlDataAdapter();
DataSet ds = new DataSet();
DataTable dt;
String strConnString, strStored;
strConnString = "Server=localhost;UID=sa;PASSWORD=;database=mydatabase; " +
" Max Pool Size=400;Connect Timeout=600;";
strStored = "getCustomer";
objCmd.Parameters.Add(new SqlParameter("@pCountryCode", SqlDbType.VarChar)).Value = "US"; // IN
objCmd.Parameters.Add(new SqlParameter("pRowFound", SqlDbType.Int)).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;
}
}
}
ค่าที่ถูกส่งมาทั้ง SELECT และ Parameters ที่เป็นแบบ OUT
Example 4 : การใช้ .NET เรียก Stored Procedure และอ่านค่า OUTPUT ที่มีตั้งแต่ 2 ค่าขึ้นไป
Stored Procedure Name : insertCustomer()
CREATE PROCEDURE [dbo].[insertCustomer]
@pCustomerID VARCHAR(4),
@pName VARCHAR(50),
@pEmail VARCHAR(50),
@pCountryCode VARCHAR(2),
@pBudget DECIMAL(18,2),
@pUsed DECIMAL(18,2),
@pResult INT OUTPUT,
@pMessage VARCHAR(500) OUTPUT
AS
BEGIN
BEGIN TRY
-- insert customer
INSERT INTO CUSTOMER VALUES (@pCustomerID,@pName,@pEmail,@pCountryCode,@pBudget,@pUsed);
SET @pResult = @@ROWCOUNT;
SET @pMessage = 'Insert Data Successfully';
END TRY
BEGIN CATCH
SET @pResult = 0;
SELECT @pMessage = ERROR_MESSAGE();
END CATCH
END
Code (C#)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
namespace MyApplication
{
class Program
{
static void Main(string[] args)
{
SqlConnection objConn = new SqlConnection();
SqlCommand objCmd = new SqlCommand();
String strConnString, strStored;
strConnString = "Server=localhost;UID=sa;PASSWORD=;database=mydatabase; " +
" Max Pool Size=400;Connect Timeout=600;";
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 SqlParameter("pCustomerID", SqlDbType.VarChar, 4)).Value = strCustomerID; // IN
objCmd.Parameters.Add(new SqlParameter("pName", SqlDbType.VarChar, 150)).Value = strName; // IN
objCmd.Parameters.Add(new SqlParameter("pEmail", SqlDbType.VarChar, 150)).Value = strEmail; // IN
objCmd.Parameters.Add(new SqlParameter("pCountryCode", SqlDbType.VarChar, 2)).Value = strCountryCode; // IN
objCmd.Parameters.Add(new SqlParameter("pBudget", SqlDbType.VarChar)).Value = dBudget; // IN
objCmd.Parameters.Add(new SqlParameter("pUsed", SqlDbType.VarChar)).Value = dUsed; // IN
objCmd.Parameters.Add(new SqlParameter("pResult", SqlDbType.Int)).Direction = ParameterDirection.Output; // OUT
objCmd.Parameters.Add(new SqlParameter("pMessage", SqlDbType.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;
}
}
}
กรณีที่ Insert ข้อมูลสำเร็จ
กรณีที่ Insert ข้อมูลไม่สำเร็จ
ตอนที่ 18 : Visual Basic (VB.Net) เรียกใช้ EXEC SQL Server Stored Procedure
|
ช่วยกันสนับสนุนรักษาเว็บไซต์ความรู้แห่งนี้ไว้ด้วยการสนับสนุน 
