Exception when passing list object as table valued parameter in C# asp.net

Question

I have a List<> object which I have to pass to a stored procedure in SQL Server 2008 as a table valued parameter. Here is the code:

List<someType> listObjects = new List<someType>();
//Fill listObjects
...
...
...
sqlcmd.Parameters.AddWithValue("@myTableValuedParameter",listObjects).SqlDbType = SqlDbType.Structured;
sqlcmd.ExecuteNonQuery();

Code is throwing an exception which says:

Object must implement IConvertible.

In past,I have successfully passed a DataTable as a table valued parameter. Structure of type of object someType is same as custom Type which I created in Sql Server.

What am I missing here and what changes do I need to include to make it run sucessfully

convert your list<> to type DataTable? as they are different stuff. — Kelmen
– Kelmen, Commented Dec 10, 2013 at 5:14

David Pilkington · Accepted Answer · 2013-12-10 05:18:48Z

2

There are 3 things that can be passed as a Table Value Parameter:

IEnumerable<SqlDataRecord>
DataTable
DbDataReader

Your list, unless it contains SqlDataRecord cannot be used

Table-Valued Parameters

System.Data.SqlClient supports populating table-valued parameters from DataTable, DbDataReader or System.Collections.Generic.IEnumerable ([T:System.Collections.Generic.IEnumerable`1)] objects.

answered Dec 10, 2013 at 5:18

David Pilkington

13.6k3 gold badges43 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Karl Anderson · Accepted Answer · 2013-12-10 05:20:26Z

0

Per the Table-Valued Parameters documentation only DataTable, DbDataReader or IEnumerable<SqlDataRecord> are supported:

System.Data.SqlClient supports populating table-valued parameters from DataTable, DbDataReader or System.Collections.Generic.IEnumerable

answered Dec 10, 2013 at 5:20

Karl Anderson

34.9k12 gold badges69 silver badges83 bronze badges

Collectives™ on Stack Overflow

Exception when passing list object as table valued parameter in C# asp.net

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related