Table Value Parameters in SQL Server 2008 and .NET (C#).docx

Table Value Parameters in SQL Server 2008 and .NET (C#) Problem We recently upgraded our database to SQL Server 2008 and I want to update our data access C# code to use Table Value Parameters with our stored procedures . In a previous tip I saw that Table Value Parameters were used with a Data Warehousing example. Can you show me how to implement Table Value Parameters with my .NET Application to insert multiple records using one round-trip? Solution Table Value Parameters isa new feature for developers in SQL Server 2008 . It allows you to pass read-only table variables into a stored procedure . In the past I have used ma delimited string or XML to simulate this purpose. I would have to parse out the string into a temp table similar to this example . Once you have the table parameter passed into the stored procedure you can leverage the Table Value Parameter just like any other table except you cannot modify the parameter as it’s read-only. Below we will apply mon example to bulk insert data using one round trip. Create table and table type The following isa sample table that we will use in this example to insert items. CREATE TABLE [dbo].[Items]( [ItemID] [int] NOT NULL, [Name] [nvarchar](50) NULL, CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED ([ItemID] ASC ))ON[PRIMARY] In order to use table value parameters you have to create a table type. The table type is used to describe t