Using Table Type Parameter In .NET Core

Introduction

We have a lot of articles and blogs that 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 the list into Datatable to send as a parameter to the stored procedure.

Create a Table in the SQL server

Create a simple Employee table with four columns. We are going to insert a list of data using table-type parameters from a stored procedure.

CREATE TABLE [dbo].[Employee](
   [Id] [uniqueidentifier] NOT NULL,
     NULL,
     NULL,
     NULL,
)

Create Table Type Parameter

Create table type parameter tvpEmployee with four columns. This table-type parameter will be used in the stored procedure. Execute the below query to create a table-type parameter,

CREATE TYPE tvpEmployee AS TABLE  
(  
   Id uniqueidentifier NOT NULL,  
   FirstName nvarchar(100) NULL,  
   LastName nvarchar(100) NULL,  
   MiddleName nvarchar(100) NULL  
)

It will create table-type parameters under user-defined Table Types. Expand your database, expand Programmability, and expand User-Defined Table Type.

Create a Stored Procedure

Created stored procedure that takes table-valued parameter as input.

CREATE PROC [dbo].[AddEmployeeList] (@tblEmployee tvpEmployee readonly)
AS
BEGIN

INSERT INTO EMPLOYEE
SELECT * FROM @tblEmployee

END

Let's test this stored procedure,

DECLARE @tblEmployee tvpEmployee  
INSERT INTO @tblEmployee VALUES ('3a30399e-2e5e-46d9-b8b7-cd14570ea36b', 'John', 'Doe', 'K')  
INSERT INTO @tblEmployee VALUES ('d02ced46-77e5-4ba9-9e48-ed85e6d39784', 'Sachin', 'Ramesh', 'Tendulkar')  
EXECUTE AddEmployeeList @tblEmployee  

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 the List of employees into Datatable (Using the Wrapper class explained in the next step). Create SQL Parameter with the same table as the variable used in a stored procedure for table type parameter (tblEmployee) with type as Structured.

Execute the Stored procedure and pass the parameters as shown below,

public int AddEmployeeList(List<Employee> employee) {  
    DataTable selloutTable = employee.ConvertToDataTable<Employee>();  
    SqlParameter[] parameters = new SqlParameter[] {  
        dBHelper.CreateParameter("@tblEmployee", selloutTable, SqlDbType.Structured),  
    };  
    var result = dBHelper.ExecuteNonQuery("dbo.AddEmployeeList", parameters);  
    return result;  
}

Converting List to Datatable

Use the below wrapper class to convert the List of objects into a data table.

public static class DataTableConverter {
    public static DataTable ConvertToDataTable<T>(this IEnumerable<T> dataList) where T: class {
        DataTable convertedTable = new DataTable();
        PropertyInfo[] propertyInfo = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
        foreach (PropertyInfo prop in propertyInfo) {
            convertedTable.Columns.Add(prop.Name);
        }
        foreach (T item in dataList) {
            var row = convertedTable.NewRow();
            var values = new object[propertyInfo.Length];
            for (int i = 0; i < propertyInfo.Length; i++) {
                var test = propertyInfo[i].GetValue(item, null);
                row[i] = propertyInfo[i].GetValue(item, null);
            }
            convertedTable.Rows.Add(row);
        }
        return convertedTable;
    }
}

DBHelper Wrapper Class

using Microsoft.EntityFrameworkCore;
using Sdz.BusinessService.Base;
using Sdz.Services.Context;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace Sdz.DbHelper {
    public class DbHelper : IDbHelper {
        private readonly IServicesContext serviceContext;

        public DbHelper(IServicesContext serviceContext) {
            this.serviceContext = serviceContext;
        }

        #region Execute Non Query

        public int ExecuteNonQuery(string commandText, SqlParameter[] parameters) {
            var conn = serviceContext.DbContext.Database.GetDbConnection();
            conn.Open();
            var command = conn.CreateCommand();
            command.CommandText = commandText;
            command.CommandType = CommandType.StoredProcedure;
            foreach (var parameter in parameters) {
                command.Parameters.Add(parameter);
            }
            var result = command.ExecuteNonQuery();
            conn.Close();
            return result;
        }

        #endregion

        #region Execute Scalar

        public string ExecuteScalar(string commandText, SqlParameter[] parameters) {
            var conn = serviceContext.DbContext.Database.GetDbConnection();
            conn.Open();
            var command = conn.CreateCommand();
            command.CommandText = commandText;
            command.CommandType = CommandType.StoredProcedure;
            foreach (var parameter in parameters) {
                command.Parameters.Add(parameter);
            }
            var result = command.ExecuteScalar();
            conn.Close();
            return result.ToString();
        }

        #endregion

        #region Execute Reader

        public SqlDataReader ExecuteReader(string commandText, SqlParameter[] parameters) {
            var conn = serviceContext.DbContext.Database.GetDbConnection();
            conn.Open();
            var command = conn.CreateCommand();
            command.CommandText = commandText;
            command.CommandType = CommandType.StoredProcedure;
            foreach (var parameter in parameters) {
                command.Parameters.Add(parameter);
            }
            var result = command.ExecuteReader();
            conn.Close();
            return result as SqlDataReader;
        }

        #endregion

        #region DataSet

        public DataSet ExecuteDataset(string commandText, SqlParameter[] parameters) {
            DataSet resultSet = new DataSet();
            using (var conn = serviceContext.DbContext.Database.GetDbConnection()) {
                conn.Open();
                var command = conn.CreateCommand() as SqlCommand;
                command.CommandText = commandText;
                command.CommandType = CommandType.StoredProcedure;
                foreach (var parameter in parameters) {
                    command.Parameters.Add(parameter);
                }
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(resultSet);
            }
            return resultSet;
        }

        #endregion

        #region Create Parameter

        public SqlParameter CreateParameter(string name, object value, SqlDbType dbType, ParameterDirection direction = ParameterDirection.Input, int size = 0) {
            return new SqlParameter() {
                ParameterName = name,
                SqlDbType = dbType,
                Value = value,
                Direction = direction,
                Size = size
            };
        }

        #endregion
    }
}

Summary

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