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

By:   |   Comments (8)   |   Related: > Table Valued Parameters


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.

SQL Profiler

Next Steps
  • Check out my blog for more on SQL Server
  • Click here to download the sample code used in this tip.
  • Check out several links on Table Value Parameters
  • Click here for an TVP example for Data Warehouse
  • Click here for an ASP.NET example using Table Value Paramerters


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author John Sterrett John Sterrett is a DBA and Software Developer with expertise in data modeling, database design, administration and development.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Saturday, August 29, 2015 - 4:10:24 PM - yaniv Back To Top (38565)

Very good article.

its work great. (I work with SQL 2014)

Thank you .

 

 


Friday, January 30, 2015 - 6:46:34 AM - Izhar Azati Back To Top (36112)

Is

_dt.Columns.Add("ItemID", typeof(string));

 not have to be:

_dt.Columns.Add("ItemID", typeof(Int32));


Monday, February 17, 2014 - 4:24:31 PM - John Sterrett Back To Top (29479)

WFB,

 

How do you plan on using the table value parameter? I am curious to see how you want implement your table value parameter with an identiy key. Ideally, its a datatable you would want to pass in from the application so you can do set based logic without parsing your data.

 

Can you provide more details and an basic example?

 

Regards,
John 


Monday, February 17, 2014 - 2:47:43 PM - WFB Back To Top (29476)

How to setup an identity (key) column in data table without attempting to populate it, for this example?


Friday, May 18, 2012 - 12:03:48 AM - cooljack Back To Top (17514)

how could i do that using gridview. when i select checkbox in gridview all i want is to save multiple record at once...

 

 


Wednesday, March 28, 2012 - 12:01:50 PM - Charles Foushee Back To Top (16678)

Question about the datatable you are passing as a parameter: Does it have to have EXACTLY the same columns as the Table type or can it have additional columns? From your example, if the data table you constructed look like below, would it still work?

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.Columns.Add("Color", typeof(string));
_dt.Rows.Add(4, "SuperBowl 9 Hat", "Red");
_dt.Rows.Add(5, "SuperBowl 10 T-Shirt", "Green");
_dt.Rows.Add(6, "SuperBowl 13 Towel", "Blue");
_dt.Rows.Add(7, "SuperBowl 14 Helmet", "Yellow");


Friday, February 18, 2011 - 3:10:19 PM - John Sterrett Back To Top (12976)

Marek, You are welcome. I am happy to know that this tip was helpful for you.


Friday, February 18, 2011 - 2:13:01 PM - marek Back To Top (12974)

Very good article. I was that I was looking for. I saved a lot of time and wrote a piece of good code. Thank you very mych.

Marek Janowski















get free sql tips
agree to terms