Introduction
In my
last post on Table Valued Parameters, I discussed about the concept and demonstrated with a small example how to implement them. The example I gave used an object of
DataTable as a table valued parameter. However, I have come across many queries on “How to pass a collection as a Table Valued Parameter?” My this post will answer this question.
Many of our first reaction to the question will be – “what is the big issue in that” and to a great extent it’s very much correct. However, when we actually start implementing it, it is then that we realize that we are missing something and it is nothing but IEnumerable<SqlDataRecord> interface. All what we need to do is inherit our collection from IEnumerable<SqlDataRecord> interface. This interface requires our collection class to implement a C# custom iterator method named GetEnumerator which ADO.NET will call for each object contained in the collection when you invoke ExecuteNonQuery or any other execute method. Please note that VB .NET doesn’t support custom iterators.
Implementation
First we’ll define the Order and OrderDetail classes and properties as under:
public class Order
{
public int OrderId { get; set; }
public string OrderNo { get; set; }
public int CustomerId { get; set; }
public DateTime Date { get; set; }
}
public class OrderDetail
{
public int OrderId { get; set; }
public int ItemId { get; set; }
public decimal Quantity { get; set; }
public decimal Price { get; set; }
public decimal Discount { get; set; }
}
Generally, List<Order> and List<OrderDetail> objects serves as collections of Order and OrderDetail objects in our application. But these collections, by default won’t support on their own as input values for TVPs because List<T> doesn’t implement IEnumerable<SqlDataRecord>. Hence, we need to add that ourselves. So we’ll define Orders and OrderDetails classes that inherit List <Order> and List <OrderDetail> respectively, and also implement IEnumerable<SqlDataRecord> to “TVP-enable” them:
public class Orders : List<Order>, IEnumerable<SqlDataRecord>
{
IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()
{
var sdr = new SqlDataRecord(
new SqlMetaData("OrderId", SqlDbType.Int),
new SqlMetaData("OrderNo", SqlDbType.VarChar),
new SqlMetaData("CustomerId", SqlDbType.Int),
new SqlMetaData("Date", SqlDbType.Date));
foreach (Order o in this)
{
sdr.SetInt32(0, o.OrderId);
sdr.SetString(1, o.OrderNo);
sdr.SetInt32(2, o.CustomerId);
sdr.SetDateTime(3, o.Date);
yield return sdr;
}
}
}
public class OrderDetails : List<OrderDetail>, IEnumerable<SqlDataRecord>
{
IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()
{
var sdr = new SqlDataRecord(
new SqlMetaData("OrderId", SqlDbType.Int),
new SqlMetaData("ItemId", SqlDbType.Int),
new SqlMetaData("Quantity", SqlDbType.Decimal),
new SqlMetaData("Price", SqlDbType.Decimal),
new SqlMetaData("Discount", SqlDbType.Decimal)
);
foreach (OrderDetail od in this)
{
sdr.SetInt32(0, od.OrderId);
sdr.SetInt32(1, od.ItemId);
sdr.SetDecimal(2, od.Quantity);
sdr.SetDecimal(3, od.Price);
sdr.SetDecimal(4, od.Discount);
yield return sdr;
}
}
}
I’ll only explain the Orders class; you can then infer any of your own collection classes – implements the custom iterator needed to support TVPs.
First, it inherits List<Order>, so with an Orders object we can do everything that could be done with List<Order>object. It implicitly also implements IEnumerable<Order> and 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.
Implementing IEnumerable<SqlDataRecord> requires implementing a GetEnumerator method that returns an IEnumerator <SqlDataRecord>. This method first initializes a new SqlDataRecord object with a similar schema of UDTTs that the TVPs are declared as. It then enters a loop that iterates all the elements in the collection. On the first iteration, it sets the column property values of the SqlDataRecord object to the property values of the first Order element, and then issues the yield return statement. Any method 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).
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. This method is never called directly.
When the first element is fetched from the collection, GetEnumerator is entered, the SqlDataRecord is initialized and is then populated with values using the Set 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.
