How to pass a table parameter to SQL Server 2008 through EntLib 5.0?

Question

How to pass a table parameter to SQL Server 2008 through EntLib 5.0?

Answer 1

I did not try to do this, but by looking quickly at the EntLib data block, I see no easy way to achieve this. You can do this using the SqlClient object, but EntLib does not support the structured data type that you need to pass in the form of TVP.

If you are guaranteed to never need anything other than the SQL Server backend, then I suggest that you could use your SQLServer-specific connection and then use SqlParameters, but that could be detrimental to the purpose of using EntLib.

Answer 2

Good luck ... http://msdn.microsoft.com/en-us/library/bb675163.aspx quote:

Table Parameter Limitations

There are several limitations to table options:

You cannot pass table-valued parameters to CLR user-defined functions. Table-valued parameters can only be indexed to support UNIQUE or PRIMARY KEY constraints. SQL Server does not maintain statistics on table-valued parameters. Table-valued parameters are read-only in Transact-SQL code. You cannot update the column values in the rows of a table-valued parameter and you cannot insert or delete rows. To modify the data that is passed to a stored procedure or parameterized statement in table-valued parameter, you must insert the data into a temporary table or into a table variable. You cannot use ALTER TABLE statements to modify the design of table-valued parameters.