Now that Visual Studio 2008 Beta 2 is available, I wanted to share how we will be able to use Table Valued Parameters (TVP) from ADO .NET. TVP is a new feature of SQL Server 2008 that I discussed in a previous post.

The core new idea in ADO .NET that makes this happen is a new parameter data type called SqlDbType.Structured. When you set up a command parameter with this type, ADO .NET and SQL Server do the magic to map your data into the previously defined table type on the server.

And speaking of your data, you need to pass your "table" of data as one of 2 types: either a DataTable (remember the thing inside a Dataset that represents individual tables?) or as a DbDataReader (a base class for DataReaders from the different data provides or maybe one you define yourself over your proprietary data).

With just those two new concepts, passing "tables" is very natural if you are familiar with ADO .NET.

Here is what it looks like:

private static void InsertOrder(int customerId, DateTime orderDate, DataTable items)

{

using (SqlConnection connection = new SqlConnection("server=.;database=test;integrated security=true"))

using (SqlCommand command = new SqlCommand("InsertOrder", connection))

{

command.CommandType = CommandType.StoredProcedure;

command.Parameters.AddWithValue("@CustomerID", customerId);

command.Parameters.AddWithValue("@OrderDate", orderDate);

command.Parameters.AddWithValue("@ItemList", items).SqlDbType = SqlDbType.Structured; ;

connection.Open();

command.ExecuteNonQuery();

}

}

Remember, your DataTable layout needs to match the column definitions of the table type you defined in SQL Server, like so:

// in code    

DataTable items = new DataTable("items");

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

items.Columns.Add("ItemQuantity", typeof(Int32));

items.Columns.Add("ItemPrice", typeof(Decimal));

-- in SQL

CREATE TYPE [dbo].[OrderItemList] AS TABLE(

    [ItemID] [int] NULL,

    [ItemQuantity] [int] NULL,

    [ItemPrice] [money] NULL

)