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

Table Value Parameter is a new feature in SQL Server 2008. It allows us to pass read-only table variable to a stored procedure. Before this we used a comma delimited string or XML for this purpose. We have to parse the string to a temp table.
Now in SQL Server 2008 we can pass the Table Value Parameter just like any other table. We cannot modify the parameter as it’s read-only. Here is an example to bulk insert data using this. 
Create table and table type
The following is a sample table that we will use in this example to insert items.
CREATE TABLE Product(

 ProductID int NOT NULL, ProductName nvarchar(35) NULL,
 CONSTRAINT [PK_ProductID] PRIMARY KEY CLUSTERED (ProductID ASC))

Now to use table value parameters we 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 TVP_Product AS TABLE(

 ProductID int NOT NULL, ProductName nvarchar(35) NULL)
GO

Using TVP in T-SQL
Now we have to declare an instance of this type to use it. The following is an example that declares an instance of the table type and 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_Product Table Type */

DECLARE @TVP TVP_Product 
/* Add some sample values into our instance of Table Type. */
INSERT INTO @TVP (ProductID, ProductName) VALUES (1, 'Product 1'), (2, 'Product 2'), 
(3, 'Product 3'), (4, 'Product 4')
/* show values that exist in table type instance. */

SELECT * FROM @TVP

Using TVP in Stored Procedure

Now to use this in Stored Procedure we 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 we cannot modify the data inside of the table type variable inside the stored procedure.
Now we will insert the data in the table value parameter into the Product table.
CREATE PROCEDURE ProductInsert @ProductTVP TVP_Product READONLY

AS
BEGIN
 INSERT INTO Product (ProductID, ProductName)
 SELECT ProductID, ProductName
 FROM @ProductTVP
END
GO

Table Value Parameters in .NET (C#)
The below code generates a data table in C# and it includes five 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 it will be used in the stored procedure created above. Instead of Data Table we can also use the DataReader and List types for this purpose.
DataTable _dt;

            // create data table to insert items
            _dt = new DataTable("Product");
            _dt.Columns.Add("ProductID", typeof(string));
            _dt.Columns.Add("ProductName", typeof(string));
            _dt.Rows.Add(5, "Product 5");
            _dt.Rows.Add(6, "Product 6");
            _dt.Rows.Add(7, "Product 7");
            _dt.Rows.Add(8, "Product 8");

_dt.Rows.Add(9, "Product 9");
Now our datatable is ready. Now we will pass this datatable as parameter to Stored Procedure.
SqlConnection con;

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