Saturday, August 2, 2014

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

No comments:

Post a Comment