SQL Server 2008 Table-Valued Parameters and C# Custom Iterators: A Match Made In Heaven!

The new Table-Valued Parameter (TVP) feature is perhaps the most significant T-SQL enhancement in SQL Server 2008. Many people have already discovered how to use this feature for passing entire sets of rows with a typed schema definition along from one stored procedure to another on the server. This is something that has not been previously possible using either table variables or temp tables, although temp tables can of course be shared between multiple stored procedures executing on the same connection. Conveniently, TVPs can be passed freely between stored procedures and user-defined functions (UDFs) on the server.

A less widely-known capability of TVPs—yet arguably their most compelling facet—is the ability to marshal an entire set of rows across the network, from your ADO.NET client to your SQL Server 2008 database, with a single stored procedure call (only one round-trip) that accepts a single table-valued parameter. Developers have resorted to a variety of clever hacks over the years to reduce multiple round-trips for processing multiple rows—including XML, delimited text, or even (gasp) accepting hundreds (up to 2100!) of parameters. But special logic then needs to be implemented for packaging and unpackaging the parameter values on both sides of the wire. Worse, the code to implement that logic is often gnarly, and tends to impair developer productivity. None of those techniques even come close to the elegance and simplicity of using TVPs as a native solution to this problem.

I won’t cover the basics of TVPs, since many folks know them already and there are numerous blog posts already on the Web that explain the basics (not to mention Chapter 2 in my book) What I will show is how to implement TVPs in ADO.NET client code for passing an entire DataTable object (all its rows and columns) to SQL Server with one stored procedure call.

If you’re a business-object person and not a DataSet/DataTable person, I’ll also show you how to do the same thing with an entire business object collection using C# custom iterators. There are few places (if any) other than this blog post that show the necessary steps to implement TVPs against business object collections (it’s not even covered in my book!). So let’s get started.

Preparing the SQL Server Database

Before diving into the C# code, we need to set up our database for a simple Order Entry scenario. That means creating an Order table and a related OrderDetail table. We’ll also need an OrderUdt and OrderDetailUdt user-defined table type (UDTT) to base our TVPs on, and a single InsertOrders stored procedure that accepts header and detail TVPs containing typed rows to be bulk inserted into the underlying tables:

CREATE TABLE [Order](
 OrderId int NOT NULL,
 CustomerId int NOT NULL,
 OrderedAt date NOT NULL,
 CreatedAt datetime2(0) NOT NULL,
  CONSTRAINT PK_Order PRIMARY KEY CLUSTERED (OrderId ASC))
GO

CREATE TABLE [OrderDetail](
 OrderId int NOT NULL,
 LineNumber int NOT NULL,
 ProductId int NOT NULL,
 Quantity int NOT NULL,
 Price money NOT NULL,
 CreatedAt datetime2(0) NOT NULL,
  CONSTRAINT PK_OrderDetail PRIMARY KEY CLUSTERED (OrderId ASC, LineNumber ASC))
GO

CREATE TYPE OrderUdt AS TABLE(
 OrderId int,
 CustomerId int,
 OrderedAt date)
GO

CREATE TYPE OrderDetailUdt AS TABLE(
 OrderId int,
 LineNumber int,
 ProductId int,
 Quantity int,
 Price money)
GO

CREATE PROCEDURE InsertOrders(
 @OrderHeaders AS OrderUdt READONLY,
 @OrderDetails AS OrderDetailUdt READONLY)
AS
 BEGIN

    -- Bulk insert order header rows from TVP
    INSERT INTO [Order]
     SELECT *, SYSDATETIME() FROM @OrderHeaders

    -- Bulk insert order detail rows from TVP
    INSERT INTO [OrderDetail]
     SELECT *, SYSDATETIME() FROM @OrderDetails

 END
GO

