Using Table Type Parameter In .NET Core

Introduction

 
We have a lot of articles and blogs which explain how to pass the table type parameter. But in .NET Core, it's a bit different approach. In this article, we are going to discuss how to pass the table type parameter to stored procedure in .NET CORE. Also we'll be converting list into Datatable to send as parameter to stored procedure.
 

Create Table in SQL server


Create a simple Employee table  with four columns. We are going to insert a list of data using table type parameters from stored procedure.
  1. CREATE TABLE [dbo].[Employee](  
  2.    [Id] [uniqueidentifier] NOT NULL,  
  3.    [FirstName] [nvarchar](100) NULL,  
  4.    [LastName] [nvarchar](100) NULL,  
  5.    [MiddleName] [nvarchar](100) NULL,  
  6. )  

Create Table Type Parameter

 
Create table type parameter tvpEmployee with four columns. This table type parameter will be used in stored procedure. Execute below query to create table type parameter,
  1. CREATE TYPE tvpEmployee AS TABLE  
  2. (  
  3.    Id uniqueidentifier NOT NULL,  
  4.    FirstName nvarchar(100) NULL,  
  5.    LastName nvarchar(100) NULL,  
  6.    MiddleName nvarchar(100) NULL  
  7. )  
It will create table type parameter under user-defined Table Types. Expand your database , expand Programmability and expand User-Defined Table Type.
 
Using Table Type Parameter In .NET Core
 

Create a Stored Procedure 

 
Created stored procedure which takes table-valued parameter as input.
  1. CREATE PROC [dbo].[AddEmployeeList] (@tblEmployee tvpEmployee readonly)
  2. AS
  3. BEGIN
  4. INSERT INTO EMPLOYEE
  5. SELECT * FROM @tblEmployee
  6. END
Let's test this stored procedure,
  1. DECLARE @tblEmployee tvpEmployee  
  2. INSERT INTO @tblEmployee VALUES ('3a30399e-2e5e-46d9-b8b7-cd14570ea36b''John''Doe''K')  
  3. INSERT INTO @tblEmployee VALUES ('d02ced46-77e5-4ba9-9e48-ed85e6d39784''Sachin','Ramesh''Tendulkar')  
  4. EXECUTE AddEmployeeList @tblEmployee  
Using Table Type Parameter In .NET Core
 
Yes, it's working fine. Let's see how to call this stored procedure in .NET CORE WEB API.
 

Call Stored Procedure in Web API .NET CORE

 
Write a method, which takes a list of employees as a parameter. Later convert List of employee into Datatable (Using Wrapper class explained in next step) . Create SQL Parameter with the same table as variable used in stored procedure for table type parameter (tblEmployee) with type as Structured.
 
Execute Stored procedure and pass the parameters as shown below,
  1. public int AddEmployeeList(List < Employee > employee) {  
  2.     DataTable selloutTable = employee.ConvertToDataTable < Employee > ();  
  3.     SqlParameter[] parameters = new SqlParameter[] {  
  4.         dBHelper.CreateParameter("@tblEmployee", selloutTable, SqlDbType.Structured),  
  5.     };  
  6.     var result = dBHelper.ExecuteNonQuery("dbo.AddEmployeeList", parameters);  
  7.     return result;  
  8. }  

Converting List to Datatable

 
Use below wrapper class to convert List of objects into data table,
  1. public static class DataTableConverter {  
  2.     public static DataTable ConvertToDataTable < T > (this IEnumerable < T > dataList) where T: class {  
  3.         DataTable convertedTable = new DataTable();  
  4.         PropertyInfo[] propertyInfo = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);  
  5.         foreach(PropertyInfo prop in propertyInfo) {  
  6.             convertedTable.Columns.Add(prop.Name);  
  7.         }  
  8.         foreach(T item in dataList) {  
  9.             var row = convertedTable.NewRow();  
  10.             var values = new object[propertyInfo.Length];  
  11.             for (int i = 0; i < propertyInfo.Length; i++) {  
  12.                 var test = propertyInfo[i].GetValue(item, null);  
  13.                 row[i] = propertyInfo[i].GetValue(item, null);  
  14.             }  
  15.             convertedTable.Rows.Add(row);  
  16.         }  
  17.         return convertedTable;  
  18.     }  
  19. }  
