In this article, I will explain you how to pass Table-Valued parameter to a Stored Procedure using ADO.Net with C#.

Database Table :

Below is the script to create table.

create table tblEmployee
(
[EmpId] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](20) NULL,
[Gender] [varchar](10) NULL
)

Table-Valued Parameter Type :

Below is the script create to table type. You can see EmployeeType as shown below.

create type [dbo].[EmployeeType] as Table
(
[Name] [varchar](20) NULL,
[Gender] [varchar](10) NULL
)

Stored Procedure :

Below is the stored procedure which takes EmployeeType as input parameter.

create procedure usp_Employee

@Employee As [dbo].[EmployeeType] readonly

as
begin
insert into tblEmployee(Name,Gender) 
select Name,Gender From @Employee
end

Import Namespaces :

Import below namespaces.

using System.Data;
using System.Data.SqlClient;

C# Code :

Below is the C# code to pass DataTable as input to stored procedure. You can pass Generic List, LINQ data etc.

DataTable dt = new DataTable();
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Gender", typeof(string));
dt.Rows.Add("Rahul", "Male");
dt.Rows.Add("John", "Male");
dt.Rows.Add("Mary", "Female");
dt.Rows.Add("Mathew", "Male");


string ConnectionString = "Server=xxxx; database=xxxx; user id=xxxx; password=xxxx";

SqlConnection con = new SqlConnection(ConnectionString);

SqlCommand cmd = new SqlCommand("usp_Employee", con);
cmd.CommandType = CommandType.StoredProcedure;


cmd.Parameters.AddWithValue("@Employee", dt);

con.Open();
cmd.ExecuteNonQuery();
con.Close();

You can check Table-Valued parameter type using SQL Query as shown below.

declare @EmployeeData As [dbo].[EmployeeType]

insert into @EmployeeData(Name, Gender) values('Rahul','Male')
insert into @EmployeeData(Name, Gender) values('Mary','Female')
insert into @EmployeeData(Name, Gender) values('John','Male')
insert into @EmployeeData(Name, Gender) values('Mathew','Male')

execute usp_Employee @EmployeeData

select * from tblEmployee