Table Value parameter in C# (TVP)

What is Table Valued parameter and It's Use

There are times when developers need to pass a large data to SQL Server from .Net Application. Using a normal ADO.NET approach will pass the data to SQL Server with Multiple rounds. This may impact performance of application as well as SQL Server.
To Eliminate this, the Table valued parameters comes to picture where a large data is passed to SQL Server using Stored procedure and a table type. This is the efficient way of inserting data to SQL Server without affecting performance of Application and SQL Server.

Steps to create Table Valued Parameter

Create a Table in SQL Server in which data to be inserted.

Create a Table valued parameter type in SQL Server.

Create a Stored Procedure.

Create a .Net Application to pass the data to Stored Procedure.

1. Create a Table in SQL Server in which data to be inserted.


CREATE TABLE EmployeeDetails
( EmpID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(20),
State VARCHAR(20),
City VARCHAR(20),
Salary int )



2. Create a Table valued parameter type in SQL Server.


CREATE TYPE dbo.EmployeeDetailsType AS TABLE
( Name VARCHAR(20),
State VARCHAR(20),
City VARCHAR(20),
Salary int )


Note: Here the column EmpID in table EmployeeDetails is set to Auto Increment. So whenever the value is inserted in the table , the EmpIDcolumn will increase the value by 1 automatically. So we don't have to declare that column in the table type. If you don't wish to set EmpIDcolumn to Auto Increment, then you have to create that column in the table type as well.


3. Create a Stored Procedure.


CREATE PROCEDURE [dbo].[sp_EmployeeDataInsert] (@EmployeeDetailsType as dbo.EmployeeDetailsType readonly)
AS
BEGIN
SET NOCOUNT ON;
Insert into EmployeeDetails select name,state,city,salary from @EmployeeDetailsType
END



4. Create a .Net Application to pass the data to Stored Procedure.


using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
namespace Table_Value_Parameter
{
class Program
{
static void Main(string[] args)
{
DataTable dt = InsertData();
dt.Rows.Add("Vishal","Maharashtra","Pune",10000);
dt.Rows.Add("Harsh", "Maharashtra", "Mumbai", 12000);
dt.Rows.Add("Raj", "Panjab", "Amritsar", 18000);
dt.Rows.Add("Suraj", "Maharashtra", "NagPur", 10000);
try
{
string conn = "Data source=VISHAL-PC\\SQL2014;database=Test;uid=sa;password=***";
SqlConnection sqlcon = new SqlConnection(conn);
sqlcon.Open();
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlcon;
cmd.CommandText = "sp_EmployeeDataInsert";
SqlParameter PARA = cmd.Parameters.AddWithValue("EmployeeDetailsType", dt);
PARA.SqlDbType = SqlDbType.Structured;
cmd.ExecuteNonQuery();
sqlcon.Close();
Console.WriteLine("Data inserted");
Console.ReadLine();
}
}
catch(SqlException ex)
{
Console.WriteLine("Error {0}", ex.Message.ToString());
Console.ReadLine();
}
}
internal static DataTable InsertData()
{
DataTable dt = new DataTable();
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("State", typeof(string));
dt.Columns.Add("City", typeof(string));
dt.Columns.Add("Salary", typeof(Int32));
return dt;
}
}
}

Output

EmpID	Name	State	City	Salary
1	Vishal	Maharashtra	Pune	10000
2	Harsh	Maharashtra	Mumbai	12000
3	Raj	Panjab	Amritsar	18000
4	Suraj	Maharashtra	NagPur	10000

Advantages

Table valued parameter is useful in cases where you have to insert large number of rows into the SQL table.

It is not only the easy way but also improves the performance of application.