Table-values parameter(TVP)系列之三: 利用Collection将其作为参数传给SP

一，回顾

上一部分讲述了“在ADO.NET中利用DataTable对象，将其作为参数传给存贮过程”。 
通过DataTable实例，完成了两部分的内容： 
  1）DataTable的数据传输给Stored Procedure 
  2）利用DataTable的TVP数据，可以参与别的实体数据读写(DataReader)。 
这一部分的内容，通过Collection对象，还是要实现上边的两个实例。

二，在ADO.NET中利用Collection对象，将其作为参数传给存贮过程

  通过实例，大家对各个特性和参数的如何使用，有更深刻的感性认识。同时，通过 
这些实例的改造，也可做出符合自己的程序代码。 
  这次，还是主要输出Sample，包含两部分的内容： 
  1）DataTable的数据传输给Stored Procedure 
  2）利用DataTable的TVP数据，可以参与别的实体数据读写(DataReader)。 
  具体实例如下：

1.     //---Main Sub 
2.       private void btnCollection_Click(object sender, EventArgs e) 
3.         { 
4.             //------利用OrderItem object 
5. List<OrderItem> orderItems = new List<OrderItem>(); 
6.             orderItems.Add(new OrderItem() { OrderId = 901, CustomerId = 67, OrderedAt = DateTime.Now }); 
7.             orderItems.Add(new OrderItem() { OrderId = 902, CustomerId = 68, OrderedAt = DateTime.Now }); 
8. OrderItemCollection itemCollection = new OrderItemCollection(); 
9.             itemCollection.AddRange(orderItems); 
10.             //------利用OrderDetail object 
11. List<OrderDetail> orderDetails = new List<OrderDetail>(); 
12.             orderDetails.Add(new OrderDetail() { OrderId = 901, LineNumber = 1, ProductId = 34, Quantity = 2, Price = 10.5m }); 
13.             orderDetails.Add(new OrderDetail() { OrderId = 901, LineNumber = 2, ProductId = 100, Quantity = 45, Price = 3.8m }); 
14.             orderDetails.Add(new OrderDetail() { OrderId = 901, LineNumber = 3, ProductId = 56, Quantity = 67, Price = 9.1m }); 
15.             orderDetails.Add(new OrderDetail() { OrderId = 902, LineNumber = 1, ProductId = 88, Quantity = 5, Price = 88.5m }); 
16.             orderDetails.Add(new OrderDetail() { OrderId = 902, LineNumber = 2, ProductId = 1, Quantity = 345, Price = 0.465m }); 
17. OrderDetailCollection detailCollection = new OrderDetailCollection(); 
18.             detailCollection.AddRange(orderDetails); 
19.           //---- 
20.             using (SqlConnection conn = new SqlConnection("Data Source=;Initial Catalog=AdventureWorks;User ID=sa;Password=")) 
21.             { 
22.                 conn.Open(); 
23.                 //---Passing a Table-Valued Parameter to a Stored Pcocedure 
24.                 using (SqlCommand cmd = new SqlCommand("dbo.OrderItem$Insert", conn)) 
25.                 { 
26.                     cmd.CommandType = CommandType.StoredProcedure; 
27.                     //---注意：下边的两个参数名，要和SP的参数名一致。 
28.                     SqlParameter tvpOrderItem = cmd.Parameters.AddWithValue("@OrderHeaders", itemCollection); 
29.                     SqlParameter tvpOrderDetail = cmd.Parameters.AddWithValue("@OrderDetails", detailCollection); 
30.                     tvpOrderItem.SqlDbType = SqlDbType.Structured; 
31.                     tvpOrderDetail.SqlDbType = SqlDbType.Structured; 
32.                     cmd.ExecuteNonQuery(); 
33.                 } 
34.                 //---Streadming rows with a dataReader 
35.                 string sql = @"select tvp1.OrderId, tvp1.CustomerId, tvp2.LineNumber, tvp2.ProductId 
36.                             from    dbo.OrderItem as tvp1 INNER JOIN @tvpDetail as tvp2 ON tvp1.OrderId=tvp2.OrderId 
37.                             order by tvp2.OrderId, tvp2.LineNumber;"; 
38.                 SqlCommand readCommand = new SqlCommand(sql, conn); 
39.                 readCommand.CommandType = CommandType.Text; 
40.                 SqlParameter tvpDetail = readCommand.Parameters.AddWithValue("@tvpDetail", detailCollection); 
41.                 tvpDetail.SqlDbType = SqlDbType.Structured; 
42.                 tvpDetail.TypeName = "dbo.OrderDetail$Udt"; 
43.                 SqlDataReader reader = readCommand.ExecuteReader(); 
44.                 while (reader.Read()) 
45.                 { 
46.                     Console.WriteLine(string.Format("OrderId={0}, CustomrId={1}, LineNumber={2}, ProductId={3}", 
47.                                         reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2), reader.GetInt32(3))); 
48.                 } 
49.                 reader.Close(); 
50.                 // Output results: 
51.                 //------------------ 
52.                 //OrderId=901, CustomrId=67, LineNumber=1, ProductId=34 
53.                 //OrderId=901, CustomrId=67, LineNumber=2, ProductId=100 
54.                 //OrderId=901, CustomrId=67, LineNumber=3, ProductId=56 
55.                 //OrderId=902, CustomrId=68, LineNumber=1, ProductId=88 
56.                 //OrderId=902, CustomrId=68, LineNumber=2, ProductId=1 
57.                 //------------------ 
58.                 conn.Close(); 
59.             } 
60.         }  
61.   //---OrderItem object  
62.   public sealed class OrderItem 
63.     { 
64.         public int OrderId { get; set; } 
65.         public int CustomerId { get; set; } 
66.         public DateTime OrderedAt { get; set; } 
67.     } 
68.     //---OrderDetail object 
69.     public sealed class OrderDetail 
70.     { 
71.         public int OrderId { get; set; } 
72.         public int LineNumber { get; set; } 
73.         public int ProductId { get; set; } 
74.         public int Quantity { get; set; } 
75.         public decimal Price { get; set; } 
76.     }    
77.   //---OrderItemCollection 
78.     public sealed class OrderItemCollection: List<OrderItem>, IEnumerable<SqlDataRecord> 
79.     { 
80.         #region IEnumerable<SqlDataRecord> Members 
81.         public new IEnumerator<SqlDataRecord> GetEnumerator() 
82.         { 
83.             SqlDataRecord record = new SqlDataRecord( 
84.                                     new SqlMetaData("OrderId", SqlDbType.Int), 
85.                                     new SqlMetaData("CustomerId", SqlDbType.Int), 
86.                                     new SqlMetaData("OrderedAt", SqlDbType.DateTime)); 
87.             foreach(OrderItem item in this.ToArray()) 
88.             { 
89.                 record.SetInt32(0, item.OrderId); 
90.                 record.SetInt32(1, item.CustomerId); 
91.                 record.SetDateTime(2, item.OrderedAt); 
92.                 yield return record; 
93.             }                      
94.         } 
95.         #endregion 
96.     } 
97.     //---OrderDetailCollection 
98.     public sealed class OrderDetailCollection: List<OrderDetail>, IEnumerable<SqlDataRecord> 
99.     { 
100.         #region IEnumerable<SqlDataRecord> Members 
101.         public new IEnumerator<SqlDataRecord> GetEnumerator() 
102.         { 
103.             SqlDataRecord record = new SqlDataRecord( 
104.                                     new SqlMetaData("OrderId", SqlDbType.Int), 
105.                                     new SqlMetaData("LineNumber", SqlDbType.Int), 
106.                                     new SqlMetaData("ProductId", SqlDbType.Int), 
107.                                     new SqlMetaData("Quantity", SqlDbType.Int), 
108.                                     new SqlMetaData("Price", SqlDbType.Money)); 
109.             foreach (OrderDetail detail in this.ToArray()) 
110.             { 
111.                 record.SetInt32(0, detail.OrderId); 
112.                 record.SetInt32(1, detail.LineNumber); 
113.                 record.SetInt32(2, detail.ProductId); 
114.                 record.SetInt32(3, detail.Quantity); 
115.                 record.SetDecimal(4, detail.Price); 
116.                 yield return record; 
117.             } 
118.         } 
119.         #endregion 
120.     } 

三，其他

  通过Table-Values三部分的内容描述和实例，相信大家对TVP的应用有了一定的了解。 
在编程中也许有人还是习惯在插入多项或Select多行数据时，使用笨拙的逗号分隔列表或XML， 
虽其能够胜任，但不是以习惯的对象方式存在，而且存取速度也很慢。 
所以，我建议大家在使用IN语句等需要发送多行数据的编程时，多考虑使用TVP的方式，会取满意的效果。 
关于这方面的内容，还需要大家不断的交流和探讨。

本文转自 powertoolsteam 51CTO博客，原文链接：http://blog.51cto.com/powertoolsteam/424193，如需转载请自行联系原作者