Notice how the schemas of the UDTTs and the actual tables themselves are almost, but not quite, identical. The CreatedAt column in the two tables is not present in the UDTTs, since we won’t be accepting the client’s date and time—as we shouldn’t trust it, not knowing the time zone, not being in sync with the server and other clients, etc. So we’ll accept all required column values for orders and order details from the client, except for CreatedAt, for which our stored procedure will call the SYSDATETIME function to provide the current date and time based on the server clock (just like the GETDATE function, but returns as a datetime2 data type rather than a datetime data type). Independent of CreatedAt, the header table has an OrderedAt column which will be accepted from the client as a date value (the “time-less” date and enhanced datetime2 are new data types in SQL Server 2008 that I’ll cover in a future post).

Passing a DataTable to SQL Server

The easiest way to call this stored procedure from a .NET client is to use a DataTable. You simply pass the entire populated DataTable object as the parameter value and set the parameter’s SqlDbType property to SqlDbType.Structured. Everything else is plain old vanilla ADO.NET:

var headers = new DataTable();
headers.Columns.Add("OrderId", typeof(int));
headers.Columns.Add("CustomerId", typeof(int));
headers.Columns.Add("OrderedAt", typeof(DateTime));

var details = new DataTable();
details.Columns.Add("OrderId", typeof(int));
details.Columns.Add("LineNumber", typeof(int));
details.Columns.Add("ProductId", typeof(int));
details.Columns.Add("Quantity", typeof(decimal));
details.Columns.Add("Price", typeof(int));

headers.Rows.Add(new object[] { 6, 51, DateTime.Today });
details.Rows.Add(new object[] { 6, 1, 12, 2, 15.95m });
details.Rows.Add(new object[] { 6, 2, 57, 1, 59.99m });
details.Rows.Add(new object[] { 6, 3, 36, 10, 8.50m });

headers.Rows.Add(new object[] { 7, 51, DateTime.Today });
details.Rows.Add(new object[] { 7, 1, 23, 2, 79.50m });
details.Rows.Add(new object[] { 7, 2, 78, 1, 3.25m });

using (var conn = new SqlConnection("Data Source=.;Initial Catalog=MyDb;Integrated Security=True;"))
{
  conn.Open();
  using (var cmd = new SqlCommand("InsertOrders", conn))
  {
    cmd.CommandType = CommandType.StoredProcedure;

    var headersParam = cmd.Parameters.AddWithValue("@OrderHeaders", headers);
    var detailsParam = cmd.Parameters.AddWithValue("@OrderDetails", details);

    headersParam.SqlDbType = SqlDbType.Structured;
    detailsParam.SqlDbType = SqlDbType.Structured;

    cmd.ExecuteNonQuery();
  }
  conn.Close();
}

The magic here is that the one line of code that invokes ExecuteNonQuery sends all the data for two orders with five details from the client over to the InsertOrders stored procedure on the server. The data is structured as a strongly-typed set of rows on the client, and winds up as a strongly-typed set of rows on the server in a single round-trip, without any extra work on our part.

This example passes a generic DataTable object to a TVP, but you can also pass a strongly typed DataTable or even a DbDataReader object (that is connected to another data source) to a TVP in exactly the same way. The only requirement is that the columns of the DataTable or DbDataReader correspond to the columns defined for the UDTT that the TVP is declared as.

So TVPs totally rock, eh? But hold on, the TVP story gets even better…

Passing a Collection of Objects to SQL Server

What if you’re working with collections populated with business objects rather than DataTable objects populated with DataRow objects? You might not think at first that business objects and TVPs could work together, but the fact is that they can… and quite gracefully too. All you need to do is implement the IEnumerable<SqlDataRecord> interface in your collection class. This interface requires your collection class to supply a C# custom iterator (sorry, VB .NET doesn’t support custom iterators) method named GetEnumerator which ADO.NET will call for each object contained in the collection when you invoke ExecuteNonQuery. Here are the detailed steps:

First we’ll define the OrderHeader and OrderDetail classes and properties, similar to the way we created the DataTable objects and columns before:

public class OrderHeader
{
  public int OrderId { get; set; }
  public int CustomerId { get; set; }
  public DateTime OrderedAt { get; set; }
}

public class OrderDetail
{
  public int OrderId { get; set; }
  public int LineNumber { get; set; }
  public int ProductId { get; set; }
  public int Quantity { get; set; }
  public decimal Price { get; set; }
}

