Error while adding multiple tvp values to datatable

Question

I try to add a list with multiple columns to MSSQL database using table valued parameters.

I get this error:

'Unable to cast object of type '...Models.OptionValue' to type 'System.IConvertible'.Couldn't store <...OptionValue> in OptionID Column. Expected type is Int32.' InnerException: InvalidCastException: Unable to cast object of type '...Models.OptionValue' to type 'System.IConvertible'.

Sql Tvp table:

CREATE TYPE [dbo].[OptionValueList] AS TABLE(
    [OptionID] [int] NULL,
    [ValueID] [int] NULL
)

classes:

public class OptionValue 
{
        public int OptionID { get; set; }
        public int ValueID { get; set; }
}

 public class OptionListVM
 {
        public int ProductID { get; set; }
        public List<OptionValue> OptionValueLst { get; set; }
 }

this is where I try to add the columns in order to pass the list to stored procedure:

    DataTable tvp = new DataTable();
    tvp.Columns.Add(new DataColumn("OptionID", typeof(int)));
    tvp.Columns.Add(new DataColumn("ValueID", typeof(int)));

    foreach (var x in o.OptionValueLst)               
        tvp.Rows.Add(x); -- error line

I implemented IConvertable interface but it doesn't work. How can I fix this ?

Answer 1

DataTable tvp = new DataTable();
tvp.Columns.Add(new DataColumn("OptionID", typeof(int)));
tvp.Columns.Add(new DataColumn("ValueID", typeof(int)));

foreach (<OptionValue>x in o.OptionValueLst) {
DataRow dr = tvp.NewRow();
dr("OptionID") = x.OptionID;
dr("ValueID") = x.ValueID; 
tvp.Rows.Add(dr); 
}