Giới thiệu
Một stored procedure là một hàm/ thủ tục được định nghĩa trước và có thể tái sử dụng trong một database (tương ứng với các query trong MS Access). SQL Server biên dịch các stored procedure giúp cho chúng làm việc hiệu quả hơn. Do đó, thay vì tạo các truy vấn động trong mã nguồn của bạn, bạn có thể được lợi ích về việc tái sử dụng và hiệu suất khi sử dụng stored procedure.. Phần sau sẽ cho bạn thấy cách để thay đổi đối tượng SqlCommand để sử dụng stored procedure. Hơn nữa, bạn sẽ thấy một lý do khác tại sao việc hỗ trợ parameter lại là một phần quan trọng của thư viện ADO.NET.
Thực thi một Stored Procedure
Ngoài việc tạo các chuỗi lệnh SQL, bạn phải thiết lập SqlCommand để thực thi stored procedure. Có hai bước để làm điều này: cho đối tượng SqlCommand biết stored procedure nào sẽ được thực thi và thiết lập chế độ thực thi stored procedure cho SqlCommand. Hai bước này được minh họa trong đoạn mã sau:
1
2
3
4
5
6
7
8
| // 1. create a command object identifying// the stored procedureSqlCommand cmd = new SqlCommand( "Ten Most Expensive Products", conn);// 2. set the command object so it knows// to execute a stored procedurecmd.CommandType = CommandType.StoredProcedure; |
Khi khai báo đối tượng SqlCommand trên, tham số đầu tiên được gán là “Ten Most Expensive Products”. Đây là tên của stored procedure trong database Northwind. Tham số thứ hai là đối tượng connection, tương tự như constructor của SqlCommand dùng để thực thi một câu truy vấn.
Dòng lệnh thứ hai chỉ cho đối tượng SqlCommand kiểu của lệnh sẽ được thực thi bằng cách gán property CommandType thành giá trị StoredProcedure của enum CommandType. Bằng cách thay đổi property CommandType này, SqlCommand sẽ hiểu được chuỗi lệnh trong tham số thứ nhất là một stored procedure. Phần còn lại của đoạn mã có thể được viết tương tự như các bài trước.
Truyền Parameter cho Stored Procedure
Dùng parameter cho stored procedure tương tự như dùng cho chuỗi lệnh truy vấn. Đoạn code sau cho thấy cách làm điều này:
1
2
3
4
5
6
7
8
9
10
11
| // 1. create a command object identifying// the stored procedureSqlCommand cmd = new SqlCommand("CustOrderHist", conn);// 2. set the command object so it knows// to execute a stored procedurecmd.CommandType = CommandType.StoredProcedure;// 3. add parameter to command, which// will be passed to the stored procedurecmd.Parameters.Add(new SqlParameter("@CustomerID", custId)); |
Constructor của SqlCommand trên xác định tên của stored procedure, CustOrderHist, trong tham số đầu tiên. Stored procedure này nhận một tham số, tên là @CustomerID. Do đó, chúng ta phải tạo một parameter bằng cách dùng đối tượng SqlParameter. Tên của parameter được truyền trong tham số đầu tiên của SqlParameter constructor phải giống với tên của tham số của stored procedure. Sau đó thực thi command giống như bạn làm với các đối tượng SqlCommand khác.
Một ví dụ hoàn chỉnh
Mã lênh trong Listing chứa một ví dụ hoàn chỉnh minh họa cách dùng stored procedure. Có các phương thức được tách riêng cho một stored procedure không tham số và cho stored procedure có tham số.
Listing 1: Executing Stored Procedures
1
2
3
4
5
6
7
8
9
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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
| using System;using System.Data;using System.Data.SqlClient;class StoredProcDemo{ static void Main() { StoredProcDemo spd = new StoredProcDemo(); // run a simple stored procedure spd.RunStoredProc(); // run a stored procedure that takes a parameter spd.RunStoredProcParams(); } // run a simple stored procedure public void RunStoredProc() { SqlConnection conn = null; SqlDataReader rdr = null; Console.WriteLine("\nTop 10 Most Expensive Products:\n"); try { // create and open a connection object conn = new SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI"); conn.Open(); // 1. create a command object identifying // the stored procedure SqlCommand cmd = new SqlCommand( "Ten Most Expensive Products", conn); // 2. set the command object so it knows // to execute a stored procedure cmd.CommandType = CommandType.StoredProcedure; // execute the command rdr = cmd.ExecuteReader(); // iterate through results, printing each to console while (rdr.Read()) { Console.WriteLine( "Product: {0,-25} Price: ${1,6:####.00}", rdr["TenMostExpensiveProducts"], rdr["UnitPrice"]); } } finally { if (conn != null) { conn.Close(); } if (rdr != null) { rdr.Close(); } } } // run a stored procedure that takes a parameter public void RunStoredProcParams() { SqlConnection conn = null; SqlDataReader rdr = null; // typically obtained from user // input, but we take a short cut string custId = "FURIB"; Console.WriteLine("\nCustomer Order History:\n"); try { // create and open a connection object conn = new SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI"); conn.Open(); // 1. create a command object identifying // the stored procedure SqlCommand cmd = new SqlCommand( "CustOrderHist", conn); // 2. set the command object so it knows // to execute a stored procedure cmd.CommandType = CommandType.StoredProcedure; // 3. add parameter to command, which // will be passed to the stored procedure cmd.Parameters.Add( new SqlParameter("@CustomerID", custId)); // execute the command rdr = cmd.ExecuteReader(); // iterate through results, printing each to console while (rdr.Read()) { Console.WriteLine( "Product: {0,-35} Total: {1,2}", rdr["ProductName"], rdr["Total"]); } } finally { if (conn != null) { conn.Close(); } if (rdr != null) { rdr.Close(); } } }} |
Phương thức RunStoredProc() trong Listing 1 đơn giản là chạy một stored procedure và in kết quả ra console. Trong phương thức RunStoredProcParams(), stored procedure nhận một tham số. Điều này cho thấy không có sự khác biệt giữa việc dùng tham số với chuỗi truy vấn và stored procedure. Các mã lệnh còn lại khá quen thuộc nếu bạn đã đọc các bài trước đây trong tutorial này.
Tổng kết
Để thực thi stored procedure, bạn cần chỉ ra tên của stored procedure trong tham số đầu tiên của một SqlCommand constructor và sau đó gán property CommandType của SqlCommand thành StoredProcedured. Bạn cũng có thể truyền các tham số cho một stored procedure bằng cách dùng đối tượng SqlParameter, tương tự như cách làm với đối tượng SqlCommand dùng để thực thi một câu truy vấn. Mỗi lần đối tượng SqlCommand được tạo, bạn có thể dùng nó giống như các đối tượng SqlCommand được mô tả trong các bài trước.