Ordinarily, List<OrderHeader> and List<OrderDetail> objects might be suitable for serving as collections of OrderHeader and OrderDetail objects in our application. But these collections they won’t suffice on their own as input values for TVPs because List<T> doesn’t implement IEnumerable<SqlDataRecord>. We need to add that ourselves. So we’ll define OrderHeaderCollection and OrderDetailCollection classes that inherit List<OrderHeader> and List<OrderDetail> respectively, and also implement IEnumerable<SqlDataRecord> to “TVP-enable” them:

public class OrderHeaderCollection : List<OrderHeader>, IEnumerable<SqlDataRecord>
{
  IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()
  {
    var sdr = new SqlDataRecord(
     new SqlMetaData("OrderId", SqlDbType.Int),
     new SqlMetaData("CustomerId", SqlDbType.Int),
     new SqlMetaData("OrderedAt", SqlDbType.Date));

    foreach (OrderHeader oh in this)
    {
      sdr.SetInt32(0, oh.OrderId);
      sdr.SetInt32(1, oh.CustomerId);
      sdr.SetDateTime(2, oh.OrderedAt);

      yield return sdr;
    }
  }
}

public class OrderDetailCollection : List<OrderDetail>, IEnumerable<SqlDataRecord>
{
  IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()
  {
    var sdr = new SqlDataRecord(
     new SqlMetaData("OrderId", SqlDbType.Int),
     new SqlMetaData("LineNumber", SqlDbType.Int),
     new SqlMetaData("ProductId", SqlDbType.Int),
     new SqlMetaData("Quantity", SqlDbType.Int),
     new SqlMetaData("Price", SqlDbType.Money));

    foreach (OrderDetail od in this)
    {
      sdr.SetInt32(0, od.OrderId);
      sdr.SetInt32(1, od.LineNumber);
      sdr.SetInt32(2, od.ProductId);
      sdr.SetInt32(3, od.Quantity);
      sdr.SetDecimal(4, od.Price);

      yield return sdr;
    }
  }
}

I’ll only explain the OrderHeaderCollection class; you can then infer how the OrderDetailCollection class–or any of your own collection classes–implements the custom iterator needed to support TVPs.

First, again, it inherits List<OrderHeader>, so an OrderHeaderCollection object is everything that a List<OrderHeader> object is. This means implicitly, by the way, that it also implements IEnumerable<OrderHeader>, which is what makes any sequence “foreach-able” or “LINQ-able”. But in addition, it explicitly implements IEnumerable<SqlDataRecord> which means it also has a customer iterator method for ADO.NET to consume when an instance of this collection class is assigned to a SqlDbType.Structured parameter for piping over to SQL Server with a TVP.

Every enumerable class requires a matching enumerator method, so not surprisingly implementing IEnumerable<SqlDataRecord> requires providing a GetEnumerator method that returns an IEnumerator<SqlDataRecord>. This method first initializes a new SqlDataRecord object with a schema that matches the UDTTs that the TVPs are declared as. It then enters a loop that iterates all the elements in the collection (possible because List<OrderHeader> implicitly implements IEnumerable<OrderHeader>). On the first iteration, it sets the column property values of the SqlDataRecord object to the property values of the first OrderHeader element, and then issues the magic yield return statement. By definition, any method (like this one) which returns IEnumerator<T> and has a yield return statement in it, is a custom iterator method that is expected to return a sequence of T objects until the method execution path completes (in this case, when the foreach loop finishes).

The crux of this is that we are never calling this method directly. Instead, when we invoke ExecuteNonQuery to run a stored procedure with a SqlDbType.Structured parameter (that is, a TVP), ADO.NET expects the collection passed for the parameter value to implement IEnumerable<SqlDataRecord> so that IEnumerable<SqlDataRecord>.GetEnumerator can be called internally to fetch each new record for piping over to the server. When the first element is fetched from the collection, GetEnumerator is entered, the SqlDataRecord is initialized and is then populated with values using the SetInt32 and SetDateTime methods (there’s a SetXXX method for each data type). That SqlDataRecord “row” is then pushed into the pipeline to the server by yield return. When the next element is fetched from the collection, the GetEnumerator method resumes from the point that it yield returned the previous element, rather than entering GetEnumerator again from the top. This means the SqlDataRecord gets initialized with schema information only once, while its population with one element after another is orchestrated by the controlling ADO.NET code for ExecuteNonQuery that actually ships one SqlDataRecord after another to the server..

