如何在.NET中运行时确定SQL Server存储过程参数？

Is there any way for an application to determine the parameters and paramter types of a stored procedure at run time?

有没有办法让应用程序在运行时确定存储过程的参数和参数类型?

3 个解决方案

#1

ADO.Net has this functionality. You'll want to use

ADO.Net具有此功能。你会想要使用

SqlCommandBuilder.DeriveParameters(mySqlCommand)

This will populate your SqlCommand object, "mySqlCommand", with the name and type of the parameters. However, it does require an extra call to the database.

这将使用参数的名称和类型填充您的SqlCommand对象“mySqlCommand”。但是,它确实需要额外调用数据库。

There are a few walkthroughs on the web if you google around for them. 4Guys has one here.

如果你在谷歌周围搜索,网上有一些演练。 4Guys在这里有一个。

#2

no, but you can get it from information_schema.parameters

不,但你可以从information_schema.parameters获得它

example procedure

create procedure prtest
@id int,
@name varchar(200),
@value decimal(20,10)
as
select 'bla'

go

now run this query

现在运行此查询

select parameter_name,data_type,* from information_schema.parameters
where specific_name = 'prtest'
order by ordinal_position

output

@id int
@name   varchar
@value  decimal

you still need to get the sizes from CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION, NUMERIC_SCALE

您仍然需要从CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE获取尺寸

#3

Yes, you can by using the DeriveParameters method of the SqlCommandBuilder class...

是的,您可以使用SqlCommandBuilder类的DeriveParameters方法...

using (var conn = new SqlConnection(myConnectionString))
using (var cmd = new SqlCommand("dbo.MyStoredProc", conn))
{
    conn.Open();
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    SqlCommandBuilder.DeriveParameters(cmd);

    foreach (SqlParameter item in cmd.Parameters)
    {
        Console.WriteLine(item.ParameterName);
    }
}

#1