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

一，回顾
        上一部分讲述了“在T-SQL中创建和使用TVP”，通过T-SQL建立如下的对象：
        1）Tables
          dbo.OrderItem
          dbo.OrderDetail
        2) User-Defined Table Types
            dbo.OrderDetail$Udt
            dbo.OrderItem$Udt
        3) Stored Procedures
            dbo.OrderItem$Insert
二，在ADO.NET中利用DataTable对象，将其作为参数传给存贮过程
  相信大家对DataTable的利用已经非常熟悉了，在此主要以Sample为主。
  ***这次的Sample数据，还是利用上一部分已有的对象，完成DataTable传输数据的操作。
  主要包含两部分的内容：
  1）DataTable的数据传输给Stored Procedure
  2）利用DataTable的TVP数据，可以参与别的实体数据读写(DataReader)。
  具体实例如下：

1. private void btnDataTable_Click(object sender, EventArgs e)
   
2.       {
   
3.           //---OrderItem
   
4.           DataTable dtOrderItem = new DataTable();
   
5.           dtOrderItem.Columns.Add("OrderId", typeof(int));
   
6.           dtOrderItem.Columns.Add("CustomerId", typeof(int));
   
7.           dtOrderItem.Columns.Add("OrderAt", typeof(DateTime));
   
8.           //---OrderDetail
   
9.           DataTable dtOrderDetail = new DataTable();
   
10.           dtOrderDetail.Columns.Add("OrderId", typeof(int));
    
11.           dtOrderDetail.Columns.Add("LineNumber", typeof(int));
    
12.           dtOrderDetail.Columns.Add("ProductId", typeof(int));
    
13.           dtOrderDetail.Columns.Add("Quantity", typeof(int));
    
14.           dtOrderDetail.Columns.Add("Price", typeof(decimal));
    
15.           //---Add data
    
16.           dtOrderItem.Rows.Add(new object[] { 1001, 5001, DateTime.Now });
    
17.           dtOrderDetail.Rows.Add(new object[] { 1001, 1, 101, 15, 12.28 });
    
18.           dtOrderDetail.Rows.Add(new object[] { 1001, 2, 102, 20, 102.12 });
    
19.           dtOrderDetail.Rows.Add(new object[] { 1001, 3, 103, 23, 0.85 });
    
20.           dtOrderItem.Rows.Add(new object[] { 2001, 6001, DateTime.Now });
    
21.           dtOrderDetail.Rows.Add(new object[] { 2001, 1, 201, 5, 79.59 });
    
22.           dtOrderDetail.Rows.Add(new object[] { 2001, 2, 202, 200, 3.25 });
    
23.           //----
    
24.           using (SqlConnection conn = new SqlConnection("Data Source=;Initial Catalog=AdventureWorks;UserID=sa;Password="))
    
25.           {
    
26.               conn.Open();
    
27.               //---Passing a Table-Valued Parameter to a Stored Pcocedure
    
28.               using (SqlCommand cmd = new SqlCommand("dbo.OrderItem$Insert", conn))
    
29.               {
    
30.                   cmd.CommandType = CommandType.StoredProcedure;
    
31.                   //---注意：下边的两个参数名，要和SP的参数名一致。
    
32.                   SqlParameter tvpOrderItem = cmd.Parameters.AddWithValue("@OrderHeaders", dtOrderItem);
    
33.                   SqlParameter tvpOrderDetail = cmd.Parameters.AddWithValue("@OrderDetails", dtOrderDetail);
    
34.                   tvpOrderItem.SqlDbType = SqlDbType.Structured;
    
35.                   tvpOrderDetail.SqlDbType = SqlDbType.Structured;
    
36.                   cmd.ExecuteNonQuery();
    
37.               }
    
38.               //---Streadming rows with a dataReader
    
39.               string sql= @"select tvp1.OrderId, tvp1.CustomerId, tvp2.LineNumber, tvp2.ProductId
    
40.                           from    dbo.OrderItem as tvp1 INNER JOIN @tvpDetail as tvp2 ON tvp1.OrderId=tvp2.OrderId
    
41.                           order by tvp2.OrderId, tvp2.LineNumber;";
    
42.               SqlCommand readCommand = new SqlCommand(sql, conn);
    
43.               readCommand.CommandType = CommandType.Text;
    
44.               SqlParameter tvpDetail = readCommand.Parameters.AddWithValue("@tvpDetail", dtOrderDetail);
    
45.               tvpDetail.SqlDbType = SqlDbType.Structured;
    
46.               tvpDetail.TypeName = "dbo.OrderDetail$Udt";
    
47.               SqlDataReader reader = readCommand.ExecuteReader();
    
48.               while (reader.Read())
    
49.               {
    
50.                   Console.WriteLine(string.Format("OrderId={0}, CustomrId={1}, LineNumber={2}, ProductId={3}",
    
51.                                       reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2), reader.GetInt32(3)));
    
52.               }
    
53.               reader.Close();
    
54.               //读出结果如下：
    
55.               //-----------------------
    
56.               //OrderId=1001, CustomrId=5001, LineNumber=1, ProductId=101
    
57.               //OrderId=1001, CustomrId=5001, LineNumber=2, ProductId=102
    
58.               //OrderId=1001, CustomrId=5001, LineNumber=3, ProductId=103
    
59.               //OrderId=2001, CustomrId=6001, LineNumber=1, ProductId=201
    
60.               //OrderId=2001, CustomrId=6001, LineNumber=2, ProductId=202
    
61.               //-----------------------
    
62.               conn.Close();
    
63.           }
    
64.       }

复制代码

三，参照文献  
  MSDN：Table-Valued Parameters in SQL Server 2008 (ADO.NET)  
  URL: ms-help://MS.MSDNQTR.v90.en/wd_adonet/html/370c16d5-db7b-43e3-945b-ccaab35b739b.htm