Pass table valued parameter using ADO.NET

Question

How to pass table valued parameter to stored procedure using ADO.NET?

Answer 1

1. Create type in SQL Server:

   CREATE TYPE [dbo].[MyDataType] As Table (     ID INT,     Name NVARCHAR(50) ) 

2. Create Procedure:

   CREATE PROCEDURE [dbo].[MyProcedure] (     @myData As [dbo].[MyDataType] Readonly ) AS  BEGIN     SELECT * FROM @myData END 

3. Create DataTable in C#:

   DataTable myDataTable = new DataTable("MyDataType"); myDataTable.Columns.Add("Name", typeof(string)); myDataTable.Columns.Add("Id", typeof(Int32)); myDataTable.Rows.Add("XYZ", 1); myDataTable.Rows.Add("ABC", 2); 

4. Create SQL Parameter:

   SqlParameter parameter = new SqlParameter(); parameter.ParameterName = "@myData"; parameter.SqlDbType = System.Data.SqlDbType.Structured; parameter.Value = myDataTable; command.Parameters.Add(parameter);