通过c#或SQL获取存储过程参数?

I was hoping to find an easy way to get a parameter list of a stored procedures parameters. If the procedure has 3 paramaters, I want a list like this:

我希望找到一种简单的方法来获取存储过程参数的参数列表。如果程序有3个参数，我想要一个这样的列表:

param1
param2
param3

param1 param2 param3

It would be best to be able to do this in C# Code, but SQL would suffice as well. Ideas?

最好能够在c#代码中这样做，但是SQL也足够了。想法吗?

4 个解决方案

#1

select * from information_schema.parameters
where specific_name='your_procedure_name'

#2

For SQL Server this should work.

对于SQL Server来说，这应该是可行的。

private void ListParms()
{
    SqlConnection conn = new SqlConnection("my sql connection string");
    SqlCommand cmd = new SqlCommand("proc name", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    conn.Open();
    SqlCommandBuilder.DeriveParameters(cmd);
    foreach (SqlParameter p in cmd.Parameters)
    {
       Console.WriteLine(p.ParameterName);
    }
}

#3

If you're familiar with Enterprise Library, there's a good method which allows to DiscoverParameters(), using the Data Access Application Block.

如果您熟悉企业库，可以使用数据访问应用程序块发现parameters()。

DbCommand command = new DbCommand();
command.CommandText = @"myStoredProc";
command.CommandType = CommandType.StoredProcedure;

Database database = new SqlDatabase(myConnectionString);
database.DiscoverParameters(command);
// ...

Some links that might help:

一些可能有帮助的链接:

DiscoverParameters Method;
DiscoverParameters方法;
Microsoft.Practices.EnterpriseLibrary.Data Namespace.
Microsoft.Practices.EnterpriseLibrary。数据名称空间。

The above links refers to EntLib 3.1. Depending on the .NET Framework version you're using, you might also consider downloading the correct EntLib version for you following this link.

以上链接引用了EntLib 3.1。根据您正在使用的. net框架版本，您还可以考虑根据这个链接下载正确的EntLib版本。

#4

You can do this without ever touching SqlConnection, which I find is a bonus.

您可以在不接触SqlConnection的情况下这样做，我发现这是一个额外的好处。

This uses the SqlServer.Management.Smo namespace, so you need a reference to Microsoft.SqlServer.ConnectionInfo, Microsoft.SqlServer.Management.Sdk, and Microsoft.SqlServer.Smo in your project.

这个使用SqlServer.Management。Smo名称空间，因此您需要引用Microsoft.SqlServer。ConnectionInfo Microsoft.SqlServer.Management。Sdk,Microsoft.SqlServer。Smo中您的项目。

Then use the following code:

然后使用以下代码:

Server srv = new Server("serverNameHere");
srv.ConnectionContext.AutoDisconnectMode = AutoDisconnectMode.NoAutoDisconnect;
srv.ConnectionContext.LoginSecure = false; //if using username/password
srv.ConnectionContext.Login = "username";
srv.ConnectionContext.Password = "password";
srv.ConnectionContext.Connect();

Database db = srv.Databases["databaseNameHere"];

foreach(StoredProcedure sp in db.StoredProcedures)
{
    foreach(var param in sp.Parameters)
    {
        string paramName = param.Name;
        var dataType = param.DataType;
        object defaultValue = param.DefaultValue;
    }
}

#1

select * from information_schema.parameters
where specific_name='your_procedure_name'

#2