Sử dụng Table-Valued Parameter để thực thi Stored Procedure với list Object

Trong bài viết này, mình sẽ hướng dẫn các bạn sử dụng param Table-Valued để truyền dữ liệu dạng list object vào Stored Procedure từ SQL query và trong Entity Framework.

Thực thi Stored Procedure từ SQL Server

Đầu tiên, bạn tạo table có cấu trúc như sau:

CREATE TABLE [dbo].[Customers](
 [Id] [uniqueidentifier] NOT NULL,
 [Name] [nvarchar](500) NOT NULL,
 [Age] [int] NOT NULL,
 [Address] [nvarchar](max) NULL,
 [Salary] [decimal](18, 0) NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


ALTER TABLE [dbo].[Customers] ADD  CONSTRAINT [DF_Customers_Id]  DEFAULT (newid()) FOR [Id]

Định nghĩa Table-Valued parameter trong SQL Server

CREATE TYPE dbo.CustomerType AS TABLE (
 Name nvarchar(200), 
 Age int, 
 Address nvarchar(200), 
 salary decimal(18,0)
 );

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

Định nghĩa Stored Procedured với tham số có kiểu dữ liệu là CustomerType

CREATE PROCEDURE usp_insertNewCustomer
 -- Add the parameters for the stored procedure here
 @customers CustomerType READONLY
AS
BEGIN
 INSERT INTO [dbo].[Customers]
           ([Id]
           ,[Name]
           ,[Age]
           ,[Address]
           ,[Salary])
     Select NEWID()
           ,[Name]
           ,[Age]
           ,[Address]
           ,[Salary] from @customers
END

Để test stored procedure, bạn thực thi câu lệnh theo mẫu:

DECLARE @LocationTVP LocationTableType;  

/* Add data to the table variable. */  
INSERT INTO @LocationTVP (Name, Value)  
    SELECT Name, Value  
    FROM TableX;  

/* Pass the table variable data to a stored procedure. */  
EXEC usp_YourSpName @LocationTVP;  

Trường hợp câu lệnh SQL trong ví dụ này:

DECLARE @customers CustomerType;  

/* Add data to the table variable. */  
INSERT INTO @customers ([Name], [Age], [Address], [Salary])
     VALUES('Mohamed Salah', 18, 'Dubai', 2000)


INSERT INTO @customers ([Name], [Age], [Address], [Salary])
     VALUES('Victoria', 20, 'England', 1800)

/* Pass the table variable data to a stored procedure. */  
EXEC usp_insertNewCustomer @customers;  

Thực thi Stored Procedure từ Entity Framework

var context = new BlogDbEntities();
//add parameter

var customersParam = new DataTable();
customersParam.Columns.Add("Name", typeof(string));
customersParam.Columns.Add("Age", typeof(int));
customersParam.Columns.Add("Address", typeof(string));
customersParam.Columns.Add("Salary", typeof(decimal));

customersParam.Rows.Add("Mohamed Salah", 18, "Dubai", 2000);
customersParam.Rows.Add("Victoria", 20, "England", 1800);

var pkeyword = new SqlParameter("@customers", SqlDbType.Structured)
{
 Value = customersParam,
 TypeName = "dbo.CustomerType"
};

context.Database.ExecuteSqlCommand("exec dbo.usp_insertNewCustomer @customers", pkeyword);

Tham khảo 

https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine

Chúc các bạn thành công!

Nhatkyhoctap's blog