Pass table valued parameter using ADO.NET
How to pass table valued parameter to stored procedure 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 ) AS BEGIN SELECT * FROM @myData END
-
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);
-
Create SQL Parameter:
SqlParameter parameter = new SqlParameter(); parameter.ParameterName = "@myData"; parameter.SqlDbType = System.Data.SqlDbType.Structured; parameter.Value = myDataTable; command.Parameters.Add(parameter);
I tried this and received the exception:
The table type parameter '@MyDataType' must have a valid type name.
I had to set the "TypeName" property of the SqlParameter:
parameter.TypeName = "MyDataType";