Table-valued parameters in ADO.NET

SQL server 2008 has a new feature called table-valued parameters. A great feature I tell you, because it allows developers to supply a table of values to a stored procedure for processing. One practical scenario for this is having an insert procedure that allows multiple inserts to be submitted at once.

Creating a stored procedure with a table-valued parameter

To use this feature you need to define a new table type in SQL using the following statements:

</p><p>CREATE TYPE dbo.CategoryTableType AS TABLE<br />    ( CategoryID int, CategoryName nvarchar(50) )
</p><p>

This type can then be used in a stored procedure like you normally would. A sample of this is shown below.

</p><p>CREATE PROCEDURE usp_UpdateCategories <br />    (@tvpNewCategories dbo.CategoryTableType READONLY)
</p><p>

Executing a stored procedure with a table-valued parameter

Now comes the fun part, because executing a stored procedure that uses table-valued parameters is somewhat trickier than one might think. According to the documentation it should be possible to pass a System.Data.DataTable, System.Data.Common.DbDataReader or an object that derives from IEnumerable as the value of the table-valued parameter.

Pretty neat, however only the first two options actually work. Jeroen (a colleague) and I have been breaking our brains on this one for quite some time and discovered that the documentation contains a rather nasty error.

When working with the IEnumerable derived types, the call will cause an InvalidCastException with a message that looks like this:

Cannot convert IList`1 to IEnumerable`1, the type does not implement IConvertible.

My first comment on this was: Gee, thanks Microsoft, yet another very descriptive error. (I’m actually introducing the YADE here. The application was trying to tell me here that it cannot cast my list of custom objects into an IEnumerable<SqlDataRecord> type. I found that last part out using my trusty reflector tooling, so it isn’t very obvious no matter how you put it.

So the best option when using table-valued parameters is passing the data in the form of a datatable. It’s a bit of overhead, but it gets the job done pretty well.