使用 ADO.NET 传递表值参数

Question

如何使用 ADO.NET 将表值参数传递给存储过程？

- NIck

---

请参见 https://dev59.com/um035IYBdhLWcg3wJcjT - Rory

Answer 1

1. 在SQL Server中创建数据类型:

   CREATE TYPE [dbo].[MyDataType] As Table
   (
       ID INT,
       Name NVARCHAR(50)
   )
   

2. 创建过程:

   CREATE PROCEDURE [dbo].[MyProcedure]
   (
       @myData As [dbo].[MyDataType] Readonly
   )
   AS
   
   BEGIN
       SELECT * FROM @myData
   END
   

3. 在C#中创建DataTable:

   DataTable myDataTable = new DataTable("MyDataType");
   myDataTable.Columns.Add("Name", typeof(string));
   myDataTable.Columns.Add("Id", typeof(Int32));
   myDataTable.Rows.Add("XYZ", 1);
   myDataTable.Rows.Add("ABC", 2);
   

4. 创建SQL参数:

   SqlParameter parameter = new SqlParameter();
   parameter.ParameterName = "@myData";
   parameter.SqlDbType = System.Data.SqlDbType.Structured;
   parameter.Value = myDataTable;
   command.Parameters.Add(parameter); 
   

- Naresh Goradara

---

这仅适用于 Sql Server 2008。 - Ashish Gupta

我的意思是这只适用于 Sql server 2008 及以上版本，因此它也可以在 2012 年运行。 - Ashish Gupta

请注意，在第三步创建的DataTable中，列的顺序必须与第一步定义的Table Type中的列顺序相匹配。你只是传递了一个数据网格，然后SQL Server按位置读取它。实际上，你在.NET中给它的列名只是为了你自己的方便而使用的字符串 - 它可能与SQL不同，因为SQL不会使用它。 - KyleMit

@KyleMit 您是正确的 - 列顺序必须匹配。链接到相关的SO问题：https://dev59.com/9Jzha4cB1Zd3GeqPGohf - callisto

如果您想在单个SQL查询中使用TVP而不是存储过程，该怎么办？在这种情况下，您是否需要进行TYPE声明？ - Dai

Answer 2

我尝试了这个代码，但是出现了异常：

表格类型参数 '@MyDataType' 必须具有有效的类型名称。

我需要设置 SqlParameter 的 "TypeName" 属性：

parameter.TypeName = "MyDataType";

- Matthew M.

---

将数据库架构作为表名的一部分添加即可：DataTable myDataTable = new DataTable("dbo.MyDataType"); - noontz

Answer 3

对于多语言用户，可能有点晚了：

a）在tsql的其他地方

--- create a vector data type
CREATE TYPE [dbo].[ItemList] AS TABLE([Item] [varchar](255) NULL)

b)

Dim Invoices As New DataTable("dbo.ItemList") 'table name is irrelevant
Invoices.Columns.Add("Invoice", GetType(String))
...
        With .SqlCommand.Parameters
            .Clear()
            .Add(New Data.SqlClient.SqlParameter() With {
                        .SqlDbType = Data.SqlDbType.Structured,
                        .Direction = Data.ParameterDirection.Input,
                        .ParameterName = "@Invoices",
                        .TypeName = "dbo.ItemList",
                        .Value = Invoices})
        End With
...
   ' using  store procedure
   .CommandText = "SELECT * FROM dbo.rpt(@invoices) "
   ' or direct reference is a select
   .CommandText = "SELECT * FROM dbo.invoicedata" +
        "where ((select count(*) from @invoices) = 0 or "+
             "InvoiceNumber in (select distinct * from @Invoices)) 

- fcm

Answer 4

这个问题是.NET代码如何将表值参数传递给存储过程的重复。请参考那个问题，以获取使用DataTable或IEnumerable<SqlDataRecord>的示例。

- Ryan Prechel

Answer 5

你可以在前面加上 Exec

using( SqlConnection con = new SqlConnection( "Server=.;database=employee;user=sa;password=12345" ) )
    {
        SqlCommand cmd = new SqlCommand( " exec ('drop table '+@tab)" , con );
        cmd.Parameters.AddWithValue( "@tab" ,"Employee" );
        con.Open( );
        cmd.ExecuteNonQuery( );
    }

- Uthaiah