Sử dụng Table-Valued Parameter trong SQL Server 2008 và ADO.NET

Table-Valued Parameter đưa ra cho bạn một phương pháp đơn giản để truyền dữ liệu từ ứng dụng vào SQL Server mà không phải thực hiện các vòng lặp hoặc các xử lý dữ liệu logic phức tạp phía server. Bạn có thể chuyển toàn bộ các dòng dữ liệu trong ứng dụng và gửi về server chỉ bằng một tham số đơn của câu lệnh SQL.

Tạo kiểu Table-Valued Parameter trong SQL Server

Kiểu dữ liệu Table-Valued Parameter được định nghĩa bằng cách sử dụng câu lệnh Transact-SQL CREATE TYPE.

Câu lệnh sau sẽ tiến hành tạo kiểu dữ liệu EmployeeTableType gồm có hai cột EmployeeID và EmployeeName:

CREATE TYPE dbo.EmployeeTableType AS TABLE ( EmployeeID int, EmployeeName nvarchar(50) );

Tạo Stored Procedure sử dụng Table-Valued Parameter


Ví dụ sau sẽ sử dụng Table-Valued Parameter để insert đồng thời nhiều dòng dữ liệu nhân viên vào table Employees:

CREATE PROCEDURE usp_InsertEmployees
(
     @Employees dbo.EmployeeTableType READONLY
)
AS
BEGIN

     INSERT INTO dbo.Employees
     SELECT EmployeeID, EmployeeName
     FROM @Employees

END

Truyền một Table-Valued Parameter vào Stored Procedure sử dụng ADO.NET

Ví dụ sau viết bằng C# sẽ mô tả cho bạn thấy cách sử dụng các Stored Procedure có tham số là một Table-Valued. Trong ví dụ này sẽ sử dụng một phương thức tên là GetAddedEmployees() để lấy danh sách các nhân viên mà bạn đã nhập trong một GridView và trả về là kiểu DataTable. Các câu lệnh tiếp theo sẽ thực hiện việc truyền DataTable như một tham số cho Stored Procedure và thực hiện chỉ với một câu lệnh đơn giản.

// Khởi tạo một kết nối SqlConnection.
using (SqlConnection connection = new SqlConnnection("..."))
{
    // Lấy DataTable chứa dữ liệu nhân viên mới cần đưa vào Database
    DataTable addedEmployees = GetAddedEmployees();
        
    //Tạo câu lệnh insert
     SqlCommand insertCommand = new SqlCommand(
          "usp_InsertCategories", connection);

     insertCommand.CommandType = CommandType.StoredProcedure;
   
     SqlParameter param = insertCommand.Parameters.AddWithValue(
          "@Employees", addedEmployees);
     param.SqlDbType = SqlDbType.Structured;

     // Thực hiện lệnh insert dữ liệu.
     connection.Open();
     insertCommand.ExecuteNonQuery();
}

Truyền một Table-Valued Parameter vào câu lệnh SQL


Trường hợp này sẽ là một ví dụ khác để mô tả cho bạn cách sử dụng trong trường hợp sử dụng một câu lệnh SQL trực tiếp thay vì dùng Stored Procedure.

// Khởi tạo một kết nối SqlConnection.
using (SqlConnection connection = new SqlConnection("..."))
{
 // Lấy DataTable chứa dữ liệu nhân viên mới cần đưa vào Database
 DataTable addedEmployees = GetAddedEmployees();

 // Định nghĩa câu lệnh INSERT
 string sqlInsert = 
  "INSERT INTO dbo.Employees (EmployeeID, EmployeeName)"
  + " SELECT e.EmployeeID, e.EmployeeName"
  + " FROM @Employees AS e;";

 //Tạo câu lệnh INSERT
 SqlCommand insertCommand = new SqlCommand(
  sqlInsert, connection);
 
 SqlParameter param = insertCommand.Parameters.AddWithValue(
  "@Employees", addedEmployees);
 //Khai báo kiểu cấu trúc và đặt kiểu Table-Valued
 param.SqlDbType = SqlDbType.Structured;
 param.TypeName = "dbo.EmployeeTableType";

 //Thực thi câu lệnh
 connection.Open();
 insertCommand.ExecuteNonQuery();
}

Kết luận
Table-Valued Parameter là một tính năng mới của SQL Server 2008 và .NET Framework 3.5. Bạn không thể sử dụng Table-Valued Parameter để gửi dữ liệu ra và cũng không chấp nhận tham số OUTPUT. Ngoài ra, bạn không thể sử dụng Table-Valued Parameter trong Function trong SQL Server.