-
// ---Main Sub
-
private void btnCollection_Click(object sender, EventArgs e)
-
{
-
// ------Use OrderItem object
-
List<OrderItem> orderItems = new List<OrderItem>();
-
orderItems. Add (new OrderItem() { OrderId = 901, CustomerId = 67, OrderedAt = DateTime.Now });
-
orderItems. Add (new OrderItem() { OrderId = 902, CustomerId = 68, OrderedAt = DateTime.Now });
-
OrderItemCollection itemCollection = new OrderItemCollection();
-
itemCollection. AddRange(orderItems);
-
// ------Use OrderDetail object
-
List<OrderDetail> orderDetails = new List<OrderDetail>();
-
orderDetails. Add (new OrderDetail() { OrderId = 901, LineNumber = 1, ProductId = 34, Quantity = 2, Price = 10.5m });
-
orderDetails. Add (new OrderDetail() { OrderId = 901, LineNumber = 2, ProductId = 100, Quantity = 45, Price = 3.8m });
-
orderDetails. Add (new OrderDetail() { OrderId = 901, LineNumber = 3, ProductId = 56, Quantity = 67, Price = 9.1m });
-
orderDetails. Add (new OrderDetail() { OrderId = 902, LineNumber = 1, ProductId = 88, Quantity = 5, Price = 88.5m });
-
orderDetails. Add (new OrderDetail() { OrderId = 902, LineNumber = 2, ProductId = 1, Quantity = 345, Price = 0.465m });
-
OrderDetailCollection detailCollection = new OrderDetailCollection();
-
detailCollection. AddRange(orderDetails);
-
// ----
-
using (SqlConnection conn = new SqlConnection( "Data Source=;Initial Catalog=AdventureWorks; User ID=sa; Password=" ))
-
{
-
conn. Open ();
-
// ---Passing a Table-Valued Parameter to a Stored Pcocedure
-
using (SqlCommand cmd = new SqlCommand( "dbo.OrderItem$Insert" , conn))
-
{
-
cmd. CommandType = CommandType. StoredProcedure;
-
// ---Note: The two parameter names below should be consistent with the SP parameter names.
-
SqlParameter tvpOrderItem = cmd. Parameters.AddWithValue( "@OrderHeaders" , itemCollection);
-
SqlParameter tvpOrderDetail = cmd. Parameters.AddWithValue( "@OrderDetails" , detailCollection);
-
tvpOrderItem. SqlDbType = SqlDbType. Structured;
-
tvpOrderDetail. SqlDbType = SqlDbType. Structured;
-
cmd. ExecuteNonQuery();
-
}
-
// ---Streadming rows with a dataReader
-
string sql = @" select tvp1.OrderId, tvp1.CustomerId, tvp2.LineNumber, tvp2.ProductId
-
from dbo. OrderItem as tvp1 INNER JOIN @tvpDetail as tvp2 ON tvp1.OrderId=tvp2.OrderId
-
order by tvp2.OrderId, tvp2.LineNumber; ";
-
SqlCommand readCommand = new SqlCommand(sql, conn);
-
readCommand. CommandType = CommandType. Text;
-
SqlParameter tvpDetail = readCommand. Parameters.AddWithValue( "@tvpDetail" , detailCollection);
-
tvpDetail. SqlDbType = SqlDbType. Structured;
-
tvpDetail. TypeName = "dbo.OrderDetail$Udt" ;
-
SqlDataReader reader = readCommand. ExecuteReader();
-
while (reader. Read ())
-
{
-
Console. WriteLine(string.Format( "OrderId={0}, CustomrId={1}, LineNumber={2}, ProductId={3}" ,
-
reader. GetInt32(0), reader. GetInt32(1), reader. GetInt32(2), reader. GetInt32(3)));
-
}
-
reader. Close ();
-
// Output results:
-
// ------------------
-
//OrderId=901, CustomrId=67, LineNumber=1, ProductId=34
-
//OrderId=901, CustomrId=67, LineNumber=2, ProductId=100
-
//OrderId=901, CustomrId=67, LineNumber=3, ProductId=56
-
//OrderId=902, CustomrId=68, LineNumber=1, ProductId=88
-
//OrderId=902, CustomrId=68, LineNumber=2, ProductId=1
-
// ------------------
-
conn. Close ();
-
}
-
}
-
// ---OrderItem object
-
public sealed class OrderItem
-
{
-
public int OrderId { get; set ; }
-
public int CustomerId { get; set ; }
-
public DateTime OrderedAt { get; set ; }
-
}
-
// ---OrderDetail object
-
public sealed 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 ; }
-
}
-
// ---OrderItemCollection
-
public sealed class OrderItemCollection: List<OrderItem>, IEnumerable<SqlDataRecord>
-
{
-
#region IEnumerable<SqlDataRecord> Members
-
public new IEnumerator<SqlDataRecord> GetEnumerator()
-
{
-
SqlDataRecord record = new SqlDataRecord(
-
new SqlMetaData( "OrderId" , SqlDbType. Int ),
-
new SqlMetaData( "CustomerId" , SqlDbType. Int ),
-
new SqlMetaData( "OrderedAt" , SqlDbType. DateTime));
-
foreach(OrderItem item in this. ToArray())
-
{
-
record. SetInt32(0, item. OrderId);
-
record. SetInt32(1, item. CustomerId);
-
record. SetDateTime(2, item. OrderedAt);
-
yield return record;
-
}
-
}
-
#endregion
-
}
-
// ---OrderDetailCollection
-
public sealed class OrderDetailCollection: List<OrderDetail>, IEnumerable<SqlDataRecord>
-
{
-
#region IEnumerable<SqlDataRecord> Members
-
public new IEnumerator<SqlDataRecord> GetEnumerator()
-
{
-
SqlDataRecord record = 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 detail in this. ToArray())
-
{
-
record. SetInt32(0, detail. OrderId);
-
record. SetInt32(1, detail. LineNumber);
-
record. SetInt32(2, detail. ProductId);
-
record. SetInt32(3, detail. Quantity);
-
record. SetDecimal(4, detail. Price);
-
yield return record;
-
}
-
}
-
#endregion
-
}