The actual ADO.NET code is 100% identical to the code at the top of the post that works with DataTable objects. Substituting a collection for a DataTable object requires no code changes and works flawlessly, provided the collection implements IEnumerator<SqlDataRecord> and provides a GetEnumerator method that maps each object instance to a SqlDataRecord and each object property to a column defined by the UDTT that the TVP is declared as.

How freaking c-o-o-l is that?

About these ads

43 Responses to “SQL Server 2008 Table-Valued Parameters and C# Custom Iterators: A Match Made In Heaven!”

  1. Streaming Into LINQ to XML Using C# Custom Iterators and XmlReader « Lenni Lobel on .NET and SQL Server Development Says:

    [...] Custom iterators are a very powerful C# language feature, not necessarily limited for use with LINQ. Streaming input into LINQ to XML queries as an alternative to using a cached input source is just one example, but there are numerous others ways to leverage custom iterators. To learn how they can be used with SQL Server 2008 Table-Valued Parameters to marshal an entire business object collection to a stored procedure in a single round-trip, view my earlier blog post that explains the details: SQL Server 2008 Table-Valued Parameters and C# Custom Iterators: A Match Made In Heaven! [...]

  2. Ram Says:

    Really, thanks for sharing the information. It helps me lot :)

  3. Ron Says:

    Thank you. Thank you. Thank you. Exactly what I needed to know with a great example. Done a lot of searching, your the best example of using a collection.

  4. Nilesh Narkhede Says:

    Thanks for sharing the information….
    It really helpful as our application is completly based on custom Data Transfer Objects

  5. vinay Says:

    very helpful information.

  6. Sam Says:

    Extremely useful!!! Thank you very very much!

  7. Matt Says:

    Your post is very helpful.
    I’m currently in process of converting old code to be atomic using TVP’s

    One issue I’ve run into though is that the ASP.NET SQLDataSource will not accept structured types as parameters for “SELECT” stored procedures.

    In my case, there are many controls databound to the SQLDataSource.
    Have you thought of any ways to pass to a TVP via a SQLDataSource?

    • Leonard Lobel Says:

      Thanks Matt. Unfortunately, I’ve never tested TVPs against the SQLDataSource, as I don’t believe in using it. :) That is, SQLDataSource retrieves data from SQL Server directly from your ASP.NET page (the UI), which violates the n-tier model where the UI requests data from the middle tier, which in turn requests data from the DAL. I will admit that I’m curious to know about it, but not enough to spend the time researching it. Though I’d certainly appreciate learning what you find out, if you’re so inclined!

      • Matt Says:

        I’m just following up here….
        As it turns out you can “trick” the SQLDataSource into this by setting the SELECT parameter equal to a session var, then stuffing a datatable into the session var. You will need to manually remove the type and default values that the SQLDataSource control sticks in the markup…. Yep, a dedicated DAL is much preferable!

      • Leonard Lobel Says:

        Great solution Matt… thanks for sharing!

  8. Vijay Says:

    Awesome! I am implementing this one.

  9. Colin Says:

    Lenny,
    you are right. That is freaking c-o-o-l!

  10. Håkan Nilsson Says:

    This is a very good article and I have tried the first part and use my TVP in Db with a DataTable in C#. It works fine, but I would like to know if I can get C# to know my TVP excactly as f it was just another datatype. Then I could be able to get rid of all inserts made to the database TVP before my my stored proc is called. I this possible? As I understand I will get the same sql result by using the second approach with custom iterators, correct? I’m interested in really good performance and the only way to get this is to send the same table structure as is declared in DB. I’m not sure this is possible.

    • Leonard Lobel Says:

      Whether using a DataTable or an IEnumerable collection, rows are sent to TVPs across the wire in TDS, which is SQL Server’s native binary networking protocol. It doesn’t get more performant than that!

  11. antmx Says:

    This is a good article and solves a long standing problem. Thanks for sharing.

  12. Ali Hijazi Says:

    thank you
    it’s a gr8 article
    thanks for sharing

  13. Naufal Says:

    Very helpful information, i how can i use this same code in vb.net
    Please help me
    Thanks in Advance
    Naufal K.K

  14. kfoster Says:

    Thanks, this is exactly what I was looking for. I need to do bulk insert/updates and Linq to Sql doesn’t support it. One enhancement I may attempt is to use attributes on the source object to define the SqlMetaData. Something like:

    public class OrderHeader
    {
    [ColumnMapping(name="OrderId",sqlDbType="Int")]
    public int OrderId { get; set; }

    [ColumnMapping(name="CustomerId",sqlDbType="Int")]
    public int CustomerId { get; set; }

    [ColumnMapping(name="OrderedAt",sqlDbType="Date")]
    public DateTime OrderedAt { get; set; }

    public string SomeNonPersistedData { get; set; }
    }

    Then you should be able to create a reusable class:

    public class TableValuedCollection : List, IEnumerable

    that can build the SqlDataRecords dynamically based on the attributes of the object. And can skip non attributed properties (like SomeNonPersistedData above).

    So if the User Defined Tables change their definition, you just change the underlying object, all the collection and ADO stuff stays the same.

    Haven’t done that yet, but your example gives me a great place to start. Thanks again,
    Ken

  15. Mau Says:

    Wow! It’s all I can say. Let me heat the fingers and begin to test.
    If this tutorial works as expected, you really have saved me many hours of developing.

    Thanks a lot pal!

  16. patelriki13 Says:

    thanks a lot dear frds…..
    i am beginner …..
    i have some confusion can you solve please….

    can you tell me how to add collection in parameter.
    i make collection classes like this
    public class OrderHeaderCollection : List, IEnumerable

    but i have List so how to add in parameter type of SqlDbType.Structured

  17. Matt Whitfield Says:

    Thanks for putting this together – answered the questions I had far better than the MSDN articles I found.

  18. Cherif Says:

    Thanks for making our life easer by saving us a lot of work and research.
    I have one ‘how to question’ for you.

    When you are doing the following part
    public class OrderDetailCollection : List, IEnumerable
    {
    IEnumerator IEnumerable.GetEnumerator()

    How do your handle a Timestamp type column?
    I’m actually using timestamp column to manager the concurrencies when updating a row in my DB.

    Thanks

  19. Manzi Aimable Says:

    What a terrific post!!!

  20. Rais Hussain Says:

    It is second article which I read after reading “Streaming Into LINQ to XML Using C# Custom Iterators and XmlReader”.

    Awesome example of Custom Iterators and a great resource to learn about usage of Custom Iterators.

    Thanks a lot.

  21. Sunshine State Programmer Says:

    Thank you very much. I spent all day working on this, and was done 5 minutes after reading this article.

  22. Steve Ramey Says:

    This is great Leonard. I’m glad I finally stumbled upon this. I was also wondering, is it possible to pass two Table-Valued Parameters to the same stored procedure?

    Thanks again.

    • Steve Ramey Says:

      Also, I saw the part about, “marshal an entire set of rows across the network . . . with a single stored procedure call (only one round-trip) that accepts a single table-valued parameter” with single in italics, but I just wanted to double check.

      • Leonard Lobel Says:

        I see how the emphasis on “single” could be misleading. I was trying to point out that one parameter holds a multi-row resultset, not that you could only have one. But again, to be clear, you can have multiple TVPs per stored proc.

    • Leonard Lobel Says:

      Glad you found it helpful. Indeed, you can pass any number of TVPs to the same stored procedure.

  23. Alasdair Craig Says:

    Three years later and this article still rocks! Thank you.

  24. Vinod Says:

    very useful article sir,I exactly looking for such article you did wonderful job which help many developers…


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 47 other followers

%d bloggers like this: