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)。
具体实例如下：

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

    //---Main Sub
      private void btnCollection_Click(object sender, EventArgs e)
        {
            //------利用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);
            //------利用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;
                    //---注意：下边的两个参数名，要和SP的参数名一致。
                    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
    }

三，其他

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

四，参考文档

参看URL:
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/5e95a382-1e01-4c74-81f5-055612c2ad99.htm
ms-help://MS.MSDNQTR.v90.en/wd_adonet/html/370c16d5-db7b-43e3-945b-ccaab35b739b.htm