[ADO.NET Tutorial] Lesson 07: Sử dụng Stored Procedure

Lesson này hướng dẫn cách thiết lập đối tượng SqlCommand để sử dụng một stored procedure, ngoài ra bạn cũng sẽ biết được cách dùng các parameter với stored procedure.

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.  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;

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.  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));

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

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.

Tags: