Wednesday, May 9, 2012

c# ควรใช้ store procedure เมื่อใด

คำถามนี้ผมเจอใน Keyword ที่เข้ามาในเว็บนี้ ก็เป็นคำถามที่น่าสนใจดีจึงอยากจะตอบตามภูมิความรู้และประสบการณ์ที่ได้พัฒนาโปรแกรมมามากพอสมควร
c# ควรใช้ store procedure เมื่อใด
๑. ควรทำเป็น Store Procedure กับคำสั่งที่ใช้กับ Table ที่มีการเข้าถึงบ่อย เช่น ตารางรายการขายสินค้า เป็นต้น จะสังเกตุว่าตารางเหล่านี้จะถูกใช้บ่อยอาจจะทุก ๆ 1 วินาทีก็ว่าได้ กรณีที่เป็นร้านค้าปลีกขนาดใหญ่ หรือ มียอดขายดีดังนั้นตารางนี้ควรเขียน Store Procedure เพื่อการบันทึกข้อมูลและควรใช้คีย์หลักเป็นแบบอัตโนมัติเพราะคุณไม่มีเวลาที่จะมารัน Auto Run เองเพราะจะเป็นภาระต่อ Server และเครื่องอย่างมาก หลังจาก Insert ก็ควร Return ค่าที่ Insert ลงไปด้วผมจะยกตัวอย่างทั้งแบบที่เป็น MySQL และ SQL Server ด้วยตามนี้
ตัวอย่าง MySQL
?
01
02
03
04
05
06
07
08
09
10
11
DELIMITER $$
 
DROP PROCEDURE IF EXISTS `wagadb`.`InsertDocinv`$$
 
CREATE DEFINER=`root`@`%` PROCEDURE `InsertDocinv`(RestaurantID int(10),Uid int(10), Discount double, total double, Sta int(10), billName char(10), ar_id int(10), out RESULT int)
BEGIN
Insert Into docinv_master(RestuarantID, Date, UserID, Discount, Total, Status, Name, AR_ID) values(RestaurantID, NOW(), Uid, Discount, total, Sta, billName, ar_id);
SELECT LAST_INSERT_ID() INTO RESULT;
END$$
 
DELIMITER ;
ตัวอย่าง Source Code เรียกใช้งาน
?
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
public int Insert(DocInvMaster doc)
 {
 int ret_id = 0;
 MySqlConnection conn = new MySqlConnection(connectionString);
 MySqlCommand cmd = new MySqlCommand("InsertDocinv", conn);
 cmd.CommandType = CommandType.StoredProcedure;
 //Add Parameter...
 cmd.Parameters.Add("@RestaurantID", MySqlDbType.Int32).Value = doc.RestuarantID;
 cmd.Parameters.Add("@billName", MySqlDbType.VarChar).Value = doc.Name;
 //cmd.Parameters.Add("@Doc_Date", MySqlDbType.DateTime).Value = doc.DocDate;
 cmd.Parameters.Add("@Uid", MySqlDbType.Int32).Value = doc.UID;
 cmd.Parameters.Add("@total", MySqlDbType.Float).Value = doc.Total;
 cmd.Parameters.Add("@Discount", MySqlDbType.Float).Value = doc.Discount;
 cmd.Parameters.Add("@ar_id", MySqlDbType.Int32).Value = doc.AR_ID;
 cmd.Parameters.Add("@Sta", MySqlDbType.Int32).Value = 0;
 MySqlParameter sqlParam = cmd.Parameters.Add("@RESULT", MySqlDbType.Int32);
 
 sqlParam.Direction = ParameterDirection.ReturnValue;
 //Execute Command...
 try
 {
 conn.Open();
 cmd.ExecuteNonQuery();
 ret_id = (int)cmd.Parameters["@RESULT"].Value;
 }
 catch (MySqlException err)
 {
 throw new ApplicationException("Insert command has error..." + err.ToString());
 }
 finally
 {
 conn.Close();
 }
 return ret_id;
 }
ตัวอย่าง SQL SERVER
?
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[InsertDocinv]
@RestuarantID int,
@UID nchar(3),
@Discount float,
@Total float,
@Status int,
@Name nchar(10),
@AR_ID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @RESULT int
SET @RESULT = 0
 
-- Insert statements for procedure here
Insert into DocInv_Master(RestuarantID,Date, UserID, Discount,Total,Status,Name,AR_ID)
Values(@RestuarantID, GETDATE(), @UID,@Discount,@Total,@Status,@Name,@AR_ID)
SET @RESULT = Cast(SCOPE_IDENTITY() AS int)
RETURN @RESULT
END
 
GO
ตัวอย่าง Source Code
?
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
public int Insert(DocInvMaster doc)
 {
 int ret_id = 0;
 SqlConnection conn = new SqlConnection(connectionString);
 SqlCommand cmd = new SqlCommand("InsertDocinv", conn);
 cmd.CommandType = CommandType.StoredProcedure;
 //Add Parameter...
 cmd.Parameters.Add("@RestuarantID", SqlDbType.Int).Value = doc.RestuarantID;
 cmd.Parameters.Add("@Name", SqlDbType.NChar).Value = doc.Name;
 //cmd.Parameters.Add("@Doc_Date", SqlDbType.DateTime).Value = doc.DocDate;
 cmd.Parameters.Add("@UID", SqlDbType.Int).Value = doc.UID;
 cmd.Parameters.Add("@Total", SqlDbType.Float).Value = doc.Total;
 cmd.Parameters.Add("@Discount", SqlDbType.Float).Value = doc.Discount;
 cmd.Parameters.Add("@AR_ID", SqlDbType.Int).Value = doc.AR_ID;
 cmd.Parameters.Add("@Status", SqlDbType.Int).Value = 0;
 SqlParameter sqlParam = cmd.Parameters.Add("@RESULT", SqlDbType.Int);
 sqlParam.Direction = ParameterDirection.ReturnValue;
 //Execute Command...
 try
 {
 conn.Open();
 cmd.ExecuteNonQuery();
 ret_id = (int)cmd.Parameters["@RESULT"].Value;
 }
 catch (SqlException err)
 {
 throw new ApplicationException("Insert command has error..." + err.ToString());
 }
 finally
 {
 conn.Close();
 }
 return ret_id;
 }
๒. ส่วนที่สองที่เขียนเป็น Store Procedure คือตารางที่มีขนาดใหญ่ หรือข้อมูลมากๆ เช่นข้อมูลสินค้า เป็นตารางที่ต้องมีขนาดใหญ่แน่นอนและสิ่งที่ควรเขียนเป็น Store Procedure คือฟังชั่นการ select ข้อมูล ไม่ว่าด้วยเหตุผลใดการสร้าง Store Procedure ย่อมเร็วกว่าการดึงด้วย Text Query จากโปรแกรมอยู่แล้ว
๓. อันที่สามที่ขอแนะนำคือ Report ของระบบ หมายถึงการสร้างรายงานควรดึงข้อมูลมาจาก Store Procedure
ขอแนะนำ ๓ อย่างนะครับ ถ้าใครมีความเห็นแตกต่างก็ตอบกลับมาด้วยจะเป็นพระคุณอย่างสูง
Posted by at
Labels:

1 comment:

Subscribe to: Post Comments (Atom)