Передать табличный параметр с помощью ADO.Net
Как передать табличный параметр в хранимую процедуру с помощью ADO.Net?
4 ответа
Решение
Создать тип в SQL Server:
CREATE TYPE [dbo].[MyDataType] As Table ( ID INT, Name NVARCHAR(50) )
Процедура создания:
CREATE PROCEDURE [dbo].[MyProcedure] ( @myData As [dbo].[MyDataType] Readonly ) AS BEGIN SELECT * FROM @myData END
Создать 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);
Создать параметр 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( );
}