Handle Table Value Type Using Node REST API + MS-SQL + Express

In my previous blog, I explained how we can achieve bulk insertion with Table value types in Entity Framework. After that, while working with Node.js API, I encountered with the same scenario there also, where I neede to insert bulk data from the application.

At that time, this clicked in my mind that many of us may have faced the same issue and if it is possible to use Table value type in Node.js.

The answer is "YES". We can insert bulk data using Table value types 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 as given below.

Prerequisites

MS SQL Server
Node.js installed with packages (Express, MSSql, and body-parser)

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 a stored procedure that will use the 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 an SQL table using Node.js and respective columns. It should match the fields of the 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));
}

Step 5

Now, set that table as input parameter.

request.input('tbl_Employee', tvp_Emp);

Step 6

Execute the stored 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 the records in database.

#CMD

#PostMan API check

That’s it; we are done.

Hope this information will be helpful.