CE's Blog

SQL Server 2008 supports a new way of passing a variable number of arguments to functions and stored procedures.
The good thing for .NET developers is that those table-valued parameters are also usable from a managed context.
So when doing a mass join/mass update using SqlCommand, this approach offers a huge increase in performance when migrating from a line-by-line approach to a single statement that is to be executed.

The following example is to demonstrate a sample usage of this technique. We are assuming that we want to insert a set of Unicode strings into the database.

First of all, TVPs are only supported with custom types that must be created prior to execution.

CREATE TYPE dbo.MyType AS TABLE( MyColumn NVARCHAR(MAX) ); 
GRANT EXEC ON TYPE::dbo.MyType TO MyRole;

This is kind of a drawback if you are using a large number of different data types from different queries.

Here is how to pass data from an array to SQL Server:

using ( SqlConnection con = new SqlConnection( "connection string" ) )
{
    con.Open();
    using ( SqlCommand cmd = con.CreateCommand() )
    {
        string[] arr = { "a", "b", "c", "d" };
        SqlMetaData meta = new SqlMetaData( "MyColumn", SqlDbType.NVarChar, SqlMetaData.Max );
        var records = arr.Select( x => { SqlDataRecord r = new SqlDataRecord( meta ); r.SetString( 0, x ); return r; } );
 
        SqlParameter UrlParam = cmd.Parameters.AddWithValue( "@MyTVP", records );
        UrlParam.SqlDbType = SqlDbType.Structured;
        UrlParam.TypeName = "dbo.MyType";
        cmd.CommandText = "INSERT INTO MyTable ( SomeColumn ) SELECT MyAlias.MyColumn FROM @MyTVP AS MyAlias;"; 
        cmd.ExecuteNonQuery();
    }
}

In order to send the array data to the database server, we need to wrap it in an IEnumerable. This is then added to the parameters collection with the special type qualifier SqlDbType.Structured. On the SQL side, the handling is the same as a real table in the database, although it might be necessary to work with aliases when referencing the TVP.

Note that in this example we are limiting the insert to a single column. However you may also create TVPs with multiple columns and different data types.

This approach does not offer much savings when dealing with a few records to be passed to the server. However when you want to insert a few hundred records at once, executing hundreds of queries instead of a single one pays off.