ADO.NET 3.5 Cookbook, 2nd Edition

3.21. Using Table-Valued Types As Parameters

Problem

You need to pass data to a stored procedure table-valued parameter.

Solution

Use the AddWithValue() method of the ParameterCollection for the Command to assign a value to the table-value parameter and specify the SqlDbType of the parameter as Structured from the SqlDbType enumeration.

The solution needs a table named TVPTable in the AdoDotNet35Cookbook database. Execute the following T-SQL statement to create the table:

	USE AdoDotNet35Cookbook
	GO
	CREATE TABLE TVPTable(
	    Id int NOT NULL PRIMARY KEY,
	    Field1 nvarchar(50) NULL,
	    Field2 nvarchar(50) NULL )

Execute the following T-SQL statement to create the user-defined table type named TVPType used in this solution:

	USE AdoDotNet35Cookbook
	GO
	CREATE TYPE TVPType AS TABLE (
	    Id int,
	    Field1 nvarchar(50),
	    Field2 nvarchar(50))

The solution also uses a stored procedure named InsertTVPTable that takes a table-valued parameter and adds the records in it to the table TVPTable. Execute the following T-SQL statement to create the stored procedure:

	USE AdoDotNet35Cookbook
	GO
	CREATE PROCEDURE InsertTVPTable (
	    @tvp TVPType READONLY)
	AS
	    SET NOCOUNT ON

	    INSERT INTO TVPTable
	    SELECT Id, Field1, Field2 FROM @tvp

The solution creates a DataTable named dvTVP that is used to pass a table value into a parameter into the stored procedure InsertTVPTable that inserts a record into the table TVPTable in the AdoDotNet35Cookbook database. A Command object is created for the stored procedure InsertTVPTable and the value ...