Sunday, 6 November 2016

How to pass table-valued parameter to stored procedure using C# with

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

Creating 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

Creating 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
Passing table valued parameter to stored procedure

Creating Stored Procedure

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

create procedure usp_Employee

@Employee As [dbo].[EmployeeType] readonly

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

Import Namespaces

Import below namespaces.

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

Complete 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);


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


Post a Comment

Email Subscription

Subscribe to our newsletter to get the latest articles directly into your inbox

Blog Archive