CODEWITHSUNDEEP
c#asp.netsql-server-2008
shrekDeep
shrekDeep

Reputation: 2328

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

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

Upvotes: 0

Views: 1779

Answers (2)

Karl Anderson
Karl Anderson

Reputation: 34844

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

Upvotes: 0

David Pilkington
David Pilkington

Reputation: 13628

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.

Upvotes: 2

Related Questions