Handle Table value type using node REST API + MS-Sql + Express

In my previous blog I have explained that how we can achieve bulk insert with Table value type in Entity framework.

After that when I was working with Node API then also there was a same scenario to insert bulk data from application.

At that time a question was raised in my mind that many of you have also in your mind that

Is it possible to use Table value type in node js?

So the answer is yes we can insert bulk data using table value type in Node js too.

So we are going to implement Table value type in Node JS.

To implement it we just need to follow some steps below.

prerequisites

Step 1:

Create Table value Type

CREATE TYPE [dbo].[Tvp_Employee] AS TABLE(
[Name] [varchar](50) NULL, 
[Salary] [numeric](18, 0) NULL
) 
GO 

Step 2:

After creating table value type. Create stored procedure that will use created Table value type as parameter.

Create PROCEDURE [dbo].[Proc_insertemployee] (@tbl_Employee TVP_EMPLOYEE readonly) 
AS 
BEGIN
BEGIN try
-- Insert statements for procedure here
INSERT INTO tbl_employee 
(NAME, 
salary) 
SELECT NAME,
salary 
FROM @tbl_Employee
Select 1 as 'Code', 'Inserted Successfuly.' as 'Message'
END try 
BEGIN catch
DECLARE @ErrorNumber INT
DECLARE @ErrorMessage VARCHAR(2000)
DECLARE @ErrorSeverity INT 
DECLARE @ErrorState INT 
SELECT @ErrorNumber = Error_number(),
@ErrorMessage = 'Error occured at time of inserting' 
+ Error_message(), 
@Errorseverity = Error_severity(),
@ErrorState = Error_state() 
RAISERROR (@Errormessage,@ErrorSeverity,@ErrorState)
END catch 
END 

Step 3:

Make a SQL table using Node js and respective columns.

It should match the fields of table value type that we created in SQL.

var tvp_Emp = new sql.Table();
// Columns must correspond with type we have created in database. 
tvp_Emp.columns.add('Name', sql.VarChar(50)); 
tvp_Emp.columns.add('Salary', sql.Decimal(5, 0));

Step 4:

Add records in that table

// Add data into the table that will be pass into the procedure 
for (var i = 1; i <= 5; i++) { 
 tvp_Emp.rows.add('MiteshGadhiya_' + i, (10000 * i)); 
} 

Now set that table as input parameter.

request.input('tbl_Employee', tvp_Emp);

Step 6:

Execute store procedure with that input parameter.

//Execute Store procedure 
request.execute('Proc_insertemployee', function (err, recordsets, returnValue) { 
 console.dir(JSON.stringify(recordsets[0][0])); 
 res.end(JSON.stringify(recordsets[0][0])); 
}); 

Step 7:

Run your API and test it… It will insert all records in database

#CMD

Â

#PostMan API check

Â