Sử dụng Stored Procedure

Người đăng: Vtv12 19/6/12



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// 1.  create a command object identifying
2//     the stored procedure
3SqlCommand cmd  = new SqlCommand(
4    "Ten Most Expensive Products", conn);
5 
6// 2. set the command object so it knows
7//    to execute a stored procedure
8cmd.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:
01// 1.  create a command object identifying
02//     the stored procedure
03SqlCommand cmd  = new SqlCommand("CustOrderHist", conn);
04 
05// 2. set the command object so it knows
06//    to execute a stored procedure
07cmd.CommandType = CommandType.StoredProcedure;
08 
09// 3. add parameter to command, which
10//    will be passed to the stored procedure
11cmd.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
001using System;
002using System.Data;
003using System.Data.SqlClient;
004 
005class StoredProcDemo
006{
007    static void Main()
008    {
009        StoredProcDemo spd = new StoredProcDemo();
010 
011        // run a simple stored procedure
012        spd.RunStoredProc();
013 
014        // run a stored procedure that takes a parameter
015        spd.RunStoredProcParams();
016    }
017 
018    // run a simple stored procedure
019    public void RunStoredProc()
020    {
021        SqlConnection conn = null;
022        SqlDataReader rdr  = null;
023 
024        Console.WriteLine("\nTop 10 Most Expensive Products:\n");
025 
026        try
027        {
028            // create and open a connection object
029            conn = new
030                SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");
031            conn.Open();
032 
033            // 1.  create a command object identifying
034            //     the stored procedure
035            SqlCommand cmd  = new SqlCommand(
036                "Ten Most Expensive Products", conn);
037 
038            // 2. set the command object so it knows
039            //    to execute a stored procedure
040            cmd.CommandType = CommandType.StoredProcedure;
041 
042            // execute the command
043            rdr = cmd.ExecuteReader();
044 
045            // iterate through results, printing each to console
046            while (rdr.Read())
047            {
048                Console.WriteLine(
049                    "Product: {0,-25} Price: ${1,6:####.00}",
050                    rdr["TenMostExpensiveProducts"],
051                    rdr["UnitPrice"]);
052            }
053        }
054        finally
055        {
056            if (conn != null)
057            {
058                conn.Close();
059            }
060            if (rdr != null)
061            {
062                rdr.Close();
063            }
064        }
065    }
066 
067    // run a stored procedure that takes a parameter
068    public void RunStoredProcParams()
069    {
070        SqlConnection conn = null;
071        SqlDataReader rdr  = null;
072 
073        // typically obtained from user
074        // input, but we take a short cut
075        string custId = "FURIB";
076 
077        Console.WriteLine("\nCustomer Order History:\n");
078 
079        try
080        {
081            // create and open a connection object
082            conn = new
083                SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");
084            conn.Open();
085 
086            // 1.  create a command object identifying
087            //     the stored procedure
088            SqlCommand cmd  = new SqlCommand(
089                "CustOrderHist", conn);
090 
091            // 2. set the command object so it knows
092            //    to execute a stored procedure
093            cmd.CommandType = CommandType.StoredProcedure;
094 
095            // 3. add parameter to command, which
096            //    will be passed to the stored procedure
097            cmd.Parameters.Add(
098                new SqlParameter("@CustomerID", custId));
099 
100            // execute the command
101            rdr = cmd.ExecuteReader();
102 
103            // iterate through results, printing each to console
104            while (rdr.Read())
105            {
106                Console.WriteLine(
107                    "Product: {0,-35} Total: {1,2}",
108                    rdr["ProductName"],
109                    rdr["Total"]);
110            }
111        }
112        finally
113        {
114            if (conn != null)
115            {
116                conn.Close();
117            }
118            if (rdr != null)
119            {
120                rdr.Close();
121            }
122        }
123    }
124}
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.
——-
Nguồn tham khảo:  http://www.csharp-station.com/Tutorials/AdoDotNet/lesson07.aspx

, | edit post

0 nhận xét

Đăng nhận xét