Table Value Parameter (TVP)

Table Value Parameter (TVP)

Create DataTable with Column Name:

 private DataTable tblcolumns()

   {

        DataTable dt = new DataTable();

        dt.Columns.Add("iID", typeof(int));

        dt.Columns.Add("strName", typeof(string));

        dt.Columns.Add("iAge", typeof(int));

        dt.Columns.Add("strDistrict", typeof(string));

        return dt;

   }

Add values to abouve created DataTable:

DataTable dttest = tblcolumns();

DataRow dr = dttest .NewRow();
dr["iID"] = 1;
dr["strName"] ="Gksamy";
dr["iAge"] =29;
dr["strDistrict"] = "Cuddalore";
dttest .Rows.Add(dr);

Add Parameter to SQL Command:

SqlParameter sqlParameter1 = new SqlParameter();
sqlParameter1.ParameterName = "@TVPData";
sqlParameter1.Value = dttest;
sqlParameter1.SqlDbType = SqlDbType.Structured;
cmd.Parameters.Add(sqlParameter1);
 
Create User Defined Table Types:

CREATE TYPE [dbo].[Personal_Data] AS TABLE(
[iID] [int] NULL,
[strName] [varchar](200) NULL,
[iAge] int NULL,
[strDistrict] [varchar](200) NULL
)


Stored Procedure Parameter:

@TVPData Personal_Data readonly   

Inside the Procedure:

select  * from @TVPData