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 
  1. MS SQL Server
  2. Node.js installed with packages (Express, MSSql, and body-parser) 

Step 1

Create Table value type.

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

Step 2

After creating Table value type, create a stored procedure that will use the created Table value type as parameter.

  1. Create PROCEDURE [dbo].[Proc_insertemployee] (@tbl_Employee TVP_EMPLOYEE readonly)  
  2. AS  
  3. BEGIN  
  4. BEGIN try  
  5. -- Insert statements for procedure here  
  6. INSERT INTO tbl_employee  
  7. (NAME,  
  8. salary)  
  9. SELECT NAME,  
  10. salary  
  11. FROM @tbl_Employee  
  12. Select 1 as 'Code''Inserted Successfuly.' as 'Message'  
  13. END try  
  14. BEGIN catch  
  15. DECLARE @ErrorNumber INT  
  16. DECLARE @ErrorMessage VARCHAR(2000)  
  17. DECLARE @ErrorSeverity INT  
  18. DECLARE @ErrorState INT  
  19. SELECT @ErrorNumber = Error_number(),  
  20. @ErrorMessage = 'Error occured at time of inserting'  
  21. + Error_message(),  
  22. @Errorseverity = Error_severity(),  
  23. @ErrorState = Error_state()  
  24. RAISERROR (@Errormessage,@ErrorSeverity,@ErrorState)  
  25. END catch  
  26. 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. 

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

Step 4

Add records in that table.

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

Now, set that table as input parameter.

  1. request.input('tbl_Employee', tvp_Emp);  

Step 6

Execute the stored procedure with that input parameter.

  1. //Execute Store procedure  
  2. request.execute('Proc_insertemployee'function (err, recordsets, returnValue) {  
  3.    console.dir(JSON.stringify(recordsets[0][0]));  
  4.    res.end(JSON.stringify(recordsets[0][0]));  
  5. });  
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.