Create and use table value parameters 2 in the T-SQL

I. Review
The previous section describes how to create and use TVP in a T-SQL and create the following objects through a 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
2. Use the DataTable object in ADO. NET and pass it as a parameter to the stored procedure
I believe you are very familiar with the use of DataTable. Here we mainly use Sample.
* ** This Sample data still uses the existing objects of the previous part to complete the data transmission operation of the DataTable.
It consists of two parts:
1) Data of DataTable is transmitted to Stored Procedure
2) The TVP data of DataTable can be used to read and write other Entity Data (DataReader ).
The specific example is as follows:

Private void btnDataTable_Click (object sender, EventArgs e) </p> <p >{</p> <p> // --- OrderItem </p> <p> DataTable dtOrderItem = new DataTable (); </p> <p> dtOrderItem. columns. add ("OrderId", typeof (int); </p> <p> dtOrderItem. columns. add ("CustomerId", typeof (int); </p> <p> dtOrderItem. columns. add ("OrderAt", typeof (DateTime); </p> <p> // --- OrderDetail </p> <p> DataTable dtOrderDetail = new DataTable (); </p> <P> dtOrderDetail. columns. add ("OrderId", typeof (int); </p> <p> dtOrderDetail. columns. add ("LineNumber", typeof (int); </p> <p> dtOrderDetail. columns. add ("ProductId", typeof (int); </p> <p> dtOrderDetail. columns. add ("Quantity", typeof (int); </p> <p> dtOrderDetail. columns. add ("Price", typeof (decimal); </p> <p> // --- Add data </p> <p> dtOrderItem. rows. add (new object [] {1001,500 1, DateTime. now}); </p> <p> DtOrderDetail. rows. add (new object [] {1001, 1,101, 15, 12.28}); </p> <p> dtOrderDetail. rows. add (new object [] {1001, 2,102, 20,102.12}); </p> <p> dtOrderDetail. rows. add (new object [] {1001, 3,103, 23, 0.85}); </p> <p> dtOrderItem. rows. add (new object [] {2001,600 1, DateTime. now}); </p> <p> dtOrderDetail. rows. add (new object [] {2001, 1,201, 5, 79.59}); </p> <p> dtOrderDetail. rows. add (new obj Ect [] {2001, 2,202,200, 3.25 }); </p> <p> // ---- </p> <p> using (SqlConnection conn = new SqlConnection ("Data Source =; Initial Catalog = AdventureWorks; UserID = sa; password = ") </p> <p >{</p> <p> conn. open (); </p> <p> // --- Passing a Table-Valued Parameter to a Stored Pcocedure </p> <p> using (SqlCommand cmd = new SqlCommand ("dbo. orderItem $ Insert ", conn) </p> <p >{</p> <p> cmd. commandType = CommandType. st OredProcedure; </p> <p> // Note: The two parameter Names below must be the same as the SP parameter names. </P> <p> SqlParameter tvpOrderItem = cmd. parameters. addWithValue ("@ OrderHeaders", dtOrderItem); </p> <p> SqlParameter tvpOrderDetail = cmd. parameters. addWithValue ("@ OrderDetails", dtOrderDetail); </p> <p> tvpOrderItem. sqlDbType = SqlDbType. structured; </p> <p> tvpOrderDetail. sqlDbType = SqlDbType. structured; </p> <p> cmd. executeNonQuery (); </p> <p >}</p> <p> // --- Streadming rows with a dataReader </p> <p> string SQL = @ "select tvp1.OrderId, tvp1.CustomerId, tvp2.LineNumber, tvp2.ProductId </p> <p> from dbo. orderItem as tvp1 inner join @ tvpDetail as tvp2 ON tvp1.OrderId = tvp2.OrderId </p> <p> order by tvp2.OrderId, tvp2.LineNumber ;"; </p> <p> SqlCommand readCommand = new SqlCommand (SQL, conn); </p> <p> readCommand. commandType = CommandType. text; </p> <p> SqlParameter tvpDetail = readCommand. parameters. addWithValue ("@ tvpDetail", dtOrderDetail); </p> <p> tvpDetail. sqlDbType = SqlDbType. structured; </p> <p> tvpDetail. typeName = "dbo. orderDetail $ Udt "; </p> <p> SqlDataReader reader = readCommand. executeReader (); </p> <p> while (reader. read () </p> <p >{</p> <p> Console. writeLine (string. format ("OrderId = {0}, CustomrId = {1}, LineNumber = {2}, ProductId = {3}", </p> <p> reader. getInt32 (0), reader. getInt32 (1), reader. getInt32 (2), reader. getInt32 (3); </p> <p >}</p> <p> reader. close (); </p> <p> // read the result as follows: </p> <p> // --------------------- </p> <p> // OrderId = 1001, customrId = 5001, LineNumber = 1, ProductId = 101 </p> <p> // OrderId = 1001, CustomrId = 5001, LineNumber = 2, productId = 102 </p> <p> // OrderId = 1001, CustomrId = 5001, LineNumber = 3, ProductId = 103 </p> <p> // OrderId = 2001, customrId = 6001, LineNumber = 1, ProductId = 201 </p> <p> // OrderId = 2001, CustomrId = 6001, LineNumber = 2, productId = 202 </p> <p> // --------------------- </p> <p> conn. close (); </p> <p >}< br/>