DBHelper Wrapper Class 
  1. using Microsoft.EntityFrameworkCore;  
  2. using Sdz.BusinessService.Base;  
  3. using Sdz.Services.Context;  
  4. using System;  
  5. using System.Collections.Generic;  
  6. using System.Data;  
  7. using System.Data.SqlClient;  
  8. using System.Text;  
  9. namespace Sdz.DbHelper {  
  10.     public class DbHelper: IDbHelper {  
  11.         private readonly IServicesContext serviceContext;  
  12.         public DbHelper(IServicesContext serviceContext) {  
  13.             this.serviceContext = serviceContext;  
  14.         }#region Execute Non Query  
  15.         public int ExecuteNonQuery(string commandText, SqlParameter[] parameters) {  
  16.             var conn = serviceContext.DbContext.Database.GetDbConnection();  
  17.             conn.Open();  
  18.             var command = conn.CreateCommand();  
  19.             command.CommandText = commandText;  
  20.             command.CommandType = CommandType.StoredProcedure;  
  21.             foreach(var parameter in parameters) {  
  22.                 command.Parameters.Add(parameter);  
  23.             }  
  24.             var result = command.ExecuteNonQuery();  
  25.             conn.Close();  
  26.             return result;  
  27.         }#endregion  
  28.         #region Execute Scalar  
  29.         public string ExecuteScalar(string commandText, SqlParameter[] parameters) {  
  30.             var conn = serviceContext.DbContext.Database.GetDbConnection();  
  31.             conn.Open();  
  32.             var command = conn.CreateCommand();  
  33.             command.CommandText = commandText;  
  34.             command.CommandType = CommandType.StoredProcedure;  
  35.             foreach(var parameter in parameters) {  
  36.                 command.Parameters.Add(parameter);  
  37.             }  
  38.             var result = command.ExecuteScalar();  
  39.             conn.Close();  
  40.             return result.ToString();  
  41.         }#endregion  
  42.         #region Execute Reader  
  43.         public SqlDataReader ExecuteReader(string commandText, SqlParameter[] parameters) {  
  44.             var conn = serviceContext.DbContext.Database.GetDbConnection();  
  45.             conn.Open();  
  46.             var command = conn.CreateCommand();  
  47.             command.CommandText = commandText;  
  48.             command.CommandType = CommandType.StoredProcedure;  
  49.             foreach(var parameter in parameters) {  
  50.                 command.Parameters.Add(parameter);  
  51.             }  
  52.             var result = command.ExecuteReader();  
  53.             conn.Close();  
  54.             return result as SqlDataReader;  
  55.         }#endregion  
  56.         #region DataSet  
  57.         public DataSet ExecuteDataset(string commandText, SqlParameter[] parameters) {  
  58.             DataSet resultSet = new DataSet();  
  59.             using(var conn = serviceContext.DbContext.Database.GetDbConnection()) {  
  60.                 conn.Open();  
  61.                 var command = conn.CreateCommand() as SqlCommand;  
  62.                 command.CommandText = commandText;  
  63.                 command.CommandType = CommandType.StoredProcedure;  
  64.                 foreach(var parameter in parameters) {  
  65.                     command.Parameters.Add(parameter);  
  66.                 }  
  67.                 SqlDataAdapter adapter = new SqlDataAdapter(command);  
  68.                 adapter.Fill(resultSet);  
  69.             }  
  70.             return resultSet;  
  71.         }#endregion  
  72.         #region Create Parameter  
  73.         public SqlParameter CreateParameter(string name, object value, SqlDbType dbType, ParameterDirection direction = ParameterDirection.Input, int size = 0) {  
  74.             return new SqlParameter() {  
  75.                 ParameterName = name,  
  76.                     SqlDbType = dbType,  
  77.                     Value = value,  
  78.                     Direction = direction,  
  79.                     Size = size  
  80.             };  
  81.         }#endregion  
  82.     }  
  83. }  

Summary

 
In this article, we discussed about how to pass table type parameter to stored procedure in .NET CORE. Also we converted list into the data table to pass it as a parameter for stored procedure. I hope this article is useful. Please comment below for any queries.