[优秀英文技术文章转载]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 is a 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 a comma 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 a common example to bulk insert data using one round trip.

---

Create table and table type

The following is a 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 the structure of the table value parameter. This is similar to making a strong type.

CREATE TYPE [dbo].[TVP_Items] AS TABLE(
 [ItemID] [int] NOT NULL,
 [Name] [nvarchar](50) NULL
)
GO

Using TVP in T-SQL

Now that you have a table type you have to declare an instance to use it. The following is an quick example that declares an instance of the table type an populates it with some values. Later we will use C# to create a data table that will populate an instance of a table type.

-- Declare instance of TVP_Items Table Type
DECLARE @TVP TVP_Items 
-- Add some sample values into our instance of Table Type.
INSERT INTO @TVP (ItemID, Name) VALUES (1, 'Hat'), (2, 'T-Shirt'), 
(3, 'Football'), (4, 'Jersey')
-- show values that exist in table type instance.
SELECT * FROM @TVP

Using TVP in Stored Procedure

In order to use table value parameters in a stored procedure you have to declare an instance of the table type and specify it as read-only. It is mandatory for the table value parameter to be read-only so you cannot modify the data inside of the table type variable inside the stored procedure.

Below we will insert the data in the table value parameter into the dbo.items table.

CREATE PROCEDURE [dbo].[InsertItemsTVP] @ItemTVP TVP_Items READONLY
AS
BEGIN
 INSERT INTO dbo.Items (ItemID, Name)
 SELECT ItemID, Name
 FROM @ItemTVP
END
GO

Table Value Parameters in .NET (C#)

The following code below generates a data table in C# and it includes four rows. A data table is a common data type used to simulate a table. This data table will be used as our table value parameter and will be used by the stored procedure created above. The data table is not the only type that can be used for table value parameters in C#. The DataReader and list types are also acceptable.

            DataTable _dt;
            // create data table to insert items
            _dt = new DataTable("Items");
            _dt.Columns.Add("ItemID", typeof(string));
            _dt.Columns.Add("Name", typeof(string));
            _dt.Rows.Add(4, "SuperBowl 9 Hat");
            _dt.Rows.Add(5, "SuperBowl 10 T-Shirt");
            _dt.Rows.Add(6, "SuperBowl 13 Towel");
            _dt.Rows.Add(7, "SuperBowl 14 Helmet");

Now that we have our data table created we can move on to the data access code. The majority of the code listed below will be the same for the majority of your data access code. The only difference is we will specify that the input type is sql data type as structured and we will pass in our data table to the input parameter. The two lines you need to focus on are underlined in the code section below.

SqlConnection con;
// modify connection string to connect to your database
string conStr = "Server=localhost;Database=MSSQLTIPS;Trusted_Connection=True;";
con = new SqlConnection(conStr);
 con.Open();
using (con)
{                
// Configure the SqlCommand and SqlParameter.
SqlCommand sqlCmd = new SqlCommand("dbo.InsertItemsTVP", con);
sqlCmd.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam = sqlCmd.Parameters.AddWithValue("@ItemTVP", _dt); //Needed TVP
tvpParam.SqlDbType = SqlDbType.Structured; //tells ADO.NET we are passing TVP
sqlCmd.ExecuteNonQuery();
 }
con.Close();

SQL Profiler

When you use this tip you can setup SQL Server Profiler to capture the .NET calls.  You will see the following three calls. You should notice that they are very similar to the T-SQL calls above.