C# 使用 ADO.NET 传递表值参数

Pass table valued parameter using ADO.NET

提问by NIck

How to pass table valued parameter to stored procedure using ADO.NET?

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

采纳答案by Naresh Goradara

1. Create type in SQL Server:

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

2. Create Procedure:

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

3. Create DataTable in C#:

   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. Create SQL Parameter:

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

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 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); 
   

回答by Ryan Prechel

This question is a duplicate of How to pass table value parameters to stored procedure from .net code. Please see that question for an example illustrating the use of either a DataTableor an IEnumerable<SqlDataRecord>.

这个问题是How to pass table value parameters to stored procedure from .net code的副本。请参阅该问题的示例，说明如何使用 aDataTable或 an IEnumerable<SqlDataRecord>。

回答by Matt Martin

I tried this and received the exception:

我试过这个并收到异常：

The table type parameter '@MyDataType' must have a valid type name.

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

I had to set the "TypeName" property of the SqlParameter:

我必须设置 SqlParameter 的“TypeName”属性：

parameter.TypeName = "MyDataType";

回答by Uthaiah

You can prefix with Exec

您可以使用 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( );
    }

回答by fcm

For multilinguals, a little late to the show:

对于会说多种语言的人来说，节目有点晚了：

a) elsewhere on tsql

a) 在 tsql 的其他地方

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

b)

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