Pass table valued parameter using ADO.NET
Create type in SQL Server:
CREATE TYPE [dbo].[MyDataType] As Table( ID INT, Name NVARCHAR(50))Create Procedure:
CREATE PROCEDURE [dbo].[MyProcedure]( @myData As [dbo].[MyDataType] Readonly)ASBEGIN SELECT * FROM @myDataENDCreate 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);Create SQL Parameter:
SqlParameter parameter = new SqlParameter();parameter.ParameterName = "@myData";parameter.SqlDbType = System.Data.SqlDbType.Structured;parameter.Value = myDataTable;command.Parameters.Add(parameter);
This question is a duplicate of How to pass table value parameters to stored procedure from .net code. Please see that question for an example illustrating the use of either a DataTable or an IEnumerable<SqlDataRecord>.