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.