Передать табличный параметр с помощью ADO.Net

Как передать табличный параметр в хранимую процедуру с помощью ADO.Net?

4 ответа

Решение
  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. Создать DataTable в 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); 
    

Я попробовал это и получил исключение:

Параметр типа таблицы '@MyDataType' должен иметь правильное имя типа.

Я должен был установить свойство "TypeName" SqlParameter:

parameter.TypeName = "MyDataType";

Этот вопрос является дубликатом Как передать параметры табличных значений в хранимую процедуру из кода.net. Пожалуйста, смотрите этот вопрос для примера, иллюстрирующего использование любого DataTable или IEnumerable<SqlDataRecord>,

Для многоязычных, немного опоздавших на шоу:

а) в другом месте на tsql

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

б)

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

Вы можете префикс с 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( );
    }
Другие вопросы по тегам