Further to Ryan's answer you will also need to set the DataColumn
's Ordinal
property if you are dealing with a table-valued parameter
with multiple columns whose ordinals are not in alphabetical order.
As an example, if you have the following table value that is used as a parameter in SQL:
CREATE TYPE NodeFilter AS TABLE (
ID int not null
Code nvarchar(10) not null,
);
You would need to order your columns as such in C#:
table.Columns["ID"].SetOrdinal(0);
// this also bumps Code to ordinal of 1
// if you have more than 2 cols then you would need to set more ordinals
If you fail to do this you will get a parse error, failed to convert nvarchar to int.