Create a user defined table type in c# to use in sql server stored procedure
Simplest option is to create a DataTable
in C# code and pass it as a parameter to your procedure. Assuming that you have created a User Defined Table Type as:
CREATE TYPE [dbo].[userdefinedtabletype] AS TABLE(
[ID] [varchar](255) NULL,
[Name] [varchar](255) NULL
)
then in your C# code you would do:
DataTable dt = new DataTable();
dt.Columns.Add("ID", typeof (string));
dt.Columns.Add("Name", typeof (string));
//populate your Datatable
SqlParameter param = new SqlParameter("@userdefinedtabletypeparameter", SqlDbType.Structured)
{
TypeName = "dbo.userdefinedtabletype",
Value = dt
};
sqlComm.Parameters.Add(param);
Remember to specify SqlDbType.Structured
as the type of parameter and specify the name you have used in creating your UDT.