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 ...

Get ADO.NET 3.5 Cookbook, 2nd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.