Table-values parameter Part 2 of 3: 利用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)。

  具体实例如下：

      private void btnDataTable_Click(object sender, EventArgs e)
   {
      //---OrderItem
      DataTable dtOrderItem = new DataTable();
      dtOrderItem.Columns.Add("OrderId", typeof(int));
      dtOrderItem.Columns.Add("CustomerId", typeof(int));
      dtOrderItem.Columns.Add("OrderAt", typeof(DateTime));

      //---OrderDetail
      DataTable dtOrderDetail = new DataTable();
      dtOrderDetail.Columns.Add("OrderId", typeof(int));
      dtOrderDetail.Columns.Add("LineNumber", typeof(int));
      dtOrderDetail.Columns.Add(&quot

roductId", typeof(int));
dtOrderDetail.Columns.Add("Quantity", typeof(int));
dtOrderDetail.Columns.Add(&quot

rice", typeof(decimal));

      //---Add data
      dtOrderItem.Rows.Add(new object[] { 1001, 5001, DateTime.Now });
      dtOrderDetail.Rows.Add(new object[] { 1001, 1, 101, 15, 12.28 });
      dtOrderDetail.Rows.Add(new object[] { 1001, 2, 102, 20, 102.12 });
      dtOrderDetail.Rows.Add(new object[] { 1001, 3, 103, 23, 0.85 });

      dtOrderItem.Rows.Add(new object[] { 2001, 6001, DateTime.Now });
      dtOrderDetail.Rows.Add(new object[] { 2001, 1, 201, 5, 79.59 });
      dtOrderDetail.Rows.Add(new object[] { 2001, 2, 202, 200, 3.25 });

      //----
      using (SqlConnection conn = new SqlConnection("Data Source=;Initial Catalog=AdventureWorks;UserID=sa

assword="))
      {
            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", dtOrderItem);
               SqlParameter tvpOrderDetail = cmd.Parameters.AddWithValue("@OrderDetails", dtOrderDetail);

               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", dtOrderDetail);
            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();

            //读出结果如下：
            //-----------------------
            //OrderId=1001, CustomrId=5001, LineNumber=1, ProductId=101
            //OrderId=1001, CustomrId=5001, LineNumber=2, ProductId=102
            //OrderId=1001, CustomrId=5001, LineNumber=3, ProductId=103
            //OrderId=2001, CustomrId=6001, LineNumber=1, ProductId=201
            //OrderId=2001, CustomrId=6001, LineNumber=2, ProductId=202
            //-----------------------

            conn.Close();
      }
   }

三，参照文献
  希望大家详细阅读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

		自动登录	找回密码
密码			立即注册

Table-values parameter Part 2 of 3: 利用DataTable将其作为参数传给SP

0 个回复

活字格认证