ADO.NET and SQL Server 2008 User-Defined Table Types

Create the UDTT
IF EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'udtt_PersonNames' AND ss.name = N'dbo')DROP TYPE [dbo].[udtt_PersonNames];


GO
CREATE TYPE [dbo].[udtt_PersonNames] AS TABLE
(ID [int] NOT NULL,
[Name] [varchar](50) NULL,
[Phone] [varchar](50) NULL,
[Email] [varchar](50) NULL
UNIQUE CLUSTERED ([ID] ASC) WITH (IGNORE_DUP_KEY = OFF));
GO


Create the Stored Procedure accepting the TVP as a UDTT


IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_InsertPersonNames')
BEGIN
DROP PROCEDURE dbo.usp_InsertPersonNames
END
GO

CREATE PROCEDURE dbo.usp_InsertPersonNames (@PersonNamesDT AS dbo.udtt_PersonNames READONLY) AS SET NOCOUNT ON;
INSERT INTO dbo.Person([ID],[Name])
SELECT [ID], [Name]
FROM @PersonNamesDT;
SET NOCOUNT OFF;

GO

Create an ADO.NET DataTable (same # of columns and order as the UDTT)

Dim dt As DataTable = New DataTable()
dt.Columns.Add(New DataColumn("ID", GetType(Integer)))
dt.Columns.Add(New DataColumn("Name", GetType(String)))
dt.Rows.Add(New Object() {1, "Joe"})
dt.Rows.Add(New Object() {2, "Mark"})
dt.Rows.Add(New Object() {3, "Ted"})

Pass the ADO.NET DataTable as a TVP to the Stored Procedure

Using conn As SqlConnection = New SqlConnection("Data Source=localhost\sqlexpress;Initial Catalog=UDTT_test;Integrated Security=True;")
conn.Open()
Dim cmd As SqlCommand = New SqlCommand()
With cmd
.Connection = conn
.CommandType = CommandType.StoredProcedure
.CommandText = "dbo.usp_InsertPersonNames"
Dim tvParam As SqlParameter = New SqlParameter("@PersonNamesDT", dt)
tvParam.SqlDbType = SqlDbType.Structured
tvParam.TypeName = "udtt_PersonNames"
.Parameters.Add(tvParam)
.ExecuteNonQuery()
End WithEnd Using