如何使用 ADO.NET 将表值参数传递给存储过程?
在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
在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);
创建SQL参数:
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@myData";
parameter.SqlDbType = System.Data.SqlDbType.Structured;
parameter.Value = myDataTable;
command.Parameters.Add(parameter);
TYPE
声明? - Dai我尝试了这个代码,但是出现了异常:
表格类型参数 '@MyDataType' 必须具有有效的类型名称。
我需要设置 SqlParameter 的 "TypeName" 属性:
parameter.TypeName = "MyDataType";
对于多语言用户,可能有点晚了:
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))
你可以在前面加上 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( );
}