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

2019独角兽企业重金招聘Python工程师标准>>>

一，回顾

上一部分讲述了“在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)。
  具体实例如下：

 

    //---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

转载于:https://my.oschina.net/powertoolsteam/blog/278114